网站建设
联系我们

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

发布于:2014-02-12 09:44来源:未知 作者:admin 点击:

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

文章出处:http://blog.csdn.net/leshami/article/details/14522189

 

tag标签:
------分隔线----------------------------
------分隔线----------------------------