Oracle Datapump Proactive Patching

I was speaking at the MOUS 2022 conference last week in Michigan, US, where I attended a session by Roy Swonger (Oracle’s VP DB Utilities) on a deep dive of Datapump. A webinar with the deep dive can be found here. In the session he spoke about recommended proactive patches for Datapump and the importance of applying these patches.
I had heard about the Proactive patches for Datapump before and what I heard in the session got me interested, so I went about learning more about the patches. Here is my experience so far.
Premise by Oracle:
Some Bug fixes for Datapump are not included in the regular DB Release updates but are instead being rolled out separately as Datapump proactive patches. These patch bundles include performance and stability fixes for Datapump. The patches are only available on DB versions 19.10 or higher.
One major benefit of these patches that was highlighted both in the MOUS session and in this blog post by Mike Dietrich is that after the application of these patches, datapatch will see a significant performance improvement.
Downloading the patches:
The MOS note (Doc ID 2819284.1) provided as a reference for Datapump proactive patches is a little confusing to read and decode.
The latest version available is on RU 19.16.0.0, which is patch 34620690 titled “MERGE ON DATABASE RU 19.16.0.0.0 OF 34570619 30155338 30430932 30582819 31050896”
Patch builds on top of 19.15 and 19.14 DB RU are available as well, and are also all called “MERGE ON DATABASE RU “, instead of Datapump Proactive patches that I was expecting.
These patches are available for Generic Platform as well as a Windows specific release. I used the Generic patch for installation on my Linux x86-64 release.
Applying the patches:
I decided to download the Generic release for the latest patch, which is 34620690 for 19.16.0.0, to check if I could apply this on my test servers which are running DB RU 19.15. As expected, the patch failed to apply since it needed the RU 16 to be already applied on the Oracle home.
[oracle@test1 34615568]$ $ORACLE_HOME/OPatch/opatch lspatches 33514440;SYS_REMAP_XMLTYPE OPERATOR IS CAUSING PERFORMANCE ISSUES WHEN IT COMES TO LARGE DATASETS 33815596;OCW RELEASE UPDATE 19.15.0.0.0 (33815596) 33806152;Database Release Update : 19.15.0.0.220419 (33806152) 33561310;OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310) OPatch succeeded. [oracle@test1 34620690]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.29 Copyright (c) 2022, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/19.0.0.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.29 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2022-10-31_11-26-21AM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" failed. The details are: Interim patch 34620690 requires prerequisite patch(es) [34133642] which are not present in the Oracle Home. Apply prerequisite patch(es) [34133642] before applying interim patch 34620690. Summary of Conflict Analysis: There are no patches that can be applied now. OPatch succeeded.
I then proceeded to apply the Merge patch for RU 15, which is 34547013. The readme for the patch states that this patch is non-RAC rolling installable. This implies that the patch can be applied while your DB instance is running.
[oracle@test1 34547013]$ $ORACLE_HOME/OPatch/opatch lspatches 33514440;SYS_REMAP_XMLTYPE OPERATOR IS CAUSING PERFORMANCE ISSUES WHEN IT COMES TO LARGE DATASETS 33815596;OCW RELEASE UPDATE 19.15.0.0.0 (33815596) 33806152;Database Release Update : 19.15.0.0.220419 (33806152) 33561310;OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310) OPatch succeeded. [oracle@test1 34547013]$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.29 Copyright (c) 2022, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19.0.0.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.29 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2022-10-31_11-41-33AM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 34547013 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Backing up files... Applying interim patch '34547013' to OH '/u01/app/oracle/product/19.0.0.0/dbhome_1' Patching component oracle.rdbms, 19.0.0.0.0... Patching component oracle.rdbms.dbscripts, 19.0.0.0.0... Patch 34547013 successfully applied. Log file location: /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2022-10-31_11-41-33AM_1.log OPatch succeeded. [oracle@test1 34547013]$ $ORACLE_HOME/OPatch/opatch lspatches 34547013;MERGE ON DATABASE RU 19.15.0.0.0 OF 34474808 28990738 31424070 32731035 33660169 33514440;SYS_REMAP_XMLTYPE OPERATOR IS CAUSING PERFORMANCE ISSUES WHEN IT COMES TO LARGE DATASETS 33815596;OCW RELEASE UPDATE 19.15.0.0.0 (33815596) 33806152;Database Release Update : 19.15.0.0.220419 (33806152) 33561310;OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310) OPatch succeeded.
Roy mentioned in his session that running datapatch after the application of the Merge patch might fail if you have a datapump process running, since datapatch will need to update the DBMS_DATAPUMP packages to apply the bug fixes. I decided to test this, so I ran a full DB metadata export from instance 3 of my RAC database and ran datapatch from instance 1. It failed as expected.
[oracle@test1 OPatch]$ ./datapatch SQL Patching tool version 19.15.0.0.0 Production on Mon Oct 31 12:16:19 2022 Copyright (c) 2012, 2022, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_285184_2022_10_31_12_16_19/sqlpatch_invocation.log Connecting to database...OK Gathering database info...done Bootstrapping registry and package to current versions...done Determining current state...done Current state of interim SQL patches: .. .. Adding patches to installation queue and performing prereq checks...done Installation queue: No interim patches need to be rolled back No release update patches need to be installed The following interim patches will be applied: 34547013 (MERGE ON DATABASE RU 19.15.0.0.0 OF 34474808 28990738 31424070 32731035 33660169) Installing patches... Patch installation complete. Total patches installed: 1 Validating logfiles...done Patch 34547013 apply: WITH ERRORS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34547013/24923375/34547013_apply_TESTSTG3_2022Oct31_12_16_42.log (errors) -> Error at line 45: script rdbms/admin/dpload.sql - ORA-20000: Retry dpload.sql script later when - Data Pump and Metadata API are not in use; current users are: - pid:149567, user:, machine:test1, sid:6757, module: - pid:325969, user:, machine:test2, sid:6757, module:KTSJ - pid:180440, user:SYS, machine:test3, sid:6757, module:Data Pump - Master - ORA-06512: at "SYS.KU$_DPLOAD", line 1042 - ORA-06512: at line 1 Please refer to MOS Note 1609718.1 and/or the invocation log /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_285184_2022_10_31_12_16_19/sqlpatch_invocation.log for information on how to resolve the above errors. SQL Patching tool complete on Mon Oct 31 12:19:43 2022
Once my metadata export was completed, I was able to successfully run the datapatch.
[oracle@test1 OPatch]$ ./datapatch SQL Patching tool version 19.15.0.0.0 Production on Mon Oct 31 12:26:05 2022 Copyright (c) 2012, 2022, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_330555_2022_10_31_12_26_05/sqlpatch_invocation.log Connecting to database...OK Gathering database info...done Bootstrapping registry and package to current versions...done .. .. Interim patch 34547013 (MERGE ON DATABASE RU 19.15.0.0.0 OF 34474808 28990738 31424070 32731035 33660169): Binary registry: Installed SQL registry: Applied with errors on 31-OCT-22 12.19.43.619512 PM Current state of release update SQL patches: Binary registry: 19.15.0.0.0 Release_Update 220331125408: Installed SQL registry: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 16-SEP-22 02.35.54.858513 PM Adding patches to installation queue and performing prereq checks...done Installation queue: No interim patches need to be rolled back No release update patches need to be installed The following interim patches will be applied: 34547013 (MERGE ON DATABASE RU 19.15.0.0.0 OF 34474808 28990738 31424070 32731035 33660169) Installing patches... Patch installation complete. Total patches installed: 1 Validating logfiles...done Patch 34547013 apply: SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34547013/24923375/34547013_apply_TESTSTG3_2022Oct31_12_26_26.log (no errors) SQL Patching tool complete on Mon Oct 31 12:29:15 2022
Conflicts:
I was curious to check if the Datapump merge patches would conflict with DB release updates, so I ran a test. Here is what I did:
- Installed DB RU 14 patch 33561310 on my RDBMS home.
- Installed Datapump merge patch on RU 14 34423086 on the same RDBMS home.
- Ran prechecks for DB RU 15 patch 33806152 on the RDBMS home.. and.. it conflicts with the Datapump Merge patch!
[oracle@test 34423086]$ $ORACLE_HOME/OPatch/opatch lspatches 33514440;SYS_REMAP_XMLTYPE OPERATOR IS CAUSING PERFORMANCE ISSUES WHEN IT COMES TO LARGE DATASETS 33566611;LGWR PLUS 4 WORKERS HAVE OPEN QPS , SHOULD BE ONLY 2 WORKERS 33561310;OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310) 33515361;Database Release Update : 19.14.0.0.220118 (33515361) 33529556;OCW RELEASE UPDATE 19.14.0.0.0 (33529556) OPatch succeeded. [oracle@test 34423086]$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.29 Copyright (c) 2022, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19.0.0.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.29 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2022-11-02_06-12-36AM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 34423086 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Backing up files... Applying interim patch '34423086' to OH '/u01/app/oracle/product/19.0.0.0/dbhome_1' Patching component oracle.rdbms.dbscripts, 19.0.0.0.0... Patching component oracle.rdbms, 19.0.0.0.0... Patch 34423086 successfully applied. Log file location: /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2022-11-02_06-12-36AM_1.log OPatch succeeded. [oracle@test 34423086]$ $ORACLE_HOME/OPatch/opatch lspatches 34423086;MERGE ON DATABASE RU 19.14.0.0.0 OF 33964404 33977398 30928455 31725941 33204663 33514440;SYS_REMAP_XMLTYPE OPERATOR IS CAUSING PERFORMANCE ISSUES WHEN IT COMES TO LARGE DATASETS 33566611;LGWR PLUS 4 WORKERS HAVE OPEN QPS , SHOULD BE ONLY 2 WORKERS 33561310;OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310) 33515361;Database Release Update : 19.14.0.0.220118 (33515361) 33529556;OCW RELEASE UPDATE 19.14.0.0.0 (33529556) OPatch succeeded. -- Testing the RU 15 on the same Oracle home fails with conflicts with the merge patch. [oracle@test patches]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./33803476/33806152 Oracle Interim Patch Installer version 12.2.0.1.29 Copyright (c) 2022, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/19.0.0.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.29 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2022-11-02_06-25-17AM_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 33806152 has Subset Patch 33515361 which has overlay patches [33566611,34423086] and these overlay patches conflict with Superset Patch Subset Patch 33515361 which has overlay patches [33566611,34423086] and these overlay patches conflict with Superset Patch
Summary:
So here is the current situation with Datapump proactive patches.
- The patches are called Merge patches and are available on top of existing release updates. The latest available at the time of this blog post is Merge on 19.16.
- You would need to apply the Merge patch for your specific RDBMS release.
- The patches are small and can be applied when the DB instance is running.
- The next time you apply the DB RU, you will need to roll back the Datapump Merge patch previously applied.
- Datapatch will need to be run to load the latest version of DBMS_DATAPUMP packages into the database. Any running datapump jobs will conflict with the datapatch run.
Conclusion:
If you are on Oracle RDBMS version 19.10 or higher and are currently facing performance/stability issues with Datapump, it is highly recommended that you apply the latest available Merge patch with Datapump fixes for your release. The patch is small, can be applied online, and includes fixes for several bugs related to Datapump for Oracle 19C. The trade-off is that you will need to rollback the patches every time you apply new Release Updates.