Script To Verify The DataPump Catalog (Doc ID 1641557.1)

郑全

Script To Verify The DataPump Catalog (Doc ID 1641557.1)

In this Document
Main Content
        Description
        Risk
        Recommendation
References
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.2.0.1 [Release 11.2 to 12.2]
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
MAIN CONTENT
Description
Script to verify the DataPump data dictionary catalog.

Risk
No risk.

Recommendation
The package VERIFY_DATAPUMP checks the objects created by different DataPump specific scripts located in $ORACLE_HOME/rdbms/admin, which are automatically called within catproc.sql.

Different versions have different DataPump objects in data dictionary. At this moment the package works only with 11gR2 and 12c.

The procedure CHECK_ALL is the core of the package. It calls other internal procedures (similar to corresponding scripts) and fills a global collection which is verified and emptied at the end.

1. Download the file verify_datapump.zip and decompress it into a certain directory. Then install the version depending procedures and packages into your database with:

connect / as sysdba
@verify_datapump_header.sql
@verify_datapump_body.sql
The body is very large and needs up to 10-15 minutes to complete.

2. Start the procedure in SQL*Plus (as SYSDBA) with:

@verify_datapump_start.sql
3. The results are printed into a trace file:

SQL> @verify_datapump_start.sql
For more details please check the trace file:
ADR_base/diag/product_type/product_id/instance_id/trace/<SID>_oraXXXX_VERIFY_DATAPUMP.trc
The file contains:

ADR_base/diag/product_type/product_id/instance_id/trace/<SID>_oraXXXX_VERIFY_DATAPUMP.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 4 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:2013M/7865M, Ph+PgF:9800M/15729M
Instance name: <SID>
Redo thread mounted by this instance: 1
Oracle process number: 24


*** 2015-06-25 13:00:42.019
*** SESSION ID:(6.22626) 2015-06-25 13:00:42.019
*** CLIENT ID:() 2015-06-25 13:00:42.019
*** SERVICE NAME:(SYS$USERS) 2015-06-25 13:00:42.019
*** MODULE NAME:(sqlplus.exe) 2015-06-25 13:00:42.019
*** CLIENT DRIVER:(SQL*PLUS) 2015-06-25 13:00:42.019
*** ACTION NAME:() 2015-06-25 13:00:42.019

A-0000000001 25.06.2015 13:00.42                      ================================================================================
A-0000000002 25.06.2015 13:00.42                      Version: 12.1.0.2.0
A-0000000003 25.06.2015 13:00.42                      Compatiblity: 12.1.0.2.0
A-0000000004 25.06.2015 13:00.42                      CATALOG version: 12.1.0.2.0
A-0000000005 25.06.2015 13:00.42                      XDB Version: 12.1.0.2.0
A-0000000006 25.06.2015 13:00.42                      Major Database Release: 12
A-0000000007 25.06.2015 13:00.42                      Database Maintenance Release: 1
A-0000000008 25.06.2015 13:00.42                      ================================================================================
I-0000000009 25.06.2015 13:00.42 prereqs              TABLE SYS.DUAL [VALID]
I-0000000010 25.06.2015 13:00.42 prereqs              SELECT priv. on SYS.DUAL to PUBLIC [GRANTED]
I-0000000011 25.06.2015 13:00.42 prereqs              SYNONYM PUBLIC.DUAL [VALID]
I-0000000012 25.06.2015 13:00.42 prereqs              Objects named SYS or SYSTEM not found. [OK]
I-0000000013 25.06.2015 13:00.42 dbmsmeta.sql         TYPE SYS.KU$_PARSED_ITEM [VALID]
I-0000000014 25.06.2015 13:00.42 dbmsmeta.sql         EXECUTE priv. on SYS.KU$_PARSED_ITEM to PUBLIC [GRANTED]
...
E-0000000945 25.06.2015 13:00.42 catmetviews.sql      VIEW SYS.KU$_ADD_SNAP_VIEW [NOT FOUND]
W-0000000946 25.06.2015 13:00.42 catmetviews.sql      VIEW SYS.KU$_REFGROUP_VIEW [INVALID]
...
E-0000001782 25.06.2015 13:00.48 catmetgrant2.sql     SELECT priv. on SYS.KU$_ADD_SNAP_VIEW to SELECT_CATALOG_ROLE [MISSING]
...
A-0000006356 25.06.2015 13:00.51                      ================================================================================
A-0000006357 25.06.2015 13:00.51                      Please run the following scripts using SQL*Plus connected to the database
A-0000006358 25.06.2015 13:00.51                      as SYSDBA. The scripts are located in directory $ORACLE_HOME/rdbms/admin:
A-0000006359 25.06.2015 13:00.51                       
A-0000006360 25.06.2015 13:00.51                                catmetviews.sql
A-0000006361 25.06.2015 13:00.51                                catmetgrant2.sql
A-0000006362 25.06.2015 13:00.51                                utlrp.sql
A-0000006363 25.06.2015 13:00.51                      ================================================================================
Or, in case the DataPump catalog is complete:

A-0000006356 25.06.2015 13:12.31                      ================================================================================
A-0000006357 25.06.2015 13:12.31                      DataPump dictionary is up to date. No further steps are required.
A-0000006358 25.06.2015 13:12.31                      ================================================================================
4. Delete the installed objects from data dictionary with:

@verify_datapump_drop

Note: DataPump within Multitenant Databases

If you are running a Multitenant Database then you should determine whether the issue that you are encountering is at the container level or with one specific pluggable databases (PDBs).
Once this is established, then run the script in the root (CDB$ROOT) and in the PDB that is experiencing the issue. Both outputs should be verified to determine whether "DataPump dictionary is up to date".



REFERENCES
NOTE:430221.1 - How To Reload Datapump Utility EXPDP/IMPDP