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

Problem Generation

i. Delete USERS tablespace datafile from operating system
[oracle@odb]$ rm -i users01.dbf
ii. Connect as user sh/sh and create a table in users tablespace

SQL> CREATE TABLE sales_test
  TABLESPACE users
     AS
SELECT *
FROM sales
WHERE ROWNUM < 10;
ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk3/ocmdb/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Solution

i. As DBA user make datafile users01.dbf offline

SQL> SELECT file#, name FROM v$datafile;
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;

ii. Restoring and recovering datafile 4 at new location

RMAN> run

{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk5/ocmdb/users01.dbf’;
 restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}

iii. Connect as user sh/sh and create a table in users tablespace

SQL> CREATE TABLE sales_test
  TABLESPACE users
  AS
SELECT *
FROM sales
WHERE ROWNUM < 10;
Table created.