数据库ALERT日志报：ORA-24010错误！HELP

xingzxzh

    遇到ORA-24010错误，已经折磨了一个月，请大神帮忙分析，先谢过了！
      基本信息：数据库版本11.1.0.7.0，操作系统AIX 6.1，单实例。
      问题描述：
1：数据库报ALERT日志中每天都报ORA-24010错误，SELECT NAME FROM USER_QUEUES;无内容，报错的当天数据库的DBLINK全部消失。
检查发现system.aq$_queues , system.aq$_queue_tables 两张表没有内容。数据库EXPDP、EXP导出分别提示一下错误：

a：expdp导出数据报错

$ expdp \"sys/******* as sysdba\" schemas=DM_APP directory=wzx_dir dumpfile=DM_APP_20140428.dmp logfile=DM_APP_20140428.log compression=data_only;
Export: Release 11.1.0.7.0 - 64bit Production on Monday, 28 April, 2014 17:44:01
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 736
ORA-39080: failed to create queues "" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1665
ORA-01403: no data found

b：EXP导出数据报错

$ exp  \"sys/******** as sysdaba\" owner=DM_APP file=/archivlog/wzx/DM_APP_20140428_exp.dmp log=DM_APP_20140428_exp.log buffer=4094000
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "SYS.DBMS_REPCAT_UTL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REPCAT_UTL"
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 87
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_REPCAT_EXP.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.
You have mail in /usr/spool/mail/oracle


2:测试环境模拟删除 system.aq$_queues , system.aq$_queue_tables两张表执行A B C 脚本，system.aq$_queues , system.aq$_queue_tables 两张表依然无内容。ALERT依然会报错。
a. SQL> @?/rdbms/admin/catalog.sql

b. SQL> @?/rdbms/admin/catproc.sql

c. SQL> utlrp.sql

xingzxzh

C.步骤描述有错误，正确的是：@?/rdbms/admin/utlrp.sql

Maclean Liu(刘相兵

ODM FINDING:

        Errors ORA-31626 ORA-31637 ORA-06512 ORA-39080 ORA-1403 Received When Using DataPump Export (Doc ID 744700.1)       

There are several solutions to this issue.  To implement the solution, please execute one of the following steps:

1. Upgrade to the 10.2.0.4 or 11.1.0.6 or higher releases:

Or:

2. If available for your platform, download and install Patch 4478139 and then reload the data pump types and views as outlined in Note:430221.1

connect as SYS then run the following:

SQL >@ $ORACLE_HOME\rdbms\admin\catdph.sql
Note: If XDB is installed, then it is required to run "catmetx.sql" script also.
Use this code to verify if XDB is installed:

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status
from dba_registry;

Sample output if XDB installed,
     Oracle XML Database XDB -version- VALID

SQL >@ $ORACLE_HOME\rdbms\admin\prvtdtde.plb
SQL >@ $ORACLE_HOME\rdbms\admin\catdpb.sql
SQL >@ $ORACLE_HOME\rdbms\admin\dbmspump.sql

Or:

3. Use the workaround:

As the SYSDBA user

Shutdown database
Start the database in the UPGRADE mode
Drop the excess SYS roles:
drop role <role name>;
Reload the data pump types and views as outlined in Note:430221.1:
connect as SYS then run the following:

SQL >@ $ORACLE_HOME\rdbms\admin\catdph.sql
Note: If XDB is installed, then it is required to run "catmetx.sql" script also.
Use this code to verify if XDB is installed:

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status
from dba_registry;

Sample output if XDB installed,
     Oracle XML Database XDB -version- VALID

SQL >@ $ORACLE_HOME\rdbms\admin\prvtdtde.plb
SQL >@ $ORACLE_HOME\rdbms\admin\catdpb.sql
SQL >@ $ORACLE_HOME\rdbms\admin\dbmspump.sql

Shutdown/startup

xingzxzh

Maclean Liu(刘相兵 发表于 2014-5-19 17:18
ODM FINDING:

        Errors ORA-31626 ORA-31637 ORA-06512 ORA-39080 ORA-1403 Received When Using DataPump  ...

以下是错做的具体步骤，错误未消除。附件为执行以下步骤过程中的ALERT日志文件。

sqlplus / as sysdba
1：查询USER_QUEUES， system.aq$_queues ，system.aq$_queue_tables数据
SQL> SELECT NAME FROM USER_QUEUES;

NAME
--------------------------------------------------------------------------------
AQ$_SYS$SERVICE_METRICS_TAB_E
SYS$SERVICE_METRICS
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_AQ_PROP_TABLE_E
AQ_PROP_NOTIFY
AQ$_AQ_SRVNTFN_TABLE_E
AQ_SRVNTFN_TABLE_Q
AQ$_AQ_EVENT_TABLE_E
AQ_EVENT_TABLE_Q
AQ$_AQ$_MEM_MC_E
SRVQUEUE

NAME
--------------------------------------------------------------------------------
AQ$_ALERT_QT_E
ALERT_QUE
AQ$_SCHEDULER$_REMDB_JOBQTAB_E
SCHEDULER$_REMDB_JOBQ
AQ$_SCHEDULER$_EVENT_QTAB_E
SCHEDULER$_EVENT_QUEUE

17 rows selected.


select count(*) from system.aq$_queues;
select count(*) from system.aq$_queue_tables;

SQL> select count(*) from system.aq$_queues;

  COUNT(*)
----------
        35

SQL> select count(*) from system.aq$_queue_tables;

  COUNT(*)
----------
        18

2：删除system.aq$_queues，system.aq$_queues_tables数据
delete         from system.aq$_queues;
delete  from system.aq$_queue_tables;


SQL> delete     from system.aq$_queues;

35 rows deleted.

SQL> delete  from system.aq$_queue_tables;

18 rows deleted.


SQL> SELECT NAME FROM USER_QUEUES;

no rows selected

SQL>  select count(*) from system.aq$_queues;

  COUNT(*)
----------
         0

SQL> select count(*) from system.aq$_queue_tables;

  COUNT(*)
----------
         0





3：EXPDP导出报错
oracle@linux-suse11-01:~> expdp /"sys/oracle as sysdba/" full=y  dumpfile=1.dump logfile=1.log;

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 19 May, 2014 20:03:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 736
ORA-39080: failed to create queues "" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1665
ORA-01403: no data found

4：关闭数据库
shutdown immediate;

5：启动到UPGRADE模式
startup upgrade;

select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status
from dba_registry where comp_id='XDB';

Oracle XML Database        XDB        11.1.0.7.0        VALID

6：执行SQL脚本，截取部分信息。
@$ORACLE_HOME/rdbms/admin/catdph.sql;

SQL> @$ORACLE_HOME/rdbms/admin/catdph.sql;

Grant succeeded.


PL/SQL procedure successfully completed.


Grant succeeded.


Grant succeeded.


Grant succeeded.


PL/SQL procedure successfully completed.


Grant succeeded.


PL/SQL procedure successfully completed.

BEGIN
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7



PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Package created.


Package body created.


PL/SQL procedure successfully completed.


@$ORACLE_HOME/rdbms/admin/catmetx.sql;
SQL> @$ORACLE_HOME/rdbms/admin/catmetx.sql;

Package altered.


Index altered.


View created.


Grant succeeded.


View created.


Grant succeeded.


Procedure created.


System altered.


System altered.


System altered.


PL/SQL procedure successfully completed.


Procedure dropped.


Package altered.


Package created.

No errors.

Type created.


Package body created.

No errors.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.




PL/SQL procedure successfully completed.


Package dropped.


@$ORACLE_HOME/rdbms/admin/prvtdtde.plb   
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql


SQL>
SQL> @$ORACLE_HOME/rdbms/admin/prvtdtde.plb

Library created.

No errors.

Package created.


Synonym created.


Package created.

SQL> @$ORACLE_HOME/rdbms/admin/catdpb.sql


Commit complete.


Table created.


Grant succeeded.


Grant succeeded.


Grant succeeded.

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

Type created.


Type created.


Library created.


Type body created.


Type body created.

7：执行完以上步骤，system.aq$_queues，system.aq$_queue_tables 表数据未回复，EXPDP 导出还是同样错误。

SQL> SELECT NAME FROM USER_QUEUES;

no rows selected

SQL> select count(*) from system.aq$_queues;
select count(*) from system.aq$_queue_tables;
  COUNT(*)
----------
         0

SQL>

  COUNT(*)
----------
         0


Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
oracle@linux-suse11-01:/opt/oracle/product/11g/db> expdp \"sys/oracle as sysdba\" full=y  dumpfile=1.dump logfile=1.log;

Export: Release 11.1.0.7.0 - 64bit Production on Monday, 19 May, 2014 22:21:09

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 736
ORA-39080: failed to create queues "" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1665
ORA-01403: no data found