otak-otak | favorite evening food

December 30, 2011

ORA-600 17182 Block corruption

Filed under: troubleshoot — ora62 @ 2:12 pm

Suddenly one of my customer make a call at night about ora-600 of their core system…

logging into machine and grep the alert log, found the ora-600; database can be opened for a while (about 2 minutes) and then terminate.

Doing block recovery for file 7 block 15312
Block recovery from logseq 147867, block 3707 to scn 3789052035
Recovery of Online Redo Log: Thread 1 Group 6 Seq 147867 Reading mem 0
Mem# 0: /redolog1/dbname/redo06.log
Block recovery stopped at EOT rba 147867.3776.16
Block recovery completed at rba 147867.3776.16, scn 0.3789052035
Thu Dec 29 21:35:26 2011
Completed: alter database open
Thu Dec 29 21:35:29 2011
Starting background process CJQ0
Thu Dec 29 21:35:29 2011
CJQ0 started with pid=45, OS id=192
Thu Dec 29 21:35:29 2011
Sweep Incident[95434]: completed
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc:
SVR4 Error: 2: No such file or directory
Additional information: 3
ORACLE Instance dbname (pid = 13) – Error 600 encountered while recovering transaction (203, 27) on object 79208.
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc:
ORA-00600: internal error code, arguments: [17182], [0xFFFFFFFF7B81D750], [], [], [], [], [], []
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc (incident=95435):
ORA-00600: internal error code, arguments: [KSMFPG2], [0xFFFFFFFF7B81D000], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17182], [0xFFFFFFFF7B81D750], [], [], [], [], [], []
Incident details in: /oracle/diag/diag/rdbms/dbnameg/dbname/incident/incdir_95435/dbname_smon_29913_i95435.trc
Thu Dec 29 21:35:32 2011
Trace dumping is performing id=[cdmp_20111229213532]
Thu Dec 29 21:35:33 2011
Fatal internal error happened while SMON was doing active transaction recovery.
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc:
ORA-00600: internal error code, arguments: [KSMFPG2], [0xFFFFFFFF7B81D000], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17182], [0xFFFFFFFF7B81D750], [], [], [], [], [], []
SMON (ospid: 29913): terminating the instance due to error 474
Instance terminated by SMON, pid = 29913

from the alert log I found there are corruption at block level, but oracle actualy can recovered it automatically, but it seems when there is undo application for a transaction got stuck due to block corrupt at the other block.

find the object name as informed by alertlog:

select owner,object_name from dba_objects where object_id=79208;

now time to scan all the logical corruption by rman validate command:

run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

confirmed there are 2 block corruption on the table:

Error backing up file 7, block 15312: logical corruption
Error backing up file 7, block 18714: logical corruption

another check by dbms_repair to investigate the real corruption, and marked it as corrupt:
declare
begin
dbms_repair.admin_tables(
table_name => ‘REPAIR_TABLE_99’,
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’);
end;
/
select owner,object_name,object_type
from dba_objects
where object_name like ‘%REPAIR_TABLE%’;

declare
begin
dbms_repair.admin_tables(
table_name => ‘ORPHAN_REP_TBL_99’,
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’);
end;
/

select owner,object_name,object_type
from dba_objects
where object_name like ‘%ORPHAN%’;

set serveroutput on

declare
rpr_count int;
begin
rpr_count :=0;

dbms_repair.check_object(
schema_name => ‘TEST’,
object_name => ‘TBL1’,
repair_table_name => ‘REPAIR_TABLE_99’,
corrupt_count => rpr_count);

dbms_output.put_line(‘rep cnt : ‘||to_char(rpr_count));
end;
/

select object_name,block_id,corrupt_type,marked_corrupt,corrupt_description,repair_description from repair_table_99;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
—————————— ———- ———— ———-
CORRUPT_DESCRIPTION
——————————————————————————–
REPAIR_DESCRIPTION
——————————————————————————–
TBL1 15312 6130 FALSE
Block Checking: DBA = 29375440, Block Type = KTB-managed data block
data header at 0x3d85b007c
kdbchk: fseo(223) < fsbo(282)
mark block software corrupt
TBL1 18714 6110 FALSE
Block Checking: DBA = 29378842, Block Type = KTB-managed data block
data header at 0x3d756e0ac
kdbchk: the amount of space used is not equal to block size
used=7403 fsc=0 avsp=769 dtl=8016
mark block software corrupt

mark those block as corrupted:

declare
l_fix_count int;
begin
l_fix_count:=0;

dbms_repair.fix_corrupt_blocks(
schema_name => ‘TEST’,
object_name => ‘TBL1’,
object_type => dbms_repair.table_object,
repair_table_name => ‘REPAIR_TABLE_99’,
fix_count => l_fix_count);

dbms_output.put_line(‘fix cnt : ‘||to_char(l_fix_count));
end;
/

select object_name,block_id,corrupt_type,marked_corrupt,corrupt_description,repair_description from repair_table_99;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
—————————— ———- ———— ———-
CORRUPT_DESCRIPTION
——————————————————————————–
REPAIR_DESCRIPTION
——————————————————————————–
TBL1 15312 6130 TRUE
Block Checking: DBA = 29375440, Block Type = KTB-managed data block
data header at 0x3d85b007c
kdbchk: fseo(223) < fsbo(282)
mark block software corrupt

TBL1 18714 6110 TRUE
Block Checking: DBA = 29378842, Block Type = KTB-managed data block
data header at 0x3d756e0ac
kdbchk: the amount of space used is not equal to block size
used=7403 fsc=0 avsp=769 dtl=8016
mark block software corrupt

now time to recover the table
skip corrupted block from reading, and get the good data:

alter session set event=’10231 trace name context forever, level 10’;
create table test.tmp as select * from test.tbl1;

ok done, now the application guy who have to look into the data and fix them
have a nice sleep then :)

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: