Lost a Data File for a Non-System Tablespace

If this is the only data file, perform an Oracle restore. If the tablespace has more than one data file and only one was lost, you may not want to perform the Catalogic DPX restore as it restores all data files. Instead, RMAN can be used to restore and recover. The following commands simulate the problem by removing a data file first:

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 422670336 bytes

Fixed Size 1300352 bytes

Variable Size 322963584 bytes

Database Buffers 92274688 bytes

Redo Buffers 6131712 bytes

RMAN> host 'ls /ora1/app/oracle/oradata/orcl/users01.dbf';

/ora1/app/oracle/oradata/orcl/users01.dbf

RMAN> host 'rm /ora1/app/oracle/oradata/orcl/users01.dbf';

RMAN> host 'ls /ora1/app/oracle/oradata/orcl/users01.dbf';

ls: /ora1/app/oracle/oradata/orcl/users01.dbf: No such file or directory

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of sql command on default channel at 10/24/2008 14:35:42

RMAN-11003: failure during parse/execution of SQL statement: alter database open

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/ora1/app/oracle/oradata/orcl/users01.dbf'

Before performing an RMAN restore, perform a Quick Restore of the latest backup so that the paths cataloged in RMAN become valid and accessible. Once the Quick Restore completes, perform an RMAN restore. In the following example, RMAN's crosscheck command finds the required data file:

Note. For PDB database tablespace restore, run the rman command as:

rman target=sys@pdb1

For a non-PDB database, use:

rman target /

RMAN> crosscheck datafilecopy 155;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=114 device type=DISK

validation succeeded for datafile copy

datafile copy file name=/tmp/bexoracle_orcl_1224864495/akash/_ora1/app/oracle/oradata/orcl/users01.dbf RECID=155 STAMP=668953031

Crosschecked 1 objects

RMAN> restore tablespace "USERS" device type disk;

Starting restore at 24-OCT-08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=151 device type=DISK

channel ORA_DISK_1: restoring datafile 00004 input datafile copy RECID=155

STAMP=668953031 file

name=/tmp/bexoracle_orcl_1224864495/akash/_ora1/app/oracle/oradata/orcl/users01.dbf

destination for restore of datafile 00004:

/ora1/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1: copied datafile

copy of datafile 00004 output file

name=/ora1/app/oracle/oradata/orcl/users01.dbf RECID=0 STAMP=0 Finished

restore at 24-OCT-08

RMAN> recover tablespace "USERS";

Starting recover at 24-OCT-08

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 24-OCT-08

RMAN> sql 'alter database open';