Reflection of my thoughts and technical expriences: November 2022

Environment:
------------------------------------------------------------------------------
Primary Database: orcldb
ORACLE_HOME: /u01/app/oracle/product/19.3.0.0/dbhome_1
GSM_HOME: /u01/app/oracle/product/19.3.0.0/gsmhome_1
AGENT_HOME: /u01/app/oracle/agent/agent_13.5.0.0.0
Database Name: orcldb

Physical Standby Database-1: orcldbp
ORACLE_HOME: /u01/app/oracle/product/19.3.0.0/dbhome_1
GSM_HOME: /u01/app/oracle/product/19.3.0.0/gsmhome_1
AGENT_HOME: /u01/app/oracle/agent/agent_13.5.0.0.0
Database Name: orcldbp

Physical Standby Database-2: orcldbs
ORACLE_HOME: /u01/app/oracle/product/19.3.0.0/dbhome_1
GSM_HOME: /u01/app/oracle/product/19.3.0.0/gsmhome_1
AGENT_HOME: /u01/app/oracle/agent/agent_13.5.0.0.0
Database Name: orcldbs

Note-1: The above databases are Oracle 19c with applied 19.17 RU.
Note-2: You must use the OPatch utility version 12.2.0.1.32 or later to apply this patch, and ensure to satisfy the following prerequisites prior to installation.

Please refer for more details: Primary Note for Database Proactive Patch Program (Doc ID 888.1)

Monthly Recommended Patch (MRP) for Oracle 19c (19.17)
------------------------------------------------------------------------------
Date Published: November 15, 2022
Version: 19.17.0.0.221115 - DATABASE MRP
Patch Number: Patch 34737974
Name: DATABASE MRP 19.17.0.0.221115
Prerequisite: Oracle Database RU 19.17

What is in the MRP1 for Oracle Database 19c (19.17)
----------------------------------------------------------------------
The following bugs are fixed by this patch:
33896423, 34333986, 30691454, 34538232, 34574048, 34366627, 34724125

Ensure that you verify the Oracle Inventory because OPatch accesses it to install the patches. To verify the inventory, run the following command.

Login to Physical Standby Database-1
-------------------------------------------------
[oracle@stbyh01 ~]$ cd $ORACLE_HOME/OPatch

[oracle@stbyh01 OPatch]$ ./opatch lsinventory -detail -oh /u01/app/oracle/product/19.3.0.0/dbhome_1
Oracle Interim Patch Installer version 12.2.0.1.33
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/19.3.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/19.3.0.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.33
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatch/opatch2022-11-17_12-21-48PM_1.log

(Output truncated for better visibility).

Maintain a location (UNZIPPED_PATCH_LOCATION) for storing the contents of the patch ZIP file.
Extract the contents of the patch ZIP file by running the following command:

[oracle@stbyh01 ~]$ unzip p34737974_1917000DBRU_Linux-x86-64.zip
Archive: p34737974_1917000DBRU_Linux-x86-64.zip
creating: 34737974/
creating: 34737974/34366627/
creating: 34737974/34366627/etc_online/
creating: 34737974/34366627/etc_online/config/
inflating: 34737974/34366627/etc_online/config/inventory.xml
inflating: 34737974/34366627/etc_online/config/actions.xml
creating: 34737974/34366627/files/
creating: 34737974/34366627/files/lib/
creating: 34737974/34366627/files/lib/libgeneric19.a/
inflating: 34737974/34366627/files/lib/libgeneric19.a/kgnfs.o

(Output truncated for better visibility).

Determine whether any currently installed interim patches conflict with MRP patch by executing the opatch command shown below

[oracle@stbyh01 OPatch]$ ./opatchauto apply -binary /home/oracle/34737974/ -analyze -online
Oracle Home : /u01/app/oracle/product/19.3.0.0/dbhome_1
OPatchAuto binary patching Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.
OPatchauto Version : 13.9.5.0.0
Running from : /u01/app/oracle/product/19.3.0.0/dbhome_1
opatchauto log file: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/opatchauto_2022-11-17_12-23-40_binary.log
Patch selected: /home/oracle/34737974

Analysing this list of patches :

[/home/oracle/34737974/33896423, /home/oracle/34737974/34333986, /home/oracle/34737974/30691454, /home/oracle/34737974/34538232, /home/oracle/34737974/34574048, /home/oracle/34737974/34366627, /home/oracle/34737974/34724125] ...

Analysis completed.

==Following patches were SUCCESSFULLY analyzed to be applied:
Patch: /home/oracle/34737974/33896423
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-23-43PM_1.log
Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-23-43PM_1.log
Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-23-43PM_1.log
Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-23-43PM_1.log
Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-23-43PM_1.log
Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-23-43PM_1.log
Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-23-43PM_1.log
opatchauto SUCCEEDED.

[oracle@stbyh01 OPatch]$

How do you apply an MRP on Oracle Database 19c (19.17)
----------------------------------------------------------------------------
Before apply, check the status of databases in dgmgrl and and also stop all the dependencies

DGMGRL> show configuration lag;
Configuration - orcldbcfg
Protection Mode: MaxAvailability
Members:
orcldb - Primary database
orcldbp - (*) Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
orcldbs - Physical standby database (receiving current redo)
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
SUCCESS (status updated 36 seconds ago)

DGMGRL> edit database orcldbp set state='APPLY-OFF';
Succeeded.
DGMGRL>

DGMGRL> edit database orcldbs set state='APPLY-OFF';
Succeeded.
DGMGRL>

Stop the OEM 13c agent
-------------------------------
[oracle@stbyh01 bin]$ ./emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
[oracle@stbyh01 bin]$

In my environment Oracle Global Data Services (GDS) are configured
------------------------------------------------------------------------------------------
[oracle@stbyh01 ~]$ ./gsm_setup.sh
GDSCTL: Version 19.0.0.0.0 - Production on Thu Nov 17 12:06:24 CST 2022
Copyright (c) 2011, 2019, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to GSMNORTH
GDSCTL> stop gsm -gsm GSMNORTH
GSM is stopped successfully
GDSCTL> exit

Shut down all instances and listeners associated with the Oracle home that you are updating
---------------------------------------------------------------------------------------------------------------------
[oracle@stbyh01 ~]$ . oraenv
ORACLE_SID = [orcldbp] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@stbyh01 ~]$

[oracle@stbyh01 ~]$ sqlplus sys/welcome@orcldbp as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 12:15:04 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@stbyh01 ~]$

[oracle@stbyh01 ~]$ . oraenv
ORACLE_SID = [orcldbp] ? catgdsdr
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@stbyh01 ~]$

[oracle@stbyh01 ~]$ sqlplus sys/welcome@catgdsdr as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 12:18:51 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@stbyh01 ~]$
[oracle@stbyh01 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2022 12:15:46
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stbyh01.localdomain)(PORT=1521)))
The command completed successfully
[oracle@stbyh01 ~]$

Run the OPatchauto utility by entering the following command:
-----------------------------------------------------------------------------------
[oracle@stbyh01 OPatch]$ ./opatchauto apply -binary /home/oracle/34737974/ -oh /u01/app/oracle/product/19.3.0.0/dbhome_1

Analysing this list of patches :
[/home/oracle/34737974/33896423, /home/oracle/34737974/34333986, /home/oracle/34737974/30691454, /home/oracle/34737974/34538232, /home/oracle/34737974/34574048, /home/oracle/34737974/34366627, /home/oracle/34737974/34724125] ...

Analysis completed.

Applying the patches ...

Patches successfully applied.

==Following patches were SUCCESSFULLY applied:
Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-31-34PM_1.log
Patch: /home/oracle/34737974/33896423
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-31-34PM_1.log
Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-31-34PM_1.log
Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-31-34PM_1.log
Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-31-34PM_1.log
Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-31-34PM_1.log
Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-31-34PM_1.log
opatchauto SUCCEEDED.
[oracle@stbyh01 OPatch]$

How does the inventory tell you that you’ve applied an MRP
------------------------------------------------------------------------------
[oracle@stbyh01 OPatch]$ ./opatch lsinventory | grep -i "Patch Description"
Patch description: "Fix for bug 34724125"
Patch description: "POD EEHO-DEV5 UNABLE TO SWITCHOVER ODS DB"
Patch description: "AFTER GI PATCHING AND DB UPGRADE TO 19.16 SESSIONS ARE CONSUMING TEMPTABLESPACE."
Patch description: "DNFS IO HANG DURING STRESS TEST"
Patch description: "AIM ORA-600 [KTUSCV1 CV BUF TOO BIG] - KTUSCV1"
Patch description: "FLUSH OUT STALE ANTILOCKS AND CONVERT KCLCLS_2 AND KCLANTILOCK_17 TO SOFT ASSERT"
Patch description: "SYD E1POD DBHOME PATCHING COMPLETELY HUNG WITH KPDBHASHTABLE_FIND MULTIPLE INSTANCE HANG"
Patch description: "Database Release Update : 19.17.0.0.221018 (34419443)"
Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
[oracle@stbyh01 OPatch]$

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Note: At this point, you will have no indication that an MRP has been applied.

These are the applied using MRP

Patch description: "Fix for bug 34724125"
Patch description: "POD EEHO-DEV5 UNABLE TO SWITCHOVER ODS DB"
Patch description: "AFTER GI PATCHING AND DB UPGRADE TO 19.16 SESSIONS ARE CONSUMING TEMPTABLESPACE."
Patch description: "DNFS IO HANG DURING STRESS TEST"
Patch description: "AIM ORA-600 [KTUSCV1 CV BUF TOO BIG] - KTUSCV1"
Patch description: "FLUSH OUT STALE ANTILOCKS AND CONVERT KCLCLS_2 AND KCLANTILOCK_17 TO SOFT ASSERT"
Patch description: "SYD E1POD DBHOME PATCHING COMPLETELY HUNG WITH KPDBHASHTABLE_FIND MULTIPLE INSTANCE HANG"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[oracle@stbyh01 OPatch]$ ./opatch lspatches
34724125;Fix for bug 34724125
34574048;POD EEHO-DEV5 UNABLE TO SWITCHOVER ODS DB
34538232;AFTER GI PATCHING AND DB UPGRADE TO 19.16 SESSIONS ARE CONSUMING TEMPTABLESPACE.
34366627;DNFS IO HANG DURING STRESS TEST
34333986;AIM ORA-600 [KTUSCV1 CV BUF TOO BIG] - KTUSCV1
33896423;FLUSH OUT STALE ANTILOCKS AND CONVERT KCLCLS_2 AND KCLANTILOCK_17 TO SOFT ASSERT
30691454;SYD E1POD DBHOME PATCHING COMPLETELY HUNG WITH KPDBHASHTABLE_FIND MULTIPLE INSTANCE HANG
34419443;Database Release Update : 19.17.0.0.221018 (34419443)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
[oracle@stbyh01 OPatch]$

Start all instances and listeners associated with the Oracle home
-----------------------------------------------------------------------------------
[oracle@stbyh01 ~]$ . oraenv
ORACLE_SID = [orcldbp] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@stbyh01 ~]$

[oracle@stbyh01 ~]$ sqlplus sys/welcome as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 16:07:29 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area 3053449816 bytes
Fixed Size 8901208 bytes
Variable Size 637534208 bytes
Database Buffers 2399141888 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@stbyh01 ~]$

[oracle@stbyh01 ~]$ . oraenv
ORACLE_SID = [orcldbp] ? catgdsdr
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@stbyh01 ~]$

[oracle@stbyh01 ~]$ sqlplus sys/welcome as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 16:08:03 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area 3053449816 bytes
Fixed Size 8901208 bytes
Variable Size 620756992 bytes
Database Buffers 2415919104 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@stbyh01 ~]$

[oracle@stbyh01 ~]$ ps -ef | grep pmon
oracle 9725 1 0 16:07 ? 00:00:00 ora_pmon_orcldbp
oracle 9953 1 0 16:08 ? 00:00:00 ora_pmon_catgdsdr
oracle 12397 3775 0 16:46 pts/0 00:00:00 grep --color=auto pmon
[oracle@stbyh01 ~]$

Login to Physical Standby Database-2
-------------------------------------------------
Execute the following steps:

1. [oracle@cstbyh01 OPatch]$ ./opatch lsinventory -detail -oh /u01/app/oracle/product/19.3.0.0/dbhome_1

2. [oracle@cstbyh01 ~]$ unzip p34737974_1917000DBRU_Linux-x86-64.zip

Determine whether any currently installed interim patches conflict with MRP patch by executing the opatch command shown below

[oracle@cstbyh01 ~]$ cd $ORACLE_HOME/OPatch
[oracle@cstbyh01 OPatch]$ ./opatchauto apply -binary /home/oracle/34737974/ -analyze -online

Analysing this list of patches :

Analysis completed.

==Following patches were SUCCESSFULLY analyzed to be applied:
Patch: /home/oracle/34737974/33896423
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-37-08PM_1.log
Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-37-08PM_1.log
Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-37-08PM_1.log
Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-37-08PM_1.log
Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-37-08PM_1.log
Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-37-08PM_1.log
Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-37-08PM_1.log
opatchauto SUCCEEDED.
[oracle@cstbyh01 OPatch]$

Stop the OEM 13c agent
-----------------------------------
[oracle@cstbyh01 ~]$ cd /u01/app/oracle/agent/agent_13.5.0.0.0/bin/

[oracle@cstbyh01 bin]$ ./emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
[oracle@cstbyh01 bin]$

In my environment Oracle Global Data Services (GDS) are configured
------------------------------------------------------------------------------------------
[oracle@cstbyh01 ~]$ ./gsm_setup.sh
GDSCTL: Version 19.0.0.0.0 - Production on Tue Nov 15 18:34:14 CST 2022
Copyright (c) 2011, 2019, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to GSMSOUTH

GDSCTL> stop gsm -gsm GSMSOUTH
GSM is stopped successfully

GDSCTL> exit

Shut down all instances and listeners associated with the Oracle home that you are updating
----------------------------------------------------------------------------------------------------------------------
[oracle@cstbyh01 ~]$ sqlplus sys/welcome@orcldbs as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 12:15:25 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@cstbyh01 ~]$

[oracle@cstbyh01 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2022 12:15:59
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cstbyh01.localdomain)(PORT=1521)))
The command completed successfully
[oracle@cstbyh01 ~]$

Run the OPatchauto utility by entering the following command:
-----------------------------------------------------------------------------------
[oracle@cstbyh01 OPatch]$ ./opatchauto apply -binary /home/oracle/34737974/ -oh /u01/app/oracle/product/19.3.0.0/dbhome_1

Oracle Home : /u01/app/oracle/product/19.3.0.0/dbhome_1
OPatchAuto binary patching Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.
OPatchauto Version : 13.9.5.0.0
Running from : /u01/app/oracle/product/19.3.0.0/dbhome_1
opatchauto log file: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/opatchauto_2022-11-17_12-39-44_binary.log
Patch selected: /home/oracle/34737974
Analysing this list of patches :
[/home/oracle/34737974/33896423, /home/oracle/34737974/34333986, /home/oracle/34737974/30691454, /home/oracle/34737974/34538232, /home/oracle/34737974/34574048, /home/oracle/34737974/34366627, /home/oracle/34737974/34724125] ...

Analysis completed.

Applying the patches ...

Patches successfully applied.
==Following patches were SUCCESSFULLY applied:
Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-39-47PM_1.log
Patch: /home/oracle/34737974/33896423
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-39-47PM_1.log
Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-39-47PM_1.log
Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-39-47PM_1.log
Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-39-47PM_1.log
Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-39-47PM_1.log
Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-39-47PM_1.log
opatchauto SUCCEEDED.

How does the inventory tell you that you’ve applied an MRP
-------------------------------------------------------------------------------
[oracle@cstbyh01 OPatch]$ ./opatch lsinventory | grep -i "Patch Description"

Patch description: "Fix for bug 34724125"
Patch description: "POD EEHO-DEV5 UNABLE TO SWITCHOVER ODS DB"
Patch description: "AFTER GI PATCHING AND DB UPGRADE TO 19.16 SESSIONS ARE CONSUMING TEMPTABLESPACE."
Patch description: "DNFS IO HANG DURING STRESS TEST"
Patch description: "AIM ORA-600 [KTUSCV1 CV BUF TOO BIG] - KTUSCV1"
Patch description: "FLUSH OUT STALE ANTILOCKS AND CONVERT KCLCLS_2 AND KCLANTILOCK_17 TO SOFT ASSERT"
Patch description: "SYD E1POD DBHOME PATCHING COMPLETELY HUNG WITH KPDBHASHTABLE_FIND MULTIPLE INSTANCE HANG"
Patch description: "Database Release Update : 19.17.0.0.221018 (34419443)"
Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
[oracle@cstbyh01 OPatch]$

[oracle@cstbyh01 OPatch]$ ./opatch lspatches
34724125;Fix for bug 34724125
34574048;POD EEHO-DEV5 UNABLE TO SWITCHOVER ODS DB
34538232;AFTER GI PATCHING AND DB UPGRADE TO 19.16 SESSIONS ARE CONSUMING TEMPTABLESPACE.
34366627;DNFS IO HANG DURING STRESS TEST
34333986;AIM ORA-600 [KTUSCV1 CV BUF TOO BIG] - KTUSCV1
33896423;FLUSH OUT STALE ANTILOCKS AND CONVERT KCLCLS_2 AND KCLANTILOCK_17 TO SOFT ASSERT
30691454;SYD E1POD DBHOME PATCHING COMPLETELY HUNG WITH KPDBHASHTABLE_FIND MULTIPLE INSTANCE HANG
34419443;Database Release Update : 19.17.0.0.221018 (34419443)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
[oracle@cstbyh01 OPatch]$

Start all instances and listeners associated with the Oracle home
-----------------------------------------------------------------------------------
[oracle@cstbyh01 ~]$ . oraenv
ORACLE_SID = [orcldbs] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cstbyh01 ~]$

[oracle@cstbyh01 ~]$ sqlplus sys/welcome as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 16:07:46 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

[oracle@cstbyh01 ~]$ . oraenv
ORACLE_SID = [orcldbs] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@cstbyh01 ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2022 16:09:27
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.3.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/cstbyh01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cstbyh01.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cstbyh01.localdomain)(PORT=1521)))

STATUS of the LISTENER
----------------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 17-NOV-2022 16:09:27
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/cstbyh01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cstbyh01.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcldbs" has 1 instance(s).
Instance "orcldbs", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@cstbyh01 ~]$

Applying MRP in Primary Database:
-------------------------------------------------
Execute the following steps:

1. [oracle@prim01 OPatch]$ ./opatch lsinventory -detail -oh /u01/app/oracle/product/19.3.0.0/dbhome_1

2. [oracle@prim01 ~]$ unzip p34737974_1917000DBRU_Linux-x86-64.zip

Determine whether any currently installed interim patches conflict with MRP patch by executing the opatch command shown below

[oracle@prim01 ~]$ cd $ORACLE_HOME/OPatch

[oracle@prim01 OPatch]$ ./opatchauto apply -binary /home/oracle/34737974/ -analyze -online
Oracle Home : /u01/app/oracle/product/19.3.0.0/dbhome_1

OPatchAuto binary patching Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.
OPatchauto Version : 13.9.5.0.0
Running from : /u01/app/oracle/product/19.3.0.0/dbhome_1
opatchauto log file: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/opatchauto_2022-11-17_12-41-39_binary.log

Patch selected: /home/oracle/34737974

Analysis completed.

==Following patches were SUCCESSFULLY analyzed to be applied:
Patch: /home/oracle/34737974/33896423
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-41-42PM_1.log
Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-41-42PM_1.log
Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-41-42PM_1.log
Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-41-42PM_1.log
Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-41-42PM_1.log
Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-41-42PM_1.log
Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-41-42PM_1.log
opatchauto SUCCEEDED.
[oracle@prim01 OPatch]$

Shutting down Primary Database and GDS Catalog Database

----------------------------------------------------------------------

[oracle@prim01 ~]$ . oraenv

ORACLE_SID = [orcldb] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@prim01 ~]$ sqlplus sys/welcome@orcldb as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 12:17:10 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@prim01 ~]$

[oracle@prim01 ~]$ sqlplus sys/welcome@catgds as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 12:18:17 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@prim01 ~]$ . oraenv
ORACLE_SID = [orcldb] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@prim01 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2022 12:40:38
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim01.localdomain)(PORT=1521)))
The command completed successfully
[oracle@prim01 ~]$

Stop the OEM 13c agent
---------------------------------
[oracle@prim01 ~]$ cd /u01/app/oracle/agent/agent_13.5.0.0.0/bin/
[oracle@prim01 bin]$ ./emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Stopping agent ...
stopped.
[oracle@prim01 bin]$

Stop the GSM in Primary Database
----------------------------------------------
[oracle@prim01 ~]$ ./gsm_setup.sh
GDSCTL: Version 19.0.0.0.0 - Production on Thu Nov 17 12:06:06 CST 2022
Copyright (c) 2011, 2019, Oracle. All rights reserved.
Welcome to GDSCTL, type "help" for information.
Current GSM is set to GSMEAST
GDSCTL> stop gsm -gsm GSMEAST
GSM is stopped successfully
GDSCTL> exit
[oracle@prim01 ~]$

Run the OPatchauto utility by entering the following command:
------------------------------------------------------------------------------------
[oracle@prim01 OPatch]$ ./opatchauto apply -binary /home/oracle/34737974/ -oh /u01/app/oracle/product/19.3.0.0/dbhome_1

Patch selected: /home/oracle/34737974

Analysis completed.

Applying the patches ...

Patches successfully applied.

==Following patches were SUCCESSFULLY applied:
Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-42-27PM_1.log
Patch: /home/oracle/34737974/33896423
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-42-27PM_1.log
Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-42-27PM_1.log
Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-42-27PM_1.log
Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-42-27PM_1.log
Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-42-27PM_1.log
Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19.3.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_12-42-27PM_1.log
opatchauto SUCCEEDED.

[oracle@prim01 OPatch]$ ./opatch lsinventory | grep -i "Patch Description"
Patch description: "Fix for bug 34724125"
Patch description: "POD EEHO-DEV5 UNABLE TO SWITCHOVER ODS DB"
Patch description: "AFTER GI PATCHING AND DB UPGRADE TO 19.16 SESSIONS ARE CONSUMING TEMPTABLESPACE."
Patch description: "DNFS IO HANG DURING STRESS TEST"
Patch description: "AIM ORA-600 [KTUSCV1 CV BUF TOO BIG] - KTUSCV1"
Patch description: "FLUSH OUT STALE ANTILOCKS AND CONVERT KCLCLS_2 AND KCLANTILOCK_17 TO SOFT ASSERT"
Patch description: "SYD E1POD DBHOME PATCHING COMPLETELY HUNG WITH KPDBHASHTABLE_FIND MULTIPLE INSTANCE HANG"
Patch description: "Database Release Update : 19.17.0.0.221018 (34419443)"
Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
[oracle@prim01 OPatch]$

[oracle@prim01 OPatch]$ ./opatch lspatches
34724125;Fix for bug 34724125
34574048;POD EEHO-DEV5 UNABLE TO SWITCHOVER ODS DB
34538232;AFTER GI PATCHING AND DB UPGRADE TO 19.16 SESSIONS ARE CONSUMING TEMPTABLESPACE.
34366627;DNFS IO HANG DURING STRESS TEST
34333986;AIM ORA-600 [KTUSCV1 CV BUF TOO BIG] - KTUSCV1
33896423;FLUSH OUT STALE ANTILOCKS AND CONVERT KCLCLS_2 AND KCLANTILOCK_17 TO SOFT ASSERT
30691454;SYD E1POD DBHOME PATCHING COMPLETELY HUNG WITH KPDBHASHTABLE_FIND MULTIPLE INSTANCE HANG
34419443;Database Release Update : 19.17.0.0.221018 (34419443)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
[oracle@prim01 OPatch]$

Starting the Primary Database and GDS Catalog Database

----------------------------------------------------------------------

[oracle@prim01 ~]$ . oraenv

ORACLE_SID = [orcldb] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@prim01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 15:52:57 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area 3053449816 bytes
Fixed Size 8901208 bytes
Variable Size 654311424 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL>

[oracle@prim01 ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-NOV-2022 15:53:45
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.3.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/prim01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim01.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim01.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 17-NOV-2022 15:53:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/prim01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim01.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcldb" has 1 instance(s).
Instance "orcldb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@prim01 ~]$

[oracle@prim01 ~]$ . oraenv
ORACLE_SID = [orcldb] ? catgds
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@prim01 ~]$

[oracle@prim01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 17 15:54:19 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area 3053449816 bytes
Fixed Size 8901208 bytes
Variable Size 704643072 bytes
Database Buffers 2332033024 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

SQL> exit
[oracle@prim01 ~]$ . oraenv
ORACLE_SID = [orcldb] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@prim01 ~]$

Login to Data Guard Broker

---------------------------------------

[oracle@prim01 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Nov 17 16:08:19 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/welcome
Connected to "orcldb"
Connected as SYSDBA.
DGMGRL>

DGMGRL> show configuration;
Configuration - orcldbcfg
Protection Mode: MaxAvailability
Members:
orcldb - Primary database
Warning: ORA-16824: multiple warnings, including fast-start failover-related warnings, detected for the database
orcldbp - (*) Physical standby database
orcldbs - Physical standby database (receiving current redo)
Fast-Start Failover: Enabled in Zero Data Loss Mode
Configuration Status:
WARNING (status updated 43 seconds ago)

DGMGRL> edit database orcldbp set state='APPLY-ON';
Succeeded.

DGMGRL> edit database orcldbs set state='APPLY-ON';
Succeeded.

Started the Observer in other window in Background Mode
----------------------------------------------------------------------------
[oracle@prim01 ~]$ . oraenv
ORACLE_SID = [orcldb] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@prim01 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Nov 17 16:10:49 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/welcome
Connected to "orcldb"
Connected as SYSDBA.
DGMGRL>

DGMGRL> start observer orcldbcfg_obs in BACKGROUND file is '/u01/orcldbcfg_obs.dat' logfile is '/u01/orcldbcfg_obs.log' connect identifier is orcldb;

Connected to "orcldb"
Submitted command "START OBSERVER" using connect identifier "orcldb"
DGMGRL> DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Nov 17 16:11:05 2022
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "orcldb"
Connected as SYSDBA.
Succeeded in opening the observer file "/u01/orcldbcfg_obs.dat".
[W000 2022-11-17T16:11:06.702-06:00] FSFO target standby is orcldbp
Observer 'orcldbcfg_obs' started
The observer log file is '/u01/orcldbcfg_obs.log'.
DGMGRL>

Hope it helps................

Reflection of my thoughts and technical expriences

Thursday, November 17, 2022

Applying the first Monthly Recommended Patches (MRP) for Oracle 19c (19.17) with Physical Standby Databases