How To Recovery After Losing UNDO Tablespace

Problem Description:
===================
After normal shutdown Oracle9i DB, if i lose all UNDO files and dont have backup, could i
just create a new UNDO tablespace & files and drop the old to recover the DB? Since the
DB was shut down normally, it rolled back all uncommited transactions, so all datafiles
dont contain uncommitted data. Then I think undo is not needed during recovery. The only
thing i will not be able to recover is I cant get flashback, which i dont care, am I
right? But I was not allow to create the UNDO tbs without opening the DB, how can I do to
recover the DB? Any difference between version 8.1.7 and 9?
Thanks!

Ok, here we go (don't try this at home, well, maybe - do try this at home cause you
probably are not running a "real" system at home ;)

1. Shutdown db

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

[ora920@tkyte-pc-isdn ora920]$ ls
control01.ctl  cwmlite01.dbf  indx01.dbf  redo02.log  redo0B.log    temp01.dbf    
users01.dbf
control02.ctl  drsys01.dbf    odm01.dbf   redo03.log  redo0C.log    tools01.dbf   
xdb01.dbf
control03.ctl  example01.dbf  redo01.log  redo0A.log  system01.dbf  undotbs01.dbf

2. Undo file has been renamed:

[ora920@tkyte-pc-isdn ora920]$ mv undotbs01.dbf undotbs01.dbf.xxx

3. Connect DB with No logs:

[ora920@tkyte-pc-isdn ora920]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:33:29 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'

idle> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

4. Change The Undo Management As Manual:

idle> alter system set undo_management = manual scope=spfile;

System altered.

5. Shutdown DB

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

6. Connect DB with No logs:

sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:35:34 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'

Note: we still have to get rid of that thing..

7. Drop Datafile With  Offline Mode:

SQL> alter database datafile  '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'
  2  offline drop;

Database altered.

SQL> alter database open;

Database altered.

8. Now Drop OLD Undo:

SQL> drop tablespace undotbs1;

Tablespace dropped.

9. Create New Undo Tablespace:

SQL> create UNDO tablespace undotbs1
  2  datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf' size 25m
  3  autoextend on next 1m maxsize 1024m;

Tablespace created

10. Make Undo Management As Auto:

SQL> alter system set undo_management = auto scope=spfile;

System altered.

11. Now, we've put the undo tablespace "back" by creating a new one.  Just bounce...

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
idle>

and we are right back where we started from

Link:

Link: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582

#################################################################### All The Best ##################################################################