本文共 12768 字，大约阅读时间需要 42 分钟。
一 switch 命令 1 switch命令用途 更新数据文件名为rman下镜像拷贝时指定的数据文件名 更新数据文件名为 set newname 命令指定的名字。 2 switch 命令使用前提条件 rman 必须连接到目标数据库 当switch tablespaces、datafiles、tempfiles时，这些文件必须离线 当switch 整个数据库时，数据库不能open
3 注意事项 switch....to copy 命令用于rman命令提示符下 switch没有to copy的命令用于run语句块中 4 语法 第一组 switch dtabase to copy; switch datafile number|name to copy; switch tablespace name to copy; 第二组 switch datafile all; switch datafile number|name; switch tempfile all; 二 举例如下：
例一：用switch datafile number to copy 更新数据文件位置和名字 RMAN> backup as copy datafile 8 format '/oracle/CRM/test.dbf';
Starting backup at 2013-02-22 10:06:21 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=/backup/test.dbf output file name=/oracle/CRM/test.dbf tag=TAG20130222T100621 RECID=14 STAMP=808049181 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2013-02-22 10:06:22
Starting Control File and SPFILE Autobackup at 2013-02-22 10:06:22 piece handle=/backup/c-3599153036-20130222-01 comment=NONE Finished Control File and SPFILE Autobackup at 2013-02-22 10:06:25
RMAN> sql 'alter tablespace test offline';
sql statement: alter tablespace test offline
RMAN> switch datafile 8 to copy;
datafile 8 switched to datafile copy "/oracle/CRM/test.dbf"
RMAN> report schema;
Report of database schema for database with db_unique_name CRM
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /oracle/CRM/system01.dbf 2 540 SYSAUX *** /oracle/CRM/sysaux01.dbf 3 100 UNDOTBS3 *** /oracle/CRM/undotbs3.dbf 4 1742 USERS *** /backup/users01.dbf 5 500 POS *** /oracle/CRM/pos.dbf 6 100 ERP *** /oracle/CRM/erp.dbf 7 5 USER01 *** /oracle/CRM/user01.dbf 8 0 TEST *** /oracle/CRM/test.dbf
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 218 TEMP 32767 /oracle/CRM/temp01.dbf 2 3072 MYNEWTEMP 40960 /oracle/CRM/newtemp.dbf
例二 用switch tablespace name to copy更新表空间所有数据文件位置和名字 RMAN> backup as copy tablespace pos format '/oracle/%N%f.dbf'; （%N为表空间名、%f为数据文件绝对文件号） Starting backup at 2013-02-22 10:55:49 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/oracle/CRM/pos.dbf channel ORA_DISK_2: starting datafile copy input datafile file number=00009 name=/oracle/CRM/pos2.dbf output file name=/oracle/POS9.dbf tag=TAG20130222T105549 RECID=18 STAMP=808052153 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:07 output file name=/oracle/POS5.dbf tag=TAG20130222T105549 RECID=19 STAMP=808052179 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36 Finished backup at 2013-02-22 10:56:25
Starting Control File and SPFILE Autobackup at 2013-02-22 10:56:25 piece handle=/backup/c-3599153036-20130222-03 comment=NONE Finished Control File and SPFILE Autobackup at 2013-02-22 10:56:28
RMAN> sql 'alter tablespace pos offline';
sql statement: alter tablespace pos offline
RMAN> switch tablespace pos to copy;
datafile 5 switched to datafile copy "/oracle/POS5.dbf" datafile 9 switched to datafile copy "/oracle/POS9.dbf"
RMAN> report schema;
Report of database schema for database with db_unique_name CRM
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /oracle/CRM/system01.dbf 2 540 SYSAUX *** /oracle/CRM/sysaux01.dbf 3 100 UNDOTBS3 *** /oracle/CRM/undotbs3.dbf 4 1742 USERS *** /backup/users01.dbf 5 0 POS *** /oracle/POS5.dbf 6 100 ERP *** /oracle/CRM/erp.dbf 7 5 USER01 *** /oracle/CRM/user01.dbf 8 0 TEST *** /oracle/CRM/test.dbf 9 0 POS *** /oracle/POS9.dbf
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 218 TEMP 32767 /oracle/CRM/temp01.dbf 2 3072 MYNEWTEMP 40960 /oracle/CRM/newtemp.dbf
例三 用switch dtabase to copy更新数据库所有数据文件位置和名字 RMAN> backup as copy database format '/oracle/CRM/test/%N%f.dbf';
Starting backup at 2013-02-22 11:26:52 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=129 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=192 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/backup/users01.dbf channel ORA_DISK_2: starting datafile copy input datafile file number=00001 name=/oracle/CRM/system01.dbf output file name=/oracle/CRM/test/SYSTEM1.dbf tag=TAG20130222T112653 RECID=52 STAMP=808054107 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:41 channel ORA_DISK_2: starting datafile copy input datafile file number=00002 name=/oracle/CRM/sysaux01.dbf output file name=/oracle/CRM/test/USERS4.dbf tag=TAG20130222T112653 RECID=53 STAMP=808054182 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:54 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/oracle/POS5.dbf output file name=/oracle/CRM/test/SYSAUX2.dbf tag=TAG20130222T112653 RECID=54 STAMP=808054193 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:11 channel ORA_DISK_2: starting datafile copy input datafile file number=00003 name=/oracle/CRM/undotbs3.dbf output file name=/oracle/CRM/test/UNDOTBS33.dbf tag=TAG20130222T112653 RECID=55 STAMP=808054203 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:16 channel ORA_DISK_2: starting datafile copy input datafile file number=00006 name=/oracle/CRM/erp.dbf output file name=/oracle/CRM/test/ERP6.dbf tag=TAG20130222T112653 RECID=56 STAMP=808054230 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_2: starting datafile copy input datafile file number=00009 name=/oracle/POS9.dbf output file name=/oracle/CRM/test/POS5.dbf tag=TAG20130222T112653 RECID=57 STAMP=808054247 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:58 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/oracle/CRM/user01.dbf output file name=/oracle/CRM/test/POS9.dbf tag=TAG20130222T112653 RECID=58 STAMP=808054247 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:08 channel ORA_DISK_2: starting datafile copy input datafile file number=00008 name=/oracle/CRM/test.dbf output file name=/oracle/CRM/test/USER017.dbf tag=TAG20130222T112653 RECID=59 STAMP=808054250 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:00 output file name=/oracle/CRM/test/TEST8.dbf tag=TAG20130222T112653 RECID=60 STAMP=808054250 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2013-02-22 11:30:51
Starting Control File and SPFILE Autobackup at 2013-02-22 11:30:51 piece handle=/backup/c-3599153036-20130222-06 comment=NONE Finished Control File and SPFILE Autobackup at 2013-02-22 11:30:59
RMAN> switch database to copy;
datafile 1 switched to datafile copy "/oracle/CRM/test/SYSTEM1.dbf" datafile 2 switched to datafile copy "/oracle/CRM/test/SYSAUX2.dbf" datafile 3 switched to datafile copy "/oracle/CRM/test/UNDOTBS33.dbf" datafile 4 switched to datafile copy "/oracle/CRM/test/USERS4.dbf" datafile 5 switched to datafile copy "/oracle/CRM/test/POS5.dbf" datafile 6 switched to datafile copy "/oracle/CRM/test/ERP6.dbf" datafile 7 switched to datafile copy "/oracle/CRM/test/USER017.dbf" datafile 8 switched to datafile copy "/oracle/CRM/test/TEST8.dbf" datafile 9 switched to datafile copy "/oracle/CRM/test/POS9.dbf"
RMAN> sql 'alter database open';
sql statement: alter database open
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS ---------- ---------------------------------------- ------- 1 /oracle/CRM/test/SYSTEM1.dbf SYSTEM 2 /oracle/CRM/test/SYSAUX2.dbf ONLINE 3 /oracle/CRM/test/UNDOTBS33.dbf ONLINE 4 /oracle/CRM/test/USERS4.dbf ONLINE 5 /oracle/CRM/test/POS5.dbf ONLINE 6 /oracle/CRM/test/ERP6.dbf ONLINE 7 /oracle/CRM/test/USER017.dbf ONLINE 8 /oracle/CRM/test/TEST8.dbf ONLINE 9 /oracle/CRM/test/POS9.dbf ONLINE 例四 用switch tempfile all更新所有临时数据文件位置和名字 SQL> select file#,name,status from v$tempfile;
FILE# NAME STATUS ---------- ---------------------------------------- ------- 1 /oracle/CRM/temp01.dbf ONLINE 2 /oracle/CRM/newtemp.dbf ONLINE 语句如下： startup force mount run{ set newname for tempfile 1 to '/oracle/CRM/test/temp01.dbf'; set newname for tempfile 2 to '/oracle/CRM/test/temp02.dbf'; switch tempfile all; alter database open; }
执行过程如下： RMAN> startup force mount
run{ set newname for tempfile 1 to '/oracle/CRM/test/temp01.dbf'; set newname for tempfile 2 to '/oracle/CRM/test/temp02.dbf'; switch tempfile all; alter database open; } Oracle instance started database mounted
Total System Global Area 1252663296 bytes
Fixed Size 2226072 bytes Variable Size 1006635112 bytes Database Buffers 234881024 bytes Redo Buffers 8921088 bytes
RMAN> 2> 3> 4> 5> 6> executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/CRM/test/temp01.dbf in control file renamed tempfile 2 to /oracle/CRM/test/temp02.dbf in control file
database opened
RMAN> report schema;
Report of database schema for database with db_unique_name CRM
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /oracle/CRM/test/SYSTEM1.dbf 2 540 SYSAUX *** /oracle/CRM/test/SYSAUX2.dbf 3 100 UNDOTBS3 *** /oracle/CRM/test/UNDOTBS33.dbf 4 1742 USERS *** /oracle/CRM/test/USERS4.dbf 5 500 POS *** /oracle/CRM/test/POS5.dbf 6 100 ERP *** /oracle/CRM/test/ERP6.dbf 7 5 USER01 *** /oracle/CRM/test/USER017.dbf 8 1 TEST *** /oracle/CRM/test/TEST8.dbf 9 10 POS *** /oracle/CRM/test/POS9.dbf
List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 218 TEMP 32767 /oracle/CRM/test/temp01.dbf 2 3072 MYNEWTEMP 40960 /oracle/CRM/test/temp02.dbf
例五 用switch datafile all更新表空间所有数据位置和名字 语句： run{ sql 'alter tablespace pos offline immediate'; set newname for datafile '/oracle/CRM/test/POS5.dbf' to '/oracle/CRM/pos1.dbf'; set newname for datafile '/oracle/CRM/test/POS9.dbf' to '/oracle/CRM/pos2.dbf'; restore tablespace pos; switch datafile all; recover tablespace pos; sql 'alter tablespace pos online'; }
执行过程如下： RMAN> run{ 2> sql 'alter tablespace pos offline immediate'; 3> set newname for datafile '/oracle/CRM/test/POS5.dbf' to '/oracle/CRM/pos1.dbf'; 4> set newname for datafile '/oracle/CRM/test/POS9.dbf' to '/oracle/CRM/pos2.dbf'; 5> restore tablespace pos; 6> switch datafile all; 7> recover tablespace pos; 8> sql 'alter tablespace pos online'; 9> }
sql statement: alter tablespace pos offline immediate
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2013-02-22 15:18:37 using channel ORA_DISK_1 using channel ORA_DISK_2
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 /oracle/CRM/pos1.dbf channel ORA_DISK_1: restoring datafile 00009 to /oracle/CRM/pos2.dbf channel ORA_DISK_1: reading from backup piece /backup/20130222_hho2k7jn_1_1 channel ORA_DISK_1: piece handle=/backup/20130222_hho2k7jn_1_1 tag=TAG20130222T145756 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 2013-02-22 15:19:03
datafile 5 switched to datafile copy input datafile copy RECID=72 STAMP=808067944 file name=/oracle/CRM/pos1.dbf datafile 9 switched to datafile copy input datafile copy RECID=73 STAMP=808067944 file name=/oracle/CRM/pos2.dbf
Starting recover at 2013-02-22 15:19:04 using channel ORA_DISK_1 using channel ORA_DISK_2
starting media recovery media recovery complete, elapsed time: 00:00:00
Finished recover at 2013-02-22 15:19:05
sql statement: alter tablespace pos online
RMAN> report schema;
Report of database schema for database with db_unique_name CRM
List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 750 SYSTEM *** /oracle/CRM/test/SYSTEM1.dbf 2 540 SYSAUX *** /oracle/CRM/test/SYSAUX2.dbf 3 100 UNDOTBS3 *** /oracle/CRM/test/UNDOTBS33.dbf 4 1742 USERS *** /oracle/CRM/test/USERS4.dbf 5 500 POS *** /oracle/CRM/pos1.dbf 6 100 ERP *** /oracle/CRM/test/ERP6.dbf 7 5 USER01 *** /oracle/CRM/test/USER017.dbf 8 1 TEST *** /oracle/CRM/test/TEST8.dbf 9 10 POS *** /oracle/CRM/pos2.dbf
转载地址：http://uwcmo.baihongyu.com/