在做資料恢復時，偶爾會碰到需要對資料檔案位置調整的案例，在這種情況下，可以在RMAN中使用SET NEWNAME命令。

在Oracle 11g之前，RMAN只支援SET NEWNAME FOR DATAFILE，在Oracle 11g中增加了SET NEWNAME FOR TEMPFILE/SET NEWNAME FOR TABLESPACE/SET NEWNAME FOR DATABASE的命令。

優先順序如下：

1.SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TEMPFILE

2.SET NEWNAME FOR TABLESPACE

3.SET NEWNAME FOR DATABASE

當使用SET NEWNAME FOR DATAFILE/TEMPFILE的時候，可以使用下面的SQL生成所有的SET NEWNAME命令：

select 'set newname for datafile ''' || name ||
       ''' to ''<newloc>/' ||
       substr(name, instr(name, '/', -1) + 1) || ''';'
  from v$datafile order by file#;

當使用FOR TABLESPACE/DATABASE命令的時候，可以指定下面的變數格式：

%b Specifies the file name stripped of directory paths.對應的檔名稱

%f Specifies the absolute file number of the data file for which the new name is generated. 資料檔案的絕對檔案號

%U Specifies the following format: data-D-%d_id-%I_TS-%N_FNO-%f

%I Specifies the DBID 對應的DBID

%N Specifies the tablespace name 對應的表空間名稱

其中前面三個變數必須指定一個，後面2個是可選的。

常見的，我們需要保持資料檔案一直，值使用%b即可。

eg：

1：SET NEWNAME FOR TABLESPACE

RMAN> sql 'alter tablespace users offline';

sql statement: alter tablespace users offline

RMAN> run {
2> set newname for tablespace users to '/arch/bentest/oradata/newloc/%b';
3> restore tablespace users;
4> switch datafile all;
5> }

executing command: SET NEWNAME

Starting restore at 09/30/2013 14:12:27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1657 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 00004 to /arch/bentest/oradata/newloc/users01.dbf
channel ORA_DISK_1: reading from backup piece /u/ora11g/fast_recovery_area/BENTEST/backupset/2013_09_30/o1_mf_nnndf_TAG20130930T140748_94l59o8x_.bkp
channel ORA_DISK_1: piece handle=/u/ora11g/fast_recovery_area/BENTEST/backupset/2013_09_30/o1_mf_nnndf_TAG20130930T140748_94l59o8x_.bkp tag=TAG20130930T140748
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 09/30/2013 14:12:35

datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=827503955 file name=/arch/bentest/oradata/newloc/users01.dbf

RMAN> recover tablespace users;

Starting recover at 09/30/2013 14:12:48
using channel ORA_DISK_1

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

Finished recover at 09/30/2013 14:12:48

RMAN> sql 'alter tablespace users online';

sql statement: alter tablespace users online

RMAN> report schema;

Report of database schema for database with db_unique_name BENTEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 325 SYSTEM *** /arch/bentest/oradata/system01.dbf
2 325 SYSAUX *** /arch/bentest/oradata/sysaux01.dbf
3 200 UNDOTBS1 *** /arch/bentest/oradata/undotbs01.dbf
4 500 USERS *** /arch/bentest/oradata/newloc/users01.dbf
5 200 BIGTBS *** /arch/bentest/oradata/bigtbs01.dbf
6 20 WE_WILL_LOST_IT *** /arch/bentest/oradata/wwli.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 20 /arch/bentest/oradata/temp01.dbf

2：SET NEWNAME FOR DATABASE

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 4175568896 bytes

Fixed Size 2233088 bytes
Variable Size 1224740096 bytes
Database Buffers 2902458368 bytes
Redo Buffers 46137344 bytes

RMAN> run {
2> set newname for database to '/arch/bentest/oradata/newloc/%b';
3> restore database;
4> recover database;
5> }

executing command: SET NEWNAME

Starting restore at 09/30/2013 14:16:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1081 device type=DISK

skipping datafile 4; already restored to file /arch/bentest/oradata/newloc/users01.dbf
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 00001 to /arch/bentest/oradata/newloc/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /arch/bentest/oradata/newloc/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /arch/bentest/oradata/newloc/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /arch/bentest/oradata/newloc/bigtbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /arch/bentest/oradata/newloc/wwli.dbf
channel ORA_DISK_1: reading from backup piece /u/ora11g/fast_recovery_area/BENTEST/backupset/2013_09_30/o1_mf_nnndf_TAG20130930T140748_94l59o8x_.bkp
channel ORA_DISK_1: piece handle=/u/ora11g/fast_recovery_area/BENTEST/backupset/2013_09_30/o1_mf_nnndf_TAG20130930T140748_94l59o8x_.bkp tag=TAG20130930T140748
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 09/30/2013 14:16:31

Starting recover at 09/30/2013 14:16:31
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 5 not processed because file is read-only

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

Finished recover at 09/30/2013 14:16:33

RMAN> report schema;

Report of database schema for database with db_unique_name BENTEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 325 SYSTEM *** /arch/bentest/oradata/system01.dbf
2 325 SYSAUX *** /arch/bentest/oradata/sysaux01.dbf
3 200 UNDOTBS1 *** /arch/bentest/oradata/undotbs01.dbf
4 500 USERS *** /arch/bentest/oradata/newloc/users01.dbf
5 200 BIGTBS *** /arch/bentest/oradata/bigtbs01.dbf
6 20 WE_WILL_LOST_IT *** /arch/bentest/oradata/wwli.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 20 /arch/bentest/oradata/temp01.dbf

RMAN> run {
2> set newname for database to '/arch/bentest/oradata/newloc/%b';
3> switch datafile all;
4> }

executing command: SET NEWNAME

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=827504182 file name=/arch/bentest/oradata/newloc/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=827504185 file name=/arch/bentest/oradata/newloc/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=827504179 file name=/arch/bentest/oradata/newloc/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=827504177 file name=/arch/bentest/oradata/newloc/bigtbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=827504178 file name=/arch/bentest/oradata/newloc/wwli.dbf

RMAN> recover database;

Starting recover at 09/30/2013 14:17:59
using channel ORA_DISK_1
datafile 4 not processed because file is read-only
datafile 5 not processed because file is read-only

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

Finished recover at 09/30/2013 14:18:01

RMAN> alter database open;

database opened

參考資料：

RMAN SET NEWNAME

相關文章