【Oracle 故障处理一则】 ORA-600 - 13013处理过程
参考:http://blog.itpub.net/4227/viewspace-714775/
1.告警错误：alert.log
Errors in file /opt/oracle/diag/rdbms/udb/udb/trace/udb_ora_47522.trc (incident=337044):
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
Mon Apr 14 15:05:08 2014
Errors in file /opt/oracle/diag/rdbms/udb/udb/trace/udb_smon_55397.trc (incident=336833):
ORA-00600: internal error code, arguments: [13013], [5001], [267], [8407725], [5], [8407725], [17], [], [], [], [], []
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 6 out of maximum 100 non-fatal internal errors.
2.处理过程—重建索引
ORA-00600: internal error code, arguments: [13013], [5001], [267], [8407725], [5], [8407725], [17], [], [], [], [], []
MOS:可能与267对象以及索引损坏有关，要进行重建
SQL> select object_name,object_type,owner from dba_objects where data_object_id=267;
SMON_SCN_TIME TABLE SYS
SMON_SCN_TO_TIME_AUX CLUSTER SYS
找到对象：SMON_SCN_TIME 和 SMON_SCN_TO_TIME_AUX
SQL> SELECT index_name FROM dba_indexes WHERE table_name='SMON_SCN_TIME';
INDEX_NAME
------------------------------
SMON_SCN_TIME_SCN_IDX
SMON_SCN_TIME_TIM_IDX
找到指定的索引：
使用方法1修复索引：
alter index SMON_SCN_TIME_SCN_IDX rebuild;
alter index SMON_SCN_TIME_TIM_IDX rebuild;
使用方法2修复索引:
（1）查找创建索引的语句
select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX') from dual;
select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX') from dual;
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("SCN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("TIME_MP")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
（2）删除索引
drop index SMON_SCN_TIME_SCN_IDX ;
drop index SMON_SCN_TIME_TIM_IDX ;
（3）执行上面的语句，重建索引
3.MOS 解释
Section 1> What information needs to be Collected
ORA-600 [13013] [a] [b] [c] [d] [e] [f]
This format relates to Oracle Server 8.0.3 to 10.1
Arg [a] Passcount
Arg [b] Data Object number
Arg [c] Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as [c])
Arg [f] Code
The Second argument would give the information about the data object id.
This would give information about the object involved.
SQL>Select object_name,object_type,owner from dba_objects where data_object_id=<value reported in argment b>
Once the Object is identified run the following :
The below command check if the table has corruption or not .
SQL> Analyze table <owner>.<table name> validate structure online ;
If this goes fine table doesnot have corruption. For next command.
If the above command fails with ORA-1498 go to Section 3
The below command check if table/index has corruption or not
SQL>Analyze table <owner>.<table name> validate structure cascade online ;
If the above command errors out with ora-1499 it indicates a corruption in index.
Go to section 2 for resolution
Run dbverify on the datafile reported in the error
Arg [c] in the ora-0600[13013] would give the Relative DBA
For example
ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],
[155254965], [17], []
Arg [c] --> rdba-->155254965
Use this value and find the file and block number for this dba
select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;
RFILE# Block#
---------- ----------
37 65717
You an run dbveirfy on datafile with rfile#=37
SQL>Select name from v$datafile where rfile#=37
dbv file=<location of datafile> blocksize=<db_block_size>
Section 2 >How to resolve if a Index is corrupted
You would need to drop and recreate the index
Ensure before dropping the Index
SQL>Spool /tmp/createindex.sql
SQL>Set long 100000000
SQL>Select dbms_metadata.get_ddl('INDEX','<Index name>',<'user name>') from dual
SQL>Spool off
Refer the Following note to Identify the index
Note 563070.1
Title: ORA-1499. Table/Index row count mismatch
Please note if there is just one index in the table then you can use dbms_metadata.get_ddl to get the script of the index and drop and recreate it.
Section 3> How to resolve if table is corrupted
Option a> Backup is available
Ora-1498 would be reported on the table.
The trace file from Ora-1498 would contain following information
Example
Block Checking: DBA = 1066265208, Block Type = KTB-managed data block --->
file 254,block 911992
data header at 0xc00000010118e07c
kdbchk: avsp(816) > tosp(812)
Block header dump: 0x3f8dea78
Object id on Block? Y
seg/obj: 0x155b452 csc: 0x05.7b2b4ee6 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3f8dde0c ver: 0x01
inc: 0 exflg: 0
Note the DBA value reported in the trace file DBA = 1066265208
Convert this to find the file number and block number having issue
Sql>Select dbms_utility.data_block_address_file('1066265208') from dual ;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE('1066265208')
--------------------------------------------------
254
Sql>Select dbms_utility.data_block_address_block('1066265208') from dual ;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK('1066265208')
---------------------------------------------------
911992
Run dbverify on the datafile containing the table
dbv file=<location of datafile> blocksize=<db_block_size>
Corruption would be reported on the block.
If you have an Rman backup do a Rman block recovery (Take the file number and block number affected from the trace file)
Rman> Blockrecover datafile <no> block <block number>
Or
If you have a user managed backup you can do an restore and recovery from good copy of the problematic datafile
Option b> Backup is not available
If no backups are available then use event 10231 at session level to create a salvage table
SQL>alter session set events '10231 trace name context forever, level 10'
SQL> Create table <owner>.salvage_table as select * from <Corrupted table> ;
Rename the Original table to old.
Rename salvage table to Original table name
or
You can use dbms_repair script to mark the block soft corrupt.
Note 556733.1
DBMS_REPAIR SCRIPT
How to resolve when Smon terminates the instance due to Ora-00600[13013]
If Smon is terminating the instance then.
Set event 10513 and startup the database
event="10513 trace name context forever, level 2"
SQL>Startup mount ;
SQL>Show parameter event
SQL>Alter datatabase open ;
Identify the object involved using information from Section 1.
How to resolve the issue if the object involved belongs to system tablespace
System objects are very important.
Please open a Service request with Oracle support if system tables are involved.
Identifying the row having issue when the table is having corruption.
1>Once the error occurs, using the ORA-600 arguments, gather the following information :
ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],
[155254965], [17], []
arg b : [57353] - it is the OBJECT_ID
arg c : [155254965] - it is the block address in Decimal
arg d : [261] - is it the slot number
2. Translate the the block address in Decimal to a file# and block #
select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;
RFILE# Block#
---------- ----------
37 65717
The Relative file is 37
The block number is 65717
Find the data_object_id for this object
Argument b is Object_id--> 57353
SQL>Select data_object_id ,object_name,owner from dba_objects where object_id=57353 ;
3. Create the rowid using dbms_rowid.rowid_create(1,DATA_OBJECT_ID,FILE#,BLOCK#,SLOT#)
In this case :
select dbms_rowid.rowid_create(1,57353,37,65717,261) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAOAJAAlAAAQC1AEF
4. You can select from table and identify the record causing the issue
SQL> Select * from <owner>.<table name> where rowid='AAAOAJAAlAAAQC1AEF';
Are there any Known Bug
There is a internal bug number 5085288
fixed in 11.1
Details
ORA-600 [13013] [5001] error can occur on a MERGE command if the DELETE pass encounters a consistent read (CR) error due to the update pass having updated the same
row and column previously.
Check for availability of one off patch using patch 5085288
Bug 4549673
Abstract: ORA-30926 / OERI:13030 during update
Fixed-Releases: 9208 A204 B106
Details:
ORA-30926 (in Oracle 9i) or ORA-600 [13030] (in Oracle10g) can occur
during an update DML. This can occur if an internal ORA-1551 error
occurs and is trapped (1551 errors are not visible to client
code and are trapped and handled internally)
Fixed In Ver: 11.0
Check for availability of one off patch using patch 5085288
