Thursday, April 13, 2023

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

Friday, February 24, 2023

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.

Sunday, February 19, 2023

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

Saturday, February 18, 2023

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.

Tuesday, February 14, 2023

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

Wednesday, January 25, 2023

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"/>

Tuesday, January 24, 2023

How to avoid error Perl lib version (5.28.1) doesn't match executable version (5.36.0)

When trying to use clone.pl to clone the latest version of the Oracle 19c database, the following error stopped me from proceeding:
/sw/oracle/product/19.18/clone/bin/clone.pl: 
Perl lib version (5.28.1) doesn't match executable '/sw/oracle/product/19.18/perl/bin/perl' 
version (5.36.0) at /sw/oracle/product/19.17/perl/lib/5.28.1/x86_64-linux-thread-multi/Config.pm 
line 62.
Turns out that that the error occured at the following point in my code:
export NEW_ORACLE_HOME=$ORACLE_BASE/product/19.18
export PRE_ORACLE_HOME=$ORACLE_BASE/product/19.17
export NEW_ORACLE_VERSION=19C_230117
su - oracle <<!
cd $NEW_ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME=$NEW_ORACLE_HOME ORACLE_HOME_NAME=$NEW_ORACLE_VERSION ORACLE_BASE=$ORACLE_BASE OSDBA_GROUP=dba
!
Cause:
The .bash_profile of the oracle user contained a variable commonly used in EBS database environments:
export PERL5LIB=/sw/oracle/product/19.17/perl/lib/5.28.1:/sw/oracle/product/19.17/perl/lib/site_perl/5.28.1:/sw/oracle/product/19.17/appsutil/perl
and when trying to run the clone.pl program from the new ORACLE_HOME, perl was unable to find the matching libraries.

Solution:
unset the PERL5LIB before running the clone.pl program:
export NEW_ORACLE_HOME=$ORACLE_BASE/product/19.18
export PRE_ORACLE_HOME=$ORACLE_BASE/product/19.17
export NEW_ORACLE_VERSION=19C_230117
su - oracle <<!
cd $NEW_ORACLE_HOME/clone/bin
unset PERL5LIB
perl clone.pl ORACLE_HOME=$NEW_ORACLE_HOME ORACLE_HOME_NAME=$NEW_ORACLE_VERSION ORACLE_BASE=$ORACLE_BASE OSDBA_GROUP=dba
!