ORA-600[13013]處理過程

ORA-600[13013]處理過程

記錄一次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

這次出現此問題的原因是因為在更新語句中使用索引找到一條記錄，然後到表中去查詢時該記錄不存在，出現此錯誤，一般解決方案是重建索引

Oracle 單一實例 從32位 遷移到 64位 方法 

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2