ORA-20001 Seen When Running Datapatch due to Incorrect Dpload.sql Script

刘泽宇

SYMPTOMS

When applying or rolling back a patch that contains a faulty version of dpload.sql, various unexpected results or errors may occur, especially in a CDB environment.
The ORA-20001 error is one possibility but there might be other manifestations, not all of which can be predicted.

There are cases when a Database Bundle Patch or PSU has a correct dpload.sql in the apply path, but in the rollback files, it has an incorrect dpload.sql script which contains the improper connect statement:
utl_file.put_line(h, 'connect sys/<password> assysdba');

The following error is reported when running datapatch because the improper version of the dpload file is getting called when the patch is applied or rolled back:

Validating logfiles...Patch 23740042 apply (pdb CDB$ROOT): WITH ERRORS   
logfile:
  <FILE_PATCH>/23740042_apply_EM
S15689_CDBROOT_2016Jul12_05_59_55.log (errors)     
Error at line 16: ORA-20001: set_patch_metadata not called     
Error at line 17: ORA-6512: at "SYS.DBMS_SQLPATCH", line 545     
Error at line 18: ORA-6512: at line 2
Patch 23740042 apply (pdb PDB$SEED): WITH ERRORS   
logfile:
  <FILE_PATCH>/23740042_apply_EM
S15689_PDBSEED_2016Jul12_05_59_55.log (errors)   
Error at line 16: ORA-20001: set_patch_metadata not called   
Error at line 17: ORA-6512: at "SYS.DBMS_SQ

CHANGES

Three cases are identified where datapatch fails to run:

1/ dpload.sql in <BUG>/files/rdbms/admin is not same as <BUG>/files/sqlpatch/<bug>/<aru>/rollback_files/rdbms/admin
2/ Under  <BUG>/files/sqlpatch/<bug>/<aru>/rollback_files/rdbms/admin - all 60+ child files of dpload.sql are not present.  If all 60+ child files are not present under rollback_files directory then datapatch will fail saying missing files.
3/ Cases where dpload.sql is not present in <BUG>/files/rdbms/admin

CAUSE

There are multiple apply/rollback DataPump patch issues while running the script dpload.sql in a Multitenant environment.
Fix of unpublished BUG 25139545 - TRACKING BUG TO INCLUDE DPLOAD.SQL FROM MAIN FOR FIXES ON 1120X AND 1210X, supplies the correct version of dpload.sql script where the connection problem is fixed, but new bugs are created to have the described datapatch issue also fixed.
This problem was investigated in the following unpublished Bugs:

  Bug 28841993 - ORA-20001 DATAPACH ISSUE DUE TO INCORRECT DPLOAD.SQL SEEN BY BOA.
  Bug 29190990 - DATAPATCH MUST RUN ALL CHILD FILES OF DPLOAD.SQL FROM $OH/RDBMS/ADMIN DURING ROLLBACK, closed as duplicate of
  BUG 33065089 - RUN DPLOAD.SQL ONLY ONCE WHEN MULTIPLE REFERENCES IN DATAPATCH PATCHING

and there is also Enhancement Request ENH 29199664 - dpload.sql to be picked from central location when it is not a part of base txn, created to address this problem, which was solved in 19.3.

Furthermore, through unpublished Bug 28865700 : SHIP DPLOAD.SQL AS PART OF 12.1.0.2 DB BUNDLE PATCH, Utilities Development team has included the most correct dpload.sql script in April Bundle Patches (for 12.1.0.2 and 11.2.0.4 databases).

SOLUTION

The following workarounds are available to have a successful execution of datapatch:

1. In patch: dpload.sql in <BUG>/files/rdbms/admin must be same as <BUG>/files/sqlpatch/<bug>/<aru>/rollback_files/rdbms/admin

    If its not the same then:
     - Copy <BUG>/files/rdbms/admin to <BUG>/files/sqlpatch/<bug>/<aru>/rollback_files/rdbms/admin

2. Under <BUG>/files/sqlpatch/<bug>/<aru>/rollback_files/rdbms/admin - all 60+ child files of dpload.sql must be present

    If all 60+ child files are not present under rollback_files directory then datapatch will fail saying missing files. The child files of dpload.sql which need to be present under rollback_files/rdbms/admin are below.
    In case any of these files is not present under rollback_files/rdbms/admin and the version of the installed Patch here matches the version of installed ORACLE_HOME, only then copy the missing files from $OH/rdbms/admin to $OH/sqlpatch/<bug>/<aru>/rollback_files/rdbms/admin.
[size=-1][size=-1]
[size=-1]Note:
1/ If a fix of Bug 25297023 is present in inventory, then additionally:
    Copy catmettypes.sql, catmetviews.sql, catmetviews_mig.sql, dbmspump.sql from $ORACLE_HOME/rdbms/admin into this rollback folder.

2/ In case the version of the installed patch doesn't match the version of ORACLE_HOME, please involve Oracle Install Team by opening a new SR, to assist with the missing files.

The child files of dpload.sql are:

catdpb.sql, catmetgrant1.sql, catmetx.sql, dbmsmet2.sql, dbmspump.sql, prvtbpd.plb, prvtbpu.plb, prvtdp.plb, prvthpdi.plb, prvthpui.plb, prvtmet2.plb, catdph.sql, catmetgrant2.sql, catnodp.sql, dbmsmeta.sql, dbmsxml.sql, prvtbpdi.plb, prvtbpui.plb, prvtdput.plb, prvthpf.plb, prvthpv.plb, prvtmeta.plb, catmetinsert.sql, catnodpt.sql, dbmsmetb.sql, prvtbpf.plb, prvtbpv.plb, prvtdputh.plb, prvthpfi.plb, prvthpvi.plb, prvtmetb.plb, catmettypes.sql, catnomta.sql, dbmsmetd.sql, prvtbpfi.plb, prvtbpvi.plb, prvthpc.plb, prvthpm.plb, prvthpw.plb, prvtmetd.plb, utlcxml.sql, catmet2.sql, catmetviews.sql, catnomtt.sql, dbmsmeti.sql, prvtbpc.plb, prvtbpm.plb, prvtbpw.plb, prvthpci.plb, prvthpp.plb, prvtmeti.plb, catmeta.sql, catmetviews_mig.sql, dbmsdp.sql, dbmsmetu.sql, prvtbpci.plb, prvtbpp.plb, prvtcxml.plb, prvthpd.plb, prvthpu.plb, prvtkupc.plb, prvtmetu.plb

3. Cases where dpload.sql is not present in <BUG>/files/rdbms/admin

    In such cases dpload.sql from 12.1.0.2.0 will be picked up for patching, but to have the proper dpload script the environment must have Patch 25139545 applied.
Patch 25139545 must be applied before running datapatch post installation step for a patch which doesn't have dpload.sql included in <BUG>/files/rdbms/admin.
Further details about Patch 25139545 can be found from: <Doc ID 2175021.1> - Alert - Multitenant Customers: The objects created by the post-install steps of 12.1.0.2 Generic DataPump Patches Are not Shared Across All PDBS.

Note:
====
If the issue was already introduced by datapatch, it is recommended to run dpload.sql from $ORACLE_HOME once all above workarounds are in place and before re-running datapatch:
SQL>@?/rdbms/admin/dpload.sql