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.dbfOf 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:53The 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.dbfDuring 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 0A 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.dbfMy 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
[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.dbfHave 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
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.
That's all.
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:11Next step is to put those datafiles offline.
RMAN> sql 'alter database datafile 5,9 offline'; sql statement: alter database datafile 5,9 offlineSwitch 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:31Let 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 onlinereport 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.