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