前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >无法成功执行catalog.sql,ORA-04045 ORA-04064

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

作者头像
Leshami
发布2018-08-13 15:17:09
1.2K0
发布2018-08-13 15:17:09
举报
文章被收录于专栏:乐沙弥的世界

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

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2013年11月08日,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档