Recover UNDO Tablespace when No Backups available

Here I'll show you how to make our Oracle database up and running after loosing UNDO tablespace/UNNDO datafile at OS level. First of all to simulate this situation let's us shutdown the database and delete the undotbs01.dbf file manually from OS level.

After droping the UNDO datafile then following error will appear on your screen that you have lost the UNDO tablespace's datafile and is no longer available.

SQL> startup

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1375792 bytes

Variable Size             654311888 bytes

Database Buffers          411041792 bytes

Redo Buffers                4603904 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: 'E:\APP\RAJKUMAR\ORADATA\ORCL\UNDOTBS01.DBF'

 SQL>

2nd step would be to make undo management parameter to manual from automatic in the parameter file.

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL>

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

System altered.

To make it effective, we have to restart the database instance.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1375792 bytes

Variable Size             654311888 bytes

Database Buffers          411041792 bytes

Redo Buffers                4603904 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: 'E:\APP\RAJKUMAR\ORADATA\ORCL\UNDOTBS01.DBF'

SQL>

Now you can check here undo management.

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------------

undo_management                      string      MANUAL

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL>

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$tablespace; 

NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

DEMO_DATA

DEMO_INDX

MIGRATION_DATA

TRIRIGAQA_DATA

TRIRIGAQA_INDX

10 rows selected.

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create undo tablespace undotbs1

  2  datafile 'e:\app\rajkumar\oradata\orcl\undotbs01.dbf' size 2048m autoextend on next 10m maxsize unlimited; 

Tablespace created.

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

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1375792 bytes

Variable Size             654311888 bytes

Database Buffers          411041792 bytes

Redo Buffers                4603904 bytes

Database mounted.

Database opened.

SQL> 

Hope it would help !