Email: service@parnassusdata.com 7 x 24 online support!

    You are here

How to Resolve Ora-00600 [3020] when Allow 1 Corruption Doesnot work

How to Resolve Ora-00600 [3020] when Allow 1 Corruption Doesnot work

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

 
 
 
 
Oracle Server - Enterprise Edition - Version: 9.2.0.4 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.
Goal
 
This Article is to help users resolve ora-00600[3020] when
 
> Restore and recovery of the datafile gives the same error .
 
> Allow 1 corruption doesnot work .
 
> Customer has no backup of the problematic datafile .
 
Warning :-
 
These steps shouldnot be used on System or Undo datafiles as they would cause data/dictionary inconsistency.
 
The options to resolve this issue is to corrupt the blocks (when recovering) and  use some salvage option to get lost data for the affected  segments .
 
ORA-00283: recovery session canceled due to errors 
ORA-00600: internal error code, arguments: [3020], [385882742], [1], [330236], 
[49015], [200], [], [] 
ORA-10567: Redo is inconsistent with data block (file# 92, block# 6774) 
ORA-10564: tablespace TSPACE5 
ORA-01110: data file 92: '/bill/oradata/data9/tspave5_07.dbf' 
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 172573 
 
Solution
 
Step 1 :- Identify the datafile on which Ora-00600[3020]
 
 
 
First step is to Identify on which datafile ora-00600[3020] is reported.
 
Taking the above example :-
 
ORA-00283: recovery session canceled due to errors 
ORA-00600: internal error code, arguments: [3020], [385882742], [1], [330236], 
[49015], [200], [], [] 
ORA-10567: Redo is inconsistent with data block (file# 92, block# 6774) 
ORA-10564: tablespace TSPACE5 
ORA-01110: data file 92: '/bill/oradata/data9/tspave5_07.dbf' 
ORA-10561: block
 
In the above example the datafile having the issue is
 
data file 92: '/bill/oradata/data9/tspave5_07.dbf'
 
Step 2 :-
 
Try to recover the block using allow 1 corruption
 
ALTER DATABASE RECOVER datafile '/bill/oradata/data9/tspave5_07.dbf' allow 1 corruption
 
This would fail on the same block with the same error.
 
 
 
Step 3 :-
 
Take a backup of the existing state of the affected datafile.
 
 
Step 4 :- Configure BBED for usage
 
From 11G onwards BBED is not available, but DD can be used.
 
a. Generate the bbed executable: 
 
                
 
cd $ORACLE_HOME/rdbms/lib 
make -f ins_rdbms.mk `pwd`/bbed 
mv bbed $ORACLE_HOME/bin 
 
b. Create file file.list with the datafile where datafile on which Ora-00600[3020] is stored: 
 
file.list has: 
 
<relative file#> <datafile name> 
In our session file.list contains: 
 
 
92  /bill/oradata/data9/tspave5_07.dbf
 
 
cat file.list 
 
92 /bill/oradata/data9/tspave5_07.dbf 
 
 
c. Create file bbed.par 
 
bbed.par has: 
 
MODE=EDIT 
LISTFILE=<File name created in step b> 
BLOCKSIZE=<db_block_size> 
 
In our session bbed.par contains 
 
 
MODE=EDIT 
LISTFILE=file.list 
BLOCKSIZE=8192
 
 
 
cat bbed.par 
 
d. Run bbed. Use password blockedit: 
 
 
 
$ bbed parfile=bbed.par 
Password: 
 
BBED: Release 2.0.0.0.0 - Limited Production on Mon Apr 13 11:20:42 2009 
 
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 
 
************* !!! For Oracle Internal Use only !!! ***************
 
BBED> 
 
 
 
e. Go to Block where the Ora-00600[3020] is reported . In our example it is block 6774: 
 
 
 
BBED> set block 6774 
 
BLOCK# 6774 
 
 
Verify that everything is set correctly: 
 
BBED > Show all 
 
-> FILE# 92 
BLOCK# 6774 
OFFSET 0 
DBA 0x17001a66 (385882726 92,6774) 
-> FILENAME /bill/oradata/data9/tspave5_07.dbf 
BIFILE bifile.bbd 
-> LISTFILE /home/oracle/bbed/listfiles.txt 
-> BLOCKSIZE 8192 
-> MODE Browse 
EDIT Unrecoverable 
IBASE Dec 
OBASE Dec 
WIDTH 80 
COUNT 512 
LOGFILE log.bbd 
SPOOL No 
 
 
f. Run map to see the C structures for the block and the DBA: 
 
 
 
BBED> map 
File: /bill/oradata/data9/tspave5_07.dbf (92) 
Block: 92 Dba:0x17001a66
------------------------------------------------------------ 
KTB Data Block (Table/Cluster) 
 
struct kcbh, 20 bytes @0 
 
struct ktbbh, 72 bytes @20 
 
struct kdbh, 14 bytes @100 
 
struct kdbt[1], 4 bytes @114 
 
sb2 kdbr[519] @118 
 
ub1 freespace[809] @1156 
 
ub1 rowdata[6223] @1965 
 
ub4 tailchk @8188 
g print kcbh
 
BBED> print kcbh 
struct kcbh, 20 bytes @0 
ub1 type_kcbh @0 0x06 
ub1 frmt_kcbh @1 0x02 
ub1 spare1_kcbh @2 0x00 
ub1 spare2_kcbh @3 0x00 
ub4 rdba_kcbh @4 0x17001a66 
ub4 bas_kcbh @8 0x002eda83 
ub2 wrp_kcbh @12 0x0000 
ub1 seq_kcbh @14 0x9b 
ub1 flg_kcbh @15 0x04 (KCBHFCKV) 
ub2 chkval_kcbh @16 0x205f 
ub2 spare3_kcbh @18 0x0000 
 
 
 We will mark the sequence as FF and Flag as 00 Corrupt the dba
 
 
 
BBED>Corrupt dba
 
BBED> print kcbh 
struct kcbh, 20 bytes @0 
ub1 type_kcbh @0 0x06 
ub1 frmt_kcbh @1 0x02 
ub1 spare1_kcbh @2 0x00 
ub1 spare2_kcbh @3 0x00 
ub4 rdba_kcbh @4 0x17001a66 
ub4 bas_kcbh @8 0x00000000   ----------------------->Zeroed out
ub2 wrp_kcbh @12 0x0000        ----------------------->Zeroed out
ub1 seq_kcbh @14 0xff            ------->Sequence marked FF
ub1 flg_kcbh @15 0x04 (KCBHFCKV) 
ub2 chkval_kcbh @16 0x2071 
ub2 spare3_kcbh @18 0x0000
 
 
 
 
 
 
 
Step 5
======
 
ALTER DATABASE RECOVER datafile '/bill/oradata/data9/tspave5_07.dbf' allow 1 corruption
 
This would go through now.
However if the issue is with other Block allow ORA-00600[3020] would be reported on next corrupt block.  Re-run allow 1 corruption again and check if it passes beyond the next block if yes bring the datafile online. Else the patch the next block using the above steps.
 
 
 
Step 6
=====
 
Once the blocks are patching the object which contains the corrupt block would error out with ORA-1578
Salvage the Good data excluding the corrupt block and recreate the Object
 
Run the query from dba_extents for the datafile and block reported corrupt during Stuck recovery
 
SQL>Select segment_name,segment_type,owner from dba_extents where file_id=<file number> and <block Id> between block_id and block_id + blocks -1 ;
 
 
 
SQL> alter session set events '10231 trace name context forever, level 10' 
 
SQL> Create table Salvage_table as select * from <original table> ;
 
You can then truncate the original table and re-insert good data from Salvage table.
 
 
 
Please note :- From 11g onwards bbed is not shipped .
 
For 11g database you can use the following Rman command to mark the block softcorrupt
 
RMAN> BLOCKRECOVER DATAFILE <file#> BLOCK <block1#> CLEAR ;
 
 
 
Please refer 
 
How to soft Corrupt Block using RMAN to produce ORA-01578