一、迁移环境
–源数据库:
操作系统: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