Oracle 11g RAC迁移到异机RAC

一、迁移环境

–源数据库：
操作系统：CentsOS 7.2
Oracle数据库版本：11.2.0.4 RAC
ORACLE_SID：ORCL
DB_NAME: ORCL

–目标数据库：
操作系统：CentOS 7.5
Oracle数据库版本：11.2.0.4 RAC
即将创建ORACLE_SID：ORCL
即将创建DB_NAME: ORCL

二、迁移准备

1、在迁移前需要在RAC源数据库做好RMAN全备，以及归档日志备份。
2、在迁移前目标库需要安装好数据库，不创建实例。
3、手动还原方式产生的dbid与源库相同（在新RAC环境还原时需要考虑pfile中控制文件的路径）。
4、还原之后需要在srvctl注册spfile路径。

查看源数据库DBID、控制文件、数据文件和redo日志文件的存储位置：

# 查看源数据库的DBID：
SQL> select dbid from v$database;
# 查看控制文件：
SQL> select name from v$controlfile;
# 查看数据文件：
SQL> select status,name from v$datafile;
# 查看日志文件：
SQL> select * from v$logfile;

三、迁移步骤

1、源服务器做RMAN全备

# RMAN备份数据文件、归档日志文件、控制文件：
rman target /

run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup as compressed backupset database format '/u01/rman/full_%d_%U.bak';
sql 'alter system archive log current';
backup as compressed backupset archivelog all format '/u01/rman/arch_%d_%U.bak';
backup current controlfile format '/u01/rman/ctl_%d_%U.bak';
release channel c1;
release channel c2;
release channel c3;
}
report obsolete; 
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
list backup summary; 

备份参数文件:
SQL> create pfile='/u01/rman/initorcl.ora' from spfile;

2、拷贝备份到目标新环境RAC数据库服务器

在rac新环境中建立rman的备份目录"/u01/rman"
mkdir /u01/rman/
scp /u01/rman/* oracle@172.16.41.11:/u01/rman

3、在目标rac数据库服务器上创建必要的目录

3.1、在 oracle 用户下创建(所有节点执行)

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/archivelog
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/oradata/orcl
[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/backup

3.2、在 grid 用户下在共享设备创建必要的目录

[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCRVOTE/
ASMCMD> cd data
ASMCMD> mkdir ORCL
ASMCMD> cd orcl
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir ARCHIVELOG
ASMCMD> cd ../../fra
ASMCMD> mkdir ORCL
ASMCMD> cd orcl
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> quit

3.3、在目标端oracle用户下配置环境变量(节点2修改SID:orcl2)

vi ~/.bash_profile
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl1
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
umask 022

4、编辑新rac环境参数文件

在rac1上编辑pfile文件（根据实际情况调整内存大小）,通过源库拷贝过来的参数文件进行修改：
vi /u01/rman/initorcl.ora

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.281.941298367','+FRA/orcl/controlfile/current.256.941298369'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
orcl2.instance_number=2
orcl1.instance_number=1
*.log_archive_dest_1='location=+data/orcl/archivelog'
*.log_archive_format='arch_%t_%s_%r.arc'
*.open_cursors=300
*.parallel_execution_message_size=8192
*.pga_aggregate_target=268435456
*.processes=1000
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.sga_target=838860800
orcl2.thread=2
orcl1.thread=1
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'

备注：
1、pfile中控制文件需要与rman还原之后的控制文件名字相同
2、cluster_database需要设置为false

5、用源端 pfile 创建目标端数据库 spfile,并将新环境rac数据库启动到nomount

[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 13 16:36:20 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount pfile='/u01/rman/initorcl.ora';
ORACLE instance started.
Total System Global Area  914440192 bytes
Fixed Size      2258600 bytes
Variable Size    297797976 bytes
Database Buffers   608174080 bytes
Redo Buffers      6209536 bytes

5.1、在目标端用 pfile.ora 在共享设备上创建spfile：

SQL> create spfile='+DATA/orcl/spfileorcl.ora' from pfile='/u01/rman/initorcl.ora';
File created.

5.2、在所有节点上创建 pfile 内容指向共享设备上的spfile文件

节点1：
[oracle@rac1 dbs]$ echo "SPFILE='+DATA/orcl/spfileorcl.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora
[oracle@rac1 dbs]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora
SPFILE='+DATA/orcl/spfileorcl.ora'
节点2：
[oracle@rac2 ~]$ echo "SPFILE='+DATA/orcl/spfileorcl.ora' " > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora
[oracle@rac2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora
SPFILE='+DATA/orcl/spfileorcl.ora'

重启数据库：
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount

查看监听是否正常：
[oracle@rac1 ~]$ lsnrctl status

6、还原控制文件（在一个节点上执行）

数据库启动到nomount
[oracle@rac1 ~]$rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 18 18:18:26 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: orcl (not mounted)

RMAN> set DBID=2882088725;
RMAN> restore controlfile from '/u01/rman/ctl_ORCL_32tvuvos_1_1.bak'
#注：不知道哪个文件还原控制文件，可以在源数据库中查看 list backup of controlfile;
#还原控制文件之后需要修改pfile的控制文件参数
*.control_files='+DATA/orcl/controlfile/current.还原之后控制文件名称'

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

7、注册备份目录还原数据文件、以及recover

RMAN> catalog start with '/u01/rman/';
注：为简单起见，路径名称完全一样，不需要 set newname，所以直接进行还原数据文件就可以。
# 目标端rac1还原数据库
RMAN> restore database;

# 恢复数据库
RMAN> recover database;  --因缺少一部分日志而报错可以忽略

如不让报错使用以下方式，查看能最大能恢复到哪个SCN：
RMAN> list backup of archivelog all;
RMAN> recover database until scn 1112026;
# 注：Thrd1 最大能恢复到 1112026 , Thrd2 最大能恢复到 1112030，故选取 1112026 为恢复CSN，如不加SCN会因缺少一部分日志而报错。

8、将源库新增的归档日志和在线日志拷贝到目标端

8.1、源库创建测试数据，模拟数据库备份后的变化

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> alter system switch logfile;
System altered.
SQL> create table t1 (id number,name varchar2(10));
Table created.
SQL> insert into t1 values (1,'sysdate');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;  --切换日志，把日志写进硬盘
System altered.

8.2、关闭源数据库

[oracle@rac1 ~]$ srvctl stop database -d orcl -o immediate
[oracle@rac1 ~]$ srvctl status database -d orcl
Instance orcl1 is not running on node rac1
Instance orcl2 is not running on node rac2

8.3、将新增的归档日志和在线日志复制到目标端数据库

a.将源端归档日志从共享设备复制到本地
[root@rac2 Desktop]# mkdir -p /u01/rman/arch
[root@rac2 Desktop]# chown grid:oinstall /u01/rman/arch

[root@rac2 Desktop]# su - grid
[grid@rac2 ~]$ asmcmd
ASMCMD> cd +DATA/orcl/archivelog/2017_04_18
ASMCMD> pwd
+DATA/orcl/archivelog/2017_04_18
ASMCMD> ls
thread_1_seq_27.266.941652809
thread_1_seq_28.256.941652811
thread_2_seq_22.278.941654057
ASMCMD> cp thread_1_seq_27.266.941652809 /u01/rman/arch
ASMCMD> cp thread_* /u01/rman/arch
......
[grid@rac2 ~]$ ll /u01/rman/arch

b.将源端本地归档日志拷贝到目标端数据库本地
[grid@rac2 tmparch]$ scp -r /u01/rman/arch/* oracle@192.168.10.165:/u01/app/oracle/archivelog

8.4、对目标数据库再次进行恢复

重新注册归档文件：
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_1_seq_27.266.941652809';
RMAN> catalog archivelog '/u01/app/oracle/archivelog/thread_1_seq_28.256.941652811';
......
# 或者，注册目录
# RMAN> catalog start with '/u01/app/arch/';

恢复数据库：
RMAN> recover database;

9、创建口令文件

节点1：
[oracle@rac1 dbs]$ orapwd file=?/dbs/orapworcl1 password=oracle
节点2：
[oracle@rac2 dbs]$ orapwd file=?/dbs/orapworcl2 password=oracle

10、打开数据库

可以read only方式打开数据库，验证数据无问题后再打开数据库，例如：
SQL> alter database open read only;
Database altered.

查询之前插入的数据是否恢复：
SQL> select * from t1;

关闭数据库，重新启动到mount状态：
SQL> shutdown immediate
SQL> startup mount

RESETLOGS打开数据库:
SQL> alter database open resetlogs;
database opened

四、迁移后参数调整

1、重建 temp表空间

1.1 因为在recover 的时候不会对temp 表空间进行recover。所以等recover 后，我们要手工重建temp表空间。

[grid@rac1 ~]$ asmcmd lsof |grep temp
orcl   orcl1        +data/orcl/tempfile/temp.277.941723763
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> alter tablespace temp add tempfile '+DATA' size 50M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
-------------------------------------------------------------------------------
+DATA/orcl/tempfile/temp.277.941723763
+DATA/orcl/tempfile/temp.270.941724325
SQL> alter database tempfile '+DATA/orcl/tempfile/temp.277.941723763' offline ;
Database altered.
SQL> alter database tempfile '+DATA/orcl/tempfile/temp.277.941723763' drop including datafiles;
alter database tempfile '+DATA/orcl/tempfile/temp01.dbf' drop including datafiles
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

1.2 重启数据库再删除原temp表空间

SQL> shutdown immediate
SQL> startup        
SQL> alter database tempfile '+DATA/orcl/tempfile/temp.277.941723763' drop including datafiles;
Database altered.

2、整理在线日志文件

SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         2 +DATA/orcl/onlinelog/group_2
         2 +DATA/orcl/onlinelog/group_2.278.941723755
         1 +DATA/orcl/onlinelog/group_1
         1 +DATA/orcl/onlinelog/group_1.281.941723753
         3 +DATA/orcl/onlinelog/group_3
         3 +DATA/orcl/onlinelog/group_3.272.941723757
         4 +DATA/orcl/onlinelog/group_4
         4 +DATA/orcl/onlinelog/group_4.271.941723759
         1 +FRA/orcl/onlinelog/group_1.258.941723753
         2 +FRA/orcl/onlinelog/group_2.259.941723755
         3 +FRA/orcl/onlinelog/group_3.260.941723757
         4 +FRA/orcl/onlinelog/group_4.263.941723759
12 rows selected.

SQL> select GROUP#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED
         4 UNUSED
SQL> alter database drop logfile member '+DATA/orcl/onlinelog/group_2';
Database altered.
SQL> alter database drop logfile member '+DATA/orcl/onlinelog/group_3';
Database altered.
SQL> alter database drop logfile member '+DATA/orcl/onlinelog/group_4';
Database altered.
SQL> alter database drop logfile member '+DATA/orcl/onlinelog/group_1';
alter database drop logfile member '+DATA/orcl/onlinelog/group_1'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '+DATA/orcl/onlinelog/group_1'
ORA-00312: online log 1 thread 1: '+DATA/orcl/onlinelog/group_1.281.941723753'
ORA-00312: online log 1 thread 1: '+FRA/orcl/onlinelog/group_1.258.941723753'
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 UNUSED
         4 UNUSED
SQL> alter system checkpoint;
System altered.
SQL> select GROUP#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 UNUSED
         4 UNUSED
SQL> alter database drop logfile member '+DATA/orcl/onlinelog/group_1';
Database altered.
SQL> shutdown immediate

3、将其他的信息注册到CRS里

[oracle@rac1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME -p +DATA/orcl/spfileorcl.ora
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl1 -n dbrac1
[oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl2 -n dbrac2
[oracle@rac1 ~]$ srvctl start database -d orcl -o open

4、配置集群监听

4.1 配置tnsnames.ora (所有节点执行)

[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rac1 admin]$ vi tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbscan-ip.orcl.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.165)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.197)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl1)
    )
  )

4.2 重启监听

[oracle@rac1 admin]$ srvctl stop listener
[oracle@rac1 admin]$ srvctl start listener
[oracle@rac1 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac1,rac2

4.3 测试远程访问，用源端连接目标端

配置tnsnames.ora
[oracle@rac1 ~]$ su - oracle
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@rac1 admin]$ vi tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.199 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
[oracle@rac1 admin]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 19 14:39:22 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
SQL>

五、验证数据库及集群情况

1、查看数据库启动情况

[oracle@rac1 admin]$ srvctl status database -d orcl
Instance orcl1 is running on node rac1
Instance orcl2 is running on node rac2

2、查看数据是否全部同步

[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 19 14:45:29 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
SQL> select * from t1;
        ID NAME
---------- ----------
         1 2017-04-19 14:45:50

3、查看集群情况

[oracle@rac1 ~]$ srvctl config database -d

[root@rac1 ~]# crsctl stat res -t