Data Pump Super Patch for Oracle 19.10 and newer

Just a few days we released a Data Pump Super Patch for Oracle 19.10 and newer. “Newer” refers to the fact that once 19.11.0 will be available, this merge patch I’m writing about will be available as well right away (sorry, got delayed a bit). And if you work with Data Pump, you may want to consider this performance patch collection.

Why do you need this patch?

Many customer will know the issue that adding changes with datapatch may take a long time as soon as dpload.sql is involved. And this Super Patch fixes almost all of the known issues. We put a lot of performance fixes into this single merge patch. “Merge” patch means that it combines a number of one-off patches. These fixes are mainly regarding dpload.

What is the patch number?

This generic patch can be downloaded from MOS with patch number 32551008.

We set the bug flag to “publish” so you can see details of the bug on MOS as well:

“Generic” means that you will get one patch for all platforms.

What’s in it?

This merge patch contains the following fixes:

  • 29835516 – Improve approach to drop objects: execute in anonymous PLSQL block
  • 30051876 – Move HCS objects out of our files to their own for easier HCS patching
  • 31014419 – Try to drop all Data Pump AQ tables
  • 31054943 – Vast performance improvement of MDAPI’s catmetgrant1/2.sql scripts
  • 31068789 – Conditionalize creating of dummy XDB views in catmetviews.sql, minimize row deletions in catmetinsert.sql
  • 31086613 – Simplify Data Pump backporting by splitting kupcmsg types specs/bodies into new files from the often updated prvtkupc
  • 31113196 – Move creation of ku$xktfbue from catmettypes.sql to catdpb.sql
  • 31207242 – Remove DBMS_OUTPUT.PUT_LINE from catmetgrant1/2.sql
  • 31207542 – Remove unnecessary SET commands from catnodpaq.sql
  • 31207734 – Remove unnecessary SET commands from catnodp.sql/ catnomta.sql scripts
  • 31214625 – Cleanup/rewrite of dpload; This subsumes fix for bug 29284656
  • 31727647 – Don’t always drop MDAPI’s XDB views during upgrade/ patch (dpload)
  • 32195077 – Split off grants/inserts in catdpb.sql into new catdpbgi.sql script
  • 32195274 – Cleanup catmetviews/catmet2/catmetx to reduce dependancies and # invalid objects.
  • 32195313 – CREATE OR REPLACE FORCE instead of DROP/ CREATE. Add dbmsplts.sql, prvtplts.plb to dpload.sql
  • 32316344 – Add sqlsessstart/end invocations to five of our install scripts
  • 32388966 – dpload concurrency improvement: remove running of dbmspump.sql
  • 32479451 – dpload concurrency improvement: only run catmet2.sql and not all of catdph.sql
  • 32525862 – dpload: fixup version checks to deal with latest db version# change

All of them are meant to allow better and faster patching of Data Pump / Metadata API.

And here is a short overview on what is included directly from the developer:

Things you will notice with this patch:

    • Huge performance improvement.  The elapsed time to install a patch via the dpload.sql apply script should run 60-90+ percent faster
    • Subsequent invocations can be even faster, at well under a minute
    • Number of objects that are left invalid has dropped by 50-90% (about 10 or less) and down to zero with subsequent invocation
    • Contention issues should be less as dpload.sql removed some operations that were unnecessary during the applying of a patch.

What’s missing?

Be aware – this patch is not on MOS yet (May 2, 2021):

Actually, there is nothing “missing”. You may want to add one additional fix which isn’t included here: bug 32233175,

This fix could also help with the elapsed time of the initial execution of dpload.sql in a PDB. Without this fix, there is an unnecessary invalidation of views in catmetviews.sql that required all of them to be recompiled, which can take minutes. But this one will require a merge with the above patch.

But it isn’t mandatory. And the fix for bug 32233175 can exist on its own, and does not need the big merge patch as basis.

Rolling – yes or no?

Surprise, surprise – this patch is rolling and standby-first applicable. See the README – it is correct. Hence, there is no outage on a RAC environment required. Technically speaking this patch is even “hot patchable” as it will only the basis to apply other patches faster and easier. So you could plop it into an Oracle Home of a running system. The files don’t get executed until the next time you are going to apply a Data Pump patch with datapatch.

Will it be in 19.11.0?

Unfortunately it won’t be part of 19.11.0 but the merge patch on top of 19.11.0 is already available. We hope to include it into one of the next RUs.

Applying 32551008

I downloaded patch 32551008 from MyOracle Support and unzipped it.

$ cd 32551008/
[CDB2] oracle@hol:~/32551008
$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, 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.23
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2021-04-15_18-17-42PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32551008  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '32551008' 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 32551008 successfully applied.
Log file location: /u01/app/oracle/product/19/cfgtoollogs/opatch/opatch2021-04-15_18-17-42PM_1.log

OPatch succeeded.

I didn’t shutdown my database instance – and when you check $ORACLE_HOME/rdbms/admin, you will see what the patch exchanged in this directory:

-rw-r--r--. 1 oracle dba   44334 Mar 29 13:36 prvtkupc_typespec.plb
-rw-r--r--. 1 oracle dba   28090 Mar 29 13:36 prvtkupc_typebody.plb
-rw-r--r--. 1 oracle dba   24749 Mar 29 13:36 prvtkupc.plb
-rw-r--r--. 1 oracle dba   68896 Mar 29 13:36 dpload.sql
-rw-r--r--. 1 oracle dba   63825 Mar 29 13:36 dbmsplts.sql
-rw-r--r--. 1 oracle dba    4433 Mar 29 13:36 catpspec.sql
-rw-r--r--. 1 oracle dba   11951 Mar 29 13:36 catpdeps.sql
-rw-r--r--. 1 oracle dba   76380 Mar 29 13:36 catnomta.sql
-rw-r--r--. 1 oracle dba   23315 Mar 29 13:36 catnodp.sql
-rw-r--r--. 1 oracle dba    1289 Mar 29 13:36 catnodpobs.sql
-rw-r--r--. 1 oracle dba    3576 Mar 29 13:36 catnodp_hcs.sql
-rw-r--r--. 1 oracle dba    3440 Mar 29 13:36 catnodpaq.sql
-rw-r--r--. 1 oracle dba    1464 Mar 29 13:36 catnodpall.sql
-rw-r--r--. 1 oracle dba   13876 Mar 29 13:36 catmetx.sql
-rw-r--r--. 1 oracle dba  546052 Mar 29 13:36 catmetviews.sql
-rw-r--r--. 1 oracle dba   21620 Mar 29 13:36 catmetviews_hcs.sql
-rw-r--r--. 1 oracle dba  389133 Mar 29 13:36 catmettypes.sql
-rw-r--r--. 1 oracle dba   18474 Mar 29 13:36 catmettypes_hcs.sql
-rw-r--r--. 1 oracle dba    1259 Mar 29 13:36 catmetloadxsl.sql
-rw-r--r--. 1 oracle dba 1071276 Mar 29 13:36 catmetinsert.sql
-rw-r--r--. 1 oracle dba    5208 Mar 29 13:36 catmetgrants_hcs.sql
-rw-r--r--. 1 oracle dba   40289 Mar 29 13:36 catmetgrant2.sql
-rw-r--r--. 1 oracle dba   42842 Mar 29 13:36 catmetgrant1.sql
-rw-r--r--. 1 oracle dba    2668 Mar 29 13:36 catmet2.sql
-rw-r--r--. 1 oracle dba   46549 Mar 29 13:36 catdwgrd.sql
-rw-r--r--. 1 oracle dba   28804 Mar 29 13:36 catdpb.sql
-rw-r--r--. 1 oracle dba   29446 Mar 29 13:36 catdpbgi.sql

Pretty straight forward.

Further Links and Information

–Mike

Share this: