使用RMAN进行数据迁移

使用RMAN进行数据迁移

应用场景：将单实例的oracle数据迁移到rac集群中

一、分别在两个节点上修改spfile参数

SQL> alter system set db_create_file_dest='+DATA' scope=both ;

System altered.

SQL> alter system set db_recovery_file_dest='+RECOVERY' scope=both ;

System altered.

二、把控制文件迁移到ASM

[oracle@oracle-rac01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 30 11:05:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

1、切换数据库到nomount状态
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 989856648 bytes
Database Buffers 3271557120 bytes
Redo Buffers 12107776 bytes


2、使用RMAN恢复控制文件到新的ASM磁盘位置
[oracle@oracle-rac01 kmb2b]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 31 16:14:42 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: KMB2B (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/data/kmb2b/control01.ctl' ;

Starting restore at 2017/08/31 16:15:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 instance=kmb2b1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/kmb2b/controlfile/current.256.953477171
output file name=+RECOVERY/kmb2b/controlfile/current.256.953477173
Finished restore at 2017/08/31 16:15:36

注意：使用RMAN恢复控制文件到新的ASM磁盘位置，需要将数据库设置为nomount状态。


三、将数据库文件复制到ASM磁盘，需要将磁盘状态切换到mount

1、将数据库切换到mount状态
RMAN> alter database mount ;

database mounted
released channel: ORA_DISK_1
备注：因为控制文件已经恢复，系统可以顺利读取控制文件，所以将数据库启动到MOUNT以便进行下一步数据库文件的迁移。

2、将数据库文件复制到ASM磁盘

备注：
1、backup as copy 命令是以副本的方式备份数据库，其作用和操作系统的COPY命令是一样的，只是二者的执行者不同而已。至于数据库文件复制过去的名字则无需指定，它由ASM自动生成。
2、backup as copy 命令只备份数据文件，不备份日志文件，也不备份临时文件

RMAN> backup as copy database format '+DATA' ;

Starting backup at 2017/08/31 16:19:58
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=kmb2b1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/data/kmb2b/sysaux01.dbf
output file name=+DATA/kmb2b/datafile/sysaux.275.953482799 tag=TAG20170831T161958 RECID=1 STAMP=953482816
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/data/kmb2b/system01.dbf
output file name=+DATA/kmb2b/datafile/system.276.953482823 tag=TAG20170831T161958 RECID=2 STAMP=953482832
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/data/kmb2b/kmdata01.dbf
output file name=+DATA/kmb2b/datafile/kmdata.277.953482839 tag=TAG20170831T161958 RECID=3 STAMP=953482842
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/data/kmb2b/kmdata02.dbf
output file name=+DATA/kmb2b/datafile/kmdata.278.953482845 tag=TAG20170831T161958 RECID=4 STAMP=953482849
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/data/kmb2b/kmdata03.dbf
output file name=+DATA/kmb2b/datafile/kmdata.279.953482853 tag=TAG20170831T161958 RECID=5 STAMP=953482856
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/data/kmb2b/undotbs01.dbf
output file name=+DATA/kmb2b/datafile/undotbs1.280.953482859 tag=TAG20170831T161958 RECID=6 STAMP=953482860
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/kmb2b/controlfile/backup.281.953482865 tag=TAG20170831T161958 RECID=7 STAMP=953482864
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/data/kmb2b/users01.dbf
output file name=+DATA/kmb2b/datafile/users.282.953482865 tag=TAG20170831T161958 RECID=8 STAMP=953482865
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2017/08/31 16:21:06
channel ORA_DISK_1: finished piece 1 at 2017/08/31 16:21:07
piece handle=+DATA/kmb2b/backupset/2017_08_31/nnsnf0_tag20170831t161958_0.283.953482867 tag=TAG20170831T161958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017/08/31 16:21:07

四、修改控制文件内部数据文件指针
RMAN> switch database to copy ；

datafile 1 switched to datafile copy "+DATA/kmb2b/datafile/system.276.953482823"
datafile 2 switched to datafile copy "+DATA/kmb2b/datafile/sysaux.275.953482799"
datafile 3 switched to datafile copy "+DATA/kmb2b/datafile/undotbs1.280.953482859"
datafile 4 switched to datafile copy "+DATA/kmb2b/datafile/users.282.953482865"
datafile 5 switched to datafile copy "+DATA/kmb2b/datafile/kmdata.277.953482839"
datafile 6 switched to datafile copy "+DATA/kmb2b/datafile/kmdata.278.953482845"
datafile 7 switched to datafile copy "+DATA/kmb2b/datafile/kmdata.279.953482853"

备注：
1、因为控制文件内部对数据文件的指向依然是本地文件系统的目录路径。我们需要让它的指向修改为ASM磁盘组的ASM文件。该命令的功能正是如此
2、此处不能使用alter database rename file 命令修改控制文件内部数据文件的指针。因为alter database rename file命令不能跨越系统进行操作。所不能使用其将文件系统的数据文件路径改为ASM系统的ASM文件路径

五、修复数据库文件
RMAN> recover database ；

Starting recover at 2017/08/31 16:31:17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2017/08/31 16:31:18

六、将日志文件迁移至ASM系统

1、启动数据库
RMAN> alter database open ;

2、查看当前系统日志文件的位置，如下图所示
SQL> set linesize 200
SQL> col member format a50

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/data/kmb2b/redo03.log NO
2 ONLINE /u01/app/oracle/data/kmb2b/redo02.log NO
1 ONLINE /u01/app/oracle/data/kmb2b/redo01.log NO


SQL> select * from v$log ;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 862 52428800 512 1 YES INACTIVE 18307820 2017/08/30 05:54:06 18346642 2017/08/31 16:35:16
2 1 863 52428800 512 1 NO CURRENT 18346642 2017/08/31 16:35:16 2.8147E+14
3 1 861 52428800 512 1 YES INACTIVE 18283306 2017/08/29 22:13:28 18307820 2017/08/30 05:54:06


2、查看当前系统现有的redo log状况（组数/大小/名称/状态）
SQL> desc v$log ;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE

SQL> select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log;

GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 CURRENT NO
3 50 INACTIVE YES

SQL> select group#,bytes/1024/1024 size_M,status,archived from v$log;

GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 CURRENT NO
3 50 INACTIVE YES

3、分别为两个实例在ASM磁盘组中添加日志组

SQL> alter database add logfile thread 1 group 4 size 50m ;
SQL> alter database add logfile thread 1 group 5 size 50m ;
SQL> alter database add logfile thread 1 group 6 size 50m;

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 size 50m;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 size 50m ;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 size 50m ;

SQL> Alter Database Enable Public Thread 2;


SQL> select * from v$logfile ;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/data/kmb2b/redo03.log NO
2 ONLINE /u01/app/oracle/data/kmb2b/redo02.log NO
1 ONLINE /u01/app/oracle/data/kmb2b/redo01.log NO
4 ONLINE +DATA/kmb2b/onlinelog/group_4.284.953485155 NO
4 ONLINE +RECOVERY/kmb2b/onlinelog/group_4.279.953485155 YES
5 ONLINE +DATA/kmb2b/onlinelog/group_5.285.953485187 NO
5 ONLINE +RECOVERY/kmb2b/onlinelog/group_5.280.953485189 YES
6 ONLINE +DATA/kmb2b/onlinelog/group_6.286.953485215 NO
6 ONLINE +RECOVERY/kmb2b/onlinelog/group_6.281.953485215 YES

9 rows selected.

SQL> select * from v$log ;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 877 52428800 512 1 YES INACTIVE 18348508 2017/08/31 16:44:51 18348555 2017/08/31 16:45:57
2 1 878 52428800 512 1 NO CURRENT 18348555 2017/08/31 16:45:57 2.8147E+14
3 1 876 52428800 512 1 YES INACTIVE 18348505 2017/08/31 16:44:50 18348508 2017/08/31 16:44:51
4 1 0 52428800 512 2 YES UNUSED 0 0
5 1 0 52428800 512 2 YES UNUSED 0 0
6 1 0 52428800 512 2 YES UNUSED 0 0

6 rows selected.

SQL> alter system switch logfile ; #日志切换
SQL> alter system switch logfile ; #日志切换
SQL> alter system switch logfile ; #日志切换
SQL> alter system switch logfile ; #日志切换
SQL> alter system switch logfile ; #日志切换
SQL> alter system switch logfile ; #日志切换
SQL> alter system switch logfile ; #日志切换

SQL> alter system checkpoint; #将脏数据写入数据文件，以使日志文件状态变为非活动

备注：
1、在删除原来的重做日志文件之前，要保证该日志文件不是当前正在使用的日志组，并且保证ASm磁盘组重的日志成员是有效的
2、我们使用alter system switch logfile指令切换日志组，这个过程需要执行几次，本地的日志文件状态为INACTIVE即可


SQL> select * from v$log ;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 883 52428800 512 1 YES INACTIVE 18350446 2017/08/31 17:03:12 18350449 2017/08/31 17:03:12
2 1 884 52428800 512 1 YES INACTIVE 18350449 2017/08/31 17:03:12 18350452 2017/08/31 17:03:14
3 1 882 52428800 512 1 YES INACTIVE 18350443 2017/08/31 17:03:10 18350446 2017/08/31 17:03:12
4 1 885 52428800 512 2 YES INACTIVE 18350452 2017/08/31 17:03:14 18350455 2017/08/31 17:03:14
5 1 886 52428800 512 2 NO CURRENT 18350455 2017/08/31 17:03:14 2.8147E+14
6 1 881 52428800 512 2 YES INACTIVE 18350434 2017/08/31 17:02:49 18350443 2017/08/31 17:03:10


4、删除基于系统文件的日志成员

SQL> alter database drop logfile group 1 ;
SQL> alter database drop logfile group 2 ;
SQL> alter database drop logfile group 3 ;
SQL> select * from v$Logfile ;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE +DATA/kmb2b/onlinelog/group_4.284.953485155 NO
4 ONLINE +RECOVERY/kmb2b/onlinelog/group_4.279.953485155 YES
5 ONLINE +DATA/kmb2b/onlinelog/group_5.285.953485187 NO
5 ONLINE +RECOVERY/kmb2b/onlinelog/group_5.280.953485189 YES
6 ONLINE +DATA/kmb2b/onlinelog/group_6.286.953485215 NO
6 ONLINE +RECOVERY/kmb2b/onlinelog/group_6.281.953485215 YES

6 rows selected.