记录一次ora-600[13013]处理过程，在一次数据库的异常处理完成后，发现alert日志中出现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
本文永久更新链接地址：