How to restore lost nonsystem datafiles on a different location while the database is closed

Tweet

In the following scenario I'm going to lose two datafiles of two different non-system critical tablespaces and restore them, while the database is CLOSED, to a location other than the original one because I'm experiencing a serious and permanent media failure.

To simulate this scenario I want to remove a datafile from EXAMPLE and APEX tablespaces.
Let's see first where are located those datafiles.

SQL> select file_name from dba_data_files
  2  where TABLESPACE_NAME IN ('EXAMPLE', 'APEX');

FILE_NAME
----------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf
/home/oracle/app/oracle/oradata/orcl/APEX.dbf
/home/oracle/app/oracle/oradata/orcl/example02.dbf
/home/oracle/app/oracle/oradata/orcl/APEX02.dbf

I'm going to remove example02.dbf and APEX02.dbf datafiles because I have a valid backup of EXAMPLE and APEX tablespaces taken days ago using RMAN.
Are you sure you have a valid backup ? Why don't you verify it ?
Let's see what id number have those datafiles querying V$DATAFILE view:

SQL> col name format a60
SQL> set linesize 180
SQL> select file#, name from v$DATAFILE;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /home/oracle/app/oracle/oradata/orcl/system01.dbf
         2 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
         3 /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
         4 /home/oracle/app/oracle/oradata/orcl/users01.dbf
         5 /home/oracle/app/oracle/oradata/orcl/example01.dbf
         6 /home/oracle/app/oracle/oradata/orcl/APEX.dbf
         7 /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
         8 /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
         9 /home/oracle/app/oracle/oradata/orcl/example02.dbf
        10 /home/oracle/app/oracle/oradata/orcl/APEX02.dbf

Now let's verify if I have a valid backup using restore ... preview command for datafiles with 9 and 10 as id number:

RMAN> restore datafile 9,10 preview;

Starting restore at 22-01-2013 21:54:35
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
227     Full    18.59M     DISK        00:00:14     22-01-2013 21:40:51
        BP Key: 228   Status: AVAILABLE  Compressed: YES  Tag: TAG20130122T214051
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T214051_8hyxz3j4_.bkp
  List of Datafiles in backup set 227
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  9       Full 14790649   22-01-2013 21:40:51 /home/oracle/app/oracle/oradata/orcl/example02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
229     Full    1.55M      DISK        00:00:02     22-01-2013 21:53:33
        BP Key: 230   Status: AVAILABLE  Compressed: YES  Tag: TAG20130122T215332
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T215332_8hyypx39_.bkp
  List of Datafiles in backup set 229
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  10      Full 14791164   22-01-2013 21:53:33 /home/oracle/app/oracle/oradata/orcl/APEX02.dbf

archived logs generated after SCN 14790649 not found in repository
Media recovery start SCN is 14790649
Recovery must be done beyond SCN 14791164 to clear datafile fuzziness
Finished restore at 22-01-2013 21:54:35

The original location of APEX and EXAMPLE datafiles is: /home/oracle/app/oracle/oradata/orcl/

[oracle@localhost orcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ls -l *02.dbf*
-rw-rw---- 1 oracle oracle  1056768 Jan 22 21:53 APEX02.dbf
-rw-rw---- 1 oracle oracle  1056768 Jan 22 21:40 example02.dbf
-rw-rw---- 1 oracle oracle 20979712 Jan 12 01:32 temp02.dbf

During the restore operation I will instruct RMAN to recreate them on a new destination: /home/oracle/app/oracle/oradata/orcl/non_default_location

[oracle@localhost orcl]$ cd non_default_location/
[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ls -l
total 0

A media failure happened and I've lost those datafiles belonging to EXAMPLE and APEX tablespace:

[oracle@localhost orcl]$ rm example02*
[oracle@localhost orcl]$ rm APEX02*
[oracle@localhost orcl]$

The instance crashed.

[oracle@localhost non_default_location]$ ps -ef|grep smon

If I try to startup the database it remains in MOUNT mode throwing the error "ORA-01157: cannot identify/lock data file 9 - see DBWR trace file".

[oracle@localhost non_default_location]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 22 22:05:09 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/home/oracle/app/oracle/oradata/orcl/example02.dbf'

SQL> select open_mode from V$DATABASE;

OPEN_MODE
--------------------
MOUNTED

If I have a look at the alert log I can see also the same error for datafile 10:

[oracle@localhost orcl]$ tail -f -n100 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log 
...
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_4818.trc:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/home/oracle/app/oracle/oradata/orcl/APEX02.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
...

Let's shutdown the instance to use a RMAN script restoring and recovering our database while is closed:

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

As already said due to a permanent disk failure I won't be able to restore example02.dbf and APEX02.dbf datafiles on the original location.
Like in the previous post I need to use set newname for datafile command to change the name of multiple files during the restore operation and then I have also to run switch datafile all command to update our controlfile with the renamed datafiles.
Note that those commands must be executed inside a run {...} block.
To identify your original datafiles you can use their absolute file numbers, full path or relative file names; to recreate them on a new location you have to specify their full path file names, using eventually some substitution variable like %U to specify a system-generated unique file name and avoid file name collisions.
We already know id numbers, but in this scenario and differently from the previous one, I want to use the full path file name of the missing datafiles.
To restore and recover them on a new location I have to execute the following run {...} block after connected to RMAN and issued a startup mount command:

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 22 22:20:34 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                360712888 bytes
Database Buffers              88080384 bytes
Redo Buffers                   6008832 bytes

RMAN> run {
2> set newname for datafile '/home/oracle/app/oracle/oradata/orcl/example02.dbf' to '/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf';
3> set newname for datafile '/home/oracle/app/oracle/oradata/orcl/APEX02.dbf' to '/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf';
4> restore datafile '/home/oracle/app/oracle/oradata/orcl/example02.dbf', '/home/oracle/app/oracle/oradata/orcl/APEX02.dbf';
5> switch datafile all;
6> recover datafile '/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf','/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf';
7> alter database open;
8> }

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 22-01-2013 22:27:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T214051_8hyxz3j4_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T214051_8hyxz3j4_.bkp tag=TAG20130122T214051
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T215332_8hyypx39_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_22/o1_mf_nnndf_TAG20130122T215332_8hyypx39_.bkp tag=TAG20130122T215332
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-01-2013 22:27:32

datafile 9 switched to datafile copy
input datafile copy RECID=44 STAMP=805415252 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=45 STAMP=805415252 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf

Starting recover at 22-01-2013 22:27:32
using channel ORA_DISK_1

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

Finished recover at 22-01-2013 22:27:33

database opened

Some considerations need to be made:
- the restore and recover operations were made while database was mounted so I did not need to put those datafiles offline;
- I could use switch datafile command in place of switch datafile all; 
- I prefer to specify in these situations the id number, otherwise you have to use the original file name for the restore datafile command and the new file name for the recover datafile command. 

This is always due to the fact that controlfile doesn't have any information about the new location until you execute the switch datafile all command: from that moment the controlfile lose the information about the original location of those datafiles. 

Just be careful to use the right file name if you prefer to specify full path file name instead querying V$DATAFILE or issuing report schema to know id numbers. 
Let's see on the new location the two datafiles:

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ls -l
total 2072
-rw-rw---- 1 oracle oracle 1056768 Jan 22 22:27 APEX02.dbf
-rw-rw---- 1 oracle oracle 1056768 Jan 22 22:27 example02.dbf

Have a look at the new output produced by report schema command:

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    475      UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    1        ZZZ                  ***     /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
9    1        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf
10   1        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

Few days later we are able to move back those datafiles on their original location because a new disk is ready to be used.

First thing to do is to copy your datafiles using the format clause, specifying you want to create your datafile copy to the original location.

RMAN> backup as copy datafile 9 format='/home/oracle/app/oracle/oradata/orcl/example02.dbf';

Starting backup at 23-01-2013 01:00:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example02.dbf
output file name=/home/oracle/app/oracle/oradata/orcl/example02.dbf tag=TAG20130123T010015 RECID=46 STAMP=805424415
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-01-2013 01:00:16

Starting Control File and SPFILE Autobackup at 23-01-2013 01:00:16
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_23/o1_mf_s_805424416_8hz9o1p5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-01-2013 01:00:20

RMAN> backup as copy datafile 10 format='/home/oracle/app/oracle/oradata/orcl/APEX02.dbf'; 

Starting backup at 23-01-2013 01:00:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/non_default_location/APEX02.dbf
output file name=/home/oracle/app/oracle/oradata/orcl/APEX02.dbf tag=TAG20130123T010042 RECID=47 STAMP=805424443
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-01-2013 01:00:44

Starting Control File and SPFILE Autobackup at 23-01-2013 01:00:44
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_23/o1_mf_s_805424444_8hz9owgy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-01-2013 01:00:45

Next step is to put the same datafiles offline.

RMAN> sql 'alter database datafile 9,10 offline';

sql statement: alter database datafile 9,10 offline

Switch to your new datafiles location updating your controlfiles.

RMAN> switch datafile 9,10 to copy;

datafile 9 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/example02.dbf"
datafile 10 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/APEX02.dbf"

Recover your datafiles because some transactions could be occurred between backup as copy datafile command and putting datafiles offline.

RMAN> recover datafile 9,10;

Starting recover at 23-01-2013 01:01:45
using channel ORA_DISK_1

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

Finished recover at 23-01-2013 01:01:46

Let datafiles be available to all the users, putting them online:

RMAN> sql 'alter database datafile 9,10 online';

sql statement: alter database datafile 9,10 online

report schema command displays the new location of example02.dbf and APEX02.dbf datafiles.

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    475      UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    1        ZZZ                  ***     /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
9    1        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example02.dbf
10   1        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf

That's all.