今天同事让帮忙处理一问题,将物理standby转换为逻辑standby失败,expdp/impdp无法使用,DBMS_JOB无法建立新的JOB.

expdp详细错误信息如下:

ORA-31626: job does not exist

ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"

ORA-06512: at "SYS.KUPV$FT", line 834

ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"

在metalink上发现一个类似问题: [ID 799625.1] 

重新编译DBMS_INTERNAL_LOGSTDBY失败:

SQL> alter package DBMS_INTERNAL_LOGSTDBY compile;

Warning: Package altered with compilation errors.

查询错误信息:

SQL> select * from dba_errors where name = 'DBMS_INTERNAL_LOGSTDBY';

表或视图不存在.

文章中提到的表SYSTEM.LOGMNR_LOG$不存在,与我遇到的情况不一样.

建立SYSTEM.LOGMNR_LOG$ =>重建数据字典?

怀疑:logmnr不能使用(可惜当时没有测试),另一篇关于ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors提到下面的解决方案,但版本不同([ID 316247.1]),

1. Connect / as sysdba to database in sql plus session

2. Execute the following scripts located in the ORACLE_HOME/rdbms/admin directory.

SQL> @dbmslmd.sql

SQL> @prvtlmd.plb

3. Check for invalids

4. If no invalids run the export..

因之前做过DBMS_LOGMNR_D包的重建没有遇到问题,就直接运行了上面的脚本.

脚本运行后,查询SYSTEM.LOGMNR_LOG$表存在,expdp,dbms_job包使用正常.

后记:

同事提到另一解决方案是重建数据字典,因是生产库就没有做;

catproc.sql中有如下内容:

REM Make sure these are always called after dbmstrig.sql has been installed

@@dbmslm.sql

@@prvtlm.plb

@@dbmslmd.sql

@@prvtlmd.plb

@@prvtlmc.plb

其实上面的解决方案重建了部份数据字典，说明expdp引用了logmnr的内容.

疑问:不知直接运行prvtlm.plb能否解决?

itpub小虫提到运行prvtlsby.plb可解决(http://www.itpub.net/thread-1043403-1-1.html),那一种方案更好?不得而知,oracle东东太多,我仍需努力…