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.
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 18.104.22.168.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 : 22.214.171.124.37 OUI version : 126.96.36.199.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 188.8.131.52.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=184.108.40.206.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 : 220.127.116.11.37 OUI version : 18.104.22.168.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.
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 22.214.171.124.0 - Production on Mon May 22 12:14:10 2023 Version 126.96.36.199.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 188.8.131.52.0 - Production Version 184.108.40.206.0 $ $ORACLE_HOME/OPatch/datapatch -verbose SQL Patching tool version 220.127.116.11.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: 18.104.22.168.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: 22.214.171.124.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: 126.96.36.199.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: 188.8.131.52.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 184.108.40.206.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: 220.127.116.11.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 18.104.22.168.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: 22.214.171.124.0 Release_Update 230111171738: Installed PDB CDB$ROOT: Applied 126.96.36.199.0 Release_Update 230111171738 successfully on 26-JAN-23 07.31.33.350590 PM PDB PDB$SEED: Applied 188.8.131.52.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 184.108.40.206.0 34786990;OJVM RELEASE UPDATE: 220.127.116.11.230117 (34786990) 34765931;Database Release Update : 18.104.22.168.230117 (34765931) 29585399;OCW RELEASE UPDATE 22.214.171.124.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 126.96.36.199.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 188.8.131.52.0 34786990;OJVM RELEASE UPDATE: 184.108.40.206.230117 (34786990) 34765931;Database Release Update : 220.127.116.11.230117 (34765931) 29585399;OCW RELEASE UPDATE 18.104.22.168.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
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.
I already open SR 3-33092338701, but support is still investigating.
Have you already tried it?
Beste Grüße, 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.
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.
thanks a lot. For now the support answer was:
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) 22.214.171.124.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,
Oracle Global Customer Support”
Not sure what to think of this…
Danke und beste Grüße, 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.
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.
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)
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.
certainly I do (or actually did already).
Cheers, sorry for the confusion and thanks for your patience