ORA-600 [qertbFetchByRowID] on SYS.ALERT_QT

在Metalink上的Note 389521.1中记录了这样一个Bug及解决办法。

症状: Getting ORA-600 [qertbFetchByRowID] when using SYS.ALERT_QT

Trace file shows the following statement

select sum(SYSDATE - cast(NVL(qt.delay, qt.enq_time) as date)) from
"SYS"."ALERT_QT" qt, "SYS"."AQ$_ALERT_QT_I" iot
where ((iot.subscriber# != 0 and iot.subscriber# in
(select subscriber_id from "SYS"."AQ$_ALERT_QT_S"where queue_name = :1
and (bitand(subscriber_type, 1) = 1))) or (iot.subscriber# = 0 and iot.queue# = :2))
and qt.msgid = iot.msgid;

SQL> analyze table sys.ALERT_Qt validate structure cascade;
analyze table sys.ALERT_Qt validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Cause

Corruption in the Index organised table associated with the queue table

Solution

The easy way to resolve the mismatch is to recreate the queue tables.

--------------------------------------------------------------------------------------------------------
NOTE: By running the script below, the tables will be recreated and the
messages in the queue will be lost
--------------------------------------------------------------------------------------------------------

1.

SQL> connect / as sysdba

SQL>alter system enable restricted session;

To drop server alert schema.

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

To re-create tables, sequence, type and queue for server alert

SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql

To recompile the invalid objects

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

SQL> alter system disable restricted session;

OR

2.

SQL> connect / as sysdba

SQL>alter system enable restricted session;

To drop server alert schema.

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

Rerun catproc.sql

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

SQL> alter system disable restricted session;

这个Case提供给我们重建ALERT_QT队列表的办法。在另外一个客户的案例中，同样在这个表上出现了如下错误：

kwqicaclcur: Error 600
             Cursor Session Number : 509
             Cursor Session Serial : 21274
             Cursor Pin     Number : 28681
Error 600 in Queue Table "SYS"."ALERT_QT"
error 600 encountered during serving 76700
*** 2010-10-25 15:00:30.744
ORA-00600: internal error code, arguments: [kclchkblk_3], [0], [184579233], [6], [], [], [], []
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00600: internal error code, arguments: [kclchkblk_3], [0], [184579233], [6], [], [], [], []

这同样是在ALERT_QT上出现了错误，所以我们实际上可以参考之前的处理方式尝试解决。