RMAN中switch命令怎么用

54次阅读

共计 14731 个字符，预计需要花费 37 分钟才能阅读完成。

这篇文章给大家分享的是有关 RMAN 中 switch 命令怎么用的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

SWITCH 命令详解

Purpose：目的，即使用 switch 命令能做什么
Use the SWITCH command to perform either of the following operations:
Update the file names for a database, tablespace, or data file to the latest image copies available for the specified files
1. 将数据库，表空间或数据文件的文件名更新为指定文件可用的最新映像副本
Update the file names for data files and temp files for which you have issued a SET NEWNAME command
2. 在 run 块中发出 set netname 后在控制文件中更新指定的文件名信息

A SWITCH is equivalent to the SQL statement ALTER DATABASE RENAME FILE: the names of the files in the RMAN repository are updated, but the database does not rename the files at the operating system level.

Prerequisites（先决条件）

RMAN must be connected to a target database. When switching tablespaces, data files, or temp files, the files must be offline. When switching the whole database, the database must not be open.
RMAN 必须连接到目标数据库。切换表空间，数据文件或临时文件时，这些文件必须处于脱机状态。切换整个数据库时，数据库一定不能打开。

Usage Notes（使用说明）

The SWITCH command deletes the RMAN repository records for the data file copy from the recovery catalog and updates the control file records to status DELETED.
SWITCH 命令从恢复目录中删除数据文件副本的 RMAN 存储库记录，并将控制文件记录更新为 DELETED 状态。

If RMAN is connected to a recovery catalog, and if the database is using a control file restored from backup, then SWITCH updates the control file with records of any data files known to the recovery catalog but missing from the control file.
如果 RMAN 连接到恢复目录，并且数据库正在使用从备份还原的控制文件，则 SWITCH 会更新控制文件，并记录恢复目录已知但控制文件中缺失的任何数据文件。

Execute SWITCH … TO COPY only at the RMAN prompt. Use SWITCH without TO COPY only within a RUN block.
只在 RMAN 提示符下执行 SWITCH … TO COPY。仅在 RUN 块内使用 SWITCH 而不要 TO COPY。

语法：

SWITCH {DATABASE | DATAFILE datafileSpec [, datafileSpec]… | TABLESPACE [] tablespace_name [] [, [] tablespace_name []]…} TO COPY

SWITCH {DATAFILE ALL | DATAFILE datafileSpec [TO DATAFILECOPY { filename | TAG [] tag_name []}] | TEMPFILE ALL | TEMPFILE tempfileSpec [TO filename]}

Semantics 语义

switch

This subclause switches file names for a database, tablespace, or
data file to the latest image copies available for the specified files.
By executing this command, you avoid restoring data files from backups.
Execute SWITCH … TO COPY only at the RMAN prompt.
这个子句将数据库，表空间或数据文件的文件名切换为可用于指定文件的最新映像副本。通过执行此命令，可以避免从备份中恢复数据文件。只在 RMAN 提示符下执行 SWITCH … TO COPY。

switch File

This subclause updates the names for data files and temp files for which you have issued a SET NEWNAME command. Use this clause only within a RUN block.
本子句更新您为其发出 SET NEWNAME 命令的数据文件和临时文件的名称。仅在 RUN 块中使用此子句。

Examples 使用实例
Example 3-55 Switching to Image Copies to Avoid Restoring from Backup（切换到图像副本以避免从备份中恢复）

Assume that a disk fails, rendering all data files in the users tablespace inaccessible. Image copies of all data files in this tablespace exist in the fast recovery area. After starting RMAN and connecting to the database as TARGET, you can run SWITCH to point to the control file to the new data files and then run RECOVER as follows: 假设一个磁盘失败，将无法访问用户表空间中的所有数据文件。快速恢复区域中存在此表空间中所有数据文件的映像副本。启动 RMAN 并以 TARGET 连接到数据库后，可以运行 SWITCH 指向新文件的控制文件，然后按如下所示运行 RECOVER。

SQL ALTER TABLESPACE users OFFLINE IMMEDIATE
SWITCH TABLESPACE users TO COPY;
RECOVER TABLESPACE users;
SQL ALTER TABLESPACE users ONLINE

Example 3-56 Switching Data File File Names After a Restore to a New Location（在还原到新位置后切换数据文件文件名称）

Assume that a disk fails, forcing you to restore a data file to a new disk location. After starting RMAN and connecting to the database as TARGET, you can use the SET NEWNAME command to rename the data file, then RESTORE to restore the missing data file. You run SWITCH to point to the control file to the new data file and then RECOVER. This example allocates both disk and tape channels. 假定磁盘发生故障，迫使您将数据文件恢复到新的磁盘位置。启动 RMAN 并以 TARGET 连接到数据库后，可以使用 SET NEWNAME 命令重命名数据文件，然后使用 RESTORE 还原缺失的数据文件。您运行 SWITCH 指向控制文件到新的数据文件，然后再恢复。本例分配磁盘和磁带通道。

RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE sbt;
SQL ALTER TABLESPACE users OFFLINE IMMEDIATE
SET NEWNAME FOR DATAFILE /disk1/oradata/prod/users01.dbf
TO /disk2/users01.dbf
RESTORE TABLESPACE users;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE users;
SQL ALTER TABLESPACE users ONLINE
}

实验第一种使用情况，如下：
Update the file names for a database, tablespace, or data file to the latest image copies available for the specified files
将数据库，表空间或数据文件的文件名更新为指定文件可用的最新映像副本

1. 查询表空间文件位置、大小

SQL select f.file#,
2 t.name tablespace,
3 f.name,
4 trunc(f.bytes / 1048576, 2) size_mb,
5 to_char(f.creation_time, yyyy-mm-dd) creation_time,
6 status
7 from v$datafile f, v$tablespace t
8 where f.ts# = t.ts#
9 order by f.creation_time;

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS
———- —————————— ———————————————————————- ———- ———- ——-
1 SYSTEM +DATA/dbrac/datafile/system.256.957563979 900 2013-08-24 SYSTEM
2 SYSAUX +DATA/dbrac/datafile/sysaux.257.957563979 610 2013-08-24 ONLINE
4 USERS +DATA/dbrac/datafile/users.259.957563981 171.25 2013-08-24 ONLINE
3 UNDOTBS1 +DATA/dbrac/datafile/undotbs1.258.957563981 100 2013-08-24 ONLINE
5 EXAMPLE +DATA/dbrac/datafile/example.264.957564205 346.25 2017-10-16 ONLINE
6 UNDOTBS2 +DATA/dbrac/datafile/undotbs2.265.957564601 100 2017-10-16 ONLINE
7 PERSON_LOB +DATA/dbrac/datafile/person_lob.269.957699435 10 2017-10-18 ONLINE
8 TEST +DATA/dbrac/datafile/test.270.957699617 20 2017-10-18 ONLINE
9 TEST_IND +DATA/dbrac/datafile/test_ind.271.957699627 20 2017-10-18 ONLINE
10 TEST_BLOB +DATA/dbrac/datafile/test_blob.273.957699641 20 2017-10-18 ONLINE
11 TEST_IND +DATA/dbrac/datafile/test_ind.274.957699781 400 2017-10-18 ONLINE
12 TEST_BLOB +DATA/dbrac/datafile/test_blob.275.957699833 400 2017-10-18 ONLINE
13 TEST +DATA/dbrac/datafile/test.276.957699883 400 2017-10-18 ONLINE

13 rows selected.

– 查询 asm 磁盘组剩余空间：
[grid@dbrac1 ~]$ asmcmd -p
ASMCMD [+] lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 30720 22892 0 11446 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 10240 4610 0 2305 0 N FRA/
MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCRVOTE/
ASMCMD [+]
[grid@dbrac2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 19 18:40:22 2017

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL select status from v$instance;

STATUS
————————
STARTED

SQL set lines 200
SQL col name for a15
SQL select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS,VOTING_FILES from v$asm_diskgroup;

GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS VO
———— ————— ———————- ———— ———- ———- ———————– ————– ————- —
1 DATA MOUNTED NORMAL 30720 22892 0 11446 0 N
2 FRA MOUNTED NORMAL 10240 4610 0 2305 0 N
3 OCRVOTE MOUNTED NORMAL 6144 5218 2048 1585 0 Y

SQL select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 1,2;

GROUP_NUMBER DISK_NUMBER MOUNT_STATUS HEADER_STA MODE_STATUS STATE REDUNDANCY TOTAL_MB FREE_MB NAME FAILGROUP PATH
———— ———– ————– ———- ————– ———- ————– ———- ———- ————— ————— ——————–
1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11446 DATA_0000 DATA_0000 /dev/asm_data1
1 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11446 DATA_0001 DATA_0001 /dev/asm_data2
2 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2305 FRA_0000 FRA_0000 /dev/asm_fra1
2 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2305 FRA_0001 FRA_0001 /dev/asm_fra2
3 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1740 OCRVOTE_0000 OCRVOTE_0000 /dev/asm_ocrvote1
3 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0001 OCRVOTE_0001 /dev/asm_ocrvote2
3 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0002 OCRVOTE_0002 /dev/asm_ocrvote3

7 rows selected.

模拟将 test 磁盘的数据文件由 DATA 磁盘组迁移到 FRA 磁盘组：
检查归档：
SQL archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 65
Next log sequence to archive 66
Current log sequence 66
SQL
SQL show parameter recover

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 4407M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SQL
SQL show parameter log_archive_dest

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
省略 …………….

检查 test 表空间及数据文件大小及位置：
SQL select f.file#,
2 t.name tablespace,
3 f.name,
4 trunc(f.bytes / 1048576, 2) size_mb,
5 to_char(f.creation_time, yyyy-mm-dd) creation_time,
6 status
7 from v$datafile f, v$tablespace t
8 where f.ts# = t.ts#
9 and t.name= TEST
10 order by f.creation_time;

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS
———- —————————— ———————————————————————- ———- ———- ——-
8 TEST +DATA/dbrac/datafile/test.270.957699617 20 2017-10-18 ONLINE
13 TEST +DATA/dbrac/datafile/test.276.957699883 400 2017-10-18 ONLINE

SQL select s.file_name,
2 s.tablespace_name,
3 s.bytes / 1024 / 1024 size_m,
4 s.ONLINE_STATUS,
5 d.status,
6 d.contents
7 from dba_data_files s, dba_tablespaces d
8 where s.TABLESPACE_NAME = d.TABLESPACE_NAME and s.tablespace_name= TEST

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
———————————————————— —————————— ———- ——- ——— ———
+DATA/dbrac/datafile/test.270.957699617 TEST 20 ONLINE ONLINE PERMANENT
+DATA/dbrac/datafile/test.276.957699883 TEST 400 ONLINE ONLINE PERMANENT

SQL

– 使用 rman 备份表空间 test，模式是映像副本 copy:
sql alter tablespace test offline
backup as copy datafile 8 format +fra
backup as copy datafile 13 format +fra
switch datafile 8 to copy;
switch datafile 13 to copy;
–recover datafile 8;
–recover datafile 13;
sql alter tablespace test online

注意：recover 可以省略，因为 offline 如下
OFFLINE NORMAL Specify NORMAL to flush all blocks in all data files in the tablespace out of the system global area (SGA). You need not perform media recovery on this tablespace before bringing it back online. This is the default.

操作记录省略 ………

执行后查询：
SQL l
1 select f.file#,
2 t.name tablespace,
3 f.name,
4 trunc(f.bytes / 1048576, 2) size_mb,
5 to_char(f.creation_time, yyyy-mm-dd) creation_time,
6 status
7 from v$datafile f, v$tablespace t
8 where f.ts# = t.ts#
9 and t.name= TEST
10* order by f.creation_time
SQL /

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS
———- —————————— ———————————————————— ———- ———- ——-
8 TEST +FRA/dbrac/datafile/test.264.957814459 20 2017-10-18 ONLINE
13 TEST +FRA/dbrac/datafile/test.362.957812657 400 2017-10-18 ONLINE

SQL

再次执行将 test 表空间文件由 FRA 磁盘组迁移到 DATA 磁盘组上：
sql alter tablespace test offline
backup as copy datafile 8 format +DATA
backup as copy datafile 13 format +DATA
switch datafile 8 to copy;
switch datafile 13 to copy;
sql alter tablespace test online

操作如下：
RMAN sql alter tablespace test offline

sql statement: alter tablespace test offline

RMAN backup as copy datafile 8 format +DATA

Starting backup at 2017-10-19 19:49:23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+FRA/dbrac/datafile/test.264.957814459
output file name=+DATA/dbrac/datafile/test.278.957815365 tag=TAG20171019T194923 RECID=8 STAMP=957815367
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2017-10-19 19:49:31

RMAN backup as copy datafile 13 format +DATA

Starting backup at 2017-10-19 19:49:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=+FRA/dbrac/datafile/test.362.957812657
output file name=+DATA/dbrac/datafile/test.277.957815379 tag=TAG20171019T194937 RECID=9 STAMP=957815430
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
Finished backup at 2017-10-19 19:50:33

RMAN switch datafile 8 to copy;

datafile 8 switched to datafile copy +DATA/dbrac/datafile/test.278.957815365

RMAN switch datafile 13 to copy;

datafile 13 switched to datafile copy +DATA/dbrac/datafile/test.277.957815379

RMAN sql alter tablespace test online

sql statement: alter tablespace test online

RMAN

最后验证：
SQL l
1 select s.file_name,
2 s.tablespace_name,
3 s.bytes / 1024 / 1024 size_m,
4 s.ONLINE_STATUS,
5 d.status,
6 d.contents
7 from dba_data_files s, dba_tablespaces d
8* where s.TABLESPACE_NAME = d.TABLESPACE_NAME and s.tablespace_name= TEST
SQL /

FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
———————————————————— —————————— ———- ——- ——— ———
+DATA/dbrac/datafile/test.278.957815365 TEST 20 ONLINE ONLINE PERMANENT
+DATA/dbrac/datafile/test.277.957815379 TEST 400 ONLINE ONLINE PERMANENT

SQL
1 select f.file#,
2 t.name tablespace,
3 f.name,
4 trunc(f.bytes / 1048576, 2) size_mb,
5 to_char(f.creation_time, yyyy-mm-dd) creation_time,
6 status
7 from v$datafile f, v$tablespace t
8 where f.ts# = t.ts#
9 and t.name= TEST
10 order by f.creation_time;

FILE# TABLESPACE NAME SIZE_MB CREATION_T STATUS
———- —————————— ———————————————————— ———- ———- ——-
8 TEST +DATA/dbrac/datafile/test.278.957815365 20 2017-10-18 ONLINE
13 TEST +DATA/dbrac/datafile/test.277.957815379 400 2017-10-18 ONLINE

SQL select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,REDUNDANCY,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 1,2;

GROUP_NUMBER DISK_NUMBER MOUNT_STATUS HEADER_STA MODE_STATUS STATE REDUNDANCY TOTAL_MB FREE_MB NAME FAILGROUP PATH
———— ———– ————– ———- ————– ———- ————– ———- ———- ————— ————— ——————–
1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11023 DATA_0000 DATA_0000 /dev/asm_data1
1 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 15360 11023 DATA_0001 DATA_0001 /dev/asm_data2
2 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2967 FRA_0000 FRA_0000 /dev/asm_fra1
2 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 5120 2967 FRA_0001 FRA_0001 /dev/asm_fra2
3 0 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1740 OCRVOTE_0000 OCRVOTE_0000 /dev/asm_ocrvote1
3 1 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0001 OCRVOTE_0001 /dev/asm_ocrvote2
3 2 CACHED MEMBER ONLINE NORMAL UNKNOWN 2048 1739 OCRVOTE_0002 OCRVOTE_0002 /dev/asm_ocrvote3

7 rows selected.

SQL select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS,VOTING_FILES from v$asm_diskgroup;

GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS VO
———— ————— ———- ———— ———- ———- ———————– ————– ————- —
1 DATA MOUNTED NORMAL 30720 22046 0 11023 0 N
2 FRA MOUNTED NORMAL 10240 5934 0 2967 0 N
3 OCRVOTE MOUNTED NORMAL 6144 5218 2048 1585 0 Y

[grid@dbrac1 ~]$ asmcmd lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 30720 22046 0 11023 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 10240 5934 0 2967 0 N FRA/
MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCRVOTE/
[grid@dbrac1 ~]$

感谢各位的阅读！关于“RMAN 中 switch 命令怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

正文完