我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！ 由于博客中有大量代码，通过页面浏览效果更佳。
故障分析：ALERT不停报ORA-06552/ORA-06553/ORA-006508错误
下面数据库来至于一个朋友，他们生产数据库alert.log后台不到的停下面类似的错误，每次报错都是不同的存储过程名。
Errors in file D:\APP\LUOPING\diag\rdbms\orcl\orcl\trace\orcl_q000_8400.trc:ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQADM_SYSORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalidORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"ORA-06512: at line 1Mon Aug 11 10:55:55 2014ORA-942 encountered when generating server alert SMG-4120ORA-942 encountered when generating server alert SMG-4121OCI error in kwqrNonDurSubCln is OCIStmtExecute : return value -1 code 4045 buf ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQADM_SYSORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalidORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"ORA-06512: at line 1KSV 4045 error in slave process*** 2014-08-11 10:55:26.426ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQADM_SYSORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalidORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"ORA-06512: at line 1OPIRIP: Uncaught error 447. Error stack:ORA-00447: fatal error in background processORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQADM_SYSORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalidORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"ORA-06512: at line 1exp,rman,expdp这些工具都不能使用。下面是处理过程。
直接tar的朋友数据文件过来操作的
1，修改文件的路径名
alter database rename file ‘Z:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF’ to ‘D:\test\orcl\orcl\SYSTEM01.DBF’;alter database rename file ‘Z:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF’ to ‘D:\test\orcl\orcl\SYSAUX01.DBF’ ;alter database rename file ‘Z:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’ to ‘D:\test\orcl\orcl\UNDOTBS01.DBF’ ;alter database rename file ‘Z:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF’ to ‘D:\test\orcl\orcl\USERS01.DBF’ ;alter database rename file ‘Z:\APP\ADMINISTRATOR\ORADATA\ORCL\DRSYS01.DBF’ to ‘D:\test\orcl\orcl\DRSYS01.DBF’ ;alter database rename file ‘Z:\APP\ADMINISTRATOR\ORADATA\ORCL\INDX01.DBF’ to ‘D:\test\orcl\orcl\INDX01.DBF’ ;alter database rename file ‘Z:\APP\ADMINISTRATOR\ORADATA\ORCL\TOOLS01.DBF’ to ‘D:\test\orcl\orcl\TOOLS01.DBF’ ;………………………………………..这里包括数据文件，日志文件，日志文件。
2，数据库正常打开
SQL> select open_mode from v$database;OPEN_MODE——————–READ WRITE3，查看数据库的版本与组件状态
SQL> @db_version.sql会话已更改。
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0 Production TNS for 64-bit Windows: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production COMP_ID COMP_NAME SCHEMA VERSION STATUS ————— ————————————————– ————— ———- —————-XDB Oracle XML Database XDB 11.2.0.3.0 VALID OWM Oracle Workspace Manager WMSYS 11.2.0.3.0 VALID CATPROC Oracle Database Packages and Types SYS 11.2.0.3.0 VALID CATALOG Oracle Database Catalog Views SYS 11.2.0.3.0 VALID ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS —————————— ———- ———- ———- ———- ————— ——————-2014-01-13 21:39:31.387000 APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0这里看到组件的状态都是VALID的，不过怀疑是假象。
4，查看无效对象
SQL> select owner,count(*) from dba_objects where owner in (‘SYS’,’SYSTEM’) group by owner order by owner; OWNER COUNT(*)
—————————— ———-
SYS 9614 SYSTEM 649 ``` 这里发现sys,system有大量的无效对象。5，utlrp编译无效对象SQL> alter system set "_system_trig_enabled"=false;
系统已更改。 这里将system_trig_enabled参数更改为false，因为怀疑是底层一些表或者是过程包出了问题。SQL> @?/rdbms/admin/utlrp.sql
运行到下面的时候报错SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
DECLARE
第 1 行出现错误:
ORA-04063: package body "SYS.DBMS_STATS" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_STATS" 的程序单元
ORA-06512: 在 "SYS.UTL_RECOMP", line 829
ORA-06512: 在 line 4
这里也提示了SYS.DBMS_STATS有问题。 6，配置10046，errorstack跟踪错误信息SQL> oradebug event 10046 trace name context forever,level 12;
SQL> oradebug event 4063 trace name errorstack level 12;
已处理的语句 这里没有发现太多有用的信息7，重建dbms_stats工具SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstai.plb
警告: 创建的包体带有编译错误。
PACKAGE BODY DBMS_STATS_INTERNAL 出现错误:
LINE/COL ERROR
——– —————————————————————–
2789/13 PLS-00323: 子程序或游标 ‘GATHER_SQL_STATS’ 已在程序包说明中声明,
必须在程序包体中对其进行定义。6909/3 PL/SQL: Item ignored
6912/26 PLS-00905: 对象 SYS.SQL_BINDS 无效
SQL> @?/rdbms/admin/prvtstat.plb
警告: 创建的包体带有编译错误。
PACKAGE BODY DBMS_STATS 出现错误:
LINE/COL ERROR
——– —————————————————————–
2049/3 PL/SQL: Item ignored
2049/34 PLS-00905: 对象 SYS.XMLTYPE 无效
20320/3 PL/SQL: Item ignored
20323/25 PLS-00905: 对象 SYS.XMLTYPE 无效
20603/25 PL/SQL: Item ignored
20603/25 PLS-00905: 对象 SYS.XMLTYPE 无效
20604/25 PL/SQL: Item ignored
20604/25 PLS-00905: 对象 SYS.SQL_BINDS 无效
20747/7 PL/SQL: Statement ignored
20747/7 PLS-00320: 此表达式的类型声明不完整或格式不正确
20779/5 PL/SQL: Statement ignored
LINE/COL ERROR
——– —————————————————————–
20782/31 PLS-00320: 此表达式的类型声明不完整或格式不正确
20797/5 PL/SQL: Statement ignored
20797/45 PLS-00320: 此表达式的类型声明不完整或格式不正确
SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’);
BEGIN dbms_stats.gather_table_stats(‘SCOTT’,’EMP’); END;
第 1 行出现错误:
ORA-04063: package body "SYS.DBMS_STATS" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_STATS" 的程序单元
ORA-06512: 在 line 1 ```
这里又报了一些其它的东西。
8，重新再跑建库脚本
@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/sqlplus/admin/pupbld.sql 还是报错 SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’); BEGIN dbms_stats.gather_table_stats(‘SCOTT’,’EMP’); END; * 第 1 行出现错误: ORA-04063: package body "SYS.DBMS_SQLTUNE" 有错误 ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_SQLTUNE" 的程序单元 ORA-06512: 在 "SYS.DBMS_STATS", line 23829 ORA-06512: 在 "SYS.DBMS_STATS", line 23880 ORA-06512: 在 line 1报这个错误
9，处理dbms_sqltune报错
SQL> drop public synonym XMLCONCAT;同义词已删除。SQL> alter package DBMS_SQLTUNE_INTERNAL compile body;程序包体已变更。再跑建库脚本再次收集统计信息SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’EMP’);PL/SQL 过程已成功完成。已经正常10，处理无效对象
SQL> select owner,count(*) from dba_objects where status=’INVALID’ and owner in (‘SYS’,’SYSTEM’) group by owner;OWNER COUNT(*)—————————— ———-SYSTEM 30SYS 2516SQL> @?/rdbms/admin/utlrp.sqlDOC> The following query reports the number of errors caught duringDOC> recompilation. If this number is non-zero, please query the errorDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due to misconfiguration or resource constraints that must beDOC> fixed before objects can compile successfully.DOC>#ERRORS DURING RECOMPILATION—————————4函数已创建。PL/SQL 过程已成功完成。函数已删除。Warning: XDB now invalid, could not find xdbconfigPL/SQL 过程已成功完成。这里只有8个无效对象了SQL> select owner,object_name,object_type ,status from dba_objects where status=’INVALID’ and OWNER=’SYS’;OWNER OBJECT_NAME OBJECT_TYPE STATUS———- ————————————————– ——————- ——-SYS XS$CATVIEW_UTIL PACKAGE BODY INVALIDSYS DBMS_NETWORK_ACL_ADMIN PACKAGE BODY INVALIDSYS DBMS_XS_PRINCIPAL_EVENTS_INT PACKAGE BODY INVALIDSYS AQ$AQ_EVENT_TABLE VIEW INVALIDSYS AQ$_AQ_EVENT_TABLE_F VIEW INVALIDSYS AQ$SCHEDULER$_REMDB_JOBQTAB VIEW INVALIDSYS AQ$_SCHEDULER$_REMDB_JOBQTAB_F VIEW INVALIDSYS KUPW$WORKER PACKAGE BODY INVALID11，xdb无效处理
SQL> @db_version.sql会话已更改。BANNER——————————————————————————–Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit ProductionPL/SQL Release 11.2.0.3.0 – ProductionCORE 11.2.0.3.0 ProductionTNS for 64-bit Windows: Version 11.2.0.3.0 – ProductionNLSRTL Version 11.2.0.3.0 – ProductionCOMP_ID COMP_NAME SCHEMA VERSION STATUS————— ————————————————– ————— ———- ———————XDB Oracle XML Database XDB 11.2.0.3.0 INVALIDOWM Oracle Workspace Manager WMSYS 11.2.0.3.0 VALIDCATPROC Oracle Database Packages and Types SYS 11.2.0.3.0 VALIDCATALOG Oracle Database Catalog Views SYS 11.2.0.3.0 VALIDSQL> grant execute on dbms_lob to xdb;SQL> grant execute on utl_file to xdb;SQL> startup upgradeSQL> ?/rdbms/adminxdbrelod.sql12，故障处理完成
SQL> set echo off 会话已更改。 BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production CORE 11.2.0.3.0 Production TNS for 64-bit Windows: Version 11.2.0.3.0 – Production NLSRTL Version 11.2.0.3.0 – Production 已选择5行。 COMP_ID COMP_NAME SCHEMA VERSION STATUS————— ————————————————– ————— ———- ——XDB Oracle XML Database XDB 11.2.0.3.0 VALIDOWM Oracle Workspace Manager WMSYS 11.2.0.3.0 VALIDCATPROC Oracle Database Packages and Types SYS 11.2.0.3.0 VALIDCATALOG Oracle Database Catalog Views SYS 11.2.0.3.0 VALID最后朋友得知，是因人误删除了底层表导致此问题。
------------------作者介绍-----------------------
姓名：黄廷忠 现就职：Oracle中国高级服务团队 曾就职：OceanBase、云和恩墨、东方龙马等 电话、微信、QQ：18081072613
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
