Re: deleted undotbs01.dbf...

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 01 Aug 2007 21:23:08 -0000
Message-ID: <1186003388.024966.191780@o61g2000hsh.googlegroups.com>

On Aug 1, 2:36 pm, sybrandb <sybra..._at_gmail.com> wrote:
> On Aug 1, 2:54 pm, "astalavista" <nob..._at_nowhere.com> wrote:
>
> > > I made a mistake and deleted undotbs01.dbf.
>
> > > now i try to restart the server without success.
> > > i try to recreate an undo tablespace too, but it is not possible due
> > > to "database not open" error and if i try to "alter database open" i
> > > get "ORA-01092: oracle instance terminated. disconnection forced".
>
> > If you comment UNDO_MANAGEMENT = auto
> > in your pfile/spfile
> > can you open the database ?
>
> The tablespace and datafile info is still in the controlfile.
> Is not going to work.

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 Received on Wed Aug 01 2007 - 16:23:08 CDT