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.
Photo by Karthik Sreenivas on Unsplash
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
- MOS Note: 888.1 – Primary Note for Database Proactive Patch Program
- MOS Note: 555.1 – Oracle Database 19c Important Recommended One-off Patches
- RURs are gone – long live MRPs
- Applying the first MRP to 19.17.0 and
- Applying the MRP from December 2022 to 19.17.0
- Apply the Data Pump Bundle Patch – non-rolling but online
Dear Mike,
thanks a lot. There is still the GI MRP (which is different from the DB MRP obviously).
The GI MRP is still using opatchauto, but it seems this is not working properly with HAS at the moment.
https://updates.oracle.com/Orion/Services/download?type=readme&aru=25220143
I already open SR 3-33092338701, but support is still investigating.
Have you already tried it?
Beste Grüße, Matthias.
Hi Matthias,
no, I haven’t tried it – but I forward your request internally as well.
Sorry for the inconvenience but the MRP for the GI is meant to use opatchauto.
The responsible people are reaching out to the RAC/HA team.
Cheers,
Mike
Hi Matthias,
I asked somebody from the RAC team to check your SR and talk to Support. I agree with your point (unless somebody tells me differently) that certainly you should be able to apply the GI MRP to your Restart environment. So I wonder what the last update from yesterday in the SR should mean.
Thanks for your patience – and I did ask in addition to get the README updated/corrected to cover your (not unusual) case.
Mike
Dear Mike,
thanks a lot. For now the support answer was:
“Hi
Since it’s single instance getting this message. MRP patches are for RAC environment.
Please find the patch readme file for more details.
Grid Infrastructure Monthly Recommended Patch (GI MRP) 19.19.0.0.230516 is a cumulative system patch, applicable to RAC customers. That is, the contents of all previous MRPs are included in the latest MRP patch.
Thanks & Regards,
Venkata Pulavarthi
Oracle Global Customer Support”
Not sure what to think of this…
Danke und beste Grüße, Matthias.
Hi Matthias,
this is why I did ask the RAC team to intervene.
The answer makes not much sense to me since it neglects that you have a GI and ASM in a Restart environment.
Cheers
Mike
Dear Mike,
today I received the following answer from support:
“Mike is not having full update on your environment.
Mike thinks that you are trying to apply this in 2 node RAC and getting error.”
Can you please double-check with the RAC team, if they understood the situation correctly?
Thanks a lot and beste Grüße, Matthias.
Hi Matthias,
this is what I wrote:
“Yes, the customer has an Oracle Restart (SIHA) environment. And not only one but many.
And in fact, this has not been tested.
So this is not a “standalone” environment – he has clusterware and ASM but no other cluster nodes.
Hence, he has a GI home.”
Is my understanding of your env correct? (I hope so – otherwise please correct me)
Cheers
Mike
Dear Mike,
thanks a lot and yes: That’s how it is. Nothing to add.
So it seems whoever read this did not fully understand it. Can you please follow up with them?
Best regards, Matthias.
Hi Matthias,
certainly I do (or actually did already).
Cheers, sorry for the confusion and thanks for your patience
Mike
Mystery solved. Thanks for the help, Mike. Hopefully the documentation will be updated to be more clear. Have a good weekend.
Hi Mike,
We are trying to create a golden image for GI (and RAC DB products) which includes a recent RU + latest MRP. For this we do a dummy installation as follows:
cd ${GI_NEW_HOME}
unzip -q /oracle_sw_zips/LINUX.X64_193000_grid_home.zip
unzip -qo ${OPATCH_ZIP}
..then attach the new GI_HOME to inventory,
${ORACLE_HOME}/oui/bin/runInstaller -attachHome ORACLE_HOME=${ORACLE_HOME} ORACLE_HOME_NAME=”GI_${ORA_VER}_${NEW_RU}_GLDIMG” -silent
..check conflicts and (if successfull) apply the latest RU..
${PATCH_SCRIPTS}/conflict_check_gi_home.sh
${PATCH_SCRIPTS}/patch_gi_home.sh
…check conflicts for the GI_DB MRP on new GI_HOME..
sudo ${GI_NEW_HOME}/OPatch/opatchauto apply -binary ${GIDB_MRP_PT}/${GI_MRP} -analyze -oh ${GI_NEW_HOME}
Here we get following set of errors on screen:
OPATCHAUTO-72050: System instance creation failed.
OPATCHAUTO-72050: Failed while retrieving system information.
OPATCHAUTO-72050: Please check log file for more details.
and below key errors in the opatchauto log file:
PRCT-1011 : Failed to run “crsctl”. Detailed error: Cannot run program “/oracle/products/gi/19.22/bin/crsctl”: error=2, No such file or directory
What is the workaround as crsctl does not yet exist in this “not-yet-instantiated” GI_HOME?
Thanks a ton.
Cheers,
Ammar
Hi Ammar,
unfortunately I don’t know.
Please understand that I can’t debug install issues with GI/crsctl.
Please check via an SR with Oracle Support.
Cheers,
Mike