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';
