Complete Recovery when NON-SYSTEM tablespace is missing and database is closed and restoring datafile to different location.

Problem Generation

i. Shutdown the database.


SQL> SHUTDOWN immediate;


ii. Delete USERS tablespace datafile from operating system


[oracle@odb]$ rm -i users01.dbf


iii. Start the database


SQL> STARTUP;
ORACLE instance started.
Total System Global Area  536870912 bytes
Fixed Size               1220460 bytes
Variable Size          213909652 bytes
Database Buffers       318767104 bytes
Redo Buffers             2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’




Solution


i. Make datafile 4 offline
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;


ii. Open the database with datafile 4 oflfine
SQL> ALTER DATABASE OPEN;


iii. Using RMAN again connect to target database
[oracle@odb]$ rman target /


iv. Restoring and recovering datafile 4 at new location
RMAN> run
 {
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}