How to deinstall Oracle 19c database software

If you are deinstalling Oracle on a Linux RHEL 8, set the variable CV_ASSUME_DISTID=OL7 to avoid the error outlined in Doc ID 2738856.1:

export CV_ASSUME_DISTID=OL7

Deinstall the binaries:

$ORACLE_HOME/deinstall/deinstall

At the end of the deinstallation, a summary is presented:
####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/oracle/product/19c' from the central inventory on the local node.
Failed to delete directory '/u01/oracle/product/19c' on the local node due to error : Either user has no permission to delete or file is in use.
Review the permissions and manually delete '/u01/oracle/product/19c' on local node.
Successfully deleted directory '/home/oracle/oraInventory' on the local node.
Oracle Universal Installer cleanup was successful.

Workaround for message "Make sure that xdpyinfo exist under PATH variable" when launching OUI

Error:

oracle@myserver :[cdb]# ./runInstaller -createGoldImage -destinationLocation /stage/oracle/images
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

Cause: missing package:

[root@myserver ~]# yum list xorg-x11-utils
Updating Subscription Management repositories.
EPEL RHEL8 x86_64                                                                                                                        97 kB/s | 2.3 kB     00:00
Puppet7 RHEL8 x86_64                                                                                                                     72 kB/s | 1.7 kB     00:00
3rdparty RHEL8                                                                                                                           83 kB/s | 2.0 kB     00:00
Available Packages
xorg-x11-utils.x86_64                                                    7.5-28.el8                                                     rhel-8-for-x86_64-appstream-rpms

Solution: install package:

[root@myserver ~]# yum install xorg-x11-utils
Updating Subscription Management repositories.
EPEL RHEL8 x86_64                                                                                                                        89 kB/s | 2.3 kB     00:00
Puppet7 RHEL8 x86_64                                                                                                                     73 kB/s | 1.7 kB     00:00
3rdparty RHEL8                                                                                                                           83 kB/s | 2.0 kB     00:00
Dependencies resolved.
========================================================================================================================================================================
 Package                               Architecture                  Version                              Repository                                               Size
========================================================================================================================================================================
Installing:
 xorg-x11-utils                        x86_64                        7.5-28.el8                           rhel-8-for-x86_64-appstream-rpms                        135 k
Installing dependencies:
 libXcomposite                         x86_64                        0.4.4-14.el8                         rhel-8-for-x86_64-appstream-rpms                         29 k
 libXxf86dga                           x86_64                        1.1.5-1.el8                          rhel-8-for-x86_64-appstream-rpms                         26 k
 libdmx                                x86_64                        1.1.4-3.el8                          rhel-8-for-x86_64-appstream-rpms                         22 k

Transaction Summary
========================================================================================================================================================================
Install  4 Packages

Total download size: 212 k
Installed size: 418 k
Is this ok [y/N]: y
Downloading Packages:
(1/4): libXcomposite-0.4.4-14.el8.x86_64.rpm                                                                                            493 kB/s |  29 kB     00:00
(2/4): libdmx-1.1.4-3.el8.x86_64.rpm                                                                                                    302 kB/s |  22 kB     00:00
(3/4): xorg-x11-utils-7.5-28.el8.x86_64.rpm                                                                                             1.7 MB/s | 135 kB     00:00
(4/4): libXxf86dga-1.1.5-1.el8.x86_64.rpm                                                                                               709 kB/s |  26 kB     00:00
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                   2.1 MB/s | 212 kB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                1/1
  Installing       : libXxf86dga-1.1.5-1.el8.x86_64                                                                                                                 1/4
  Installing       : libXcomposite-0.4.4-14.el8.x86_64                                                                                                              2/4
  Installing       : libdmx-1.1.4-3.el8.x86_64                                                                                                                      3/4
  Installing       : xorg-x11-utils-7.5-28.el8.x86_64                                                                                                               4/4
  Running scriptlet: xorg-x11-utils-7.5-28.el8.x86_64                                                                                                               4/4
  Verifying        : xorg-x11-utils-7.5-28.el8.x86_64                                                                                                               1/4
  Verifying        : libdmx-1.1.4-3.el8.x86_64                                                                                                                      2/4
  Verifying        : libXcomposite-0.4.4-14.el8.x86_64                                                                                                              3/4
  Verifying        : libXxf86dga-1.1.5-1.el8.x86_64                                                                                                                 4/4
Installed products updated.

Installed:
  libXcomposite-0.4.4-14.el8.x86_64           libXxf86dga-1.1.5-1.el8.x86_64           libdmx-1.1.4-3.el8.x86_64           xorg-x11-utils-7.5-28.el8.x86_64

Complete!

Relaunch OUI:

oracle@myserver :[cdb]# ./runInstaller -createGoldImage -destinationLocation /stage/oracle/images

Getting ORA-01722 when running adstats.sql after an EBS database upgrade to 19c

An old error from 2015 surfaced today, when we were trying to upgrade a 12.1 EBS database to 19c.

The problem occured during the running of adstats.sql, which should be executed at the end of the installation procedure, right before the database is converted to a PDB in a multitenant architecture:

sqlplus / as sysdba @adstats.sql apps
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 23 16:18:26 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Koblet til:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
Tilkoblet.
--------------------------------------------------
--- adstats.sql started at 2023-02-23 16:18:26 ---
Checking for the DB version and collecting statistics ...
declare
*
Error on line 1:
ORA-01722: invalid number
ORA-06512: on line 9

The solution is the same as in "ORA-01722 when running ausy1120.sql during preparation of new target database for EBS":

Modify the adstats.sql script slightly and correct a typo. In an editor, change the statement

select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;

to

select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;

Then rerun the script.

How to identified active files/executables when using opatch to deinstall software

During a opatch rollback operation, we saw the following error message in the opatch logfile:

[Feb 19, 2023 2:29:51 PM] [INFO]    Prerequisite check "CheckActiveFilesAndExecutables" failed.
                                    The details are:


                                    Following active files/executables/libs are used by ORACLE_HOME :/sw/oracle/product/19.18
                                    /sw/oracle/product/19.18/lib/libclntsh.so.19.1

Reason:
Some processes are still using the /sw/oracle/product/19.18/lib/libclntsh.so.19.1.
Use the fuser utility with verbose output to find the process:

fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac
                     oracle    595787 ....m ggsci

There were two open processes using the file libclntsh.so.19.1: 1. the Golden Gate Manager 2. a utility called prometheus_oracle_exporter

Solution:

log in as the Golden Gate software owner
ggsci --> info all --> list all processes
stop mgr !

One of two processes quit its handler on the file:

 fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac

For the prometheus agent, we simply kill the agent, and the output from fuser now reveals that no file handlers are open:

kill 48439
fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1

How to prevent sed to destroy your symlinks

Because of EBS, we have a subdirectory in our $TNS_ADMIN which has the following name

PDB1_test1.oric.no

In other words, the files listener.ora, sqlnet.ora and tnsnames.ora are symlinks pointing to this subdirectory:

# ls -altr
lrwxrwxrwx  1 oracle dba   34 Feb 10 13:29 sqlnet.ora -> PDB1_test1.oric.no/sqlnet.ora
lrwxrwxrwx  1 oracle dba   36 Feb 10 13:29 tnsnames.ora -> PDB1_test1.oric.no/tnsnames.ora
lrwxrwxrwx  1 oracle dba   36 Feb 18 12:17 listener.ora -> PDB1_test1.oric.no/listener.ora
drwxr-xr-x  2 oracle dba 4096 Feb 18 12:18 PDB1_test1.oric.no
drwxr-xr-x  5 oracle dba 4096 Feb 18 12:19 .

If you are to exchange a string in these files, for example when switching to a new version of the Oracle software, make sure you use the directive --follow-symlinks to preserve your symlinks:

export PRE_ORACLE_VERSION=19.17
export NEW_ORACLE_VERSION=19.18
cd $TNS_ADMIN
sed --follow-symlinks -i "s/$PRE_ORACLE_VERSION/$NEW_ORACLE_VERSION/g" listener.ora

If you don't, the listener.ora will be placed directly in $TNS_ADMIN, and the symlinks will be dropped.

Error fetching the value from a shell script executed by root, when doing "su - oracle"

Problem:
Inside a new bash shell session, executed as user "oracle", like this:

su - oracle <<!

instructions here

!

I was not able to save the value of a string extracted from an xml file in a session variable.
I tried all possible variants (I thought), but was still unable to get my shell script to hold the value in a variable and use it locally within the oracle user session.

Solution:
Use backslash around the entire command, marked in yellow below:

su - oracle <<!
export CNTXT_FILE_PERL_VERSION=\`grep PERL5LIB \$CONTEXT_FILE | awk -F "site_perl/" '{print \$2}' | cut -f 1 -d":"\`
!

Credit goes to an experienced and clever colleague of mine, who shall remain nameless unless he actively wants his name listed on my blog ;-)

clone.pl is deprecated, substituted by runInstaller - how to use

1. create a "Golden Image" of the Oracle software home you want to clone

oracle@testserver1.oric.no:[testdb01]# /sw/oracle/product/19c_230117/runInstaller -silent -createGoldImage -destinationLocation /sw/oracle/product
Launching Oracle Database Setup Wizard...

Successfully Setup Software.
Gold Image location: /sw/oracle/product/db_home_2023-01-24_04-05-28PM.zip

Note that I use the -silent flag. Without it, I received the following two errors:

ERROR: Could not read any NLS message catalogue!

and

Can't connect to X11 window server using 'localhost:10.0' as the value of the DISPLAY variable.

2. Transfer the zip file to your destination server using scp

ssh testserver2.oric.no
su - oracle
cd /sw/oracle/product
scp testserver1:/sw/oracle/product/db_home_2023-01-24_04-05-28PM.zip .

3. Make a directory for the new ORACLE_HOME

mkdir 19c_230117

4. Unzip the image file

unzip -d 19c_230117 db_home_2023-01-24_04-05-28PM.zip

5. create a simple script for the installation process

vi exec_runInstaller.sh; chmod 755 exec_runInstaller.sh

Add the following to exec_runInstaller.sh:

#!/bin/bash
export GLOGFILE=/sw/oracle/product/exec_runInstaller.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "starting installer at " `date`
export CV_ASSUME_DISTID=OEL8.1
export NEW_ORACLE_HOME=/sw/oracle/product/19c_230117
cd $NEW_ORACLE_HOME
./runInstaller -silent -debug -force \
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0 \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=dba \
ORACLE_HOME=$NEW_ORACLE_HOME \
ORACLE_BASE=$ORACLE_BASE \
oracle.install.db.InstallEdition=EE \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
DECLINE_SECURITY_UPDATES=true

exit

Note that I enter the newly unpacked ORACLE_HOME to execute runInstaller.

6. execute the script

./exec_runInstaller.sh

7. Run root scripts

Upon completion, check the logfile /sw/oracle/product/install_sw_${TS}.log and you are reminded to run two root scripts:

You can find the log of this install session at:
 /home/oracle/oraInventory/logs/InstallActions2023-01-25_10-04-01AM/installActions2023-01-25_10-04-01AM.log

As a root user, execute the following script(s):
        1. /sw/oracle/product/19c_230117/root.sh

Execute /sw/oracle/product/19c_230117/root.sh on the following nodes:
[testserver02.oric.no]

8. Verify that the new ORACLE_HOME is registered in the inventory

cat  /home/oracle/oraInventory/ContentsXML/inventory.xml

Output (abbreviated):

<HOME NAME="OraDB19Home1" LOC="/sw/oracle/product/19c_230117" TYPE="O" IDX="3"/>