• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Script to verify the Oracle DataPump Data Dictionary Catalog

By admin

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 the 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:
C:\DATABASES\O12102\diag\rdbms\o12102\o12102\trace\o12102_ora_7868_VERIFY_DATAPUMP.trc

The file contains:

Trace file C:\DATABASES\O12102\diag\rdbms\o12102\o12102\trace\o12102_ora_7880_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: o12102
Redo thread mounted by this instance: 1
Oracle process number: 24
Windows thread id: 7880, image: ORACLE.EXE (SHAD)


*** 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

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”.

Filed Under: oracle, Oracle 11g, oracle 12c

Some more articles you might also be interested in …

  1. Oracle ASM 12c – New Features with examples
  2. Dynamic Oracle Net Server Tracing
  3. How to set custom device names using udev in CentOS/RHEL 7
  4. Roles and Privileges Administration and Restrictions in Oracle Database
  5. Oracle Database 12c New Feature – Move a Datafile Online
  6. How to Trace Dynamic Registration from PMON or LREG (Oracle 12c)
  7. Oracle RMAN 12c – New Features
  8. How to Disable AUTOEXTEND Mode on a datafile in Oracle Database
  9. How to get the Values Assigned by Default to a Profile in Oracle Database
  10. How to extend ASM disk from OS level in CentOS/RHEL

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary