Full export error caused by DBMS_LOGMNR_LOGREP_DICT invalid

Full export successfully finished with warning. I checked the export log file and found the following error:



EXP-00008: ORACLE error 4063 encounteredORA-04063: package body "SYS.DBMS_LOGMNR_LOGREP_DICT" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGMNR_LOGREP_DICT"ORA-06512: at line 1EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGMNR_LOGREP_DICT.system_info_expEXP-00008: ORACLE error 4063 encounteredORA-04063: package body "SYS.DBMS_LOGMNR_LOGREP_DICT" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGMNR_LOGREP_DICT"ORA-06512: at line 1EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGMNR_LOGREP_DICT.system_info_exp



Using sql*plus found that the sys schema package body DBMS_LOGMNR_LOGREP_DICT is invalid.



Trying to validate this package using the following statement not success:

Alter package DBMS_LOGMNR_LOGREP_DICT compile;



Searched the $ORACLE_HOME/rdbms/admin and found that this package is created with the Oracle supplied script prvtlmrd.plb. Trying to validate this package using this script is still not working.

@$ORACLE_HOME/rdbms/admin/prvtlmrd.plb



Finally, try to rebuild the whole database stored procedures using catproc.sql did not validate this package body(but has a side effect, explained later). So I asked the Oracle support for help. The reply went back quickly with an action plan based on Doc ID 550197.1.



For the packages of the LogMiner to be created successfully, you need to drop the SYSTEM.LOGMNR_SESSION$ table first, and then you can run the Oracle supplied scripts to recreate the LogMiner packages successfully. But the action plan missed the prvtlmrd.plb.



So, it should be:



SQL> connect / as sysdba

SQL> drop table SYSTEM.LOGMNR_SESSION$ purge;

SQL> @?/rdbms/admin/dbmslm.sql

SQL> @?/rdbms/admin/prvtlm.plb

SQL> @?/rdbms/admin/dbmslmd.sql

SQL> @?/rdbms/admin/prvtlmd.plb

SQL> @?/rdbms/admin/prvtlmc.plb

SQL> @?/rdbms/admin/prvtlmrd.plb



This makes the invalid sys schema package body DBMS_LOGMNR_LOGREP_DICT valided.



But the export still got error. This time, the log is like this:



EXP-00056: ORACLE error 932 encountered

ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

EXP-00056: ORACLE error 932 encountered

ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

EXP-00000: Export terminated unsuccessfully



Searched on Metalink and found the Doc ID 339938.1. I made some SYS.KU$_% views crashed. Fixed this problem with the following steps:



SQL> connect / as sysdba

SQL> @?/rdbms/admin/catmetx.sql

SQL> @?/rdbms/admin/utlrp.sql



Now, I could successfully export my full datase. Cheers!