Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 conncted as /sysdba and tried to login and could not restore Oracle.
  • Then I went to Google and tried various methods and while working I accidently 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 dont have any backups please.

Thanks Ven

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

share|improve this question

migrated from stackoverflow.com Jan 3 at 10:25

This question came from our site for professional and enthusiast programmers.

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

2 Answers 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.

share|improve this answer
    
"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. –  kubanczyk Jun 30 at 12:04

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..

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.