526互联

db2高可用基础-主从hadr实践手册

发布时间 2023-04-18 16:11:51作者: 啊内哈赛哟

db2高可用基础-主从hadr实践手册（centos8）

环境说明：
服务器地址：primary 192.168.247.128
secondary 192.168.247.168
数据库版本：db2高级企业版V10.5+
环境需求：主备两侧关闭系统防火墙,信任网络互通，scp、ssh可正常跳转
查看状态
systemctl status firewalld.service
关闭防火墙
systemctl stop firewalld.service
禁用防火墙
systemctl disable firewalld.service
数据库实例：在primary,secondary上分别创建 db2inst1 （可以创建在不同实例名上，建议配置一致）
数据库：在primary上创建 sample
端口号：DB2_db2inst1_HADR 60006/tcp（primary，secondary中/etc/services加入端口说明端口，端口号可以不一致）
img1.修改主节点sample数据库参数如下(primary节点：db2inst1用户)
db2 update db cfg for sample using LOGARCHMETH1 disk:/home/db2inst1/db2log/sample #确认开启数据库归档
db2 UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST 192.168.247.128 #设置HADR本地IP
db2 UPDATE DB CFG FOR sample USING HADR_LOCAL_SVC 60006 #设置HADR本地端口号
db2 UPDATE DB CFG FOR sample USING HADR_REMOTE_HOST 192.168.247.168 #设置HADR远程IP
db2 UPDATE DB CFG FOR sample USING HADR_REMOTE_SVC 60006 #设置HADR远程端口号
db2 UPDATE DB CFG FOR sample USING HADR_REMOTE_INST db2inst1 #HADR远程实例名
db2 UPDATE DB CFG FOR sample USING HADR_SYNCMODE NEARSYNC #HADR模式
db2 UPDATE DB CFG FOR sample USING HADR_PEER_WINDOW 300
db2 UPDATE DB CFG FOR sample USING HADR_TIMEOUT 120
db2 UPDATE DB CFG FOR sample USING INDEXREC RESTART LOGINDEXBUILD ON LOGFILSIZ 40962.创建归档日志文件夹，备份文件夹(primary节点：db2inst1用户)
mkdir -r /home/db2inst1/db2log/sample
mkdir -r /home/db2inst1/db2bak3.重启数据库库使参数生效(primary节点：db2inst1用户)
关闭sample数据库
db2 connect to sample
db2 quiesce db immediate force connections
db2 terminate
db2 deactivate database sample
img确认活动数据库列表里sample数据库已关闭
db2 list active databases
img激活数据库
db2 activate database sample
或者db2 connect to sample注意：如出现SQL0752N Connecting to a database is not permitted within a logical unit of work when the CONNECT type 1 setting is in use.
img
db2 connect reset #终止数据库连接,包含一个commit的动作
reset后再连接数据库注意：如出现SQL1116N错误，需要冷备份sample后，才能激活数据库
img压缩备份sample至备份文件夹
db2 backup db sample to /home/db2inst1/db2bak compress
img重启数据库
img检查设置参数是否生效
db2 get db cfg for sample show detail |grep -i hadr
img4.将全备份sample文件，传输到secondary服务器(primary节点：db2inst1用户)
scp SAMPLE.0.db2inst1.DBPART000.20210408014304.001 192.168.247.168:/home/db2inst1/db2bak
img5.恢复secondary节点sample数据库恢复数据到rollforward pending状态，不要前滚(secondary节点：db2inst1用户)
db2 restore db sample
img6.secondary节点数据库参数修改(secondary节点：db2inst1用户)
db2 update db cfg for sample using LOGARCHMETH1 disk:/home/db2inst1/db2log/sample
db2 UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST 192.168.247.168
db2 UPDATE DB CFG FOR sample USING HADR_LOCAL_SVC 60006
db2 UPDATE DB CFG FOR sample USING HADR_REMOTE_HOST 192.168.247.128
db2 UPDATE DB CFG FOR sample USING HADR_REMOTE_SVC 60006
db2 UPDATE DB CFG FOR sample USING HADR_REMOTE_INST db2inst1
db2 UPDATE DB CFG FOR sample USING HADR_SYNCMODE NEARSYNC
db2 UPDATE DB CFG FOR sample USING HADR_PEER_WINDOW 300
db2 UPDATE DB CFG FOR sample USING HADR_TIMEOUT 120
db2 UPDATE DB CFG FOR sample USING INDEXREC RESTART LOGINDEXBUILD ON LOGFILSIZ 4096
img7.primary节点HADR自动客户端更新目标地址(primary节点：db2inst1用户)
db2 UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.247.168:60006
img8.secondary节点HADR自动客户端更新目标地址(secondary节点：db2inst1用户)
db2 UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.247.128:60006
img9.首先将secondary节点切换为标准模式(secondary节点：db2inst1用户)
db2 START HADR ON DATABASE sample AS STANDBY
img10.再将primary节点切换为主节点模式(primary节点：db2inst1用户)
关闭sample数据库
db2 connect to sample
db2 quiesce db immediate force connections
db2 terminate
db2 deactivate database sample
启动primary节点HADR
db2 START HADR ON DATABASE sample AS PRIMARY
img如初始化HADR失败，SQL1768N Unable to start HADR, Reason Code 7
则可能为以下原因导致
1）网络问题
2）secondary节点数据库未处于STANDBY模式
3）两台数据库版本不一致
4）系统防火墙原因导致
5)映射实例名称错误
6)HADR_TIMEOUT超时时间设置过短11.验证主备节点状态
db2pd -db sample -hadr
primary节点：
img
secondary节点：
img12.备节点只读模式
测试数据录入(primary节点：db2inst1用户)
create table XXZX(ID INT)
img
insert into XXZX values (1),(2)
img
img

连接secondary节点(secondary节点：db2inst1用户)
报错SQL1776N The command cannot be issued on an HADR database. Reason code = "1".
img
将 DB2_HADR_ROS 注册表变量设置为 ON，启用“在备用数据库上读取”(主备节点，同时设置)
db2set DB2_HADR_ROS=ON
img
重启主备实例，重启HADR使参数生效（先主后备命令相同）
db2 deactivate database sample
db2 stop hadr on db sample
db2stop
db2start
重复步骤9，10
img
img

sql查询报错
SQL1773N The statement or command failed because it requires functionality
that is not supported on a read-enabled HADR standby database. Reason code =
"1".
img
将 DB2_STANDBY_ISO注册表变量设置为 ON
db2set DB2_STANDBY_ISO=UR
当 DB2_STANDBY_ISO 设置为 UR 时，会以静默方式将隔离级别强制为 UR。此设置优先于所有其他隔离设置，如语句隔离和程序包隔离。
img
重启主备实例，重启HADR使参数生效（先主后备命令相同）
db2 deactivate database sample
db2 stop hadr on db sample
db2stop
db2start
重复步骤9，10即可正常读取对应数据
img13.主备切换
切换前检查
db2pd -d sample -hadr
备机端检查HADR_STATE = PEER，则可以正常切换。
非peer为不一致状态，切换会数据丢失。备节点：secondary节点，db2inst1用户执行
db2 takeover hadr on db sample切换后检查
db2pd -d sample -hadr
img
img备注：强制切换
db2 TAKEOVER HADR ON DB sample BY FORCE
primary故障时，可以将standby向primary进行强制切换
primary侧则没有发生任何处理
一定要在primary为down的状态下执行14.开启和关闭HADR同步
开启顺序先启动备机端，再主机端。
备机端：db2 start hadr on db sample as standby
主机端：db2 start hadr on db sample as primary
关闭顺序先主机再备机。
断开所有应用连接：
主机端（可选）：
db2 connect to sample
db2 quiesce db immediate force connections
db2 terminate
主机端：db2 deactivate database sample
主机端：db2 stop hadr on database sample
备机端：db2 deactivate database sample
备机端：db2 stop hadr on database sample

原文连接地址：https://www.cnblogs.com/hongliang-dba/p/14632655.html##