search

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:

circle-info

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

rman target=sys@pdb1

For a non-PDB database, use:

rman target /

Last updated