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

Hi Sybrand ,

Thank you for pointing me to where to look in the future and for providing me a solution that worked for you. I will go to the office later on today and try it out.

What is a good starting point for a newbie like me to learn more about Oracle? I am very interested in learning it by myself.

Thank you again!

LiLi

On Aug 11, 1:48 am, sybra..._at_hccnet.nl wrote:
> On Fri, 10 Aug 2007 19:21:51 -0700, litu..._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 usinghttp://groups.google.comor 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- Hide quoted text -
>
> - Show quoted text -
Received on Sat Aug 11 2007 - 09:43:49 CDT