Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 6 November 2012

ORA-10567: Redo is inconsistent with data block

RMAN> RESTORE DATABASE;
Starting restore at 03-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
.....
piece handle=H:\ORABACK\MONTHLY_20120929_FULL_SADHAN-2984_1.DB tag=SADHAN_FULL_D
BBACKUP params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 03-OCT-12
RMAN> RECOVER DATABASE;
Starting recover at 03-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
...
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\ORABACK\DAILY_20121003_INCR_SADHAN-3044_1.DB tag=DIFFERENTIAL_L1
_DATAFILE params=NULL
channel ORA_DISK_1: restore complete
....

Starting media recovery
Media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/03/2012 20:00:52
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [37833581], [1], [4504], [70575], [244], [], []
ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)
ORA-10564: tablespace SDH_HRMS_DBF
ORA-01110: data file 9: 'G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918
One fine morning (03rd October) our DBserver suddenly break down after repairing the hardware and OS we found that database needs recovery. I used the simple restore & recovery procedure. Restore database is working fine while applying “recover database” it is able to apply all the incremental backup incase of media recovery is giving the above error. Recovery interrupted with the above error:
I am not even able to open the database, while trying to open the database. It is giving the following error:
RMAN> ALTER DATABASE OPEN;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 10/03/2012 20:01:30
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF'

I have full backup of 30-Sep and cumulative and incremental backup of each day till 03-Oct 01 AM. Disaster happens in the morning 8 am. I found all the archive logs are available on the system till the failure.
When I check the alert log the entries are following:
Completed: alter database recover datafile list
Wed Oct 03 20:00:31 2012
alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14
Completed: alter database recover datafile list 1 , 2 , 3 ,
Wed Oct 03 20:00:31 2012
alter database recover if needed start
Media Recovery Start
Wed Oct 03 20:00:32 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4502 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO01.LOG
  Mem# 1 errs 0: E:\ORACLE\ORADATA\SADHAN\REDO01B.LOG
Wed Oct 03 20:00:33 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 4503 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO03.LOG
  Mem# 1 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO03B.LOG
Wed Oct 03 20:00:33 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 4504 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\SADHAN\REDO02.LOG
  Mem# 1 errs 0: E:\ORACLE\ORADATA\SADHAN\REDO02B.LOG
Wed Oct 03 20:00:47 2012
Errors in file d:\oracle\admin\sadhan\udump\sadhan_ora_5952.trc:
ORA-00600: internal error code, arguments: [3020], [37833581],[1],[4504],[70575],[244],[],[]
ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)
ORA-10564: tablespace SDH_HRMS_DBF
ORA-01110: data file 9: 'G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918
Wed Oct 03 20:00:52 2012
Media Recovery failed with error 600
ORA-283 signalled during: alter database recover if needed

From the error, we see this corruption is in the user tablespace not the system tablespace. So this is not an internal transaction. Thus my doubt about Oracle bug is clear. It is not an oracle bug.
Cause: After searching I came to know this is a common error when doing recovery if you are not on patched version or higher. Prior to Oracle 10g it did not record all the changes in the redo stream and when you do recovery you get this error. So, there is an inconsistency between the information stored in the redo and the information stored in a database block being recovered.
Solution: Incomplete Recovery
You can use incomplete recovery either cancel based or point in time. As I already apply the simple restore and recovery command. So I preferred to go with the cancel based recovery and finally able to open the database.
Login to SQL database will be mount phase
SQL> recover database until cancel;
Press enter as long as you reach to the missing log
SQL> alter database open resetlogs;
Note: You must take fresh full backup after opening the database with resetlogs option and must perform "reset database" command to the rman if open the database using sql prompt.  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
While searching to Oracle support I see so many related bugs exist regarding ORA-00600 [3020].  Below solution can be used in case problem related with system tablespace or oracle bug ORA-00600 [3020]
Solution: Try to do a manual recovery with allow 1 corruption. That is "recover database allow 1 corruption;" which will skip the bad transaction.
RMAN> recover database allow 1 corruption;
RMAN> recover database allow 1 corruption;
RMAN> recover database allow 1 corruption;
We need to repeat this command until the recovery completes.
RMAN> recover database allow 1 corruption;
Starting recover at 03-OCT-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 03-OCT-12

After trying the above command 8-9 times if you are not able to recover the database. You can use incomplete recovery point in time just before the corruption.
CONNECT RMAN TARGET SYS/*****@SADHAN.WORD CATALOG CATALOG/*****@RMAN
run {
set until time to_date(’03-Oct-2012 07:50:00′, ‘DD-MON-YYYY HH24:MI:SS’);
restore database;
recover database;
}
RMAN> Alter database open Resetlogs;

3 comments:

  1. DreamHost is ultimately one of the best hosting provider with plans for all of your hosting needs.

    ReplyDelete