目地:Db2 10.5 搭建一个HADR的环境,db2a作为主机,db2b作为备机。数据库名SAMPLE,实例名inst105
详细步骤:
hostname: db2a
IP: 192.168.127.130
servicename:60013 //注意, 这个不能是dbm cfg中的svcename或者svcename+1
Standby:
hostname: db2b
IP: 192.168.127.128
servicename:60013
inst105@db2a:~$ db2 backup db sample
inst105@db2a:~$ scp SAMPLE.0.inst105.DBPART000.20180324211339.001 192.168.127.128:/home/inst105
备机:
inst105@db2b:~$ db2 "drop db sample"
inst105@db2b:~$ db2 restore db sample
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.127.130"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC 60013"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE SYNC"
备机:
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.127.128"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC 60013"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE SYNC"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.127.128:60013"
备机:
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.127.130:60013"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.127.128"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC 60013"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST inst105"
备机:
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.127.130"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC 60013"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST inst105"
inst105@db2a:~$ db2pd -db sample -hadr
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:00:35 -- Date 2018-03-24-22.08.23.279879
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS =
PRIMARY_MEMBER_HOST = 192.168.127.130
PRIMARY_INSTANCE = inst105
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.127.128
STANDBY_INSTANCE = inst105
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
...
备机:
inst105@db2b:~$ db2pd -db sample -hadr
Database Member 0 -- Database SAMPLE -- Standby -- Up 0 days 00:01:03 -- Date 2018-03-24-22.08.38.570978
HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 0
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS =
PRIMARY_MEMBER_HOST = 192.168.127.130
PRIMARY_INSTANCE = inst105
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.127.128
STANDBY_INSTANCE = inst105
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
...
参考资料:
Initializing high availability disaster recovery (HADR)
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/t0011725.html
详细步骤:
1. 查看主备机的hostname、IP地址,决定一个service name
Primary:hostname: db2a
IP: 192.168.127.130
servicename:60013 //注意, 这个不能是dbm cfg中的svcename或者svcename+1
Standby:
hostname: db2b
IP: 192.168.127.128
servicename:60013
2. 主机配置LOGINDEXBUILD和LOGARCHMETH1
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING LOGINDEXBUILD ON LOGARCHMETH1 logretain"3. 主机备份数据库、并恢复到备机
主机:inst105@db2a:~$ db2 backup db sample
inst105@db2a:~$ scp SAMPLE.0.inst105.DBPART000.20180324211339.001 192.168.127.128:/home/inst105
备机:
inst105@db2b:~$ db2 "drop db sample"
inst105@db2b:~$ db2 restore db sample
4. 配置数据库参数
4.1 主备机配置 hadr_local_host, hadr_local_svc, 和 hadr_syncmode
主机:inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.127.130"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC 60013"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE SYNC"
备机:
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST 192.168.127.128"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC 60013"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE SYNC"
4.2 主备机配置hadr_target_list
主机:inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.127.128:60013"
备机:
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_TARGET_LIST 192.168.127.130:60013"
4.3 主备机配置 hadr_remote_host, hadr_remote_svc, 和 hadr_remote_inst
主机:inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.127.128"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC 60013"
inst105@db2a:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST inst105"
备机:
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST 192.168.127.130"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC 60013"
inst105@db2b:~$ db2 "UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST inst105"
5. 备机启动HADR
inst105@db2b:~$ db2 START HADR ON DB SAMPLE AS STANDBY6. 主机启动HADR
inst105@db2a:~$ db2 START HADR ON DB SAMPLE AS PRIMARY7. 查看主备机HADR状态:
主机:inst105@db2a:~$ db2pd -db sample -hadr
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:00:35 -- Date 2018-03-24-22.08.23.279879
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS =
PRIMARY_MEMBER_HOST = 192.168.127.130
PRIMARY_INSTANCE = inst105
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.127.128
STANDBY_INSTANCE = inst105
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
...
备机:
inst105@db2b:~$ db2pd -db sample -hadr
Database Member 0 -- Database SAMPLE -- Standby -- Up 0 days 00:01:03 -- Date 2018-03-24-22.08.38.570978
HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = SYNC
STANDBY_ID = 0
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS =
PRIMARY_MEMBER_HOST = 192.168.127.130
PRIMARY_INSTANCE = inst105
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = 192.168.127.128
STANDBY_INSTANCE = inst105
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
...
参考资料:
Initializing high availability disaster recovery (HADR)
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.ha.doc/doc/t0011725.html