Mid-of January. It’s patching time again. So let me show you the quarterly exercise of Patching my environments with the January 2023 Bundle Patches. Let’s see this time if I will have to remove my Data Pump Bundle Patch and the 19.17.0 MRP2 at first when I patch in-place.

Photo by karokrasinska on Unsplash
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 January 2023
Let me start with the Security Alert and the Database Server Risk Matrix for January 2023.

Database Server Risk Matrix – Jan 2023
VERY important is the column Supported Versions Affected. You will spot only Oracle 19c and Oracle 21c there. Now that does not mean that any of these issues may not exist in previous versions as well, for instance in your Oracle 10.2.0.4, Oracle 11.2.0.4 or Oracle 12.1.0.2 databases. It just means that those systems aren’t under any standard support agreement anymore, so they aren’t listed. In reverse, you can almost always assume that something happening in 19c with a very high probability is happening in the previous releases, too.
In case you haven’t realized by now: It’s time to upgrade to Oracle Database 19c.
Database Patch Bundles January 2023
I will start with a quick overview about the patch bundles I will apply to my 19c and 21c environments. You’ll find them via MOS Note: 2906899.1 – Critical Patch Update (CPU) Program Jan 2023 Patch Availability Document (DB-only). In case you read this in late January 2023, please pay attention to the fact that the 19c bundles for Database and Grid Infrastructure have been removed temporarily. Read Important alert for Database and Grid Infrastructure 19.18.0.
- Oracle Database 21c
- Database Release Update 21.9.0.0.230117 Patch 34839741 for UNIX
- README
- List of fixes: MOS Note: 2814015.1
- Contents: 61 new fixes on top of 21.8.0
- Database Release Update 21.9.0.0.230117 Patch 34839741 for UNIX
- Oracle Database 19c
- Database Release Update 19.18.0.0.230117 Patch 34765931 for UNIX
- README
- List of fixes: MOS Note: 2523220.1
- Contents: 718 new fixes on top of 19.17.0
- Please see also when you use RAC: Important Alert for Oracle 19.18.0 for Database and Grid Infrastructure
- OJVM Release Update 19.18.0.0.230117 Patch 34786990 for all platforms
- README
- Contents: 49 new fixes on top of OJVM 19.17.0
- Data Pump Bundle Patch on top of 19.18.0 Patch 34972375 for all platforms
- README
- Contents: 24 new fixes
- Database Release Update 19.18.0.0.230117 Patch 34765931 for UNIX
- Oracle Database 12.1.0.2
- Since this release is under MDS (Market Driven Support), the bundles are only available to customers with ES contracts. Hence, I will apply it but won’t blog about it anymore. But I will mention if anything unusual happens.
- Oracle Database 11.2.0.4
- Since this release is under MDS (Market Driven Support), the bundles are only available to customers with MDS contracts. Hence, I will apply it but won’t blog about it anymore. But I will mention if anything unusual happens.
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 30 version on all platforms. But let me double check with the READMEs:
- 21.9.0 DBRU requires opatch 12.2.0.1.34 or later
- 19.18.0 DBRU requires opatch 12.2.0.1.33 or later
- 19.18.0 OJVM requires opatch 12.2.0.1.34 or later
My current version is opatch 33.
$ $OH19/OPatch/opatch version OPatch Version: 12.2.0.1.33 OPatch succeeded.
Hence, I will update opatch in all my homes. The 6880880 link from the readmes takes you directly to the correct download. With this download I will get opatch 36. It may not wonder you, all 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.
Hence, action no.1 for all my environments before I will start patching:
- Either wipe out the current
OPatch
directory in each home, then unzip the new OPatch bundles – or use unzip -o option instead
Applying the RU 21.9.0 to my Oracle 21c home
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.9.0 is again pretty straight forward.
-
Conflict check
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.36 Copyright (c) 2023, 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.36 OUI version : 12.2.0.9.0 Log file location : /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2023-01-23_22-04-04PM_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.36 Copyright (c) 2023, 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.36 OUI version : 12.2.0.9.0 Log file location : /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2023-01-23_22-12-25PM_1.log Invoking prereq "checksystemspace" Prereq "checkSystemSpace" passed. OPatch succeeded.
- Applying the 21.9.0 Release Update
$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.36 Copyright (c) 2023, 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.36 OUI version : 12.2.0.9.0 Log file location : /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2023-01-23_22-13-42PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 34839741 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 '34839741' 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.rdbms.ic, 21.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 21.0.0.0.0 ] , [ oracle.network.cman, 21.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 21.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 21.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 21.0.0.0.0 ] , [ oracle.oraolap.mgmt, 21.0.0.0.0 ] , [ oracle.duma, 21.0.0.0.0 ] , [ oracle.oid.client, 21.0.0.0.0 ] , [ oracle.net.cman, 21.0.0.0.0 ] , [ oracle.sdo.companion, 21.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 21.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 21.0.0.0.0 ] , [ oracle.rdbms.tg4ifxm, 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.util, 21.0.0.0.0... Patching component oracle.rdbms, 21.0.0.0.0... Patching component oracle.network.rsf, 21.0.0.0.0... Patching component oracle.blaslapack, 21.0.0.0.0... Patching component oracle.rdbms.install.common, 21.0.0.0.0... ... Patching component oracle.rdbms.oci, 21.0.0.0.0... Patching component oracle.rdbms.scheduler, 21.0.0.0.0... Patching component oracle.ons.ic, 21.0.0.0.0... Patching component oracle.ovm, 21.0.0.0.0... Patching component oracle.ctx, 21.0.0.0.0... Patching component oracle.xdk, 21.0.0.0.0... Patching component oracle.ctx.atg, 21.0.0.0.0... Patching component oracle.xdk.parser.java, 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 34839741 successfully applied. Sub-set patch [34527084] has become inactive due to the application of a super-set patch [34839741]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /u01/app/oracle/product/21/cfgtoollogs/opatch/opatch2023-01-23_22-13-42PM_1.log OPatch succeeded.
-
Invoking datapatch
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Jan 23 22:23:47 2023 Version 21.9.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 1845489680 bytes Fixed Size 9687056 bytes Variable Size 536870912 bytes Database Buffers 1291845632 bytes Redo Buffers 7086080 bytes Database mounted. Database opened. SQL> alter pluggable database all open; Pluggable database altered. SQL> exit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.9.0.0.0 $ $ORACLE_HOME/OPatch/datapatch -verbose SQL Patching tool version 21.9.0.0.0 Production on Mon Jan 23 22:24:36 2023 Copyright (c) 2012, 2023, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/sqlpatch_5193_2023_01_23_22_24_36/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.9.0.0.0 Release_Update 230111084300: Installed PDB CDB$ROOT: Applied 21.8.0.0.0 Release_Update 220930172918 successfully on 09-NOV-22 12.31.29.454902 PM PDB PDB$SEED: Applied 21.8.0.0.0 Release_Update 220930172918 successfully on 09-NOV-22 12.31.29.802721 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 34839741 (Database Release Update : 21.9.0.0.230117 (34839741)): Apply from 21.8.0.0.0 Release_Update 220930172918 to 21.9.0.0.0 Release_Update 230111084300 No interim patches need to be applied For the following PDBs: PDB$SEED No interim patches need to be rolled back Patch 34839741 (Database Release Update : 21.9.0.0.230117 (34839741)): Apply from 21.8.0.0.0 Release_Update 220930172918 to 21.9.0.0.0 Release_Update 230111084300 No interim patches need to be applied Installing patches... Patch installation complete. Total patches installed: 2 Validating logfiles...done Patch 34839741 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/34839741/25076790/34839741_apply_CDB3_CDBROOT_2023Jan23_22_24_53.log (no errors) Patch 34839741 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/34839741/25076790/34839741_apply_CDB3_PDBSEED_2023Jan23_22_26_01.log (no errors) SQL Patching tool complete on Mon Jan 23 22:26:54 2023
-
Enabling new optimizer fixes
Let me clarify beforehand: You don’t have to enable the fixes, especially when you patch. You should definitely enable the fixes when you upgrade, migrate or deploy a new database. But patching is different. And I would recommend careful testing before you do this, going for instance from 19.15.0 to 19.17.0.
In the previous apply process, there was an issue with the fixes listed – and I filed Bug 34782416 – AFTER MOVING TO RU 21.8.0 DBMS_OPTIM_BUNDLE STILL LISTS THE 21.7.0 FIXES for it.And as you can see below, the problem still persists and hasn’t been fixed on 21.9.0. But I received a confirmation from the developer that it is fixed in 21.10.0, the April 2023 RU for Oracle Database 21c.
SQL> set serveroutput on; SQL> execute dbms_optim_bundle.getBugsforBundle; 21.7.0.0.220719DBRU: Bug: 30771009, fix_controls: 30771009 Bug: 29413205, fix_controls: 29413205 Bug: 28044739, fix_controls: 28044739 Bug: 33089096, fix_controls: 31545400 PL/SQL procedure successfully completed.
Seems like nobody is turning on fixes in 21c.
Applying RU and OJVM 19.18.0 January 2023 to my 19c home
Next exercise is my 19c home, the one I am using the most often. And here I have also OJVM installed to reproduce several cases crossing my radar from time to time. And there is one important thing to take care on for me. Since I patch in-place, I have the MRP2 for 19.17.0 in my home, and the Data Pump Bundle Patch for 19.17.0 as well. Hence, the precheck is quite important. I need to understand whether I will have to remove a patch beforehand.
Please note that this does not affect you at all when you patch out-of-place into a new home if you provision the home from scratch. You need to take care only in case you patch in-place or when you clone your existing home and apply then 19.18.0 to it. This is why I highly recommend to provison a new home from scratch. The OUI with -applyRU and -applyOneOffs is your best friend here (unless you are on MS Windows).
-
Prechecks
Without tweaking my oui-patch.xml as in the previous quarter with this workaround, this part is very slow of course. It gets worse with every patch bundle. Since the opatch documentation usually is not very informative about improvements, I can’t tell you whether the fixes they’d like to do are in, or not. I’d guess, at least for this part of the patching process, there aren’t any improvements I could see/feel in my environment. The opatch team has committed to deliver a solution. And I keep the faith that this will happen soon. ETA should be sometime in Spring 2023. Let’s keep our fingers crossed.
But back to my current home. The precheck should tell me about the conflict.
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.36 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.36 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-23_22-47-24PM_1.log Invoking prereq "checkconflictagainstohwithdetail" ZOP-47: The patch(es) has supersets with other patches installed in the Oracle Home (or) among themselves. Prereq "checkConflictAgainstOHWithDetail" failed. The details are: Reason - Superset Patch 34765931 has Subset Patch 34419443 which has overlay patches [34734035] and these overlay patches conflict with Superset Patch Subset Patch 34419443 which has overlay patches [34734035] and these overlay patches conflict with Superset Patch OPatch recommends any one of the following actions - Please rebuild the superset patch to make sure it supersedes all the relevent patch(es) which are conflicting. The rebuilt patch should contain bug fix [34050156, 31314885, ... now an incredibly long list of +7000 patch numbers follows ... , 29013832, 31531079]. - Please rollback the relevant overlay patches of the subset patches which are conflicting and apply the superset patch Summary of Conflict Analysis: There are no patches that can be applied now. Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches : 34734035, 34765931 Conflicts/Supersets for each patch are: Patch : 34765931 Conflict with 34734035 Conflict details: /u01/app/oracle/product/19/rdbms/admin/catmetviews.sql /u01/app/oracle/product/19/rdbms/admin/catpdeps.sql /u01/app/oracle/product/19/rdbms/xml/xsl/kutable.xsl /u01/app/oracle/product/19/rdbms/admin/catpspec.sql /u01/app/oracle/product/19/rdbms/admin/sqlfiles.xml /u01/app/oracle/product/19/rdbms/xml/xsl/kucommon.xsl /u01/app/oracle/product/19/rdbms/admin/catnomta.sql /u01/app/oracle/product/19/rdbms/admin/c18.sql /u01/app/oracle/product/19/rdbms/admin/catmetinsert.sql /u01/app/oracle/product/19/rdbms/admin/catdwgrd.sql /u01/app/oracle/product/19/rdbms/admin/catmettypes.sql /u01/app/oracle/product/19/rdbms/admin/prvtbpm.plb /u01/app/oracle/product/19/rdbms/xml/xsl/kulob.xsl /u01/app/oracle/product/19/rdbms/admin/catmetloadxsl.sql /u01/app/oracle/product/19/rdbms/xml/xsl/kuconstr.xsl /u01/app/oracle/product/19/rdbms/admin/catmetgrant1.sql /u01/app/oracle/product/19/rdbms/admin/catmet2.sql /u01/app/oracle/product/19/rdbms/xml/xsl/kustbphy.xsl /u01/app/oracle/product/19/rdbms/admin/prvtbpf.plb /u01/app/oracle/product/19/rdbms/admin/prvtbpui.plb /u01/app/oracle/product/19/rdbms/admin/prvthpui.plb OPatch succeeded.
This is a terrible output if you’d ask me. Instead of adding the patch name it only displays a number. So I need to lookup at first the numbers in MOS to understand which one conflict with which patch.
— 34765931 is the RU 19.18.0
— 34734035 is the Data Pump BP on top of 19.17.0
— 34419443 is the RU 19.17.0According to the output above, the 19.18.0 RU conflicts ONLY with the DPBP for 19.17.0. In my humble logic, I would expect it the other way round, or at least make it more obvious. But you potentially are already more experienced and used to it. I will learn to read it the right way as well.
Why does the MRP2 not raise a conflict warning? I have no idea. I would have expected that I’ll have to remove MRP2 as well. But that is not the case.
In essence, this means that I need to remove the Data Pump Bundle Patch for 19.17.0 before I can attempt installing the 19.18.0 RU.
Of course, this would not be the case when I’d patch out-of place into a new home. And it clearly shows as well why you should not patch in-place. In a live environment, this would mean that the entire time of the following actions my home is not accessible. That’s really bad.
- Removal of tthe Data Pump Bundle Patch for 19.17.0I accidentally specified two patches below – and 34734035 would have been enough as you can see in the check afterwards. But this way you see the -nrollback command in action as well.
$ $ORACLE_HOME/OPatch/opatch nrollback -id 34734035,34765931 Oracle Interim Patch Installer version 12.2.0.1.36 Copyright (c) 2023, 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.36 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-23_23-05-53PM_1.log Following patches are not present in the Oracle Home. 34765931 Patches will be rolled back in the following order: 34734035 The following patch(es) will be rolled back: 34734035 Rolling back patch 34734035... RollbackSession rolling back interim patch '34734035' from OH '/u01/app/oracle/product/19' Patching component oracle.rdbms, 19.0.0.0.0... Patching component oracle.rdbms.dbscripts, 19.0.0.0.0... RollbackSession removing interim patch '34734035' from inventory Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-23_23-05-53PM_1.log OPatch succeeded.
I ran another check just to be sure and clear that nothing else needs to be removed. But it looks good now.
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.36 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.36 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-23_23-39-24PM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded.
.
-
opatch apply for the 19.18.0 RU
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.36 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.36 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-23_23-39-24PM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded. [CDB2] oracle@hol:/media/sf_TEMP/19/p34765931_190000_Linux-x86-64/34765931 $ $OH19/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.36 Copyright (c) 2023, 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.36 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-23_23-51-38PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 34765931 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 '34765931' 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.sdo.companion, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 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.slax.rsf, 19.0.0.0.0... Patching component oracle.ordim.jai, 19.0.0.0.0... Patching component oracle.bali.jewt, 11.1.1.6.0... Patching component oracle.bali.ewt, 11.1.1.6.0... Patching component oracle.help.ohj, 11.1.1.7.0... Patching component oracle.rdbms.locator, 19.0.0.0.0... ... Patching component oracle.install.deinstalltool, 19.0.0.0.0... Patching component oracle.dbtoolslistener, 19.0.0.0.0... Patching component oracle.precomp.lang, 19.0.0.0.0... Patching component oracle.precomp.common, 19.0.0.0.0... Patching component oracle.jdk, 1.8.0.201.0... Patch 34765931 successfully applied. Sub-set patch [34419443] has become inactive due to the application of a super-set patch [34765931]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-23_23-51-38PM_1.log OPatch succeeded.
-
Applying the OJVM Bundle 19.18.0
Until Oracle Database 19c, the OJVM bundle must be applied separately. From Oracle 21c it is part of the standard RU. But of course, this applies only if you have JVM in your database (see DBA_REGISTRY or CDB_REGISTRY) and you don’t use the Mitigation Patch.
$ $OH19/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.36 Copyright (c) 2023, 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.36 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-26_17-11-14PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 34786990 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 '34786990' 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 34786990 successfully applied. Sub-set patch [34411846] has become inactive due to the application of a super-set patch [34786990]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-26_17-11-14PM_1.log OPatch succeeded.
-
Data Pump Bundle Patch apply
For further information on the Data Pump Bundle Patch, please see my previous blog post here. I’m applying Patch 34972375 to my 19.18.0 home.
$ $OH19/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.36 Copyright (c) 2023, 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.36 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-26_17-48-47PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 34972375 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Backing up files... Applying interim patch '34972375' to OH '/u01/app/oracle/product/19' Patching component oracle.rdbms.dbscripts, 19.0.0.0.0... Patching component oracle.rdbms, 19.0.0.0.0... Patch 34972375 successfully applied. Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2023-01-26_17-48-47PM_1.log OPatch succeeded.
-
datapatch needs to be run
Be aware, at least in my environments this took quite a bit. When I monitored it in the alert.log, it seems to be that the XDB initialization takes long. But I didn’t dig deeper.
And I received a lot of these warnings, too:2023-01-26T22:07:02.512151+01:00 KGL object name :0xaj1zwz5s2vj:ALTER VIEW "SYS"."KU$_FHTABLE_VIEW" COMPILE 2023-01-26T22:07:15.266728+01:00 Memory Notification: Library Cache Object loaded into SGA Heap size 57437K exceeds notification threshold (51200K)
I didn’t investigate further. Still glad that customers read the blog and give advice and hints. If you want to supress these warnings, then read further in Supress nasty error messages and warning when datapatch gets run. To be clear: This is NOT a datapatch issue. It just happens when datapatch gets executed. Anyhow, it looks to me that the compilation of these objects took quite some time.
$ $OH19/OPatch/datapatch -verbose SQL Patching tool version 19.18.0.0.0 Production on Thu Jan 26 19:22:47 2023 Copyright (c) 2012, 2023, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_6016_2023_01_26_19_22_47/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: 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: Not 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 Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)): 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: 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.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 The following interim patches will be rolled back: 34411846 (OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)) 34734035 (MERGE ON DATABASE RU 19.17.0.0.0 OF 34650250 34660465 24338134 25143018 26565187) Patch 34765931 (Database Release Update : 19.18.0.0.230117 (34765931)): Apply from 19.17.0.0.0 Release_Update 220924224051 to 19.18.0.0.0 Release_Update 230111171738 The following interim patches will be applied: 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)) 34972375 (DATAPUMP BUNDLE PATCH 19.18.0.0.0) Installing patches... Patch installation complete. Total patches installed: 10 Validating logfiles...done Patch 34411846 rollback (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB2_CDBROOT_2023Jan26_19_23_42.log (no errors) Patch 34734035 rollback (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34734035/24992341/34734035_rollback_CDB2_CDBROOT_2023Jan26_19_24_16.log (no errors) Patch 34765931 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25078403/34765931_apply_CDB2_CDBROOT_2023Jan26_19_24_16.log (no errors) Patch 34786990 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_CDB2_CDBROOT_2023Jan26_19_24_16.log (no errors) Patch 34972375 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34972375/25075164/34972375_apply_CDB2_CDBROOT_2023Jan26_19_24_40.log (no errors) Patch 34411846 rollback (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB2_PDBSEED_2023Jan26_19_25_14.log (no errors) Patch 34734035 rollback (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34734035/24992341/34734035_rollback_CDB2_PDBSEED_2023Jan26_19_25_15.log (no errors) Patch 34765931 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25078403/34765931_apply_CDB2_PDBSEED_2023Jan26_19_25_15.log (no errors) Patch 34786990 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_CDB2_PDBSEED_2023Jan26_19_25_15.log (no errors) Patch 34972375 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34972375/25075164/34972375_apply_CDB2_PDBSEED_2023Jan26_19_25_34.log (no errors) SQL Patching tool complete on Thu Jan 26 19:31:37 2023
-
Checking and enabling the new optimizer fixes
As discussed previously, if you’d like to do this as well when you patch, please carefully test beforehand. Enabling those fixes should be the norm if you upgrade or deploy a new database. But patching is a different story.
SQL> set serverout on SQL> execute dbms_optim_bundle.getBugsforBundle; 19.18.0.0.230117DBRU: Bug: 31209735, fix_controls: 31209735 Bug: 30609737, fix_controls: 30609737 Bug: 32498602, fix_controls: 32498602 Bug: 29499077, fix_controls: 29499077 Bug: 32378953, fix_controls: 32527739 Bug: 31821701, fix_controls: 31266779, 31487332 Bug: 25869323, fix_controls: 25869323 Bug: 31925765, fix_controls: 31925765 Bug: 33667505, fix_controls: 33667505 Bug: 32107664, fix_controls: 33369863 Bug: 32933936, fix_controls: 32933936 Bug: 34131435, fix_controls: 34131435 Bug: 34012165, fix_controls: 33745469 Bug: 34774426, fix_controls: 29015273 Bug: 34701323, fix_controls: 34701323 Bug: 34123350, fix_controls: 34123350 Bug: 34958012, fix_controls: 32016340 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 29331066:1 28965084:1 28776811:1 28498976:1 28567417:1 28558645:1 29132869:1 29450812:1 29687220:1 29939400:1 30232638:1 30001331:0 29304314:1 29930457:1 30028663:1 28144569:1 28776431:1 27261477:1 31069997:1 31077481:1 28602253:1 29653132:0 29937655:1 30347410:1 30602828:1 30896685:0 29487407:1 30998035:1 30786641:1 31444353:0 30486896:1 28999046:1 30902655:1 30681521:1 29302565:1 30972817:1 30222669:1 31668694:1 31001490:1 30198239:7 30980115:1 30616738:0 31895670:0 19138896:1 31670824:0 9876287:0 30564898:1 32075777:0 30570982:1 32037237:1 30927440:1 30822446:1 24561942:1 31625959:1 31579233:1 29696242:1 30228422:1 17295505:1 29725425:1 30618230:1 30008456:1 30537403:1 30235878:1 30646077:1 29657973:1 29712727:1 20922160:1 30006705:1 29463553:1 30751171:1 31009032:1 30063629:1 30207519:1 31517502:1 30617002:1 30483217:1 30235691:1 30568514:1 28414968:3 32014520:1 30249927:1 31580374:1 29590666:0 29435966:1 28173995:1 29867728:1 30776676:1 26577716:1 30470947:1 30979701:1 30483184:1 31001295:1 31191224:1 31974424:1 29385774:1 28234255:3 31459242:0 31082719:1 28708585:1 31821701:1 32107621:1 26758837:1 31558194:1 30781970:0 30142527:1 31143146:1 31961578:0 31496840:1 22387320:1 30652595:1 25979242:1 32578113:1 32205825:1 32408640:1 31988833:1 32800137:0 31360214:1 32913527:0 29738374:1 33325981:1 31945701:0 32212062:0 32766397:0 32508585:1 29651517:1 31912834:1 33145153:1 31880080:0 31050103:1 30018126:1 33303725:1 32856375:1 32754044:1 33297275:1 32851615:1 32302470:1 27825962:0 33323903:1 31162457:1 31843716:0 28044739:1 30771009:1 33636280:0 31545400:1 30618406:1 32614157:1 33329027:1 33311488:1 32396085:1 29972495:1 32363981:1 31582179:1 30978868:1 33381775:1 33906515:1 33443834:1 33730024:1 33649782:1 33236729:1 34092979:0 33987911:1 34028486:1 32874571:0 26491973:1 30675651:0 10123661:1 30887435:1 30231086:1 30195773:1 31091402:1 33547527:1 18101156:0 34428819:1 31209735:1 30609737:1 32498602:1 29499077:1 32527739:1 31266779:1 31487332:1 25869323:1 31925765:0 33667505:1 33369863:0 32933936:0 34131435:1 33745469:1 29015273:0 34701323:1 34123350:1 32016340:0 ...
-
Copying in the newest AutoUpgrade Tool
This is not required but you could use AutoUpgrade for the change to the new home and the patching as well. And I feel much better having always the newest AU on disk everywhere. You’ll find it in MOS Note: 2485457.1 – AutoUpgrade Tool. Copy it into your $ORACLE_HOME/rdbms/admin directories, overwriting the previous version. The output you should see right now tells you:
$ java -jar $OH19/rdbms/admin/autoupgrade.jar -version build.version 22.5.221011 build.date 2022/10/11 14:23:59 -0400 build.hash e9428661 build.hash_date 2022/10/11 12:55:45 -0400 build.supported_target_versions 12.2,18,19,21 build.type production
Applying RUs, BPs and PSUs to older releases
Since the previous releases, Oracle Database 11.2.0.4, Oracle Database 12.1.0.2 and Oracle Database 12.2.0.1 are not under Extended Support anymore, I won’t list them here. But I applied the available fixes to my environments as well without any issues.
Further Links and Information
- January 2023 Security Alerts
- January 2023 Oracle Database Server Risk Matrix
- Applying the October 2022 Patch Bundles to my environments
- MOS Note: 2906899.1 – Critical Patch Update (CPU) Program Jan 2023 Patch Availability Document (DB-only)
- Important Alert for Oracle 19.18.0 for Database and Grid Infrastructure
- Apply the Data Pump Bundle Patch – non-rolling but online
- Supress nasty error messages and warning when datapatch gets run.
–Mike
This is really bad. Especially for us who have hundreds of servers where the patching are scripted. Really bad.
What exactly do you mean with “this is really bad”? Patching generally? The RU? The process?
Please let me know. I’ll happily discuss with you.
Cheers,
Mike
Warnings and Tracefiles
Datapatch and database creation bring a lot of the memory notifications and also a lot of rather not so useful tracefiles for this matter. We are experimenting with setting
*._kgl_large_heap_assert_threshold=1048576000
*._kgl_large_heap_warning_threshold=104857600
while creating / patching the databases (can be set online)
according to
Oracle Support Document 330239.1 (Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded]) .
We hoped this would help to speed up both (seeming to get slower and slower on aix), what unfortunately didn’t work but at least we avoid this warnings and their belonging tracefiles.
Do you see any disadantage doing so?
Walter,
thanks for sharing this with me. Actually, I discovered those annoying messages the first time during DB creation as well, especially when you do a CUSTOM creation and when it comes to PDB$SEED. Let me experiment a bit.
Just from hitting the wrong button in our ORAdiff app (will be available to customers hopefully soon as well), I see that there was a fix included in 19.15.0 and forward:
31585319 _KGL_LARGE_HEAP_ASSERT_THRESHOLD MODIFIES TO 1.5G WHEN CREATING CTX INDEX
So I guess there are some known issues.
The parameter: _kgl_large_heap_assert_threshold
means: maximum heap size before KGL raises an internal error
The parameter: _kgl_large_heap_warning_threshold
means: maximum heap size before KGL writes warnings to the alert log
Hence, due to the nature of just defining “log” thresholds, I don’t see any bod implication with setting those.
But I may release a general blog post sometime next week about it as well and quote your recommendation if you don’t mind.
Cheers,
Mike
Mike,
thanks for clearifying and feel free to quote,
Cheers,
Walter
Thanks Walter!
Cheers,
Mike
Hi Mike, it appears a bit strange to me you discuss here the installation of the January 2023 Patch Bundle but in a previous post you tell that the 19.18 patch has been withdrawn. And it is still not possible to download this patch (for Linux patch 34765931).
It is available now again. They made a recut with the fix for the trace file issue.
Sorry for the inconvenience.
Cheers,
Mike
With the Release of Opatch 13.9.4.2.11 for WebLogic and OPatch 12.2.0.1.36 for Oracle A new feature is added to Obfuscate files in .patch_storage to avoid false alert from Security scanners for e.g. log4j. What i noticed that after upgrading, it automatically obfuscates the current patch files, but not all the previous patch files. This is a welcome change and documented in below MOS document.
OPatch 13.9.4.2.11 Introduces a New Feature to Obfuscate the ORACLE_HOME/.patch_storage Directory (Doc ID 2909604.1)
I also ran below after the patch is applied to obfuscate all previous patches
opatch util Obfuscate
After that Users can run below and no issues will be reported.
log4j2-scan –scan-log4j1 $ORACLE_HOME/.patch_storage
Thanks Ketan,
I added this to my blog post (and other features as well) since I needed confirmation from the opatch team.
Cheers,
Mike
Hello Mike, FYI…
The Readme file for Windows Database Bundle Patch 19.18.0.0.230117 is showing the wrong quarter, ‘OCT 2022’
“Patch 34750795 – Windows Database Oct2022 Bundle Patch 19.18.0.0.230117”
After my third download I noticed that is a typo and not me downloading the wrong patch…
Regards,
Jorge
Hi Jorge,
thanks for letting us know – I reported this to the responsible people, and I hope that by now this is fixed.
Thanks for the pointer!
Mike
I’m applying the Jan 2023 Oracle 19c patch for windows, I see that Oracle does not have a Jan 23 patch for OJVM for windows. When patching the RDBMS you first have to rollback the previous OJVM patch before applying the next RDBMS patch (in this case 34750795), then typically I apply the new OJVM patch. Because Oracle did not release an OJVM patch for Jan 23 I tried to apply the previous OJVM (Oct 22) but it telling me it conflicts with the new RDBMS 34750795 patch that I just applied. What is your recommendation for this? Thanks David
Hi David,
you will not need to rollback any patches when you patch out of place.
Rollback is needed for one-off patches (but I am not sure whether OJVM is treated as a one-off – I thought that you don’t need to remove it, at least on Linux I never removed it).
V23 should be available by now, it got released really late this time.
Thanks
Mike
What’s the process for applying patches in a data guard environment with a logical standby?
All documentation I’ve found online is is for physicals.
Hi Sam,
interesting question.
I need to check with the PM for DG since I am not 100% sure whether I recommend the right strategy.
My guess would be the exact same as you’d do for standby-first-apply. But I wonder whether the SQL Apply process will complain when you have 19.18.0 executables but receive logs from a 19.15.0 database. I would hope that this is fine – but I am not sure by 100%.
I’ll get back to you.
Cheers,
Mike
Mike, can you also check if the process if the same on Windows?
I am noticing that some steps are not the same when I am patching Data Guard on Linux than on Windows.
Regards,
jorge
Hi Jorge,
the process is more or less exactly the same – but when you patch out-of-place, you need to create a new service from the new home, and you need to make sure you did copy the important files (spfile etc) into the new home as well.
Cheers
Mike
Thanks for getting back to me.
One more question. After installing Patch 34750795 – Windows Database Jan2023 Bundle Patch 19.18.0.0.230117 I keep getting ORA-12546: TNS:permission denied?
No issues with 19.16, I am skipping 19.17 and going to 19.18.
Thoughts?
Regards,
jorge
Clearly: Open an SR. And I guess you did this already.
Thanks,
Mike
Hi Mike, do you know of any issues with running opatch and opatchauto inside a “tmux” (or “screen”) window? In case of network disconnection while patching from home, I could log in again and reconnect to my opatch session.
Thanks
Hi Paul,
this should actually work quite nicely.
Cheers,
Mike
Hi Mike,
To make sure datapatch patches the user created PDBs, should I open them in read-write mode or can they be open in read-only mode and datapatch would still work?
Thanks
Datapatch requires that they are open in read-write mode – otherwise they can’t be patched.
Cheers
Mike
Mike, FYI….for some unknow issue my SQLNET.ora file was missing the following entry: SQLNET.NO_NTLM=FALSE.
NTLM is disabled by default
thanks
Thanks for the update, Jorge!
Cheers
Mike
Hi Mike.
The listener and database is turned off.
I applied patches 19.18 to the oracle home.
Database version is 19.17.
I startup the database.
At this moment occurs In-Memory population, jobs can start, etc.
I run the datapatch utility.
In-Memory populating and the launch of jobs occurs before the patch is installed to database. (even if it’s less than a minute passes)
Is it correct?
Alex,
I really can’t tell you. But your observation sounds very interesting. If we would not be caught up so heavily with OCW prep and customer engagements I would love to test it and give you a definite answer. But at this point I can only ask you to open an SR and check with Support. I know that IM populations starts pretty early during startup – so my blunt assumption would be exactly what you’ve observed. But I have no confirmed proof for it.
Cheers,
Mike
Mike. It turns out that the refusal of the startup upgrade for run the datapatch utility can have its negative effects?
And if RUs contain fixes for IMs or jobs – what then?
And in README.html only Step 3 SQL> startup…) thanks
Nope – only the fact that it will take down your database is not right.
There is no negative side effect when STARTUP UPGRADE is NOT used.
If you encounter any downside, then this needs to be logged as a bug to Oracle.
The README.html does not state STARTUP UPGRADE, doesn’t it?
If it does, please let me know which README states it.
Thanks,
Mike