RMAN SET NEWNAME
在做資料恢復時，偶爾會碰到需要對資料檔案位置調整的案例，在這種情況下，可以在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#;
''' 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> }
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
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
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
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
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
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
===========================
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
=======================
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
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
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> }
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
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
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
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
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
===========================
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
=======================
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> }
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
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
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
media recovery complete, elapsed time: 00:00:02
Finished recover at 09/30/2013 14:18:01
RMAN> alter database open;
database opened
參考資料：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2147205/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rman set newname switch 用法
- RMAN : set newname for命令的一點研究
- 關於rman中set newname的探討
- SET NEWNAME FOR
- [20160720]rman set newname for datafile
- oracle rman 異機還原測試--set newnameOracle
- oracle10g_rman_syntac testing_set newname_switch tempfile_datafile_all_tagOracle
- 解決set newname 極慢的問題
- 【備份恢復】set newname切換日誌
- SET NEWNAME 切換檔案並恢復影像副本
- [20201103]set newname for datafile.txt
- RMAN說，我能備份(16)--RMAN中的SET命令
- 【RMAN】RMAN-20020: DATABASE INCARNATION NOT SETDatabase
- 監控和優化RMAN之SET COMMAND ID優化
- RMAN中SET命令的常見用法的小結 (ZT)
- Oracle OCP 1Z0 053 Q94(RMAN SET COMMAND ID)Oracle
- Set
- set /?
- lombok get/set 與 JavaBean get/setLombokJavaBean
- Jet Set
- Vue.set與vue.$set的使用Vue
- set pause on，set pagesize N小知識點。
- alter system set event和set events的區別
- Redis 入門 - 3（集合 set、有序集合 sort set）Redis
- JavaScript Set物件JavaScript物件
- Set delete() 方法delete
- oracle set eventsOracle
- alter session setSession
- 【轉】SET SERVEROUTPUTServer
- 【轉】SET SERVEROUTPUT ONServer
- oracle set roleOracle
- 訓練集(train set)，驗證集(validation set)和測試集(test set)AI
- 【RMAN】RMAN備份至ASMASM
- 使用RMAN工具-RMAN詳解
- cmd_sqlplus:set head off and set head onSQL
- Set size 屬性
- JavaScript Set與WeakSetJavaScript
- set_time_limitMIT