Database Fails to Open after Recover TableSpace - No Backups!

From: Habib <habibsyed_at_gmail.com>
Date: 17 Nov 2005 11:34:19 -0800
Message-ID: <1132256059.173536.22010@f14g2000cwb.googlegroups.com>

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.

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';
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. 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. Is there any way tool to RECOVER from such a catastrophic situation?

Any help will be greatly appreciated.
Thanks,

Regards,
-Habib- Received on Thu Nov 17 2005 - 13:34:19 CST