ORA-600[13013]处理过程

记录一次ORA-600[13013]处理过程在一次数据库的异常处理完成后，发现alert日志中出现ORA-600[13013]错误
Thu Mar 08 23:29:37 2012Errors 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.trcUse 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.trcORA-00600: internal error code, arguments: [13013], [5001], [518], [4198427], [170], [4198427], [17], [], [], [], [], []========= Dump forincident 38681 （ORA 600 [13013]） ========*** 2012-03-08 23:29:37.400dbkedDefDump（）: 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# = :objn and intcol# = :coln
MOS中关于ORA-600 [13013]描述
Format: ORA-600 [13013] [a] [b] {c} [d] [e] [f]Arg [a] PasscountArg [b] Data Object numberArg {c} Tablespace Decimal Relative DBA （RDBA） of block containing the row to be updatedArg [d] Row Slot numberArg [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） blocks3 fromdual;RFILE BLOCKS---------- ----------1 4123SQL> SELECTOWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME2 FROMDBA_EXTENTS A3 WHEREFILE_ID = &FILE_ID4 AND&BLOCK_ID BETWEENBLOCK_ID ANDBLOCK_ID + BLOCKS - 1;Enter value forfile_id: 1old 3: WHEREFILE_ID = &FILE_IDnew 3: WHEREFILE_ID = 1Enter value forblock_id: 4123old 4: AND&BLOCK_ID BETWEENBLOCK_ID ANDBLOCK_ID + BLOCKS - 1new 4: AND4123 BETWEENBLOCK_ID ANDBLOCK_ID + BLOCKS - 1OWNER 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 fileSQL> selectindex_name,COLUMN_NAME,COLUMN_POSITION FROMDBA_IND_COLUMNS2 WHERETABLE_NAME="COL_USAGE$";INDEX_NAME COLUMN_NAM COLUMN_POSITION--------------- ---------- ---------------I_COL_USAGE$ OBJ# 1I_COL_USAGE$ INTCOL# 2SQL> setautot trace expSQL> SELECT/*+ FULL（t1） */ OBJ#,INTCOL#2 FROMsys.COL_USAGE$ t13 MINUS4 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# isnotnull3 MINUS4 SELECT/*+ FULL（t1） */ OBJ#,INTCOL#5 FROMsys.COL_USAGE$ t1;OBJ# INTCOL#---------- ----------4294951004 24294951004 34294951004 44294951004 264294951004 274294951037 44294951037 54294951037 64294951037 94294951037 104294951840 11OBJ# INTCOL#---------- ----------4294951840 124294951906 44294952709 34294952867 44294952867 916 rowsselected.--证明index中的记录比表中多了16条
解决问题并验证
SQL> alterindexsys.I_COL_USAGE$ rebuild online;Indexaltered.SQL> SELECT/*+ FULL（t1） */ OBJ#,INTCOL#FROMsys.COL_USAGE$ t12 3 MINUS4 SELECT/*+ index（t I_COL_USAGE$） */ OBJ#,INTCOL#5 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull6 ;norowsselectedSQL> SELECT/*+ index（t I_COL_USAGE$） */ OBJ#,INTCOL#2 FROMsys.COL_USAGE$ t whereOBJ# isnotnullorINTCOL# isnotnull3 MINUS4 SELECT/*+ FULL（t1） */ OBJ#,INTCOL#5 FROMsys.COL_USAGE$ t1;norowsselected这次出现此问题的原因是因为在更新语句中使用索引找到一条记录，然后到表中去查询时该记录不存在，出现此错误，一般解决方法是重建索引Oracle 单实例从32位迁移到 64位方法 http://www.linuxidc.com/Linux/2012-03/55759.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址