Re: Database Fails to Open after Recover TableSpace - No Backups!

Comments embedded...

On 17 Nov 2005 11:34:19 -0800, "Habib" <habibsyed_at_gmail.com> wrote:

>OS: Windows NT 4.0 SP5
>Oracle Version: 8.1.7
>_____________________________________________________________
>
>My database doesn't OPEN. This is what I get.
>
>SVRMGR> startup;
>ORACLE instance started.
>Total System Global Area 1219614748 bytes
>Fixed Size 75804 bytes
>Variable Size 314675200 bytes
>Database Buffers 799997952 bytes
>Redo Buffers 104865792 bytes
>Database mounted.
>ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
>SVRMGR> alter database open resetlogs;
>alter database open resetlogs
>*
>ORA-01113: file 1 needs media recovery
>ORA-01110: data file 1: 'E:\ORACLE\PRG\SIEBELDATA1\SYSTEM.DATA1'
>SVRMGR>
>
>I have NO recent enough cold backups.
>My redo logs go back to May but when I try to recover the SYSTEM.DATA1
>file it starts asking for log files way back from January 24, 2005.
>
>
>
>
>Background:
>This is how it all started...
>This is similar to what I saw on my Oracle instance earlier in the
>week...
>
>SVRMGR> startup
>ORACLE instance started.
>Total System Global Area 223119388 bytes
>Fixed Size 75804 bytes
>Variable Size 79622144 bytes
>Database Buffers 143343616 bytes
>Redo Buffers 77824 bytes
>Database mounted.
>ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
>ORA-01110: data file 12: 'E:\ORACLE\PRG\SIEBELDATA5\SIEBELDATA5.DBF'
>
>This is what I did to correct the problem...
>
>SVRMGR> alter database datafile
>'E:\ORACLE\PRG\SIEBELDATA5\SIEBELDATA5.DBF' offline drop;
>Statement processed.
>SVRMGR> alter database open;
>Statement processed.
>

Not sure why you actually dropped the datafile. What did the first error message indicate? Why didn't you do as the error message indicated?

>The database did open and everything seemed to be working after this
>for almost 3 days before we started seeing the error...
>
>"ORA-00376: file 12 cannot be read at this time
>ORA-01110: data file 12: 'E:\ORACLE\PRG\SIEBELDATA5\SIEBELDATA5.DBF'
>ORA-06512: at line 6"
>
>It seemed like we could read everything from the database but eveytime
>we tried to write to the database it would return this error. After
>some attempts to remove any references to the corrupted datafile and
>adding another datafile. We hastily made the mistake of running a
>RECOVER DATABASE Tablespace instead of RECOVER DATAFILE
>'E:\ORACLE\PRG\SIEBELDATA5\SIEBELDATA5.DBF';
Not sure why you didn't decide to drop the datafile again....

>
>We had almost all of the redo log files that we were asked for but one.
>So we tried the Recover Until Cancel option and got to a point where it
>went through all my redo log files till it started asking for a log
>file sequence that hadn't been created in the system yet.
Not sure why you didn't use the
until cancel using backup controlfile
When you leave the 'using backup controlfile' out, the scn in the controlfile is driving, and it will continue to prompt you. Did you try to specify the online redo log? Would have probably worked.

When we try
>to start the database we get the error that I posted at the very
>beginning.
>
>My understanding is that running the recover tablespace somehow put the
>(header) information between the SYSTEM file and the DATAFILES out of
>synch.

Not true. You need to differentiate between complete and incomplete recovery. When using complete recovery (as you did when you issued the recover tablespace command, as you left out the until cancel using backup controlfile), Oracle will sync the SCN in ALL datafiles with the SCN in the CONTROLFILE.

 Is there any way tool to RECOVER from such a catastrophic
>situation?

Eh, why don't you edit the controlfile? You seem to like a bit of adventure.

>
>Any help will be greatly appreciated.
>Thanks,
>
>Regards,
>-Habib-

-- 
--
Sybrand Bakker, Senior Oracle DBA