Applying the first MRP for Oracle 19.17.0

A few weeks ago I blogged about the fact that we retired the RURs, and instead release the MRPs (Monthly Recommended Patches). In my previous blog post about this topic you’ll find also an FAQ hopefully answering all the questions you may have (had). And now it is mid of November, so I can show you the process of applying the first MRP for Oracle 19.17.0.

Photo by Glenov Brankovic on Unsplash

How do you get access to the MRPs?

At the moment while I write this, you can find the MRPs only in MOS Note: 888.1 -Primary Note for Database Proactive Patch Program – but a link/section in MOS Note: 555.1 – Oracle Database 19c Important Recommended One-Off Patches should appear soon as well.

MOS Note: 888.1 -Primary Note for Database Proactive Patch Program

.

What is in the MRP1 for Oracle Database 19.17.0?

Speaking about the contents, those 7 fixes are included.

34737974/
├── 30691454
├── 33896423
├── 34333986
├── 34366627
├── 34538232
├── 34574048
├── 34724125
├── automation
├── bundle.xml
├── README.html
└── README.txt

3 of them are from MOS Note: 555.1, 3 are important additional fixes, and 1 is a security fix for a 3rd party software. But find the description further down below in my blog post under How does the inventory tell you that you’ve applied an MRP?.

How do you apply an MRP?

At first, it is of vital importance that you’ll have the most recent opatch applied, but also that you do the conflict check beforehand as I usually show in my quarterly blog post such as in Patching all my environments with the October 2022 Bundle Patches. As MRPs are nothing different then a few one-off patches bundled together, I thought you should make sure that no conflicting patch is installed already.

Conflict and Space Check

But … surprise surprise … I better should have studied the README before 🙂

$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.33
Copyright (c) 2022, 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.33
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2022-11-16_16-36-53PM_1.log

This command doesn't support System Patch.

OPatch failed with error code 21

So better read the README.

Conflict check done the right way for this MRP

$ $ORACLE_HOME/OPatch/opatchauto apply -binary . -analyze -online
Oracle Home : /u01/app/oracle/product/19

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

opatchauto log file: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/opatchauto_2022-11-16_16-40-23_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/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-16_16-40-24PM_1.log

Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-16_16-40-24PM_1.log

Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-16_16-40-24PM_1.log

Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-16_16-40-24PM_1.log

Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-16_16-40-24PM_1.log

Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-16_16-40-24PM_1.log

Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-16_16-40-24PM_1.log


opatchauto SUCCEEDED.

Be prepared that this may take a while depending on the number of patches you have in your home – and I have a number of them. In my playground environment the apply took start-till-end almost 12 minutes which has to do at first with the dependency checks but also the sole patch apply itself took 5 minutes alone.

Do you have to use opatchauto?

It is total news to me that the README does not speak about opatch but instead solely opatchauto. Above you could read already that the prechecks as I used to do them with opatch for space and conflicts fail. But I had to try whether I at least could apply the MRP with opatch.

This is the error I received when I used opatch:

$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.33
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


ZOP-51: The patch location is not valid for apply, because it doesn't have correct metadata, or it points to a patch directory. 
Argument(s) Error... Patch location is not valid for apply

Please check the arguments and try again.

OPatch failed with error code 135

But I could climb down to every subdirectory, and install each patch separately with opatch. Of course, this is not the idea of a patch bundle. But I wonder why I can install the Data Pump Bundle Patch with opatch – and the new MRP requires opatchauto. Let me check this with our patching authorities – and I will update this blog post once I have more knowledge about it.

So in summary, the answer is (right now): Yes, you will have to use opatchauto at the moment since this is built like a system patch.

Timur asked me whether I tried out opatch napply – but this will fail as well with the This is not a system patch error message.

Do you need to invoke datapatch?

Again, let’s consult the README. Since it would be painful for you to walk through all the patches manually, and in case you haven’t seen this in the README:

• Note: MRP 19.17.0.0.221115 does not include any SQL changes and hence datapatch does not need to be run post installation. This may change for future MRPs.

what would happen if you ran datapatch accidentially?

$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.17.0.0.0 Production on Thu Nov 17 13:20:54 2022
Copyright (c) 2012, 2022, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21181_2022_11_17_13_20_54/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: Installed
  PDB CDB$ROOT: Applied successfully on 08-NOV-22 11.05.35.742058 PM
  PDB PDB$SEED: Applied successfully on 08-NOV-22 11.05.37.696607 PM
Interim patch 34734035 (MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 24338134 25143018 26565187):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 08-NOV-22 11.05.36.721787 PM
  PDB PDB$SEED: Applied successfully on 08-NOV-22 11.05.38.671338 PM

Current state of release update SQL patches:
  Binary registry:
    19.17.0.0.0 Release_Update 220924224051: Installed
  PDB CDB$ROOT:
    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 08-NOV-22 11.05.35.738500 PM
  PDB PDB$SEED:
    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 08-NOV-22 11.05.37.692881 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
    No interim patches need to be applied

SQL Patching tool complete on Thu Nov 17 13:21:25 2022

No worries. Nothing happens since datapatch detects that nothing needs to be done.

How does the inventory tell you that you’ve applied an MRP?

This is actually a little bit disappointing. You will see it below:

$ $ORACLE_HOME/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:  "MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 24338134 25143018 26565187"
Patch description:  "OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)"
Patch description:  "Database Release Update : 19.17.0.0.221018 (34419443)"
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"

At the moment you’ll have no indication that an MRP has been applied. The MERGE patch you see in my list above is the 19.17.0 Data Pump Bundle Patch, not the MRP1 for 19.17.0. There you’ll find the first 7 fixes as the one-offs the MRP applied. This will make it hard to maintain MRPs in my humble opinion. But let us see what we can improve here.

Quick addition since I’ve been asked:

If you wonder what “Fix for bug 34724125” stands for, at first you won’t be able to find anything in the searchable bugs in MOS, and second, the reason is that every fix labeled only with “Fix for bug …” is most likely a security fix.

Can you rollback the MRP easily?

Of course I’ve had to try the rollback as well. But I hit another surprise. Every patch got rolled back one-after-another separately. I guess that opatch checks my inventory for every patch it rolls back over and over again. So for 7 patches to rollback, it takes 6 minutes for each of them. Now you guessed it already, the rollback of the MRP in my environment took over 40 minutes.

$ $ORACLE_HOME/OPatch/opatchauto rollback -binary . -oh /u01/app/oracle/product/19 -target_type oracle_database
Oracle Home : /u01/app/oracle/product/19

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

opatchauto log file: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/opatchauto_2022-11-17_10-56-30_binary.log

Target type : oracle_database

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.

Rolling back the patches ...


Patches successfully rolled back.


==Following patches were SUCCESSFULLY rolled back:

Patch: /home/oracle/34737974/33896423
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_10-56-31AM_1.log

Patch: /home/oracle/34737974/34333986
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_10-56-31AM_1.log

Patch: /home/oracle/34737974/30691454
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_10-56-31AM_1.log

Patch: /home/oracle/34737974/34538232
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_10-56-31AM_1.log

Patch: /home/oracle/34737974/34574048
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_10-56-31AM_1.log

Patch: /home/oracle/34737974/34366627
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_10-56-31AM_1.log

Patch: /home/oracle/34737974/34724125
Log: /u01/app/oracle/product/19/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-17_10-56-31AM_1.log


opatchauto SUCCEEDED.

My inventory looks fine afterwards.

$ $ORACLE_HOME/OPatch/opatch lsinventory | grep -i "Patch Description"
Patch description:  "MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 24338134 25143018 26565187"
Patch description:  "OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)"
Patch description:  "Database Release Update : 19.17.0.0.221018 (34419443)"
Patch description:  "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"

So in summary, the rollback works fine but takes quite a bit since each patch gets rolled back separately – and I guess the inventory check happens over and over again:

2022-11-17 11:30:32,257 INFO  [1] oracle.glcm.opatch.common.impl.SingletonPatch - /u01/app/oracle/product/19/inventory/oneoffs/34574048/etc/config/maintenance_alias.xml does not exist
2022-11-17 11:30:48,148 FINE  [1] oracle.opatch.util.perfmonitor.MethodTrackerObj - Error while ending the tracker. Message - null
2022-11-17 11:37:11,046 INFO  [1] oracle.glcm.opatch.common.impl.PatchFactoryImpl - Singleton  patch is found
2022-11-17 11:37:11,046 INFO  [1] oracle.glcm.opatch.common.impl.PatchFactoryImpl - Patch type found: SINGLETON_PATCH

taking a full 6 minutes in my environment for every patch. We’ll investigate this further.

But keep in mind that rolling back an RU will require that you’d rollback the MRP at first. Otherwise this will happen to you:

Patches will be rolled back in the following order: 
   34419443
Prerequisite check "CheckPatchRollbackDependents" failed.
The details are:
OPatch will not roll back patch(es) "34419443" until you have rolled back dependent patch(es) "34734035,30691454,33896423,34333986,34366627,34538232,34574048,34724125".

So I need to rollback the 7 one-offs at first before I can rollback the RU. Doesn’t make life easier to be frank. And please scroll up a little but to see how to rollback the MRP one-offs with opatchauto in one pass.

.

Summary after applying the first MRP

I expected a lot. And I see some room for improvement. Certainly we sent feedback to the group owning the process to build the MRPs. So let us see what we can get, and in mid December I will blog again about MRPs as soon as MRP2 for 19.17.0 will be available.

When you take the fact that I’ve had to download the 7 fixes delivered with the MRP manually by myself, apply them etc – then the MRP is a conveniently easing this procedure for me. But I can sense that you’d like to have the ability to use opatch instead of opatchauto, and to have a more obvious way to maintain MRPs within the inventory.

So overall, MRPs are a good thing – but not a must-apply – patch bundle. And let us see which improvements we can get for the next one in mid December.

We highly recommend that you provision a new home with ./runInstaller -applyRU … -applyOneOffs … . Daniel tested this successfully yesterday already. He applied not only 19.17.0 but the MRP1, the Data Pump Bundle Patch, the OJVM and the most recent time zone patches all in one command. And this way you don’t have to fiddle around with opatchauto and the long-running rollback. And you don’t carry around a full-blown patch inventory which makes every patch apply taking longer and longer.

Further Information and Links

• Bye bye RURs – Long live MRPs
• FAQ about MRPs
• MOS Note: 2898381.1 – Sunsetting of 19c RURs and FAQ
• MOS Note: 2898740.1 – Introducing Monthly Recommended Patches (MRPs) and FAQ
• MOS Note: 555.1 – Oracle Database 19c Important Recommended One-Off Patches
• MOS Note: 888.1 -Primary Note for Database Proactive Patch Program
• Patching all my environments with the October 2022 Bundle Patches
• Binary Patching is slow because of the inventory
• Simple Database Installation with applyRU and applyOneOffs

–Mike