configure automatic undo management tagged posts

Recover from loss of undo tablespace datafile

by Snehashish Ghosh


 Undo tablespace datafile has been corrupted or had been deleted by sysadmin.

Database was properly shutdown i.e. no active rollback segment exists

Location of Undo Tablespace


 Undo Tablespace is corrupted and sysadmin deleted the undo file, no backup of undo file exists


 Issue Identification

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1385840 bytes

Variable Size             297798288 bytes

Database Buffers          230686720 bytes

Redo Buffers                5791744 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 – see DBWR trac...

Read More