How to recreate or restore Corrupted Data Files in Oracle 11g

Question

Few days back ago my power cable went down and I was working in Oracle. So after power cable restoration when I tried to login to SQL PLus:

• It did not let me to login and I got error "Oracle Shutdown".
• Then I went to command prompt and connected as /sysdba and tried to login and could not restore Oracle.
• Then I went to Google and tried various methods and while working I accidentally deleted UNDOTBS01.dbf and USERS1.DBF.
• Then when I tried to restore I got new error "Data file could not found".
• Then I retrieved the deleted files form Recycle Bin and restored them back to where they are in C:\app\ven\oradata\dev directory.
• Then when I tried shutdown and 'startup mount' I am getting new Error and messages from Alert LOg as follows:

Corrupt block relative dba: 0x00c00001 (file 3, block 1) Bad header found during kcvxfh v10 Data in bad block: Corrupt block relative dba: 0x01000001 (file 4, block 1) Bad header found during kcvxfh v10 Data in bad block:

Could you help me in restoring my database back to normal. I don't have any backups please.

Date :--01/16/2014 10:39 PM
I did the following :

Shutdown Immediate 
Startup mount 
restore database 
recover database 
alter database open 

Now my Alert Log As follows:

Reread (file 4, block 1) found same corrupt data
Thu Jan 16 22:33:46 2014
Hex dump of (file 3, block 1) in trace file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_ora_5556.trc
Corrupt block relative dba: 0x00c00001 (file 3, block 1)
Bad header found during kcvxfh v10
Data in bad block:
 type: 21 format: 7 rdba: 0x0f8b5198
 last change scn: 0x33a3.049aae4f seq: 0x36 flg: 0x6f
 spare1: 0xec spare2: 0xcc spare3: 0xc3ba
 consistency value in tail: 0x76830457
 check value in block header: 0x9b73
 computed block checksum: 0x6736
Reading datafile 'C:\APP\VENKAT\ORADATA\DEV\UNDOTBS01.DBF' for corruption at rdba: 0x00c00001 (file 3, block 1)
Reread (file 3, block 1) found same corrupt data
Hex dump of (file 4, block 1) in trace file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_ora_5556.trc
Corrupt block relative dba: 0x01000001 (file 4, block 1)
Bad header found during kcvxfh v10
Data in bad block:
 type: 70 format: 6 rdba: 0x230a00ea
 last change scn: 0xf206.00f72638 seq: 0xc flg: 0x01
 spare1: 0x38 spare2: 0x26 spare3: 0x0
 consistency value in tail: 0x010c2fd2
 check value in block header: 0x200
 block checksum disabled
Reading datafile 'C:\APP\VENKAT\ORADATA\DEV\USERS01.DBF' for corruption at rdba: 0x01000001 (file 4, block 1)
Reread (file 4, block 1) found same corrupt data
Hex dump of (file 3, block 1) in trace file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_ora_5556.trc
Corrupt block relative dba: 0x00c00001 (file 3, block 1)
Bad header found during kcvxfh v10
Data in bad block:
 type: 21 format: 7 rdba: 0x0f8b5198
 last change scn: 0x33a3.049aae4f seq: 0x36 flg: 0x6f
 spare1: 0xec spare2: 0xcc spare3: 0xc3ba
 consistency value in tail: 0x76830457
 check value in block header: 0x9b73
 computed block checksum: 0x6736
Reading datafile 'C:\APP\VENKAT\ORADATA\DEV\UNDOTBS01.DBF' for corruption at rdba: 0x00c00001 (file 3, block 1)
Reread (file 3, block 1) found same corrupt data
Thu Jan 16 22:34:07 2014
alter database open
Errors in file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_ora_5556.trc:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'C:\APP\VENKAT\ORADATA\DEV\SYSTEM01.DBF'
ORA-1190 signalled during: alter database open...
Thu Jan 16 22:34:08 2014
Errors in file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_m000_2012.trc:
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: 'C:\APP\VENKAT\ORADATA\DEV\REDO01.LOG'
Errors in file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_m000_2012.trc:
ORA-00338: log 1 of thread 1 is more recent than control file
ORA-00312: online log 1 thread 1: 'C:\APP\VENKAT\ORADATA\DEV\REDO01.LOG'
Errors in file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_m000_2012.trc:
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1: 'C:\APP\VENKAT\ORADATA\DEV\REDO02.LOG'
Errors in file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_m000_2012.trc:
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1: 'C:\APP\VENKAT\ORADATA\DEV\REDO02.LOG'
Errors in file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_m000_2012.trc:
ORA-00338: log 3 of thread 1 is more recent than control file
ORA-00312: online log 3 thread 1: 'C:\APP\VENKAT\ORADATA\DEV\REDO03.LOG'
Errors in file c:\app\venkat\diag\rdbms\dev\dev\trace\dev_m000_2012.trc:
ORA-00338: log 3 of thread 1 is more recent than control file
ORA-00312: online log 3 thread 1: 'C:\APP\VENKAT\ORADATA\DEV\REDO03.LOG'

Please Help me

• user3141079: Sir Thank you for your reply What I did is that I recreated new control file and issued comment "Alter Database Open"
• user3141079: Hi I tried as you said still the DB not open It is not letting me to open
• Colin 't Hart: Do you have a backup -- preferably with archived log files and using rman? Is this a production database?

Answer 1

If you don't have any backups. Well... You're just flat down.

Nothing to be done.

You might as well drop the entire database and recreate.

Reasons follow:

1.- Corrupt blocks can only be repaired by getting the original block from a full backup (at least of the datafile that has the corrupted blocks) and applying the archivelogs after the original block has been restored to the original location.

2.- No backups, No archivelogs and no exports means you lost your data. There is no way to get it back as you cannot find out the actual storage order of each row once it got corrupted.

3.- If the undo tablespace got corupted as well, you can't even recover a datafile/block because the RDBMS won't find the undo data needed to make it consistent. In the best of cases you could apply forward changes on the redo logs, but you couln't rollback unfinished transactions before the crash.

Next time, as soon as you have a running database, get it to work in archivelog mode, make a full back every once in a while and, to be on the safe side, export the most important data you have there..

Answer 2

Whether it is feasible depends on whether you have more problems than just corrupted UNDOTBS01.dbf and USERS1.dbf data files. When these are your only problems, you can best drop the USERS tablespace and recreate a new UNDO tablespace. The data in USERS can be considered lost. There are (complex) options to salvage data from a dbf but that is not recommended for starters. The data in UNDO are only recent changes not yet committed, so in a low use environment with acceptible data loss in terms of hours that should not be a problem.

You can detect as follows whether the UNDOTBS01.dbf and USERS1.dbf are corrupt:

• Note the file number in the error message.
• startup nomount
• Look in v$datafile what the actual file is corresponding to this number.
• When it is undo or users, drop tablespace and recreate.
• Try to mount and open database.

If you have any errors after that, please post them fully.

• kubanczyk: "The data in UNDO are only recent changes not yet committed" - no. The data in UNDO are the ORIGINAL data, that were overwritten (doesn't matter if overwriting transactions were committed or not). Just a nitpick. Or not.

Answer 3

Do you have any rman physical backup available ? If yes , you can restore your database to previous point or current point ; else you can force your database open by setting some hidden parameter like _allow_corrupted_resetlogs _corrupted_rollback_segments etc.

If you really have no backup & force open database don't work . Then PRM-DUL (an Oracle Recovery/Rescue Tools ) can be your last resort . PRM-DUL scan datbase directly without Oracle instance , it write out every table into flat file or other database.

Good Luck!