咨询微信： dbservice1234 7 x 24 在线支持！

首页

下载 DBRECOVER

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

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

Posted by PDSERVICE on 3月 15, 2017 In

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