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 ;-)