记录一次ORA-600[13013]处理过程

发表于 2012 年 3 月 9 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：记录一次ORA-600[13013]处理过程

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在一次数据库的异常处理完成后，发现alert日志中出现ORA-600[13013]错误

Thu Mar 08 23:29:37 2012
Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc  (incident=38681):
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/chf/chf/incident/incdir_38681/chf_smon_24137_i38681.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Non-fatal internal error happenned while SMON was doing flushing of monitored table stats.
SMON encountered 1 out of maximum 100 non-fatal internal errors.

trace文件中信息
从这里可以看出是对sys.col_usage$表进行update操作导致该错误发生

Dump continued from file: /opt/oracle/diag/rdbms/chf/chf/trace/chf_smon_24137.trc
ORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []

========= Dump for incident 38681 (ORA 600 [13013]) ========

*** 2012-03-08 23:29:37.400
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=3c1kubcdjnppq) -----
update sys.col_usage$ set   equality_preds    = equality_preds    + decode(bitand(:flag,1),0,0,1),   equijoin_preds    = equijoin_preds    + decode(bitand(:flag,2),0,0
,1),   nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1),   range_preds       = range_preds       + decode(bitand(:flag,8),0,0,1),   like_preds    
    = like_preds        + decode(bitand(:flag,16),0,0,1),   null_preds        = null_preds        + decode(bitand(:flag,32),0,0,1),  timestamp = :time where obj# = :ob
jn and intcol# = :coln

MOS中关于ORA-600 [13013]描述

Format: ORA-600 [13013] [a] [b] {c} [d] [e] [f]
Arg [a] Passcount
Arg [b] Data Object number
Arg {c} Tablespace Decimal Relative DBA (RDBA) of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Decimal RDBA of block being updated (Typically same as {c})
Arg [f] Code

验证MOS中描述

SQL> select dbms_utility.data_block_address_file(4198427) rfile,
  2  dbms_utility.data_block_address_block(4198427) blocks
  3  from dual;

     RFILE     BLOCKS
---------- ----------
         1       4123

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
Enter value for file_id: 1
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 1
Enter value for block_id: 4123
old   4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
new   4:    AND 4123 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE PARTI
----- ------------ ---------- ---------- -----
SYS   COL_USAGE$   TABLE      SYSTEM
--和trace文件中异常表一致

SQL>  select object_type,object_name from dba_objects where object_id=518;

OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
TABLE               COL_USAGE$
--也和trace文件中异常表一致

分析异常表

SQL> ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.COL_USAGE$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

SQL> select index_name,COLUMN_NAME,COLUMN_POSITION FROM DBA_IND_COLUMNS 
2    WHERE TABLE_NAME='COL_USAGE$';

INDEX_NAME      COLUMN_NAM COLUMN_POSITION
--------------- ---------- ---------------
I_COL_USAGE$    OBJ#                     1
I_COL_USAGE$    INTCOL#                  2

SQL> set autot trace exp
SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t1
  3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null;

no rows selected
--无记录返回

Execution Plan
----------------------------------------------------------
Plan hash value: 399371572

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time
   |

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

|   0 | SELECT STATEMENT    |              |  4262 | 76716 |    27  (71)| 00:00:01 |
|   1 |  MINUS              |              |       |       |            |          |
|   2 |   SORT UNIQUE       |              |  4262 | 38358 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| COL_USAGE$   |  4262 | 38358 |     8   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|              |  4262 | 38358 |    18   (6)| 00:00:01 |
|*  5 |    INDEX FULL SCAN  | I_COL_USAGE$ |  4262 | 38358 |    17   (0)| 00:00:01 |

------------------------------------------------------------------------------------
--验证表两个sql是否正确(一个全表扫描，另个index 快速扫描)


SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;

      OBJ#    INTCOL#
---------- ----------
4294951004          2
4294951004          3
4294951004          4
4294951004         26
4294951004         27
4294951037          4
4294951037          5
4294951037          6
4294951037          9
4294951037         10
4294951840         11

      OBJ#    INTCOL#
---------- ----------
4294951840         12
4294951906          4
4294952709          3
4294952867          4
4294952867          9

16 rows selected.
--证明index中的记录比表中多了16条

解决问题并验证

SQL> alter index sys.I_COL_USAGE$ rebuild online;

Index altered.

SQL>  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
 FROM sys.COL_USAGE$ t1
  2    3   MINUS
  4   SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  6  ;

no rows selected

SQL>  SELECT /*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL#
  2   FROM sys.COL_USAGE$ t where OBJ# is not null or INTCOL# is not null
  3   MINUS
  4  SELECT /*+ FULL(t1) */ OBJ#,INTCOL#
  5   FROM sys.COL_USAGE$ t1;

no rows selected

这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引
官方关于ORA-600[13013]说明

此条目发表在 ORA-xxxxx 分类目录，贴了 , 标签。将固定链接加入收藏夹。

《记录一次ORA-600[13013]处理过程》有 3 条评论

  1. HANK 说：
    2013 年 7 月 15 日11:58

    HANK,

    看到了知道了

  2. HANK 说：
    2013 年 7 月 15 日11:58

    怎么知道4198427是BLOCK地址呢

  3. 惜分飞 说：
    2012 年 3 月 9 日16:18

    补充一个类此的错误案例ORA-600 [13011]
    alert文件

    Dumping diagnostic data in directory=[cdmp_20120309160037], requested by (instance=1, osid=26630 (J000)), summary=[abnormal process termination].
Errors in file /opt/oracle/diag/rdbms/chf/chf/trace/chf_j000_26630.trc  (incident=39782):
ORA-00600: internal error code, arguments: [13011], [50687], [12612847], [1], [12617474], [0], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/chf/chf/incident/incdir_39782/chf_j000_26630_i39782.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

    trace文件

    Dump continued from file: /opt/oracle/diag/rdbms/chf/chf/trace/chf_j000_26630.trc
ORA-00600: internal error code, arguments: [13011], [50687], [12612847], [1], [12617474], [0], [], [], [], [], [], []

========= Dump for incident 39782 (ORA 600 [13011]) ========

*** 2012-03-09 16:00:42.937
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=11p815z8hkfms) -----
DELETE MGMT_SYSTEM_PERFORMANCE_LOG WHERE TIME < :B2 AND ROWNUM <= :B1

    MOS解释

    ERROR: 
  Format: ORA-600 [13011] [a] [b] [c] [d] [e]
 
VERSIONS:
  versions 7.3 and above
 

ARGUMENTS:

  The number of arguments and their meaning vary depending on the 
  object types relating to the error.

3 Argument format
=================

This format relates to Oracle 8.0.3 and above for deletes from Views or Index Only Tables (IOT)

  Arg [a] Passcount
  Arg [b] Object Name
  Arg [c] Code

5 Argument format
=================

  Arg [a] Data Object ID
  Arg [b] Tablespace Decimal Relative DBA (RDBA) of block containing the row to be deleted
  Arg [c] Row slot number.
  Arg [d] Decimal RDBA of block being updated (Typically same as [b]). 
  Arg [e] Code


SUGGESTIONS:        

  Run ANALYZE INDEX <index_name> VALIDATE STRUCTURE;
  on any indices involved.  Drop and recreate if needed.

  If the Known Issues section below does not help in terms of identifying
  a solution, please submit the trace files and alert.log to Oracle 
  Support Services for further analysis.

    处理方法

    SQL> select object_type,object_name,owner from dba_objects where DATA_OBJECT_ID=50687;

OBJECT_TYPE         OBJECT_NAME                         OWNER
------------------- ----------------------------------- --------
TABLE               MGMT_SYSTEM_PERFORMANCE_LOG         SYSMAN
SQL> select index_name,owner from dba_indexes where table_name='MGMT_SYSTEM_PERFORMANCE_LOG';

INDEX_NAME                     OWNER
------------------------------ --------
MGMT_SYSTEM_PERF_LOG_IDX_02    SYSMAN
MGMT_SYSTEM_PERF_LOG_IDX_01    SYSMAN

SQL> alter index SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_02 rebuild online;

Index altered.

SQL>  alter index SYSMAN.MGMT_SYSTEM_PERF_LOG_IDX_01 rebuild online;

Index altered.