Re: Oracle 9i - database cannot open due to UNDOTBS01

From: <sybrandb_at_hccnet.nl>
Date: Sat, 11 Aug 2007 07:48:38 +0200
Message-ID: <i5jqb39392rsuum4cbmaftp2j695fu4gar@4ax.com>

On Fri, 10 Aug 2007 19:21:51 -0700, litung8_at_gmail.com wrote:

>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

One of the biggest problems with this forum is that people like you not only don't read their manuals, and as a result of that run into problems, they also don't search this forum using http://groups.google.com or their newsreader prior to posting.

There has been a recent thread, with UNDOTBS01 in the subject, about *exact* the same problem.
So obviously you didn't search Usenet.

For your reference here is one of the solutions well the following worked for me after moving the datafile (6) for the undo tablespace.

SYS @ nl102 >STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size                  1250452 bytes
Variable Size             289409900 bytes
Database Buffers          318767104 bytes
Redo Buffers                2940928 bytes

Database mounted.
SYS @ nl102 >alter system set undo_management='MANUAL' scope=spfile;

System altered.

SYS @ nl102 >alter system set undo_management='MANUAL' scope=spfile;

System altered.

SYS @ nl102 >alter database datafile 6 offline drop;

Database altered.

SYS @ nl102 >alter database open;

Database altered.

SYS @ nl102 >create undo tablespace undotbs3 datafile 'c:\oracle \10.2.0\oradata\nl102\undotbs03dbf' size 1024m;

Tablespace created.

SYS @ nl102 >alter system set undo_tablespace=undotbs3 scope=spfile;

System altered.

SYS @ nl102 >alter system set undo_management=auto scope=spfile;

System altered.

SYS @ nl102 >startup force;
ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size                  1250452 bytes
Variable Size             289409900 bytes
Database Buffers          318767104 bytes
Redo Buffers                2940928 bytes

Database mounted.
Database opened.
SYS @ nl102 >

should it not then loss of an undo tablespace datafile will kill your database. not I think likely.

Niall

For your information: as you are not on 9.2.0.8, as Oracle 9iR2 has gone out of Premier Support into Extended Support, Oracle won't support you, as running the terminal release of 9iR2 is a requirement for extended support.

-- 
 
Sybrand Bakker
Senior Oracle DBA

Received on Sat Aug 11 2007 - 00:48:38 CDT