Case 2 : THE DATABASE WAS NOT CLEANLY SHUT DOWN

Situation where the database was last shut down abort or crashed. In this case, it is almost certain that the rollback segments that had extents in the lost data file still contain active transactions. Therefore, the file cannot be offlined or dropped. You must restore the lost data file from a backup and apply media recovery to it.

When you are trying to start database:

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1279120 bytes
Variable Size 71306096 bytes
Database Buffers 92274688 bytes
Redo Buffers 2912256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/DB/ANUP/undotbs02.dbf'


Step 1: Shutdown the database with immediate option

Step 2: Restore the lost file from a backup.

Step 3: Mount the database:

SQL> STARTUP MOUNT;

Step 4: Issue the following query:

SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

Step 5: If the status of the file you just restored is "OFFLINE," you must online it before proceeding.

SQL> ALTER DATABASE DATAFILE '' ONLINE;

Step 6: Recover the updo datafile

SQL> recover datafile ‘’;

Step 7: After recovery open the database.

SQL> alter database open;