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:

1. Installed DB RU 14 patch 33561310 on my RDBMS home.
2. Installed Datapump merge patch on RU 14 34423086 on the same RDBMS home.
3. 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.