526互联

Oracle重建data pump（expdpd，impdp）How To Reload Datapump Utility EXPDP/IMPDP (Doc ID 430221.1)

发布时间 2023-06-09 10:05:29作者: 阿西吧li

APPLIES TO:

Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

GOAL

How to reload DATAPUMP utility? This may be required in different situations, like hanging issues, internal errors, data dictionary incompatibility, etc, all of them related to initiating phase of DataPump.

 

SOLUTION

 

In some cases DataPump utility may get corrupted and we need to recreate DataPump utility to overcome internal corruption. To do this, run specified scripts for Oracle version that you are running as given below. In a RAC environment, run the scripts only on one instance.

Note:  Run the following as sysdba user:

SQL> connect / as sysdba


For Oracle version 10.1 :

-- 1. Catdp.sql orders the installation of all its components including the Metadata API which was previously installed separately.
SQL> @$ORACLE_HOME/rdbms/admin/catdp.sql


-- 2. dbmspump.sql will create DBMS procedures for DATAPUMP


SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

 
For Oracle version 10.2:

-- 1. Catdph.sql will Re-Install DataPump types and views

SQL> @$ORACLE_HOME/rdbms/admin/catdph.sql


-- Note:
-- If XDB is installed, then it is required to run "catmetx.sql" script also.
-- Use this code to verify if XDB is installed:

SQL> select substr(comp_name,1,30) comp_name,
     substr(comp_id,1,10) comp_id,
     substr(version,1,12) version,
     status
     from dba_registry;

-- Sample output if XDB installed,
Oracle XML Database    XDB    -version-    VALID


-- 2.prvtdtde.plb will re-install tde_library packages

SQL> @$ORACLE_HOME/rdbms/admin/prvtdtde.plb


-- 3. Catdpb.sql will Re-Install DataPump packages

SQL> @$ORACLE_HOME/rdbms/admin/catdpb.sql


-- 4.Dbmspump.sql will Re-Install DBMS DataPump objects

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql


-- 5. To recompile  invalid objects, if any

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

 
For Oracle version 11g and higher prior to 12c:

 

 

-- 1. Run catproc script, Catproc.sql

For running catproc.sql, please refer to


Note:863312.1 - Best Practices for running catalog, catproc and utlrp script

  

NOTE: IMPORTANT!

The catproc script should be run after the database has been opened with STARTUP UPGRADE.
The catproc script should NOT be run when the database is opened with unrestricted access. This can cause the database to experience performance issues, invalid objects and can even lead to a hanging situation.

 

  
 

 



-- 2.To recompile invalid objects, if any

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

 

 

Note: For Oracle release 12.1.0.1, there is no dpload.sql in $ORACLE_HOME/rdbms/admin.
To reload DataPump in Oracle release 12.1.0.1, please use the above steps shown for "For Oracle version 11g and higher prior to 12c".

 

 

For Oracle version 12c:

Note 1: Prior rebuilding DP catalog in 12.1.0.2 CDB , install Patch 25139545 as alerted in Document 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".
            Failure of Datapatch on Multitenant Environment Can Cause DataPump Dictionary Inconsistency in Pluggable Database (PDB), see Document 2327652.1.
Note 2:  For issues regarding KU$ Invalid Objects Owned by SYS after upgrading or applying datapatch, refer to Document 2289785.1 to rebuild Datapump.

 

On Multitenant Environment, for Oracle versions 12.1, 12.2, 18c, 19c, 21c, and 23c:

-- 1. To rebuild the DataPump packages with the following steps.

Under the ORACLE_HOME, execute:
cd rdbms/admin

-- run the dpload.sql in the CDB with all of the PDBs open, as described in "How to execute sql scripts in Multitenant environment (catcon.pl)" NOTE 1932340.1

Syntax:
$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l <logging directory> -b <prefix for logfile of dpload for each PDB> dpload.sql

 
Where:
-l - directory to use for spool log files; for example, /tmp
-b - base name for log and spool file names; for example, dp_rebuild_log_base_name

For example, at OS prompt

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl -d /u01/oracle -l /tmp -b dp_rebuild_log_base_name dpload.sql

NOTE: After dpload.sql is run via caton.pl, to recompile any invalid objects, utlrp.sql can be run in all containers vi catcon.pl as well, e.g.

host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /path_for_logs -b name_for_logs $ORACLE_HOME/rdbms/admin/utlrp.sql;
This command can be run several times to compile objects which did not get processed during the first run.

Also, if there are Data Pump components that are still invalid in any one container, utlrp.sql can be run individually in that container, e.g.

connect / as sysdba
alter session set container = <PDB_NAME>
@utlrp.sql

 

On Non-Multitenant Environment, for Oracle version 12c and higher:

-- 1. Rebuild the DataPump packages with the following steps.

Under the ORACLE_HOME, execute:
cd rdbms/admin

-- run SQL*Plus as sysdba

@dpload.sql

 

-- 2. To recompile  invalid objects, if any

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

 

Additional Resources

Community: Database Utilities

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.