Patching process changes with MRPs

I blogged about the Monthly Recommended Patches (MRP) several times before. And due to the feedback you and we had given, a few things got changed. The main change is from having the MRP bundled as a system patch making opatchauto a requirement. See below what Patching process changes with MRPs were implemented recently.

Before …

As you could read already in:

the introduction of the MRPs added one major flaw: They’ve been built as system patches which made opatchauto a requirement, many of you never used before as it was neither a requirement nor did it add much benefit for single instance environments. And in addition, it broke the patching automation for many customers who don’t use FPP (Fleet Patching and Provisioning).

With a previous version, I think the one from February 2022, this packaging has been changed and adjusted. So it was time for me to apply a newer MRP to my 19.18.0 environment before stepping forward to 19.19.0 for the pure sake of trying it out.

 

Applying the May 2023 MRP to my 19.18.0 environment

The quickest path to download the MRPs is either MOS Note: 555.1  – Oracle Database 19c Important Recommended One-off Patches or MOS Note: 888.1 – Primary Note for Database Proactive Patch Program – both are easy to remember, and both give you direct access to the MRPs.

In my case, I chose Patch 35333818, which is the MRP 4 for 19.18.0 from May 2023. There will be two additional MRPs for 19.18.0, one in June and the final and 6th one in July. I use this just as an example to demonstrate the changes in the apply process.

When I look at the README, I quickly spot that the MRP isn’t a system patch anymore. It can now applied with my regular OS credentials, and instead of opatchauto, I use opatch napply.

Let me do the usual conflict check at first:

$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph .

Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19/oraInst.loc
OPatch version    : 12.2.0.1.37
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-05-22_12-02-42PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

And since it passed, I can go on from here.

I will cut the output here since it is VERY long – and I don’t see a deeper benefit from adding thousands of lines from opatch napply to the blog post here.

$ $ORACLE_HOME/OPatch/opatch napply /media/sf_TEMP/2023-04/MRP19180/35333818 -verbose

Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.

Environment:
   OPatch.ORACLE_HOME=/u01/app/oracle/product/19
   oracle.installer.invPtrLoc=/u01/app/oracle/product/19/oraInst.loc
   oracle.installer.oui_loc=/u01/app/oracle/product/19/oui
   oracle.installer.library_loc=/u01/app/oracle/product/19/oui/lib/linux64
   oracle.installer.startup_location=/u01/app/oracle/product/19/oui
   OPatch.PLATFORM_ID=
   os.name=Linux
   OPatch.NO_FUSER=
   OPatch.SKIP_VERIFY=null
   OPatch.SKIP_VERIFY_SPACE=null
   oracle.installer.clusterEnabled=false
   TRACING.ENABLED=null
   TRACING.LEVEL=null
   OPatch.DEBUG=false
   OPATCH_VERSION=12.2.0.1.37
   Bundled OPatch Property File=properties
   Minimum OUI version: 10.2
   OPatch.PATH=/u01/app/oracle/product/19/bin:/u01/app/oracle/product/19/bin:/u01/app/oracle/product/19/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/oracle/.local/bin:/home/oracle/bin
   OPatch.MW_HOME=
   OPatch.WL_HOME=
   OPatch.COMMON_COMPONENTS_HOME=



Oracle Home       : /u01/app/oracle/product/19
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19/oraInst.loc
OPatch version    : 12.2.0.1.37
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-05-22_12-04-27PM_1.log

Verifying environment and performing prerequisite checks...
Lock the home to access inventory.

Running prerequisite check "CheckMinimumOPatchVersion" ...

All patches can be applied using this OPatch.
Prerequisite check "CheckMinimumOPatchVersion" passed.

[...]

Running prerequisite check "CheckSystemSpace" ...

Finding the total space needed...

Total space needed for the patches are: 90289958

Checking if enough space is present on the disk...

Required amount of space is availale on the disk
Enough system space is available.

Running prerequisite check "CheckSystemCommandAvailable" ...

All the required commands are available.
All the required system commands are present.

Fuser command is needed

Archive command is needed

Make commands is needed


[...]


Make result:
  Command: /usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/u01/app/oracle/product/19 OPATCH_SESSION=napply

  Returned code: 0
  Stdout output:
/u01/app/oracle/product/19/bin/genclntsh
/u01/app/oracle/product/19/bin/genagtsh /u01/app/oracle/product/19/lib/libagtsh.so 1.0

  Stderr output:


     Make Action done
Patches 31222103,32727143,33973908,34340632,34557500,34783802,34793099,34810252,34861493,34871935,34879016,34974052,35156936,35160800,35162446,35213579,35246710 successfully applied.
Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-05-22_12-05-20PM_1.log

OPatch succeeded.

So you see that there are space checks done, and basically napply applies patch after patch sequentially.

 

Executing datapatch

For this MRP the README clearly tells us to execute datapatch after the MRP has been applied successfully since it contains SQL changes. This is not always the case but it does not harm to add the datapatch call to your scripting since it happens with all databases and all PDBs online anyway. Just keep in mind that you need to execute it once per database.

SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 22 12:14:10 2023
Version 19.18.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 1577055336 bytes
Fixed Size		    9163880 bytes
Variable Size		  620756992 bytes
Database Buffers	  939524096 bytes
Redo Buffers		    7610368 bytes
Database mounted.
Database opened.

SQL> alter pluggable database all open;

Pluggable database altered.

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



$ $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 19.18.0.0.0 Production on Mon May 22 12:14:49 2023
Copyright (c) 2012, 2023, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_15246_2023_05_22_12_14_49/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 33192694 (OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)):
  Binary registry: Not installed
  PDB CDB$ROOT: Rolled back successfully on 19-JAN-22 10.14.44.327180 PM
  PDB PDB$SEED: Rolled back successfully on 19-JAN-22 10.14.44.347566 PM
Interim patch 33561310 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)):
  Binary registry: Not installed
  PDB CDB$ROOT: Rolled back successfully on 20-JUL-22 09.17.02.767339 PM
  PDB PDB$SEED: Rolled back successfully on 20-JUL-22 09.17.02.812896 PM
Interim patch 34086870 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)):
  Binary registry: Not installed
  PDB CDB$ROOT: Rolled back successfully on 08-NOV-22 11.05.34.788571 PM
  PDB PDB$SEED: Rolled back successfully on 08-NOV-22 11.05.36.745638 PM
Interim patch 34411846 (OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)):
  Binary registry: Not installed
  PDB CDB$ROOT: Rolled back successfully on 26-JAN-23 07.31.32.708629 PM
  PDB PDB$SEED: Rolled back successfully on 26-JAN-23 07.31.34.257652 PM
Interim patch 34734035 (MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 24338134 25143018 26565187):
  Binary registry: Not installed
  PDB CDB$ROOT: Rolled back successfully on 26-JAN-23 07.31.32.718271 PM
  PDB PDB$SEED: Rolled back successfully on 26-JAN-23 07.31.34.263871 PM
Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 26-JAN-23 07.31.33.354003 PM
  PDB PDB$SEED: Applied successfully on 26-JAN-23 07.31.34.884337 PM
Interim patch 34861493 (RESYNC CATALOG FAILED IN ZDLRA CATALOG AFTER PROTECTED DATABASE PATCHED TO 19.17):
  Binary registry: Installed
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed
Interim patch 34972375 (DATAPUMP BUNDLE PATCH 19.18.0.0.0):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 26-JAN-23 07.31.34.243438 PM
  PDB PDB$SEED: Applied successfully on 26-JAN-23 07.31.35.810067 PM
Interim patch 35160800 (GG IE FAILS WITH ORA-14400 AT SYSTEM.LOGMNRC_USER AFTER ORACLE DB UPGRADE TO 19.18DBRU):
  Binary registry: Installed
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed

Current state of release update SQL patches:
  Binary registry:
    19.18.0.0.0 Release_Update 230111171738: Installed
  PDB CDB$ROOT:
    Applied 19.18.0.0.0 Release_Update 230111171738 successfully on 26-JAN-23 07.31.33.350590 PM
  PDB PDB$SEED:
    Applied 19.18.0.0.0 Release_Update 230111171738 successfully on 26-JAN-23 07.31.34.881983 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    No interim patches need to be rolled back
    No release update patches need to be installed
    The following interim patches will be applied:
      34861493 (RESYNC CATALOG FAILED IN ZDLRA CATALOG AFTER PROTECTED DATABASE PATCHED TO 19.17)
      35160800 (GG IE FAILS WITH ORA-14400 AT SYSTEM.LOGMNRC_USER AFTER ORACLE DB UPGRADE TO 19.18DBRU)

Installing patches...
Patch installation complete.  Total patches installed: 4

Validating logfiles...done
Patch 34861493 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34861493/25121986/34861493_apply_CDB2_CDBROOT_2023May22_12_15_08.log (no errors)
Patch 35160800 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35160800/25179433/35160800_apply_CDB2_CDBROOT_2023May22_12_15_11.log (no errors)
Patch 34861493 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34861493/25121986/34861493_apply_CDB2_PDBSEED_2023May22_12_15_14.log (no errors)
Patch 35160800 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35160800/25179433/35160800_apply_CDB2_PDBSEED_2023May22_12_15_18.log (no errors)
SQL Patching tool complete on Mon May 22 12:15:22 2023

 

 

How does the inventory look like?

One of our criticism points to MRP was the fact that it does not get applied as a single piece such as the Data Pump Bundle Patch – and hence, it is hard to identify for you (or anybody else) whether an MRP has been applied, or not.

This is how my inventory looked like before I applied this MRP:

$ $ORACLE_HOME/OPatch/opatch lspatches

34972375;DATAPUMP BUNDLE PATCH 19.18.0.0.0
34786990;OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)
34765931;Database Release Update : 19.18.0.0.230117 (34765931)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

And this is how it looks after applying the MRP to my 19.18.0 home:

$ $ORACLE_HOME/OPatch/opatch lspatches

35246710;HIGH DIRECT PATH READ AFTER 19.18 DBRU PATCHING
35213579;MERGE ON DATABASE RU 19.18.0.0.0 OF 35037877 35046819
35162446;NEED BEHAVIOR CHANGE TO BE SWITCHED OFF
35160800;GG IE FAILS WITH ORA-14400 AT SYSTEM.LOGMNRC_USER AFTER ORACLE DB UPGRADE TO 19.18DBRU
35156936;ORA-7445 [KFFBNEW()+351]  AFTER CONVERT TO ASM FLEX DISKGROUP
34974052;DIRECT NFS CONNECTION RESET MESSAGES
34879016;ALL SESSIONS HANG DUE TO INST_RCV BUFFER IS NOT GETTING WRITE PERMISSION
34871935;SBI  QUEUE BUILDUP - SESSIONS SPIKE WITH GC CURRENT REQUEST  (6-DEC-2022)
34861493;RESYNC CATALOG FAILED IN ZDLRA CATALOG AFTER PROTECTED DATABASE PATCHED TO 19.17
34810252;SPIN OFF FOR BUG 34808861 [ORA-00600  INTERNAL ERROR CODE, ARGUMENTS  [KFDS_GETSEGREUSEENQ01] TERMINATED ALL DB INSTANCES
34793099;STRESS FA CDB CREATION FAILS ON 19.17 WITH THE ORA-00704  BOOTSTRAP PROCESS FAILURE WHILE OPENING PDB$SEED
34783802;PARALLEL QUERY ON PARTITIONED TABLE RETURNS WRONG RESULT
34557500;CTWR CAUSED MULTIPLE INSTANCES IN HUNG STATE ON THE RAC STANDBY DATABASE
34340632;AQAH  SMART MONITORING & RESILIENCY IN QUEUE KGL MEMORY USAGE
33973908;DBWR NOT PICKING UP WRITES FOR SOME TIME
32727143;TRANSACTION-LEVEL CONTENT ISOLATION FOR TRANSACTION-DURATION GLOBAL TEMPORARY TABLES
31222103;STRESS RAC ATPD FAN EVENTS ARE NOT GETTING PROCESSED WITH 21C GI AND 19.4 DB
34972375;DATAPUMP BUNDLE PATCH 19.18.0.0.0
34786990;OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)
34765931;Database Release Update : 19.18.0.0.230117 (34765931)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

Uhhh … that looks … not ideal I’d say.

The last 4 lines of the output are the inventory from before applying the MRP. And the MRP adds 17 additional entries into the inventory. So please have a good list, description or inventory overview on your environments since otherwise it can be a bit hard. And certainly, when you’d apply all these fixes as one-offs, the inventory would look exactly the same. I just had hoped that we would have one single entry for the MRP telling us the MRP number. Maybe, one day …

 

Further Links and Information

Share this: