Reflection of my thoughts and technical expriences: Applying the first Monthly Recommended Patches (MRP) for Oracle 19c (19.17) with Physical Standby Databases

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

No comments:

Post a Comment