Patching all my environments with the April 2022 Patch Bundles

Easter time is patching time. And since I was hiking and biking every day the last week up in the Northern Italian mountains, this blog post is coming with a slight delay. But better late than never. Time to show you my usual exercise, this time Patching all my environments with the April 2022 Patch Bundles.

As usual, an important annotation upfront: I patch in-place due to space issues. But in reality, you please patch always out-of-place with a separate home. Please see this blog post about how to apply the RU directly when you provision a new home with OUI.

Security Alert April 2022

You will find the April 2022 Security Alerts for all products here. And as usual, please pay close attention to the Database Server Products Risk Matrix. You will find the usual suspects such as OJVM but also products which weren’t there before too often, for instance Sharding. My advice is certainly to apply the patch bundles to all your environments if you haven’t done so already.

Patching all my environments with the April 2022 Patch Bundles

This time you may realize another change to the Supported Versions Affected column: Of course there is neither 11.2.0.4 nor 18c  mentioned but also 12.2.0.1 has been disappeared since it went out of Limited Error Correction Support on March 31, 2022. Be always aware that the issues noted in the risk matrix may happen in older releases, too, even though they are not mentioned anymore.

Database Patch Bundles

You will find the links to the individual patch bundles in MOS Note: 2844795.1 – Critical Patch Update (CPU) Program Apr 2022 Patch Availability Document (DB-only). But you can use as well MOS Note: 2118136.2 – Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases to download “your” bundle.

Do I need a new OPatch?

And then it is time again for the opatch check. Without even seeing the number, I can tell you that you MUST refresh your opatch since there are some significant performance improvements in the 29 version on all platforms. But let me double check with the READMEs:

  • 21.6.0 requires opatch 12.2.0.1.29 or later
  • 19.15.0 requires opatch 12.2.0.1.29 or later
  • 12.1.0.2 April 2022 requires opatch 12.2.0.1.29 or later
  • 11.2.0.4 April 2022 requires opatch 11.2.0.3.33  or later

Hence, I will update opatch in all my homes. The 6880880 link from the readmes takes you directly to the correct download. BUT … you will be directed to the 12.2.0.1.30 version of opatch, not the 29 version. And it may not wonder you, all three opatch releases for 12.1 – 21c are identical even though all sail with different display labels on MOS. So it is fairly enough in my case to download it only once.

For the 11g opatch, it will point you to the 11.2.0.3.34 version as well. Which is fine. I just wanted to mention it.

Hence, action no.1 for all my environments before I will start patching:

  • Wipe out the current OPatch directory in each home. Once I unzip the new OPatch bundles. I can proceed.

Applying RU 21.6.0 to my 21c home

I always do something you shouldn’t do. I apply the patch to my existing home. This has to do with the available space in my lab environment. You please provide a new home and apply the patch(es) right away as I describe here.

Patching my 21c database home to RU 21.6.0 is again pretty straight forward.

  1. Conflict and space checks

    $ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    PREREQ session
    
    Oracle Home       : /u01/app/oracle/product/21
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/21/oraInst.loc
    OPatch version    : 12.2.0.1.30
    OUI version       : 12.2.0.9.0
    Log file location : /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2022-04-24_17-50-16PM_1.log
    
    Invoking prereq "checkconflictagainstohwithdetail"
    
    Prereq "checkConflictAgainstOHWithDetail" passed.
    
    OPatch succeeded.
    
    
    $ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -ph ./
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    PREREQ session
    
    Oracle Home       : /u01/app/oracle/product/21
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/21/oraInst.loc
    OPatch version    : 12.2.0.1.30
    OUI version       : 12.2.0.9.0
    Log file location : /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2022-04-24_17-51-35PM_1.log
    
    Invoking prereq "checksystemspace"
    
    Prereq "checkSystemSpace" passed.
    
    OPatch succeeded.

    All set, I’m good to go.

  2. opatch apply
    $ $ORACLE_HOME/OPatch/opatch apply
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home       : /u01/app/oracle/product/21
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/21/oraInst.loc
    OPatch version    : 12.2.0.1.30
    OUI version       : 12.2.0.9.0
    Log file location : /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2022-04-24_17-52-41PM_1.log
    
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   33843745  
    
    Do you want to proceed? [y|n]
    y
    User Responded with: Y
    All checks passed.
    
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/u01/app/oracle/product/21')
    
    
    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files...
    Applying interim patch '33843745' to OH '/u01/app/oracle/product/21'
    ApplySession: Optional component(s) [ oracle.network.gsm, 21.0.0.0.0 ] , [ oracle.tfa, 21.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 21.0.0.0.0 ] , [ oracle.rdbms.tg4ifxm, 21.0.0.0.0 ] , [ oracle.sdo.companion, 21.0.0.0.0 ] , [ oracle.rdbms.ic, 21.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 21.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 21.0.0.0.0 ] , [ oracle.network.cman, 21.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 21.0.0.0.0 ] , [ oracle.net.cman, 21.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 21.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 21.0.0.0.0 ] , [ oracle.ons.cclient, 21.0.0.0.0 ] , [ oracle.jdk, 1.8.0.271.00 ]  not present in the Oracle Home or a higher version is found.
    
    Patching component oracle.rdbms.rsf, 21.0.0.0.0...
    
    Patching component oracle.rdbms, 21.0.0.0.0...
    
    Patching component oracle.rdbms.util, 21.0.0.0.0...
    
    Patching component oracle.network.rsf, 21.0.0.0.0...
    
    Patching component oracle.dbjava.ic, 21.0.0.0.0...
    
    Patching component oracle.dbjava.jdbc, 21.0.0.0.0...
    
    Patching component oracle.dbjava.ucp, 21.0.0.0.0...
    
    Patching component oracle.has.common.cvu, 21.0.0.0.0...
    
    Patching component oracle.javavm.server, 21.0.0.0.0...
    
    Patching component oracle.javavm.server.core, 21.0.0.0.0...
    
    Patching component oracle.ldap.owm, 21.0.0.0.0...
    
    Patching component oracle.ldap.rsf, 21.0.0.0.0...
    
    Patching component oracle.ldap.rsf.ic, 21.0.0.0.0...
    
    Patching component oracle.ldap.security.osdt, 21.0.0.0.0...
    
    Patching component oracle.nlsrtl.rsf, 21.0.0.0.0...
    
    Patching component oracle.oraml.server, 21.0.0.0.0...
    
    Patching component oracle.rdbms.crs, 21.0.0.0.0...
    
    Patching component oracle.rdbms.dbscripts, 21.0.0.0.0...
    
    Patching component oracle.rdbms.deconfig, 21.0.0.0.0...
    
    Patching component oracle.rdbms.hsodbc, 21.0.0.0.0...
    
    Patching component oracle.rdbms.install.plugins, 21.0.0.0.0...
    
    Patching component oracle.rdbms.rman, 21.0.0.0.0...
    
    Patching component oracle.rdbms.rsf.ic, 21.0.0.0.0...
    
    Patching component oracle.sdo, 21.0.0.0.0...
    
    Patching component oracle.sdo.locator.jrf, 21.0.0.0.0...
    
    Patching component oracle.sqlplus, 21.0.0.0.0...
    
    Patching component oracle.tfa.db, 21.0.0.0.0...
    
    Patching component oracle.rdbms.lbac, 21.0.0.0.0...
    
    Patching component oracle.odbc, 21.0.0.0.0...
    
    Patching component oracle.rdbms.oci, 21.0.0.0.0...
    
    Patching component oracle.ovm, 21.0.0.0.0...
    
    Patching component oracle.ctx.rsf, 21.0.0.0.0...
    
    Patching component oracle.assistants.server, 21.0.0.0.0...
    
    Patching component oracle.rdbms.dv, 21.0.0.0.0...
    
    Patching component oracle.ons.ic, 21.0.0.0.0...
    
    Patching component oracle.rdbms.hs_common, 21.0.0.0.0...
    
    Patching component oracle.xdk.rsf, 21.0.0.0.0...
    
    Patching component oracle.ctx, 21.0.0.0.0...
    
    Patching component oracle.xdk.parser.java, 21.0.0.0.0...
    
    Patching component oracle.assistants.acf, 21.0.0.0.0...
    
    Patching component oracle.assistants.deconfig, 21.0.0.0.0...
    
    Patching component oracle.rdbms.scheduler, 21.0.0.0.0...
    
    Patching component oracle.ldap.ssl, 21.0.0.0.0...
    
    Patching component oracle.network.listener, 21.0.0.0.0...
    
    Patching component oracle.ons, 21.0.0.0.0...
    
    Patching component oracle.xdk, 21.0.0.0.0...
    
    Patching component oracle.sdo.locator, 21.0.0.0.0...
    
    Patching component oracle.precomp.rsf, 21.0.0.0.0...
    
    Patching component oracle.sqlplus.ic, 21.0.0.0.0...
    
    Patching component oracle.precomp.common, 21.0.0.0.0...
    
    Patching component oracle.precomp.lang, 21.0.0.0.0...
    
    Patching component oracle.jdk, 1.8.0.291.09...
    Patch 33843745 successfully applied.
    Sub-set patch [33516412] has become inactive due to the application of a super-set patch [33843745].
    Please refer to Doc ID 2161861.1 for any possible further required actions.
    Log file location: /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2022-04-24_17-52-41PM_1.log
    
    OPatch succeeded.
    

    Please note that with Oracle Database 21c there is no separate OJVM bundle anymore needed. Everything is included into the regular RU.

  3. datapatch
    $ $ORACLE_HOME/OPatch/datapatch -verbose
    SQL Patching tool version 21.6.0.0.0 Production on Sun Apr 24 18:01:16 2022
    Copyright (c) 2012, 2022, Oracle.  All rights reserved.
    
    Log file for this invocation: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/sqlpatch_3698_2022_04_24_18_01_16/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:
      No interim patches found
    
    Current state of release update SQL patches:
      Binary registry:
        21.6.0.0.0 Release_Update 220402023231: Installed
      PDB CDB$ROOT:
        Applied 21.5.0.0.0 Release_Update 220107185933 successfully on 19-JAN-22 02.27.19.938360 PM
      PDB PDB$SEED:
        Applied 21.5.0.0.0 Release_Update 220107185933 successfully on 19-JAN-22 02.27.20.278645 PM
    
    Adding patches to installation queue and performing prereq checks...done
    Installation queue:
      For the following PDBs: CDB$ROOT
        No interim patches need to be rolled back
        Patch 33843745 (Database Release Update : 21.6.0.0.220419 (33843745)):
          Apply from 21.5.0.0.0 Release_Update 220107185933 to 21.6.0.0.0 Release_Update 220402023231
        No interim patches need to be applied
      For the following PDBs: PDB$SEED
        No interim patches need to be rolled back
        Patch 33843745 (Database Release Update : 21.6.0.0.220419 (33843745)):
          Apply from 21.5.0.0.0 Release_Update 220107185933 to 21.6.0.0.0 Release_Update 220402023231
        No interim patches need to be applied
    
    Installing patches...
    Patch installation complete.  Total patches installed: 2
    
    Validating logfiles...done
    Patch 33843745 apply (pdb CDB$ROOT): SUCCESS
      logfile: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/33843745/24723035/33843745_apply_CDB3_CDBROOT_2022Apr24_18_01_31.log (no errors)
    Patch 33843745 apply (pdb PDB$SEED): SUCCESS
      logfile: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/33843745/24723035/33843745_apply_CDB3_PDBSEED_2022Apr24_18_02_51.log (no errors)
    SQL Patching tool complete on Sun Apr 24 18:03:48 2022
    

    Don’t forget to startup the database and all PDBs at first.

  4. Enabling new optimizer fixes
    SQL*Plus: Release 21.0.0.0.0 - Production on Sun Apr 24 18:37:44 2022
    Version 21.6.0.0.0
    
    Copyright (c) 1982, 2022, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
    Version 21.6.0.0.0
    
    SQL> set serveroutput on;
    SQL> execute dbms_optim_bundle.getBugsforBundle;
    
    21.6.0.0.220419DBRU:
        Bug: 32856375,  fix_controls: 32856375
        Bug: 33297275,  fix_controls: 33297275
        Bug: 33323903,  fix_controls: 33323903
        Bug: 32302470,  fix_controls: 32302470
        Bug: 32851615,  fix_controls: 32851615
        Bug: 32754044,  fix_controls: 32754044
    
    PL/SQL procedure successfully completed.
    
    SQL> execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')
    DBMS_OPTIM command:  dbms_optim_bundle.enable_optim_fixes('ON', 'BOTH', 'YES')
    
    Bundle's _fix_control setting as per action:ON
    31988833:1  32800137:0	32408640:1  29738374:1	33325981:1  32913527:0
    32766397:0  31912834:1	33145153:1  31843716:0	32212062:0  31880080:0
    32856375:1  33297275:1	33323903:1  32302470:1	32851615:1  32754044:1
    
    Taking current instance CDB3 as base, details on _fix_control setting for
    CON_ID 1 :
    
    1) Current _fix_control setting for spfile:
    31988833:1  32800137:0	32408640:1  29738374:1	33325981:1  32913527:0
    32766397:0  31912834:1	33145153:1  31843716:0	32212062:0  31880080:0
    
    2) Final _fix_control setting for spfile considering current_setting_precedence
    is YES
    31988833:1  32800137:0	32408640:1  29738374:1	33325981:1  32913527:0
    32766397:0  31912834:1	33145153:1  31843716:0	32212062:0  31880080:0
    32856375:1  33297275:1	33323903:1  32302470:1	32851615:1  32754044:1
    
    3) Current _fix_control setting in memory:
    31988833:1  32800137:0	32408640:1  29738374:1	33325981:1  32913527:0
    32766397:0  31912834:1	33145153:1  31843716:0	32212062:0  31880080:0
    32856375:0  33297275:0	33323903:0  32302470:0	32851615:0  32754044:0
    
    4) Final _fix_control setting for memory considering current_setting_precedence
    is YES
    31988833:1  32800137:0	32408640:1  29738374:1	33325981:1  32913527:0
    32766397:0  31912834:1	33145153:1  31843716:0	32212062:0  31880080:0
    32856375:0  33297275:0	33323903:0  32302470:0	32851615:0  32754044:0
    
    WARNING: final _fix_control setting for memory is not same as final
    _fix_control setting for spfile. Please look at point 2 and 4 above to see the
    differences.
    
    PL/SQL procedure successfully completed.
    

    New optimizer fixes are there for a reason. But it is your choice whether you’d like to enable them after patching, or keep them disabled. Just make sure when you upgrade or create a new database to always enable them.
    .

Applying RU 19.15.0 to my 19c home

Next step is patching my 19c environment. But since my last patching attempts with 19.13.0 and 19.14.0 both took an incredible long time due to unknown reasons, I will try a workaround a customer shared with me.

In my first attempt, opatch – even in the most recent version 30 – spend more than 5 minutes on the prerequisites check. And since it does this twice, it wastes 10 minutes of my precious time on a Sunday evening.

But Balaji Govindu documented a fully unsupported workaround and edited $ORACLE_HOME/inventory/ContentsXML/oui-patch.xml. This file contains the patch history and all bug fixes included in EVERY patch you’ve applied to this home.

I need to clarify two things at this point:

  • If you do out-of-place patching with a new home, you won’t be affected since your new home has “no history”
  • It is not officially supported to edit this file as you can clearly read in the file header

But hey, I like experiments. And since the result is remarkable, I will publish a blog post explaining this in more detail in a few days. But kudos to Balaji for pointing me to his hack.

  1. Conflict and space check
    Flawless.
  2. opatch apply

    $ $ORACLE_HOME/OPatch/opatch apply
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    
    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.30
    OUI version       : 12.2.0.7.0
    Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2022-04-24_21-57-54PM_1.log
    
    Verifying environment and performing prerequisite checks...
    
    --------------------------------------------------------------------------------
    Start OOP by Prereq process.
    Launch OOP...
    
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    
    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.30
    OUI version       : 12.2.0.7.0
    Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2022-04-24_21-58-23PM_1.log
    
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   33806152  
    
    Do you want to proceed? [y|n]
    y
    User Responded with: Y
    All checks passed.
    
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/u01/app/oracle/product/19')
    
    
    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files...
    Applying interim patch '33806152' to OH '/u01/app/oracle/product/19'
    ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ]  not present in the Oracle Home or a higher version is found.
    
    Patching component oracle.bali.ewt, 11.1.1.6.0...
    
    Patching component oracle.help.ohj, 11.1.1.7.0...
    
    ... CUT ...
    
    Patching component oracle.precomp.common, 19.0.0.0.0...
    
    Patching component oracle.precomp.lang, 19.0.0.0.0...
    
    Patching component oracle.jdk, 1.8.0.201.0...
    Patch 33806152 successfully applied.
    Sub-set patch [33515361] has become inactive due to the application of a super-set patch [33806152].
    Please refer to Doc ID 2161861.1 for any possible further required actions.
    Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2022-04-24_21-58-23PM_1.log
    
    OPatch succeeded.
    

    After my tiny modification, it completed very fast.

  3. OJVM patch bundle

    $ $ORACLE_HOME/OPatch/opatch apply
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    
    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.30
    OUI version       : 12.2.0.7.0
    Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2022-04-24_22-16-17PM_1.log
    
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   33808367  
    
    Do you want to proceed? [y|n]
    y
    User Responded with: Y
    All checks passed.
    
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/u01/app/oracle/product/19')
    
    
    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files...
    Applying interim patch '33808367' to OH '/u01/app/oracle/product/19'
    
    Patching component oracle.javavm.server, 19.0.0.0.0...
    
    Patching component oracle.javavm.server.core, 19.0.0.0.0...
    
    Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
    
    Patching component oracle.rdbms, 19.0.0.0.0...
    
    Patching component oracle.javavm.client, 19.0.0.0.0...
    Patch 33808367 successfully applied.
    Sub-set patch [33561310] has become inactive due to the application of a super-set patch [33808367].
    Please refer to Doc ID 2161861.1 for any possible further required actions.
    Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2022-04-24_22-16-17PM_1.log
    
    OPatch succeeded.
    

    Flawless again. And really required in this cycle as you saw in the risk matrix.

  4. datapatch

    $ $ORACLE_HOME/OPatch/datapatch -verbose
    SQL Patching tool version 19.15.0.0.0 Production on Sun Apr 24 22:20:00 2022
    Copyright (c) 2012, 2022, Oracle.  All rights reserved.
    
    Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12385_2022_04_24_22_20_00/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: Applied successfully on 19-JAN-22 10.14.44.331372 PM
      PDB PDB$SEED: Applied successfully on 19-JAN-22 10.14.44.350816 PM
    Interim patch 33808367 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367)):
      Binary registry: Installed
      PDB CDB$ROOT: Not installed
      PDB PDB$SEED: Not installed
    
    Current state of release update SQL patches:
      Binary registry:
        19.15.0.0.0 Release_Update 220331125408: Installed
      PDB CDB$ROOT:
        Applied 19.14.0.0.0 Release_Update 211225122123 successfully on 19-JAN-22 09.48.19.540225 PM
      PDB PDB$SEED:
        Applied 19.14.0.0.0 Release_Update 211225122123 successfully on 19-JAN-22 09.48.19.756424 PM
    
    Adding patches to installation queue and performing prereq checks...done
    Installation queue:
      For the following PDBs: CDB$ROOT PDB$SEED
        The following interim patches will be rolled back:
          33561310 (OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310))
        Patch 33806152 (Database Release Update : 19.15.0.0.220419 (33806152)):
          Apply from 19.14.0.0.0 Release_Update 211225122123 to 19.15.0.0.0 Release_Update 220331125408
        The following interim patches will be applied:
          33808367 (OJVM RELEASE UPDATE: 19.15.0.0.220419 (33808367))
    
    Installing patches...
    Patch installation complete.  Total patches installed: 6
    
    Validating logfiles...done
    Patch 33561310 rollback (pdb CDB$ROOT): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33561310/24538862/33561310_rollback_CDB2_CDBROOT_2022Apr24_22_20_32.log (no errors)
    Patch 33806152 apply (pdb CDB$ROOT): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB2_CDBROOT_2022Apr24_22_21_09.log (no errors)
    Patch 33808367 apply (pdb CDB$ROOT): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33808367/24680225/33808367_apply_CDB2_CDBROOT_2022Apr24_22_21_09.log (no errors)
    Patch 33561310 rollback (pdb PDB$SEED): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33561310/24538862/33561310_rollback_CDB2_PDBSEED_2022Apr24_22_21_59.log (no errors)
    Patch 33806152 apply (pdb PDB$SEED): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33806152/24713297/33806152_apply_CDB2_PDBSEED_2022Apr24_22_21_59.log (no errors)
    Patch 33808367 apply (pdb PDB$SEED): SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33808367/24680225/33808367_apply_CDB2_PDBSEED_2022Apr24_22_21_59.log (no errors)
    SQL Patching tool complete on Sun Apr 24 22:22:40 2022

    No issues.

  5. Optimizer fixes
    SQL> set serverout on
    SQL> execute dbms_optim_bundle.getBugsforBundle;
    
    19.15.0.0.220419DBRU:
        Bug: 31050103,  fix_controls: 31050103
        Bug: 30018126,  fix_controls: 30018126
        Bug: 33303725,  fix_controls: 33303725
        Bug: 32856375,  fix_controls: 32856375
        Bug: 32754044,  fix_controls: 32754044
        Bug: 33297275,  fix_controls: 33297275
        Bug: 32851615,  fix_controls: 32851615
        Bug: 32302470,  fix_controls: 32302470
        Bug: 27825962,  fix_controls: 27825962
        Bug: 33323903,  fix_controls: 33323903
        Bug: 31162457,  fix_controls: 31162457
        Bug: 31843716,  fix_controls: 31843716
        Bug: 33961800,  fix_controls: 32119144
    
    PL/SQL procedure successfully completed.
    
    SQL> execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')

    All set now.
    .

Applying BP 12.1.0.1.220419 to my 12.1 home

As I mentioned before, the patch download is huge: 7.9GB in total.Or 14.6GB unzipped. That’s a lot larger than the 3.2GB for the base release Enterprise Edition image. I’m wondering whether it be easier just to produce a gold image instead of a patch download in this case.

Actually when I unzipped the full archive it dawned me that it just accumulates all existing BPs so far with a ton of duplication. No idea how many TFA subdirectories I saw flying by in my unzip session. What a weird way to build a patch.

These are the subdirectories directly under the root patch:

26983807  32758932  33112931  33711072  automation  bundle.xml  README.html  README.txt

Mapping this to the README tells me that I’ve got not only the Database BP but also an OCW PSU from July 2021 (1.6GB) , an ACFS PSU from July 2021 (4.1GB !!), too, and – very interesting – a DBWLM (if you wonder, this is Workload Management now known as QoS – Quality of Service, a RAC component) from January 2018 (!!). Please DON’T ask me why these things are bundled to a database BP from 2022. Open an SR if you have such questions please.

  1. Conflict and space check
    $ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    PREREQ session
    
    Oracle Home       : /u01/app/oracle/product/12.1.0.2
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
    OPatch version    : 12.2.0.1.30
    OUI version       : 12.1.0.2.0
    Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2022-04-24_20-04-07PM_1.log
    
    Invoking prereq "checkconflictagainstohwithdetail"
    
    Prereq "checkConflictAgainstOHWithDetail" passed.
    
    OPatch succeeded.
    [V121] oracle@hol:/u03/33880550/33711072
    $ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -ph ./
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    PREREQ session
    
    Oracle Home       : /u01/app/oracle/product/12.1.0.2
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
    OPatch version    : 12.2.0.1.30
    OUI version       : 12.1.0.2.0
    Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2022-04-24_20-04-24PM_1.log
    
    Invoking prereq "checksystemspace"
    
    Prereq "checkSystemSpace" passed.
    
    OPatch succeeded.

    Since I use the modern opatch, the commands from the README are just obsolete – you can certainly use the more modern and much shorter checks I use for my 19c and 21c bundles, too.

  2. patch apply
    $ $ORACLE_HOME/OPatch/opatch apply
    Oracle Interim Patch Installer version 12.2.0.1.30
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home       : /u01/app/oracle/product/12.1.0.2
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
    OPatch version    : 12.2.0.1.30
    OUI version       : 12.1.0.2.0
    Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2022-04-24_20-05-56PM_1.log
    
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   33711072  
    
    ...
    
    Composite patch 33711072 successfully applied.
    Log file location: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2022-04-24_20-05-56PM_1.log
    
    OPatch succeeded.

    Works.

  3. datapatch
    $ $ORACLE_HOME/OPatch/datapatch -verbose
    SQL Patching tool version 12.1.0.2.0 Production on Sun Apr 24 20:07:47 2022
    Copyright (c) 2012, 2017, Oracle.  All rights reserved.
    
    Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_19940_2022_04_24_20_07_47/sqlpatch_invocation.log
    
    Connecting to database...OK
    Bootstrapping registry and package to current versions...done
    Determining current state...done
    
    Current state of SQL patches:
    Bundle series DBBP:
      ID 220419 in the binary registry and not installed in the SQL registry
    
    Adding patches to installation queue and performing prereq checks...
    Installation queue:
      Nothing to roll back
      The following patches will be applied:
        33711072 (DATABASE BUNDLE PATCH 12.1.0.2.220419)
    
    Installing patches...
    Patch installation complete.  Total patches installed: 1
    
    Validating logfiles...
    Patch 33711072 apply: SUCCESS
      logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33711072/24717745/33711072_apply_V121_2022Apr24_20_08_02.log (no errors)
    SQL Patching tool complete on Sun Apr 24 20:09:23 2022

    Ok.

  4. Enabling optimizer fixes
    SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 24 20:10:39 2022
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> set serveroutput on;
    SQL> execute dbms_optim_bundle.getBugsforBundle;
    
    12.1.0.2.220419DBBP:
        Bug: 25575369,  fix_controls: 25575369
    
    PL/SQL procedure successfully completed.
    
    SQL> execute dbms_optim_bundle.enable_optim_fixes('ON','BOTH', 'YES')
    DBMS_OPTIM command:  dbms_optim_bundle.enable_optim_fixes('ON', 'BOTH', 'YES')
    
    Bundle's _fix_control setting as per action:ON
    21971099:1  22159570:1	21802552:1  21509656:1	21099502:1  22518491:1
    19475484:1  22258300:1	22077191:1  22123025:1	20243268:1  16732417:1
    26664361:0  21833220:1	20129763:1  19563657:1	18876528:1  18558952:1
    18302923:1  18182018:1	17973658:1  23197730:1	25476149:1  22090662:1
    24010030:1  19507904:1	20270511:1  22533539:1	28072567:1  24926999:1
    24745366:1  25405100:1	18969167:1  20923950:1	23136865:1  23738553:1
    21273039:1  24841671:1	23738304:1  18795224:1	19847091:1  22174392:1
    22540411:0  22272439:1	23102649:1  25643889:0	14402409:1  8932139:1
    31310771:0  20636003:0	23596611:1  29867728:1	23473108:1  28173995:1
    30786641:1  26270685:0	31444353:0  19498595:1	18907562:1  25575369:1
    
    Taking current instance V121 as base, details on _fix_control setting for
    CON_ID 0 :
    
    1) Current _fix_control setting for spfile:
    None
    
    2) Final _fix_control setting for spfile considering current_setting_precedence
    is YES
    21971099:1  22159570:1	21802552:1  21509656:1	21099502:1  22518491:1
    19475484:1  22258300:1	22077191:1  22123025:1	20243268:1  16732417:1
    26664361:0  21833220:1	20129763:1  19563657:1	18876528:1  18558952:1
    18302923:1  18182018:1	17973658:1  23197730:1	25476149:1  22090662:1
    24010030:1  19507904:1	20270511:1  22533539:1	28072567:1  24926999:1
    24745366:1  25405100:1	18969167:1  20923950:1	23136865:1  23738553:1
    21273039:1  24841671:1	23738304:1  18795224:1	19847091:1  22174392:1
    22540411:0  22272439:1	23102649:1  25643889:0	14402409:1  8932139:1
    31310771:0  20636003:0	23596611:1  29867728:1	23473108:1  28173995:1
    30786641:1  26270685:0	31444353:0  19498595:1	18907562:1  25575369:1
    
    3) Current _fix_control setting in memory:
    21971099:0  22159570:0	21802552:0  21509656:0	21099502:0  22518491:0
    19475484:0  22258300:0	22077191:0  22123025:0	20243268:0  16732417:0
    26664361:0  21833220:0	20129763:0  19563657:0	18876528:0  18558952:0
    18302923:0  18182018:0	17973658:0  23197730:0	25476149:0  22090662:0
    24010030:0  19507904:0	20270511:0  22533539:0	28072567:0  24926999:0
    24745366:0  25405100:0	18969167:0  20923950:0	23136865:0  23738553:0
    21273039:0  24841671:0	23738304:0  18795224:0	19847091:0  22174392:0
    22540411:0  22272439:0	23102649:0  25643889:0	14402409:0  8932139:0
    31310771:0  20636003:0	23596611:0  29867728:0	23473108:0  28173995:0
    30786641:0  26270685:0	31444353:0  19498595:0	18907562:0  25575369:0
    
    4) Final _fix_control setting for memory considering current_setting_precedence
    is YES
    21971099:0  22159570:0	21802552:0  21509656:0	21099502:0  22518491:0
    19475484:0  22258300:0	22077191:0  22123025:0	20243268:0  16732417:0
    26664361:0  21833220:0	20129763:0  19563657:0	18876528:0  18558952:0
    18302923:0  18182018:0	17973658:0  23197730:0	25476149:0  22090662:0
    24010030:0  19507904:0	20270511:0  22533539:0	28072567:0  24926999:0
    24745366:0  25405100:0	18969167:0  20923950:0	23136865:0  23738553:0
    21273039:0  24841671:0	23738304:0  18795224:0	19847091:0  22174392:0
    22540411:0  22272439:0	23102649:0  25643889:0	14402409:0  8932139:0
    31310771:0  20636003:0	23596611:0  29867728:0	23473108:0  28173995:0
    30786641:0  26270685:0	31444353:0  19498595:0	18907562:0  25575369:0
    
    WARNING: final _fix_control setting for memory is not same as final
    _fix_control setting for spfile. Please look at point 2 and 4 above to see the
    differences.
    
    PL/SQL procedure successfully completed.
    

    One new behavior changing optimizer fix in this BP delivered.

That’s it. Looks fine.
.

Applying PSU 11.2.0.4.220419 to my 11.2 home

Next step is patching my 11.2.0.4 environment.

  1. Conflict and space checks

    $ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
    Oracle Interim Patch Installer version 11.2.0.3.34
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    PREREQ session
    
    Oracle Home       : /u01/app/oracle/product/11.2.0.4
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
    OPatch version    : 11.2.0.3.34
    OUI version       : 11.2.0.4.0
    Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2022-04-24_19-13-08PM_1.log
    
    Invoking prereq "checkconflictagainstohwithdetail"
    
    Prereq "checkConflictAgainstOHWithDetail" passed.
    
    OPatch succeeded.
    

     

  2. opatch apply

    $ $ORACLE_HOME/OPatch/opatch apply
    Oracle Interim Patch Installer version 11.2.0.3.34
    Copyright (c) 2022, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home       : /u01/app/oracle/product/11.2.0.4
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/11.2.0.4/oraInst.loc
    OPatch version    : 11.2.0.3.34
    OUI version       : 11.2.0.4.0
    Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2022-04-24_19-14-05PM_1.log
    
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   33711103  
    
    Do you want to proceed? [y|n]
    y
    User Responded with: Y
    All checks passed.
    
    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
    (Oracle Home = '/u01/app/oracle/product/11.2.0.4')
    
    
    Is the local system ready for patching? [y|n]
    y
    User Responded with: Y
    Backing up files...
    Applying sub-patch '33711103' to OH '/u01/app/oracle/product/11.2.0.4'
    ApplySession: Optional component(s) [ oracle.rdbms.tg4tera, 11.2.0.4.0 ] , [ oracle.rdbms.tg4sybs, 11.2.0.4.0 ] , [ oracle.rdbms.tg4msql, 11.2.0.4.0 ] , [ oracle.rdbms.tg4ifmx, 11.2.0.4.0 ] , [ oracle.rdbms.tg4db2, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.
    
    Patching component oracle.rdbms.rsf, 11.2.0.4.0...
    
    Patching component oracle.rdbms, 11.2.0.4.0...
    
    Patching component oracle.rdbms.rsf, 11.2.0.4.0...
    
    Patching component oracle.network.rsf, 11.2.0.4.0...
    
    Patching component oracle.rdbms.hsodbc, 11.2.0.4.0...
    
    Patching component oracle.rdbms.hs_common, 11.2.0.4.0...
    
    Patching component oracle.buildtools.rsf, 11.2.0.4.0...
    
    Patching component oracle.buildtools.rsf, 11.2.0.4.0...
    Composite patch 33711103 successfully applied.
    Log file location: /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2022-04-24_19-14-05PM_1.log
    
    OPatch succeeded.
    

     

  3. catbundle.sql in all my 11g databases
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 1152450560 bytes
    Fixed Size		    2252584 bytes
    Variable Size		  335544536 bytes
    Database Buffers	  805306368 bytes
    Redo Buffers		    9347072 bytes
    Database mounted.
    Database opened.
    SQL> @catbundle.sql psu apply
    
    PL/SQL procedure successfully completed.
    
    Function created.
    
    Function created.
    
    PL/SQL procedure successfully completed.
    
    PL/SQL procedure successfully completed.
    
    
    Generating apply and rollback scripts...
    Check the following file for errors:
    /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_FTEX_GENERATE_2022Apr24_19_15_53.log
    Apply script: /u01/app/oracle/product/11.2.0.4/rdbms/admin/catbundle_PSU_FTEX_APPLY.sql
    Rollback script: /u01/app/oracle/product/11.2.0.4/rdbms/admin/catbundle_PSU_FTEX_ROLLBACK.sql
    
    PL/SQL procedure successfully completed.
    
    Executing script file...
    
    SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
    SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
    
    
    SQL> SPOOL &spool_file
    SQL> exec sys.dbms_registry.set_session_namespace('SERVER')
    
    PL/SQL procedure successfully completed.
    
    SQL> PROMPT Skipping Oracle Text because it is not installed or versions mismatch...
    Skipping Oracle Text because it is not installed or versions mismatch...
    SQL> PROMPT Skipping Oracle Database Vault because it is not installed or versions mismatch...
    Skipping Oracle Database Vault because it is not installed or versions mismatch...
    SQL> PROMPT Skipping Oracle XML Database because it is not installed or versions mismatch...
    Skipping Oracle XML Database because it is not installed or versions mismatch...
    SQL> PROMPT Skipping OLAP Analytic Workspace because it is not installed or versions mismatch...
    Skipping OLAP Analytic Workspace because it is not installed or versions mismatch...
    SQL> PROMPT Skipping Oracle Database Vault because it is not installed or versions mismatch...
    Skipping Oracle Database Vault because it is not installed or versions mismatch...
    SQL> PROMPT Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
    Skipping Oracle Workspace Manager because it is not installed or versions mismatch...
    SQL> PROMPT Skipping Spatial because it is not installed or versions mismatch...
    Skipping Spatial because it is not installed or versions mismatch...
    SQL> ALTER SESSION SET current_schema = SYS;
    
    Session altered.
    
    SQL> PROMPT Updating registry...
    Updating registry...
    SQL> INSERT INTO registry$history
      2    (action_time, action,
      3     namespace, version, id,
      4     bundle_series, comments)
      5  VALUES
      6    (SYSTIMESTAMP, 'APPLY',
      7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
      8     '11.2.0.4',
      9     220419,
     10     'PSU',
     11     'PSU 11.2.0.4.220419');
    
    1 row created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> SPOOL off
    SQL> SET echo off
    Check the following log file for errors:
    /u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_FTEX_APPLY_2022Apr24_19_15_53.log

    No errors this time.

Your patch is not available right now?

In case you miss the patch bundle for your release and platform, please read this blog post.

Finally … clean up

Oh yes, my patching experience doesn’t get easier every time unfortunately. Since opatch does still not allow to define a “n-1” rule or pattern meaning “remove everything from my box except the previous patch bundle” I will need to do the cleanup by myself.

See here on how to do the cleanup of older bundles to free up space:

Further Links and Information

–Mike

Share this: