记录另一起ORA-00600[13013]处理

发现ORA-00600[13013]错误
During the execution of an UPDATE statement, after several attempts (Arg [a]passcount) we are unable to get a stable set of rows that conform to the WHEREclause.

Fri Jun 1 03:00:33 2012

Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_12104.trc:

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12596578], [17], []

Sat Jun 2 03:01:05 2012

Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_1052.trc:

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12596578], [17], []

Sun Jun 3 15:00:50 2012

Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_13876.trc:

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12603219], [17], []

Mon Jun 4 03:01:05 2012

Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_7704.trc:

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12596578], [17], []

Tue Jun 5 03:00:35 2012

Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_27983.trc:

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12596578], [17], []

Wed Jun 6 03:01:07 2012

Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_19204.trc:

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12596578], [17], []

Thu Jun 7 03:00:37 2012

Errors in file /opt/oracle/admin/oraapp/bdump/oraapp_m000_7273.trc:

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12605556], [17], []

以前处理过一次ORA-600[13013],里面包含了各参数含义,这次也按照常规方法处理,分析如下:
1.通过trace文件找出对应表

*** 2012-06-01 03:00:33.325

ksedmp: internal or fatal error

ORA-00600: internal error code, arguments: [13013],  [5001], [8943], [12596577], [25], [12596578], [17], []

Current SQL statement for this session:

UPDATE WRH$_SQL_BIND_METADATA SET snap_id =  :lah_snap_id WHERE dbid = :dbid AND (SQL_ID)

IN (SELECT STR1_KEWRATTR FROM X$KEWRATTRSTALE)

2.通过ORA-600[13013]中表示rdba参数找出表

SQL> select DBMS_UTILITY.data_block_address_file  (12596577) "file#",

2 DBMS_UTILITY.data_block_address_block (12596577) "block#"

3 from dual;

file# block#

---------- ----------

3 13665

SQL> select * from dba_extents where 13665 between block_id and block_id + blocks and file_id=3;

OWNER SEGMENT_NAME SEGMENT_TYPE EXTENT_ID FILE_ID  BLOCK_ID BLOCKS

---------- -------------------- ---------------  --------------- ---------- ------- ------

SYS SYS_LOB0000008933C00 LOBSEGMENT 7 3 13657 8

SYS WRH$_SQL_BIND_METADA TABLE 1 3 13665 8

检查对象WRH$_SQL_BIND_METADA是否有坏块或者表和index不一致

SQL> analyze table SYS.WRH$_SQL_BIND_METADATA validate structure cascade online;

Table analyzed.

这里分析WRH$_SQL_BIND_METADA表正常,但是通过上面的查询证明WRH$_SQL_BIND_METADA的第一个extent的第一个数据块上可能出现问题,使得analyze未检查(自己猜猜,未做深入验证).针对这个问题,直接备份WRH$_SQL_BIND_METADATA表,truncate掉该表,然后重新插入数据(注意操作时间避开awr插入数据时间段)

create table SQL_BIND_METADATA_BAK

AS

SELECT * FROM SYS.WRH$_SQL_BIND_METADATA;

TRUNCATE TABLE SYS.WRH$_SQL_BIND_METADATA;

INSERT INTO SYS.WRH$_SQL_BIND_METADATA

SELECT * FROM SQL_BIND_METADATA_BAK;

DROP TABBLE SQL_BIND_METADATA_BAK PURGE;