Re: deleted undotbs01.dbf...

On Aug 1, 11:23 pm, Niall Litchfield <niall.litchfi..._at_gmail.com> wrote:
> 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

Maybe that undo tablespace contains data needed for database integrity (in fact Undo tablespace has to be backed up as others tablespaces containing data), for example due to "delayed block cleanout": it has happened to me; in this case you can open your database but when you select from a table you got ORA-00376 (I'm not sure about the error code). So if you are in this case your last resort is opening SR on Metalink as already suggested by others, but whitout a backup or at least an export i can't immagine how can they solve your problem.

Bye
 Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Thu Aug 02 2007 - 05:47:14 CDT