当前位置: 首页 > ORA > 正文

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_SYS

ORA-06552: PL/SQL: Compilation unit analysis terminated

ORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalid

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

ORA-06512: at line 1

Mon Aug 11 10:55:55 2014

ORA-942 encountered when generating server alert SMG-4120

ORA-942 encountered when generating server alert SMG-4121

OCI error in kwqrNonDurSubCln is OCIStmtExecute : return value -1 code 4045 buf ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQADM_SYS

ORA-06552: PL/SQL: Compilation unit analysis terminated

ORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalid

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

ORA-06512: at line 1

 

KSV 4045 error in slave process

 

*** 2014-08-11 10:55:26.426

ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQADM_SYS

ORA-06552: PL/SQL: Compilation unit analysis terminated

ORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalid

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

ORA-06512: at line 1

OPIRIP: Uncaught error 447. Error stack:

ORA-00447: fatal error in background process

ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQADM_SYS

ORA-06552: PL/SQL: Compilation unit analysis terminated

ORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalid

ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

ORA-06512: at line 1

exp,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 WRITE

3,查看数据库的版本与组件状态

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有大量的无效对象。

5utlrp编译无效对象

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,配置10046errorstack跟踪错误信息

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                                 30

SYS                                  2516

 

 

SQL> @?/rdbms/admin/utlrp.sql

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

 

ERRORS DURING RECOMPILATION

—————————

                          4

 

 

函数已创建。

 

 

PL/SQL 过程已成功完成。

 

 

函数已删除。

 

Warning: XDB now invalid, could not find xdbconfig

 

PL/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        INVALID

SYS        DBMS_NETWORK_ACL_ADMIN                             PACKAGE BODY        INVALID

SYS        DBMS_XS_PRINCIPAL_EVENTS_INT                       PACKAGE BODY        INVALID

SYS        AQ$AQ_EVENT_TABLE                                  VIEW                INVALID

SYS        AQ$_AQ_EVENT_TABLE_F                               VIEW                INVALID

SYS        AQ$SCHEDULER$_REMDB_JOBQTAB                        VIEW                INVALID

SYS        AQ$_SCHEDULER$_REMDB_JOBQTAB_F                     VIEW                INVALID

SYS        KUPW$WORKER                                        PACKAGE BODY        INVALID

 

11xdb无效处理

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 INVALID

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

 

SQL> grant execute on dbms_lob to xdb;

SQL> grant execute on utl_file to xdb;

SQL> startup upgrade

SQL> ?/rdbms/adminxdbrelod.sql

12,故障处理完成

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 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

最后朋友得知,是由于有人误删除了底层表导致的。

本文固定链接: http://www.htz.pw/2014/08/11/alert%e4%b8%8d%e5%81%9c%e6%8a%a5ora-06552ora-06553ora-006508%e9%94%99%e8%af%af.html | 认真就输

该日志由 huangtingzhong 于2014年08月11日发表在 ORA 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ALERT不停报ORA-06552/ORA-06553/ORA-006508错误 | 认真就输
关键字: