Recovery After Losing UNDO Tablespace

Recover database after losing UNDO Tablespace

Steps:

SQL> CONNECT / AS SYSDBA

SQL> SHUTDOWN IMMEDIATE

[oracle]$ ls
control01.ctl ctxlite01.dbf indx01.dbf redo02.log temp01.dbf
sysaux01.dbf users01.dbf control02.ctl drsys01.dbf redo03.log xdb01.dbf control03.ctl example01.dbf redo01.log system01.dbf undotbs01.dbf

[oracle]$ mv undotbs01.dbf undotbs01.dbf.test

SQL> CONNECT / AS SYSDBA
Connected to an idle instance.

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: '/u01/app/oracle/product/10.2.0/oradata/TEST/undotbs01.dbf'


SQL> SHOW PARAMETER UNDO

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

SQL> ALTER SYSTEM SET UNDO_MANAGEMENT = MANUAL SCOPE=SPFILE;

Disabling the automatic undo management and go back to use "rollback segments".


SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> CONNECT / AS SYSDBA
Connected to an idle instance.

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: '/u01/app/oracle/product/10.2.0/oradata/TEST/undotbs01.dbf'

To get rid of this we will drop the old UNDO tablespace.

SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/product/10.2.0/oradata/TEST/undotbs01.dbf' OFFLINE DROP;


SQL> ALTER DATABASE OPEN;

SQL> DROP TABLESPACE UNDOTBS1;

SQL> CREATE UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/product/10.2.0/oradata/TEST/undotbs01.dbf' SIZE 25M AUTOEXTEND ON NEXT 1M MAXSIZE 1024M;

SQL> ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE=SPFILE;

We've put the undo tablespace "back" by creating a new one. Just restart the database for the modification to take effect.

SQL> CONNECT / AS SYSDBA

SQL> SHUTDOWN IMMEDIATE

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 3600
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1