Increasing Database Patching Performance

While applying the October 2023 Critical Patch Update (CPU) - that contains various critical fixes as can be found over there https://www.oracle.com/security-alerts/cpuoct2023.html - I realized that the quarterly database patching takes longer quarter by quarter.

My impression was that especially the analysis before the actual patching starts takes longer every time. This time when going from 19.20 to 19.21 the patching of DB RU, Grid Infrastructure and OJVM took more than an hour on the first development system where I've applied the patch (for a single instance database).

My first idea was to do out-of-band patching and thus just

1. copy the patched Oracle Home from DEV to TEST and later to PROD. This seemed especially reasonable, since DEV and TEST are regular copies from production anyway OR

2. Do a clean installation of a new 19c Oracle Home and patch that one directly to 19.21 (and add all other required one-off-patches).

While this is a supported approach, when looking into (1) I wondered, why the Oracle Home was so huge (more than 70 Gigabytes).

I realized, that large parts of the size came from $ORACLE_HOME/.patch_storage:

[oracle@prod122db ~]$ du -hs $ORACLE_HOME/.patch_storage
32G /u01/install/APPS/19.0.0/.patch_storage

When looking into cleaning that up I stumbled onto a quite new option of Opatch: "opatch util deleteinactivepatches". The feature is described over there: "OPatch 12.2.0.1.37+ Introduces a New Feature to Delete Inactive Patches in the ORACLE_HOME/.patch_storage Directory (Doc ID 2942102.1)"

The tool has a "diagnostic" command, that just lists the patches that can be removed: "$ORACLE_HOME/OPatch/opatch util listorderedinactivepatches" The output in my case reveleas:

Output of listorderedinactivepatches before cleanup

When run in "real" mode - with "opatch util DeleteInactivePatches" - it also lists those patches and then allows to delete them by confirming to do so.

The Support Notes suggest that older versions of OPatch require multiple consecutive runs of the cleanup. In my case though, everything got removed with a single shot (even though I have multiple chains with OJVM, DB RU and OCW):

[oracle@entw230928db ~]$ du -hs $ORACLE_HOME/.patch_storage
5.3G /u01/install/APPS/19.0.0/.patch_storage

This can also be confirmed with a second "listorderedinactivepatches":

[oracle@entw230928db ~]$ $ORACLE_HOME/OPatch/opatch util listorderedinactivepatches
Oracle Interim Patch Installer version 12.2.0.1.40
Copyright (c) 2023, Oracle Corporation. All rights reserved.

Oracle Home : /u01/install/APPS/19.0.0
Central Inventory : /u01/install/APPS/oraInventory
from : /u01/install/APPS/19.0.0/oraInst.loc
OPatch version : 12.2.0.1.40
OUI version : 12.2.0.7.0
Log file location : /u01/install/APPS/19.0.0/cfgtoollogs/opatch/opatch2023-10-22_18-17-06PM_1.log

Invoking utility "listorderedinactivepatches"
List Inactive patches option provided

The oracle home has the following inactive patch(es) and their respective overlay patches:

The number of RU chains is 3

***** There are 1 inactive RU patches in chain 1
-Inactive RU/BP 35354406:OJVM RELEASE UPDATE: 19.20.0.0.230718 (35354406), installed on: Tue Aug 08 18:18:39 CEST 2023, with no overlays
-Active RU/BP 35648110:OJVM RELEASE UPDATE: 19.21.0.0.231017 (35648110), installed on: Thu Oct 19 20:03:50 CEST 2023, with no overlays

***** There are 1 inactive RU patches in chain 2
-Inactive RU/BP 35320149:OCW RELEASE UPDATE 19.20.0.0.0 (35320149), installed on: Tue Aug 08 18:10:49 CEST 2023, with no overlays
-Active RU/BP 35655527:OCW RELEASE UPDATE 19.21.0.0.0 (35655527), installed on: Thu Oct 19 19:50:29 CEST 2023, with no overlays

***** There are 1 inactive RU patches in chain 3
-Inactive RU/BP 35320081:Database Release Update : 19.20.0.0.230718 (35320081), installed on: Tue Aug 08 17:50:00 CEST 2023, with no overlays
-Active RU/BP 35643107:Database Release Update : 19.21.0.0.231017 (35643107), installed on: Thu Oct 19 19:20:53 CEST 2023, with no overlays

OPatch succeeded.

After seeing "Opatch Apply And Rollback Getting Slower After Oneoff Or RU Patch Numbers Increased (Doc ID 2946849.1)" - where it is explained that the tool may also have a good impact on performance beside "shrinking" the Oracle Home I gave an apply another chance on my second testing instance. The instance is similar sized, and the 3 OPatch apply commands came down from almost 1.5 hours before the cleanup to 14 Minutes after the cleanup.

That was "good enough" for me so I did not pursue the Out-Of-Band Patching approach any further.