记录一次ORA-600[13013]处理过程
在一次数据库的异常处理完成后，发现alert日志中出现ORA-600[13013]错误
| Thu Mar 08 23:29:37 2012 Errors infile/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 forerror and packaging details. Non-fatal internal error happenned whileSMON 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 forincident 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 forthis session (sql_id=3c1kubcdjnppq) ----- update sys.col_usage$ setequality_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 = :timewhere 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> selectdbms_utility.data_block_address_file(4198427) rfile, 2 dbms_utility.data_block_address_block(4198427) blocks 3 fromdual; RFILE BLOCKS ---------- ---------- 1 4123 SQL> SELECTOWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME 2 FROMDBA_EXTENTS A 3 WHEREFILE_ID = &FILE_ID 4 AND&BLOCK_ID BETWEENBLOCK_ID ANDBLOCK_ID + BLOCKS - 1; Enter value forfile_id: 1 old 3: WHEREFILE_ID = &FILE_ID new 3: WHEREFILE_ID = 1 Enter value forblock_id: 4123 old 4: AND&BLOCK_ID BETWEENBLOCK_ID ANDBLOCK_ID + BLOCKS - 1 new 4: AND4123 BETWEENBLOCK_ID ANDBLOCK_ID + BLOCKS - 1 OWNER SEGMENT_NAME SEGMENT_TY TABLESPACE PARTI ----- ------------ ---------- ---------- ----- SYS COL_USAGE$ TABLESYSTEM --和trace文件中异常表一致 SQL> selectobject_type,object_name fromdba_objects whereobject_id=518; OBJECT_TYPE OBJECT_NAME ------------------- ------------------------------ TABLECOL_USAGE$ --也和trace文件中异常表一致 |
分析异常表
| SQL> ANALYZE TABLEsys.COL_USAGE$ VALIDATE STRUCTURE CASCADE; ANALYZE TABLEsys.COL_USAGE$ VALIDATE STRUCTURE CASCADE * ERROR atline 1: ORA-01499: table/indexcrossreference failure - see trace file SQL> selectindex_name,COLUMN_NAME,COLUMN_POSITION FROMDBA_IND_COLUMNS 2 WHERETABLE_NAME='COL_USAGE$'; INDEX_NAME COLUMN_NAM COLUMN_POSITION --------------- ---------- --------------- I_COL_USAGE$ OBJ# 1 I_COL_USAGE$ INTCOL# 2 SQL> setautot trace exp SQL> SELECT/*+ FULL(t1) */ OBJ#,INTCOL# 2 FROMsys.COL_USAGE$ t1 3 MINUS 4 SELECT/*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL# 5 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull; norowsselected --无记录返回 Execution Plan ---------------------------------------------------------- Plan hash value: 399371572 ------------------------------------------------------------------------------------ | Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECTSTATEMENT | | 4262 | 76716 | 27 (71)| 00:00:01 | | 1 | MINUS | | | | | | | 2 | SORT UNIQUE| | 4262 | 38358 | 9 (12)| 00:00:01 | | 3 | TABLEACCESS FULL| COL_USAGE$ | 4262 | 38358 | 8 (0)| 00:00:01 | | 4 | SORT UNIQUENOSORT| | 4262 | 38358 | 18 (6)| 00:00:01 | |* 5 | INDEXFULLSCAN | I_COL_USAGE$ | 4262 | 38358 | 17 (0)| 00:00:01 | ------------------------------------------------------------------------------------ --验证表两个sql是否正确(一个全表扫描，另个index 快速扫描) SQL> SELECT/*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL# 2 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull 3 MINUS 4 SELECT/*+ FULL(t1) */ OBJ#,INTCOL# 5 FROMsys.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 rowsselected. --证明index中的记录比表中多了16条 |
解决问题并验证
| SQL> alterindexsys.I_COL_USAGE$ rebuild online; Indexaltered. SQL> SELECT/*+ FULL(t1) */ OBJ#,INTCOL# FROMsys.COL_USAGE$ t1 2 3 MINUS 4 SELECT/*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL# 5 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull 6 ; norowsselected SQL> SELECT/*+ index(t I_COL_USAGE$) */ OBJ#,INTCOL# 2 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull 3 MINUS 4 SELECT/*+ FULL(t1) */ OBJ#,INTCOL# 5 FROMsys.COL_USAGE$ t1; norowsselected |
这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引
本文转自东方之子736651CTO博客，原文链接：http://blog.51cto.com/ecloud/1561684 ，如需转载请自行联系原作者
