How to restore a lost nonsystem datafile on a different location while the database is open

Tweet

In the following scenario I'm going to lose datafiles of a non-system critical tablespace and restore them, while the database is open, to a location other than the original one because I'm experiencing a serious and permanent media failure.
Before proceeding I add another datafile to EXAMPLE tablespace so it is now formed by 2 different datafiles.

SQL> select file_name from dba_data_files
  2  where TABLESPACE_NAME = 'EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/example01.dbf

SQL> alter tablespace example add datafile '/home/oracle/app/oracle/oradata/orcl/example02.dbf' size 1M autoextend on next 5M maxsize 50M;

Tablespace altered.

SQL> select file_name from dba_data_files
  2  where TABLESPACE_NAME = 'EXAMPLE';

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

Of course to restore a tablespace you need to have a valid backup so I'm going to execute a backup tablespace command for the EXAMPLE tablespace using RMAN:

RMAN> backup tablespace example;

Starting backup at 21-01-2013 08:09:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/example02.dbf
channel ORA_DISK_1: starting piece 1 at 21-01-2013 08:09:24
channel ORA_DISK_1: finished piece 1 at 21-01-2013 08:09:50
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_21/o1_mf_nnndf_TAG20130121T080924_8htt1o4b_.bkp tag=TAG20130121T080924 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 21-01-2013 08:09:50

Starting Control File and SPFILE Autobackup at 21-01-2013 08:09:50
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_21/o1_mf_s_805277390_8htt2hst_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-01-2013 08:09:53

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

[oracle@localhost orcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ls -l example0*
-rw-rw---- 1 oracle oracle 85991424 Jan 21 08:09 example01.dbf
-rw-rw---- 1 oracle oracle  1056768 Jan 21 08:09 example02.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 all datafiles belonging to EXAMPLE tablespace:

[oracle@localhost orcl]$ rm example0*
[oracle@localhost orcl]$

I'm not able to use objects created into EXAMPLE tablespace.

SQL> select prod_name, prod_desc from sh.products where prod_id = 1;
select prod_name, prod_desc from sh.products where prod_id = 1
                                    *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

I discover also it is a permanent disk failure and I won't be able to restore EXAMPLE's datafiles on the original location, perhaps in a second moment in the next days, but now I have to solve this issue as soon as possible.
Within RMAN client you can use set newname for datafile command to change the name of multiple files during the restore operation:
after you specify the above command you have to run also switch datafile all command to update your controlfile with the renamed datafiles. If you don't use the switch command RMAN records the restored files as datafile copy in RMAN repository.
An RMAN switch is equivalent to the SQL alter database rename file command.
It's important to note that both 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.

While connected to your database you can query V$DATAFILE, V$DATAFILE_HEADER or V$DATAFILE_COPY to obtain file number of the missing datafile or run the report schema command from RMAN client.

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Jan 21 21:59:42 2013

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

connected to target database: ORCL (DBID=1229390655)

RMAN> report schema;

using target database control file instead of recovery catalog
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    0        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    0        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example02.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

My missing datafiles have 5 and 9 as file number. To restore and recover them on a new location I have to execute the following run {...} block:

RMAN> run {
2> sql 'alter database datafile 5,9 offline';
3> set newname for datafile 5 to '/home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf';
4> set newname for datafile 9 to '/home/oracle/app/oracle/oradata/orcl/non_default_location/%U';
5> restore datafile 5,9;
6> switch datafile all;
7> recover datafile 5,9;
8> sql 'alter database datafile 5,9 online';
9> }

sql statement: alter database datafile 5,9 offline

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 21-01-2013 22:20:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 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 00005 to /home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/orcl/non_default_location/data_D-ORCL_TS-EXAMPLE_FNO-9
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_21/o1_mf_nnndf_TAG20130121T080924_8htt1o4b_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_01_21/o1_mf_nnndf_TAG20130121T080924_8htt1o4b_.bkp tag=TAG20130121T080924
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 21-01-2013 22:20:59

datafile 5 switched to datafile copy
input datafile copy RECID=36 STAMP=805328459 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=37 STAMP=805328459 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/data_D-ORCL_TS-EXAMPLE_FNO-9

Starting recover at 21-01-2013 22:21:00
using channel ORA_DISK_1

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

Finished recover at 21-01-2013 22:21:01

sql statement: alter database datafile 5,9 online

Some considerations need to be made:
- the restore and recover operations were made while database was open so I needed to put those datafiles offline;
- because all missing datafiles belonged to the same tablespace I could use alter tablespace ... offline immediate, restore tablespace and recover tablespace syntax, but I want to show their uses in another post;
- I could use switch datafile command in place of switch datafile all. 

On the new location are now available two files. File data_D-ORCL_TS-EXAMPLE_FNO-9 is that one created automatically by Oracle using %U as substitution variable.

[oracle@localhost non_default_location]$ pwd
/home/oracle/app/oracle/oradata/orcl/non_default_location
[oracle@localhost non_default_location]$ ls -l
total 85100
-rw-rw---- 1 oracle oracle  1056768 Jan 21 22:21 data_D-ORCL_TS-EXAMPLE_FNO-9
-rw-rw---- 1 oracle oracle 85991424 Jan 21 22:21 example01.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/non_default_location/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/data_D-ORCL_TS-EXAMPLE_FNO-9

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 our EXAMPLE datafiles on their original location because a new disk is ready to be used.

How can we proceed ? 

I will follow the steps already described in this post.
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 5 format='/home/oracle/app/oracle/oradata/orcl/example01.dbf';

Starting backup at 22-01-2013 06:47:40
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/non_default_location/example01.dbf
output file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf tag=TAG20130122T064741 RECID=38 STAMP=805358864
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 22-01-2013 06:47:44

Starting Control File and SPFILE Autobackup at 22-01-2013 06:47:44
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_22/o1_mf_s_805358864_8hx9nkbn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-01-2013 06:47:47

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

Starting backup at 22-01-2013 06:48:08
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/data_D-ORCL_TS-EXAMPLE_FNO-9
output file name=/home/oracle/app/oracle/oradata/orcl/example02.dbf tag=TAG20130122T064808 RECID=39 STAMP=805358889
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 22-01-2013 06:48:10

Starting Control File and SPFILE Autobackup at 22-01-2013 06:48:10
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_01_22/o1_mf_s_805358890_8hx9objd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-01-2013 06:48:11

Next step is to put those datafiles offline.

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

sql statement: alter database datafile 5,9 offline

Switch to your new datafiles location updating your controlfiles.

RMAN> switch datafile 5,9 to copy;

datafile 5 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/example01.dbf"
datafile 9 switched to datafile copy "/home/oracle/app/oracle/oradata/orcl/example02.dbf"

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

RMAN> recover datafile 5,9;

Starting recover at 22-01-2013 06:48:31
using channel ORA_DISK_1

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

Finished recover at 22-01-2013 06:48:31

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

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

sql statement: alter database datafile 5,9 online

report schema command displays the new location of EXAMPLE 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

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.