RMAN switch 命令及使用back as copy 和switch datafile迁移存储数据

2023-09-23 13:02:45

SWITCH命令详解

Purpose：目的，即使用switch命令能做什么

1.将数据库，表空间或数据文件的文件名更新为指定文件可用的最新映像副本

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必须连接到目标数据库。切换表空间，数据文件或临时文件时，这些文件必须处于脱机状态。切换整个数据库时，数据库一定不能打开。

Usage Notes（使用说明）

SWITCH命令从恢复目录中删除数据文件副本的RMAN存储库记录，并将控制文件记录更新为DELETED状态。

如果RMAN连接到恢复目录，并且数据库正在使用从备份还原的控制文件，则SWITCH会更新控制文件，并记录恢复目录已知但控制文件中缺失的任何数据文件。

只在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

这个子句将数据库，表空间或数据文件的文件名切换为可用于指定文件的最新映像副本。通过执行此命令，可以避免从备份中恢复数据文件。只在RMAN提示符下执行SWITCH ... TO COPY。

switch File

本子句更新您为其发出SET NEWNAME命令的数据文件和临时文件的名称。仅在RUN块中使用此子句。

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

假设一个磁盘失败，将无法访问用户表空间中的所有数据文件。快速恢复区域中存在此表空间中所有数据文件的映像副本。启动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（在还原到新位置后切换数据文件文件名称）

假定磁盘发生故障，迫使您将数据文件恢复到新的磁盘位置。启动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;

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;

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 ~]$

其他几种asm和操作系统数据文件迁移方法参阅如下：几种ASM与File System数据文件转移方法

码农公寓

相关文章