If you cannot recover data by yourself, ask DBRECOVER, the professional Oracle database recovery team for help.
DBRECOVER Software Database Recovery Team
Service Hotline: +86 13764045638 E-mail: [email protected]
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 does not 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# 172573Solution
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: blockIn the above example the datafile having the issue is
Datafile 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=8192Cat bbed.par
D. Run bbed. Use password blockedit
$ bbed parfile=bbed.parPassword
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
DBRECOVER Recovery Options
For Oracle incidents, start with the DBRECOVER for Oracle trial to verify table visibility, row previews, and export readiness on copied datafiles. For MySQL and InnoDB incidents, DBRECOVER for MySQL is free software and can inspect.ibd files, ibdata1, and database directories locally.
When the case is urgent, preserve the original files first, work from copies, and contact paid emergency support with the database version, platform, error messages, file list, and recovery objective.