Email: [email protected] 7 x 24 online support!
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.
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