数据库迁移之主备切换及主备ip替换方案

  背 景  

原数据库主机维保到期,需要做数据库迁移,制定的最终迁移方案是新搭建一套rac成为原库的ADG备库,割接当晚做主备切换,为了让业务侧改动最小化,切换完以后做主备IP替换操作,按照生产环境db_unique_name添加注册service_names,确保业务侧几乎零改动。 变更过程 

一、主备切换

1、确保adg节点只启动一个节点

检查两节点启动情况srvctl status instance -d TESTDB -i TESTDB1,TESTDB2停止二节点实例srvctl stop instance -d TESTDB -i TESTDB2

2、主库切换

(1)主库查询:SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS——————–SESSIONS ACTIVE(2)TO STANDBY或者SESSIONS ACTIVE状态下,主库可以切换成备库角色ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;srvctl stop database -d TESTDBsrvctl start database -d TESTDB -o mount

3、查询switchover目标RAC备库

(1)TO PRIMARY或者SESSIONS ACTIVE 状态说明备库已经准备好切换成主库角色SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS——————–TO PRIMARY切换目标RAC物理备库成为主库ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;节点一执行:查看数据库状态是否是mountselect open_mode from v$database;ALTER DATABASE OPEN;节点二执行:查看数据库状态是否是mountselect open_mode from v$database;ALTER DATABASE OPEN;

4、新备库一节点启动日志应用

(1)启动日志应用被激活备库:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;(2)启动新备库到openalter database recover managed standby database cancel;alter database open;alter database recover managed standby database using current logfile disconnect from session;

5、检查备库日志应用

(1)alter日志检查:Tail -100f $ORACLE_BASE/rdbms/TEST/TESTDB1/alert/log.xml(2) 查看主备应用状态set linesize 200column name format a22column value format a16column unit format a28column time_computed format a25select * from v$dataguard_stats;

二、主备数据库IP替换

基础环境

主库备库
IP地址规划cat /etc/hosts192.168.0.21 testdb1 testdb1.com192.168.0.23 testdb1-vip192.168.0.22 testdb2 testdb2.com192.168.0.24 testdb2-vip192.168.0.25 testdb-cluster testdb-cluster-scan192.168.0.121 testdb1-priv192.168.0.122 testdb2-priv192.168.0.245 testdb1 testdb1.com192.168.0.247 testdb1-vip192.168.0.246 testdb2 testdb2.com192.168.0.248 testdb2-vip192.168.0.251 testdb-cluster testdb-cluster-scan192.168.99.121 testdb1-priv192.168.99.122 testdb2-priv
替换后IP地址规划192.168.0.245 testdb1 testdb1.com192.168.0.247 testdb1-vip192.168.0.246 testdb2 testdb2.com192.168.0.248 testdb2-vip192.168.0.251 testdb-cluster testdb-cluster-scan192.168.0.121 testdb1-priv192.168.0.122 testdb2-priv192.168.0.21 testdb1 testdb1.com192.168.0.23 testdb1-vip192.168.0.22 testdb2 testdb2.com192.168.0.24 testdb2-vip192.168.0.25 testdb-cluster testdb-cluster-scan192.168.99.121 testdb1-priv192.168.99.122 testdb2-priv
db_nameTESTDBTESTDB
db_unique_nameTESTDBTESTDB _NEW
Instance_nameTESTDB1TESTDB2TESTDB1TESTDB2

1、基础环境准备

主备环境处理私有IP不替换意外,公网ip,虚ip,scan_ip都需要替换1:提前准备好需要替换的hosts->hosts_bak(替换后的新hosts 信息)2:提前准备好tnsnames.ora->qiehuan_tnsnames.ora(ip互换以后新的tnsnames信息)3:提前申请两个IP4:由于私网IP不做替换,所以没有停集群的必要

2、备库停止日志应用

停止日志应用:alter database recover managed standby database cancel;

3、原rac数据库停止数据库和集群相关服务

(1)root下执行cd $GRID_HOME/bin./srvctl stop database -d testdb./srvctl disable database -d testdb(2) 禁用和停止listener:./srvctl disable listener./srvctl stop listener(3) 禁用和停止vip./srvctl disable vip -i “test1-vip”./srvctl disable vip -i “test2-vip”./srvctl stop vip -n test1./srvctl stop vip -n test2(4) 禁用和停止scan及scan_listener./srvctl disable scan_listener./srvctl stop scan_listener./srvctl disable scan./srvctl stop scan(5)停crs 两节点执行(因为此种变更肯定有停机窗口,建议停止集群)./crsctl stop crs

4、原数据库修改IP重启整个网络

(1)修改网卡ip成备用ip#192.168.0.232 《=》 21   #192.168.0.233 《=》22192.168.0.21:vi /etc/sysconfig/network-scripts/ifcfg-bond0   IPADDR=192.168.0.21->IPADDR=192.168.0.232192.168.0.22:vi /etc/sysconfig/network-scripts/ifcfg-bond0   IPADDR=192.168.0.22->IPADDR=192.168.0.233(2):重启网卡重启整个网络:192.168.0.21 192.168.0.22 两节点执行nohup /etc/init.d/network restart &

5、新rac节点开始IP替换

(1)停止备库两节点实例并禁止自启动cd $GRID_HOME/bin./srvctl stop database -d testdb./srvctl disable database -d testdb(2)禁用和停止listener./srvctl disable listener./srvctl stop listener(3)禁用和停止vip./srvctl disable vip -i “test1-vip”./srvctl disable vip -i “test2-vip”./srvctl stop vip -n test1./srvctl stop vip -n test2(4)禁用和停止scan及scan_listener./srvctl disable scan_listener./srvctl stop scan_listener./srvctl disable scan./srvctl stop scan(5)在所有节点停止CRS服务./crsctl stop crs(6) 修改网卡ip重启整个网络192.168.0.245:vi /etc/sysconfig/network-scripts/ifcfg-bond0   IPADDR=192.168.0.245 -> IPADDR=192.168.0.21192.168.0.246:vi /etc/sysconfig/network-scripts/ifcfg-bond0   IPADDR=192.168.0.246 -> IPADDR=192.168.0.22nohup /etc/init.d/network restart &重启网络后登陆ip将不再是 245,246 ,需要登陆新的ip 245-21   246-223.8 修改/etc/hosts登陆新的两节点:21 22 (可以通过test1-priv网判断192.168.99.121,192.168.99.122为待修改主库ip)cd /etc/      cp hosts hosts_bak     mv zzj_hosts hosts

6、新主库替换IP

(1)重启crs服务(同网段IP修改,public是设置网段,所以不需要处理,只需要处理VIP,scanIP)#两节点执行:cd /g01/11ggrid/app/11.2.0/grid/bin./crsctl start crs#启动时可以查看启动进度,一般五分钟可以启动完#ps -ef | grep d.bin(2)查看并重新配置vip./srvctl config vip -n test1./srvctl config vip -n test2./srvctl modify nodeapps -A 192.168.0.23/255.255.255.0/bond0 -n test1./srvctl modify nodeapps -A 192.168.0.24/255.255.255.0/bond0 -n test2./srvctl config vip -n test1./srvctl config vip -n test2(3)查看并重新配置SCAN./srvctl config scan./srvctl modify scan -n test-cluster-scan./srvctl config scan(4)配置完成,启动相关的服务和resource./srvctl enable listener./srvctl enable vip -i “test1-vip”./srvctl enable vip -i “test2-vip”./srvctl enable scan_listener./srvctl enable scan./srvctl enable database -d testdb./srvctl start listener./srvctl start vip -n test1./srvctl start vip -n test2./srvctl start scan_listener./srvctl start scan./srvctl start database -d testdb(5) 检查集群状态./crsctl status res -t

7、替换老racIP信息

1)修改ifcfg-bond0重启网络#192.168.0.232vi /etc/sysconfig/network-scripts/ifcfg-bond0   IPADDR=192.168.0.232->IPADDR=192.168.0.245#192.168.0.233vi /etc/sysconfig/network-scripts/ifcfg-bond0   IPADDR=192.168.0.233->IPADDR=192.168.0.246nohup /etc/init.d/network restart &
2) 重新登陆192.168.0.245,192.168.0.246(通过私有ip 192.168.0.121,192.168.0.122判断为原来的21,22)cd /etc/      cp hosts hosts_bak     mv zzj_hosts hosts3)重启crs服务两节点root操作cd#两节点执行:cd $GRID_HOME/bin./crsctl start crs#启动时可以查看启动进度,一般五分钟可以启动完#ps -ef | grep d.bin4) 通过oifcfg 工具修改ip#在不调整ip网段的情况下此步骤可忽略查看配置信息./oifcfg iflist./oifcfg iflist -p./oifcfg iflist -p -n./oifcfg getif -global删除原来的网卡ip 配置信息./oifcfg delif -global bond0./oifcfg getif./oifcfg getif -global重新配置网卡ip信息./oifcfg setif -global bond0/192.168.0.0:public./oifcfg getif./oifcfg iflist./oifcfg iflist -p -n5)查看并重新配置vip./srvctl config vip -n test1./srvctl config vip -n test2./srvctl modify nodeapps -A 192.168.0.247/255.255.255.0/bond0 -n test1./srvctl modify nodeapps -A 192.168.0.248/255.255.255.0/bond0 –n test2./srvctl config vip -n test1./srvctl config vip -n test26)查看并重新配置SCAN./srvctl config scan./srvctl modify scan -n test-cluster-scan./srvctl config scan7)配置完成,启动相关的服务和resource./srvctl enable listener./srvctl enable vip -i “test1-vip”./srvctl enable vip -i “test2-vip”./srvctl enable scan_listener./srvctl enable scan./srvctl enable database -d testdb./srvctl start listener./srvctl start vip -n test1./srvctl start vip -n test2./srvctl start scan_listener./srvctl start scan./srvctl start database -d testdb8)检查集群状态./crsctl status res -t

8、替换tnsnames.ora

操作主机:192.168.0.21,22,192.168.0.245,246,另一个备库目前就没有tnsnames,此次切换先不替换su – oracle  (四台主机上执行)cd $ORACLE_HOME/network/admincp tnsnames.ora tnsnames.ora20210122mv qiehuan_tnsnames.ora tnsnames.ora

9、启动主备同步修改服务名

alter database recover managed standby database using current logfile disconnect from session;新增服务名:alter system set db_unique_name=’testdb,testdb_new’;手工注册:Alter system register;

问题总结

一:切换后备库一直无法应用日志

1:监听正常

2:归档地址指定的tns正常

3:LOG_ARCHIVE_DEST_STATE_n是enable状态

4:log_archive_configDG参数设置没问题

5:核实主库alter日志,发现有连接报错,经核实主库传输日志会多次尝试目的地连通性问题,多次尝试不通以后会停止日志传输,此时只需要重新设置一些dg参数,即可再次激活日志传输。

解决方案:altersystem set LOG_ARCHIVE_DEST_STATE_3=defer;

altersystem set LOG_ARCHIVE_DEST_STATE_3=enable;

重新设置一下即可激活日志传输,问题解决。

二:监听服务名问题

lsnrctl status:默认注册的服务名是db_unique_name,而ADG搭建db_unique_name必须是唯一的,所以在做主备切换和IP替换以后需要在新的数据库实例中多注册一个服务名,这样才能做到业务侧零修改。数据库迁移之主备切换及主备ip替换方案

来源:IT那活儿,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/124072

发表评论

登录后才能评论
侵权联系
返回顶部