How to perform out-of-place patching of Oracle Restart (SIHA) 19c

1. Create empty directory for future Oracle Restart Home and unpack base grid installation along with required OPatch into it.

# mkdir -p /u01/app/19.21/grid

# chown grid:oinstall /u01/app/19.21/grid

# su - grid

$ cd /u01/app/19.21/grid

$ unzip -q /u01/app/oracle/install/19/SOLARIS.SPARC64_193000_grid_home.zip -d .

$ unzip -q /u01/app/oracle/install/19/p6880880_210000_SOLARIS64.zip -d .

2. Run installation using 'software only' mode. It's possible to perform silent installation using response file from previous installation, in this case set oracle.install.option=HA_SWONLY:

$ ./gridSetup.sh -silent -responseFile /u01/app/19.21/grid/gi_install.rsp -applyRU /u01/app/oracle/install/19/1921/35742441/35642822

Follow all postinstall steps (root.sh)

3. Run roothas.sh with -prepatch option.

If it requires clsecho file, copy it from previous Oracle Restart installation and edit the ORACLE_HOME variable at the beginning of it.

# /u01/app/19.21/grid/crs/install/roothas.sh -verbose -prepatch

At this step the whole Oracle Restart stack is being stopped. 

4. Run roothas.sh with -postinstall option.

# /u01/app/19.21/grid/crs/install/roothas.sh -verbose -postpatch -dstcrshome /u01/app/19.21/grid

At this step the whole Oracle Restart stack is being started from the newest Oracle Restart Home.

5. Set CRS=TRUE for the newest ORACLE_HOME and unset for the previous.

$ /u01/app/19.19/grid/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/19.19/grid CRS=FALSE
 

$/ u01/app/19.21/grid/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/u01/app/19.21/grid CRS=TRUE

$ cat /u01/app/oraInventory/ContentsXML/inventory.xml

P.S. Check the owner of the newest ORACLE_HOME (must be root). If not, run roothas.sh with -lock option.

# /u01/app/19.21/grid/crs/install/roothas.sh -verbose -lock

RMAN-06054: media recovery requesting unknown archived log

I wish every DBA should take care of its backups properly. If they had done so, there could never have been so much damage to people's nervous system 😁 (easier said than done). 

Nevertheless, there was a 100GB gap of (?) lost archived redo log files along with 3 weeks old backup. I had to restore and open that Oracle database at any cost. What did I do :

1) I restored database (cf, spfile, datafiles etc.) from level 0 backup and recovered it as much as possible using level 1 backups. It doesn't need to say - at that point the database was inconsistent (mildly saying);

2) I created pfile and set the following parameters in it and brought database back in mount state (right before opening with resetlogs option) :

"_allow_resetlogs_corruption"    = TRUE
"_allow_error_simulation"        = true
undo_management                  = 'MANUAL'

3) alter database open resetlogs ;

At the end the database was opened successfully (unexpectedly 😀), I recreated another undo tablespace and extracted the data I needed.

That's it ! Keep an eye on your backups !

CLSRSC-318: Failed to start Oracle OHASD service. Died at crsinstall.pm line 3114. Oracle Linux 9 (OL9)

Caught this error during the upgrade of Oracle Restart (SIHA) from 19.19 to 21.11. Here is the log :

Performing root user operation.

The following environment variables are set as:
   ORACLE_OWNER= grid
   ORACLE_HOME=  /u01/siha_2111

Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)  
[n]:    Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)  
[n]:    Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/siha_2111/crs/install/crsconfig_params
The log of current session can be found at:
 /u01/app/grid/crsdata/db-04/crsconfig/roothas_2023-10-09_11-36-24AM.log
2023/10/09 11:36:25 CLSRSC-595: Executing upgrade step 1 of 12: 'UpgPrechecks'.
2023/10/09 11:36:29 CLSRSC-595: Executing upgrade step 2 of 12: 'GetOldConfig'.
2023/10/09 11:36:31 CLSRSC-595: Executing upgrade step 3 of 12: 'GenSiteGUIDs'.
2023/10/09 11:36:31 CLSRSC-595: Executing upgrade step 4 of 12: 'SetupOSD'.
2023/10/09 11:36:31 CLSRSC-595: Executing upgrade step 5 of 12: 'PreUpgrade'.
2023/10/09 11:37:30 CLSRSC-595: Executing upgrade step 6 of 12: 'UpgradeAFD'.
2023/10/09 11:37:31 CLSRSC-595: Executing upgrade step 7 of 12: 'UpgradeOLR'.
clscfg: EXISTING configuration version 0 detected.
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
2023/10/09 11:37:34 CLSRSC-595: Executing upgrade step 8 of 12: 'UpgradeOCR'.
LOCAL ONLY MODE  
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node db-04 successfully pinned.
2023/10/09 11:37:36 CLSRSC-595: Executing upgrade step 9 of 12: 'CreateOHASD'.
2023/10/09 11:37:37 CLSRSC-595: Executing upgrade step 10 of 12: 'ConfigOHASD'.
2023/10/09 11:37:37 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.service'
2023/10/09 11:39:56 CLSRSC-214: Failed to start the resource 'ohasd' 

Died at /u01/siha_2111/crs/install/crsinstall.pm line 3114.

/var/log/messages contained lots of the following :

db-04 clsecho: /etc/init.d/init.ohasd: Waiting for ohasd.bin PID 12851 to move. CGROUP

The cause is Linux resource control groups (cgroups v2, which is default in OL9) in operating system. The solution - revert back to the previous state (if possible), enable legacy (v1) cgroups in the kernel command line and rerun the upgrade. You need to add systemd.unified_cgroup_hierarchy=0 systemd.legacy_systemd_cgroup_controller into /etc/default/grub file and regenerate grub2 menu if you'd like to keep it in after reboot.

# cat /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="rhgb numa=off transparent_hugepage=never crashkernel=1G-64G:448M,64G-:512M systemd.unified_cgroup_hierarchy=0 systemd.legacy_systemd_cgroup_controller"
GRUB_DISABLE_RECOVERY="true"
GRUB_ENABLE_BLSCFG=true

# grub2-mkconfig -o /boot/grub2/grub.cfg

That's it !

Error in invoking target 'irman ioracle' when installing Oracle 19c SIHA on Oracle Linux 9 (OL9)

Although Oracle Corp. hasn't certified yet the use of Oracle 19c software on OL9, I decided to try (at least) the installation of the Oracle Restart "Single Instance High Availability (SIHA)". 

Expected message about (un)supported OS was ignored, in particular, by setting CV_ASSUME_DISTID in the shell before invoking ./gridSetup.sh :

$ export CV_ASSUME_DISTID=OL8.8

Then, after getting an error from the title, I simply scp'ed /usr/lib64/libc_nonshared.a over from another OL8 server, retried the linking, and the installation went on pretty well with one exception at the end - Oracle CVU thrown the error :

INFO:  [Oct 6, 2023 3:08:33 PM] RPM Package Manager database ...FAILED (PRVG-13702)
INFO:  [Oct 6, 2023 3:08:33 PM] Post-check for Oracle Restart configuration was unsuccessful.  
INFO:  [Oct 6, 2023 3:08:33 PM] Failures were encountered during execution of CVU verification request "stage -post hacfg".
INFO:  [Oct 6, 2023 3:08:33 PM] RPM Package Manager database ...FAILED
INFO:  [Oct 6, 2023 3:08:33 PM] PRVG-13702 : RPM Package Manager database files are corrupt on nodes "aaa".

The culprit were existing rpm packages in the system with obsolete SHA1 hash algorithm in their signatures. It worth to mention that this server was gradually upgraded from OL6 to OL9 through out of its life, so there were still some rpms signed by SHA1 signature which isn't supported anymore in OL9. The solution was to temporary implement the support of old (unsupported) signatures :

# update-crypto-policies --set LEGACY

and to check:

# update-crypto-policies --show
LEGACY

Next attempt of running CVU has finally succeeded and the installation process (including root scripts for the upgrade part) finished up without any errors.

I run :

# update-crypto-policies --set DEFAULT

to return the changed things back.

Example of setting up sftp (ssh) session behind the proxy to the server on the internet on Linux

% sftp -o "ProxyCommand nc -X connect -x proxy_server_address:proxy_server_port %h %p" -P sftp_server_port username@sftp_server_address

% ssh -o "ProxyCommand nc -X connect -x proxy_server_address:proxy_server_port %h %p" -p ssh_server_port username@ssh_server_address

dbca - [INS-04008] Invalid combination of arguments passed from command line. One or more mandatory dependent arguments are not passed for the argument: -useWalletForDBCredentials

During calling of dbca you might encounter into that error running dbca in silent mode. The point is that the argument from the error (-useWalletForDBCredentials) isn't mandatory, as dbca help message says :

 $ dbca -silent -createDatabase -help
       -createDatabase - Command to Create a database.
               -responseFile | (-gdbName,-templateName)
               -responseFile - <Fully qualified path for a response file>
               -gdbName <Global database name>
               -templateName <Specify an existing template in default location or the complete template path for DB Creation or provide a new template name for template creation>
               [-useWalletForDBCredentials <true | false> Specify true to load database credentials from wallet]
                       -dbCredentialsWalletLocation <Path of the directory containing the wallet files>
                       [-dbCredentialsWalletPassword <Password to open wallet with auto login disabled>]
               [-characterSet <Character set for the database>]

...

But there is an dependent mandatory argument which depends on non-mandatory argument 😀. I.e. the argument 

-dbCredentialsWalletLocation

, which goes next after 

-dbCredentialsWalletLocation

, is actually required.

So, in case of such error just include 

-dbCredentialsWalletLocation <existing_path>

in your dbca call, and if you want, without -dbCredentialsWalletLocation 😀

 

Good luck !

Lots of INVALID objects in Oracle supplied schemas after PDB remote cloning to/from 19c; ORA-04023 error

I encountered into weird situation the other day. Imagine that you've made an successful upgrade of CDB from 12.2 to 19c version without any error. But after cloning new PDB over database link (to or from upgraded CDB) you're getting a warning in the alert log file like this :

PDB_TEST(19):*************************************************************** PDB_TEST(19):WARNING: Pluggable Database PDB_TEST with pdb id - 19 is
PDB_TEST(19):         altered with errors or warnings. Please look into
PDB_TEST(19):         PDB_PLUG_IN_VIOLATIONS view for more details.
PDB_TEST(19):***************************************************************

Moreover, there are hundreds of INVALID objects in Oracle supplied schemas, i.e. package bodies of DBMS_STATS, DBMS_MONITOR and so on packages are invalid.

What are you gonna do ?

I took a look into alert log file and found other lines :

ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"
ORA-06512: at line 1

I got the ORA-04023 error (Object SYS.AQ$_POST_INFO could not be validated or authorized
) trying to compile this package. 

I used the following "method" to overcome this issue :

1. I dropped the object(s) generated ORA-04023 error (it was a pl/sql type) and recreated the dictionary with checking invalid objects.

2. After dictionary recreation, I analyzed spool file for ORA-04023 error(s), and if they were, I moved on to step 1 again. 

Finally, I ended up with 4 objects to delete (DBMS_AQ is dependable of those). Here is the script :

 

rem get connected to PDB

set echo on timi on
spool drop_inv_types.out append

alter session set "_oracle_script" = true ;
drop type SYS.AQ$_REG_INFO force ;
drop type SYS.AQ$_POST_INFO force ;
drop type SYS.MSG_PROP_T force ;
drop type SYS.AQ$_SUBSCRIBER force ;

rem
rem restart PDB in upgrade mode and recreate data dictionary (catalog.sql and catproc.sql)
rem

shutdown immediate
startup upgrade

@?/rdbms/admin/catalog
@?/rdbms/admin/catproc

@?/rdbms/admin/utlrp

shutdown immediate

startup

spool off

To sum up - I still don't know the real cause of the issue, I bumped into it only once and it occured only in particular CDB. Therefore I haven't got the information how to prevent it.

Hope it will help. Good Luck !!!