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

阿尔萨斯

浏览: 4187632 次

最近访客更多访客>>

snower_tt

iams13

u012363178

wangyy

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2014-10 ( 581)
2014-09 ( 572)
2014-08 ( 545)
更多存档...

最近同事报料，数据库无法执行一些关于数据字典的查询。即使是查询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';