Undo tablespace recovery in case of no backups.

This method to recover Undo tablespace is not a substitute for recovery procedures
through backups as mentioned in the official Oracle documentation.

But if you get stuck in a situation where you don’t have any backups and need to
recover your Undo tablespace , then use this method as a last resort.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes                                       
Fixed Size                  1218992 bytes                                       
Variable Size              96470608 bytes                                       
Database Buffers          184549376 bytes                                       
Redo Buffers                2973696 bytes                                       
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file 
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' 


SQL> show parameter undo

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
undo_management                      string      AUTO                           
undo_retention                       integer     900                            
undo_tablespace                      string      UNDOTBS1   

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

System altered.


SQL> shutdown immediate;
ORA-01109: database not open 


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes                                       
Fixed Size                  1218992 bytes                                       
Variable Size              96470608 bytes                                       
Database Buffers          184549376 bytes                                       
Redo Buffers                2973696 bytes                                       
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file 
ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' 


SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
  2  offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace undotbs1;

Tablespace dropped.


SQL> create undo tablespace undotbs1
  2  datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 50m
  3  autoextend on next 10m maxsize 1024m;

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  285212672 bytes                                       
Fixed Size                  1218992 bytes                                       
Variable Size              96470608 bytes                                       
Database Buffers          184549376 bytes                                       
Redo Buffers                2973696 bytes                                       
Database mounted.
Database opened.


SQL> show parameter undo

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
undo_management                      string      AUTO                           
undo_retention                       integer     900                            
undo_tablespace                      string      UNDOTBS1