Oracle 9i - database cannot open due to UNDOTBS01

Hi all,

I am new to using Oracle and I am using 9.2.0.7. Basically I did a coldbackup of all the datafiles (except for the UNDO), control files and spfile of my database.

I accidentally overwrite one of the schemas by mistake. I need to do a full database recover using the cold backup files in order to restore it back to original.

The database is in NOARCHIVELOG mode. The only datafile I cannot/did not backup was the UNDO.

I basically did the following trying to recover it:

shutdown abort
copy all the controlfiles, datafiles to their appropriate places as the original (overwrite them)
startup => I get errors saying that the data file "UNDOTBS01" cannot be located and that the controlfile is more recent than the older controlfile. (cannot recall the errors..sorry).

I think what happened is that when I copied the backup files to their appropriate places and replaced the most recent versions of the files - except for the UNDO tablespace - which is still pointing to the current transaction and while all the other datafiles are referencing the time from the backup was made.

I tried with no luck:
removed the UNDO data management in init.ora (commented out) startup mount spfile='/init.ora' restrict alter database datafile 'UNDOTBS01.DBF' OFFLINE DROP; alter database open RESETLOGS; => get error saying that the database did not encounter recovery failure and cannot perform this recover database end until cancel => cannot recover database because of UNDOTBS01.dbf and the controlfile error message

Any idea as to how I can open the database without the undo table/ datafile?

Thank you!

LiLi Received on Fri Aug 10 2007 - 21:21:51 CDT