ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RMAN SET NEWNAME

RMAN SET NEWNAME

原创 Linux操作系统 作者:dbinsight 时间:2013-09-30 15:06:28 0 删除 编辑
在做数据恢复时,偶尔会碰到需要对数据文件位置调整的案例,在这种情况下,可以在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 ''/' ||
       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

参考资料:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta055.htm#i1003832
http://docs.oracle.com/cd/B10501_01/server.920/a96565/rcmsynta50.htm#RCMRF153

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29154652/viewspace-773654/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 12.1.0.1.1 PSU发布
请登录后发表评论 登录
全部评论

注册时间:2013-09-05

  • 博文量
    27
  • 访问量
    127649