Sunday, October 12, 2014

How to rebuild datapump metadata? (if corrupted)

How to rebuild datapump metadata? (if corrupted)
Some times, due to some internal errors, datapump utility binaries may get corrupted. In such cases, we can rebuild that using below ways
In Oracle database 10.1 version :
SQL> connect / as sysdba
SQL >@ $ORACLE_HOME/rdbms/admin/catdp.sql – this will install metadata
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql – this will create DBMS procedures for datapump
In Oracle database 10.2 version :
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql
After performing above steps, all the binaries will be re-build. So, it is better to execute utlrp.sql to recompile invalid objects
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql
In Oracle 11g :
SQL >@ $ORACLE_HOME/rdbms/admin/catproc.sql (catproc.sql will take care of all other script execution)
To recompile invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql
Note: If you encounter this datapump binaries corruption issue on a production database, before executing above steps, it is strongly recommended to first test this and based on results you can implement in production

No comments:

Post a Comment