Oracle数据库 ORA-600 [13013]故障处理
时间:2014-08-30 19:11 来源:linux.it.net.cn 作者:it
中午接到电话，客户的核心系统Oracle数据库实例宕机，远程过去在告警日志中看到大量的如下报错，报错很频繁：
......
Fri Jul 25 13:20:14 2014
Errors in file /u01/app/oracle/diag/rdbms/d012band/d012band/trace/d012band_smon_5964354.trc (incident=43361):
ORA-00600: internal error code, arguments: [13013], [5001], [268], [8452274], [7], [8452274], [17], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/d012band/d012band/incident/incdir_43361/d012band_smon_5964354_i43361.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 logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Fri Jul 25 13:20:17 2014
Dumping diagnostic data in directory=[cdmp_20140725132017], requested by (instance=1, osid=5964354 (SMON)), summary=[incident=43361].
Starting background process SMCO
......
一段时间后，最后实例被PMON进程终止，导致核心系统宕机。
客户环境：Oracle Database 11.2.0.3.0 for IBM AIX 6.1，单机，IBM HACMP主备模式保护。
参考《 New and Improved: ORA-600 [13013] "Unable to get a Stable set of Records" (文档 ID 1438920.1)》和《ORA-600 [13013] "Unable to get a Stable set of Records" (文档 ID 28185.1)》文章；该报错是由于对某个表执行DML操作，该表对应的某个索引损坏导致的，解决的办法是找出操作的表和受损的索引，重建索引即可。
下面是对ORA-600的参数说明：
6 Argument format
=================
This format relates to Oracle Server 8.0.3 and above
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
由此执行下面的SQL语句查询找出DML操作的表，以及表对应的索引：
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE DATA_OBJECT_ID=268;
发现问题对象是SYS用户下面的SMON_SCN_TIME表，SYS.SMON_SCN_TIME是Oracle更新非常频繁的一张表，该数据字典用于维护SCN和时间的关系。
SQL> SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='SMON_SCN_TIME';
发现这个表对应有SMON_SCN_TIME_TIM_IDX和SMON_SCN_TIME_SCN_IDX两个索引。
对表进行分析，找出有问题的索引：
SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE;
Table analyzed.
SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE;
table/Index Cross Reference Failure - see trace file
能够正常的分析表，但是不能正常的分析表上的索引。
参考《OERR: ORA-1499 table/Index Cross Reference Failure - see trace file (文档 ID 1499.1)》文章，尝试找出受影响的索引：
查看trace file:
BH (0x70000078fe2df98) file#: 2 rdba: 0x0081483e (2/84030) class: 1 ba: 0x70000078ce52000
set: 174 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 2272,19
dbwrid: 5 obj: 272 objn: 272 tsn: 1 afn: 2 hint: f
hash: [0x700000801ff36a0,0x700000801ff36a0] lru: [0x70000074bf81f40,0x70000074bf81ce0]
ckptq: [NULL] fileq: [NULL] objq: [0x7000007f59b4138,0x7000007f59b4138] objaq: [0x7000007f59b4128,0x7000007f59b4128]
use: [NULL] wait: [NULL] fast-cr-pins: 2
st: XCURRENT md: NULL fpin: 'kdiwh15: kdifxs' tch: 12
flags:
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 1 rdba: 0x0081483e (2/84030)
scn: 0x0000.2f4752d8 seq: 0x01 flg: 0x06 tail: 0x52d80601
frmt: 0x02 chkval: 0x0778 type: 0x06=trans data
0x0081483e要去掉0x，0x表示十六进制。
执行下面的SQL语句：
SELECT owner, segment_name, segment_type, partition_name
FROM DBA_SEGMENTS
WHERE header_file = (SELECT file#
FROM v$datafile
WHERE rfile# = dbms_utility.data_block_address_file(to_number('0080f8b2','XXXXXXXX'))
AND ts#= 1)
AND header_block = dbms_utility.data_block_address_block(to_number('0080f8b2','XXXXXXXX'));
可惜没有任何的查询结果。
继续参考《Instance Terminated With Error ORA-00474: SMON Process Terminated With Error (文档 ID 1361872.1)》文章，文章有下面一段描述：
CAUSE
ORA600 [13011] is raised due to indexes corruption. To verify the corruption run the following statements:
SQL> conn / as sysdba
SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE;
Table analyzed.
-- It should come out clean giving message table analyzed.
SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE;
-- it should fail with Ora-1499 if at least one index is corrupted
ORA-1499就是我们前面遇到的报错：
Error: ORA 1499
Text: table/Index Cross Reference Failure - see trace file
-------------------------------------------------------------------------------
Cause: An error occurred when validating an index or a table using the
ANALYZE command.
One or more entries does not point to the appropriate cross-reference.
Action: Check the trace file for more descriptive messages about the problem.
Correct these errors.
如果在分析表的时候收到ORA-1499的错误，表示至少有一个索引是损坏的。
SOLUTION
Rebuild corrupted indexes:
SQL> conn as sysdba
SQL> ALTER INDEX SMON_SCN_TIME_TIM_IDX REBUILD ONLINE;
SQL> ALTER INDEX SMON_SCN_TIME_SCN_IDX REBUILD ONLINE;
then re-run
SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE;
Table analyzed.
Note: The last statement should not report any errors.
两个索引重建之后，ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE语句能顺利的分析，告警日志不再有报错产生。
总结：要学会充分利用MOS，根据不同的线索搜索不同的文章来看，不要在一篇文章上吊死。
--end--
(责任编辑：IT)
| 中午接到电话，客户的核心系统Oracle数据库实例宕机，远程过去在告警日志中看到大量的如下报错，报错很频繁： ...... Fri Jul 25 13:20:14 2014 Errors in file /u01/app/oracle/diag/rdbms/d012band/d012band/trace/d012band_smon_5964354.trc (incident=43361): ORA-00600: internal error code, arguments: [13013], [5001], [268], [8452274], [7], [8452274], [17], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/d012band/d012band/incident/incdir_43361/d012band_smon_5964354_i43361.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 logging scn->time mapping. SMON encountered 1 out of maximum 100 non-fatal internal errors. Fri Jul 25 13:20:17 2014 Dumping diagnostic data in directory=[cdmp_20140725132017], requested by (instance=1, osid=5964354 (SMON)), summary=[incident=43361]. Starting background process SMCO ...... 一段时间后，最后实例被PMON进程终止，导致核心系统宕机。 客户环境：Oracle Database 11.2.0.3.0 for IBM AIX 6.1，单机，IBM HACMP主备模式保护。 参考《 New and Improved: ORA-600 [13013] "Unable to get a Stable set of Records" (文档 ID 1438920.1)》和《ORA-600 [13013] "Unable to get a Stable set of Records" (文档 ID 28185.1)》文章；该报错是由于对某个表执行DML操作，该表对应的某个索引损坏导致的，解决的办法是找出操作的表和受损的索引，重建索引即可。 下面是对ORA-600的参数说明： 6 Argument format ================= This format relates to Oracle Server 8.0.3 and above 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 由此执行下面的SQL语句查询找出DML操作的表，以及表对应的索引： SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE DATA_OBJECT_ID=268; 发现问题对象是SYS用户下面的SMON_SCN_TIME表，SYS.SMON_SCN_TIME是Oracle更新非常频繁的一张表，该数据字典用于维护SCN和时间的关系。 SQL> SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='SMON_SCN_TIME'; 发现这个表对应有SMON_SCN_TIME_TIM_IDX和SMON_SCN_TIME_SCN_IDX两个索引。 对表进行分析，找出有问题的索引： SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE; Table analyzed. SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE; table/Index Cross Reference Failure - see trace file 能够正常的分析表，但是不能正常的分析表上的索引。 参考《OERR: ORA-1499 table/Index Cross Reference Failure - see trace file (文档 ID 1499.1)》文章，尝试找出受影响的索引： 查看trace file: BH (0x70000078fe2df98) file#: 2 rdba: 0x0081483e (2/84030) class: 1 ba: 0x70000078ce52000 set: 174 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 2272,19 dbwrid: 5 obj: 272 objn: 272 tsn: 1 afn: 2 hint: f hash: [0x700000801ff36a0,0x700000801ff36a0] lru: [0x70000074bf81f40,0x70000074bf81ce0] ckptq: [NULL] fileq: [NULL] objq: [0x7000007f59b4138,0x7000007f59b4138] objaq: [0x7000007f59b4128,0x7000007f59b4128] use: [NULL] wait: [NULL] fast-cr-pins: 2 st: XCURRENT md: NULL fpin: 'kdiwh15: kdifxs' tch: 12 flags: LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] buffer tsn: 1 rdba: 0x0081483e (2/84030) scn: 0x0000.2f4752d8 seq: 0x01 flg: 0x06 tail: 0x52d80601 frmt: 0x02 chkval: 0x0778 type: 0x06=trans data 0x0081483e要去掉0x，0x表示十六进制。 执行下面的SQL语句： SELECT owner, segment_name, segment_type, partition_name FROM DBA_SEGMENTS WHERE header_file = (SELECT file# FROM v$datafile WHERE rfile# = dbms_utility.data_block_address_file(to_number('0080f8b2','XXXXXXXX')) AND ts#= 1) AND header_block = dbms_utility.data_block_address_block(to_number('0080f8b2','XXXXXXXX')); 可惜没有任何的查询结果。 继续参考《Instance Terminated With Error ORA-00474: SMON Process Terminated With Error (文档 ID 1361872.1)》文章，文章有下面一段描述： CAUSE ORA600 [13011] is raised due to indexes corruption. To verify the corruption run the following statements: SQL> conn / as sysdba SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE; Table analyzed. -- It should come out clean giving message table analyzed. SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE; -- it should fail with Ora-1499 if at least one index is corrupted ORA-1499就是我们前面遇到的报错： Error: ORA 1499 Text: table/Index Cross Reference Failure - see trace file ------------------------------------------------------------------------------- Cause: An error occurred when validating an index or a table using the ANALYZE command. One or more entries does not point to the appropriate cross-reference. Action: Check the trace file for more descriptive messages about the problem. Correct these errors. 如果在分析表的时候收到ORA-1499的错误，表示至少有一个索引是损坏的。 SOLUTION Rebuild corrupted indexes: SQL> conn as sysdba SQL> ALTER INDEX SMON_SCN_TIME_TIM_IDX REBUILD ONLINE; SQL> ALTER INDEX SMON_SCN_TIME_SCN_IDX REBUILD ONLINE; then re-run SQL> ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE; Table analyzed. Note: The last statement should not report any errors. 两个索引重建之后，ANALYZE TABLE smon_scn_time VALIDATE STRUCTURE CASCADE ONLINE语句能顺利的分析，告警日志不再有报错产生。 总结：要学会充分利用MOS，根据不同的线索搜索不同的文章来看，不要在一篇文章上吊死。 --end-- (责任编辑：IT) |