db2高可用基础-主从hadr实验（centos8）

环境说明：
服务器地址：primary 192.168.247.128
secondary 192.168.247.168
数据库版本：db2高级企业版V10.5+
环境需求：主备两侧关闭系统防火墙,信任网络互通，scp、ssh可正常跳转
查看状态
systemctl status firewalld.service
打开防火墙
systemctl start firewalld.service
关闭防火墙
systemctl stop firewalld.service
开启防火墙
systemctl enable firewalld.service
禁用防火墙
systemctl disable firewalld.service
数据库实例：在primary,secondary上分别创建 db2inst1 （可以创建在不同实例名上，建议配置一致）
数据库：在primary上创建 sample
端口号：DB2_db2inst1_HADR 60006/tcp（primary，secondary中/etc/services加入端口说明端口，端口号可以不一致）

1.修改主节点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 4096

2.创建归档日志文件(primary节点：db2inst1用户)
mkdir -r /home/db2inst1/db2log/sample
mkdir -r /home/db2inst1/db2bak

3.重启数据库库使参数生效(primary节点：db2inst1用户)
关闭sample数据库
db2 connect to sample
db2 quiesce db immediate force connections
db2 terminate
db2 deactivate database sample

确认活动数据库列表里sample数据库已关闭
db2 list active databases

激活数据库
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.

db2 connect reset #终止数据库连接,包含一个commit的动作
reset后再连接数据库

注意：如出现SQL1116N错误，需要冷备份sample后，才能激活数据库

压缩备份sample至
db2 backup db sample to /home/db2inst1/db2bak compress

重启数据库

检查设置参数是否生效
db2 get db cfg for sample show detail |grep -i hadr

4.将全备份sample文件，传输到secondary服务器(primary节点：db2inst1用户)
scp SAMPLE.0.db2inst1.DBPART000.20210408014304.001 192.168.247.168:/home/db2inst1/db2bak

5.恢复secondary节点sample数据库恢复数据到rollforward pending状态，不要前滚(secondary节点：db2inst1用户)
db2 restore db sample

6.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

7.primary节点HADR自动客户端更新目标地址(primary节点：db2inst1用户)
db2 UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.247.168:60006

8.secondary节点HADR自动客户端更新目标地址(secondary节点：db2inst1用户)
db2 UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.247.128:60006

9.首先将secondary节点切换为标准模式(secondary节点：db2inst1用户)
db2 START HADR ON DATABASE sample AS STANDBY

10.再将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

11.验证主备节点状态
db2pd -db sample -hadr
primary节点：

secondary节点：

12.测试环节：数据验证
create table XXZX(ID INT)

insert into XXZX values (1),(2)

db2高可用基础-主从hadr实验（centos8）

原文：https://www.cnblogs.com/hongliang-dba/p/14632655.html