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'
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 !
