无法成功执行catalog.sql，ORA-04045 ORA-04064

      最近同事报料，数据库无法执行一些关于数据字典的查询。即使是查询dba_users都会出现错误。从错误日志来看，全部是数据字典的一些错误信息，而用户数据还是ok的。Google了一下，看了看metalink，需要重建数据字典。那就执行catalog.sql吧。晕，竟然连执行这个SQL语句都报错！数据没有备份呢，汗!!! 如果你也是无法成功执行catalog.sql，接着往下看。

一、故障现象 --后台alert log日志出现大量的ORA-00604，ORA-04045的错误信息 Tue Nov  5 14:14:02 2013 Errors in file /u02/database/XA4701/udump/XA4701_ora_30871.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS ORA-04064: not executed, invalidated ORA-04064: not executed, invalidated package body "LBACSYS.LBAC_EVENTS" ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2 ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2

--下面是trace文件的具体信息 oracle@DBBK10P:~> more /u02/database/XA4701/udump/XA4701_ora_30871.trc /u02/database/XA4701/udump/XA4701_ora_30871.trc Oracle Database 10g Release 10.2.0.3.0 - 64bit Production ORACLE_HOME = /users/oracle/OraHome10g System name:    Linux Node name:      linux-ejad Release:        2.6.16.60-0.54.5-smp Version:        #1 SMP Fri Sep 4 01:28:03 UTC 2009 Machine:        x86_64 Instance name: XA4701 Redo thread mounted by this instance: 1 Oracle process number: 22 Unix process pid: 30871, image: oracleXA4701@linux-ejad

*** SERVICE NAME:(XA4701) 2013-11-05 14:14:02.308 *** SESSION ID:(1092.3369) 2013-11-05 14:14:02.308 Skipped error 604 during the execution of LBACSYS.LBAC$LOGON *** 2013-11-05 14:14:02.326 ksedmp: internal or fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS ORA-04064: not executed, invalidated ORA-04064: not executed, invalidated package body "LBACSYS.LBAC_EVENTS" ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2 ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2

--查询数据字典，收到类似的错误 SQL> select comp_id, comp_name, version, status from dba_registry; select comp_id, comp_name, version, status from dba_registry                                                 * ERROR at line 1: ORA-04045: errors during recompilation/revalidation of SYS.DBA_REGISTRY ORA-04064: not executed, invalidated ORA-04064: not executed, invalidated package body "LBACSYS.LBAC_EVENTS" ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2 ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2 ORA-00904: "DBMS_REGISTRY"."SCHEMA_LIST_STRING": invalid identifier

二、故障分析 oracle@DBBK10P:~> oerr ora 4045 04045, 00000, "errors during recompilation/revalidation of %s.%s" // *Cause:  This message indicates the object to which the following //          errors apply.  The errors occurred during implicit //          recompilation/revalidation of the object. // *Action: Check the following errors for more information, and //          make the necessary corrections to the object. --从前面的错误描述来看及所发生的对象来看，系统用户LBACSYS这个对象错误了，为无效对象

--尝试重新编译，问题依旧

--下面直接执行catalog，执行时其日志文件出现大量的错误提示。也就是说无法重建数据字典 --尝试了使用restirct 方式来重启DB后执行，依旧出现相同的错误 SQL> @?/rdbms/admin/catalog create or replace * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS ORA-04064: not executed, invalidated ORA-04064: not executed, invalidated package body "LBACSYS.LBAC_EVENTS" ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2 ORA-06508: PL/SQL: could not find program unit being called: "LBACSYS.LBAC_EVENTS" ORA-06512: at line 2 --Author : Leshami --Blog   : http://blog.csdn.net/leshami

--折腾了一下下午，后来参照Doc ID 457415.1给出的解决方案搞定

三、故障解决 --下面直接列出Doc ID 457415.1文章给出的解决方案与步骤，具体不再演示 metalink上的文章：ORA-4045 Error During Recompilation/Revalidation Of SYS.DBMS_STANDARD (Doc ID 457415.1) Symptoms Catproc.sql fails with the following error:

ERROR at line 1: ORA-04045: errors during recompilation/revalidation of SYS.DBMS_STANDARD ORA-04021: timeout occurred while waiting to lock object SYS.CDC_ALTER_CTABLE_BEFORE Cause Trying to create a database from a database that already exists.

1. Modify init.ora to contain:

    _system_trig_enabled=FALSE     job_queue_processes=0     aq_tm_processes=0     Save init.ora

The following instructions are to be completed from sqlplus as the SYS user:

2. Issue a shutdown immediate:      SQL> shutdown immediate

3. SQL> startup pfile='<insert full path of init.ora here>'

4. SQL> @catalog.sql

5. SQL> @catproc.sql

6. Check for invalids from dba_objects:

    SQL> select owner, object_name from dba_objects where status='INVALID';

7. Run utlrp.sql:      SQL> @utlrp.sql

8. Check for invalids again.

    SQL> select owner, object_name from dba_objects where status='INVALID';