Re: Oracle 9i - database cannot open due to UNDOTBS01
- From: sybrandb@xxxxxxxxx
- Date: Sat, 11 Aug 2007 07:48:38 +0200
On Fri, 10 Aug 2007 19:21:51 -0700, litung8@xxxxxxxxx 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
.
- Follow-Ups:
- Re: Oracle 9i - database cannot open due to UNDOTBS01
- From: litung8
- Re: Oracle 9i - database cannot open due to UNDOTBS01
- References:
- Oracle 9i - database cannot open due to UNDOTBS01
- From: litung8
- Oracle 9i - database cannot open due to UNDOTBS01
- Prev by Date: Re: Cache Hit Ratio from system views
- Next by Date: select * from V$SESSION from SQL+ but not application
- Previous by thread: Oracle 9i - database cannot open due to UNDOTBS01
- Next by thread: Re: Oracle 9i - database cannot open due to UNDOTBS01
- Index(es):
Relevant Pages
|
Loading
