DB2 HADR 配置 centos 7配置 DB2 HADR 版本 11.1，【亲测可用】全网最细

DB2 HADR 配置 centos 7配置 DB2 HADR 版本 11.1，【亲测可用】全网最细的男人

操作系统 linux centos7

DB2版本 11.1

主库 192.168.46.70

备库 192.168.46.71

参考文章：一步一个脚印 DB2 10.5 HADR 主备库配置

前置条件

• 机器之间时间必须同步，生产环境配置时钟同步服务；
• 建议操作系统版本、DB2数据库版本等保持一致；
• 建议db2数据库用户标识等保持一致；
  关于如何安装db2,请参考我的上一篇文章 centos7安装db2 version11.1

主备均配置

安装完整依赖，不然HADR会配置失败

yum install -y bc binutils* compat-libcap1* compat-libstdc* elfutils-libelf* elfutils-libelf-devel* fontconfig-devel* glibc* glibc-devel* ksh libaio* libaio-devel* libX11* libXau* libXi* libXtst* libXrender* libXrender-devel* libgcc* libstdc++* libstdc++-devel* libxcb* make net-tools nfs-utils smartmontools sysstat unixODBC gcc gcc-c++ python python-configshell python-rtslib python-six targetcli ntp

yum install -y libstdc++.i686

yum install -y pam.i686
1
2
3
4
5

配置/etc/hosts，主备均配置

192.168.46.70 db2_node1 apple
192.168.46.71 db2_node2 pear
1
2

配置 /etc/services 主备均配置

vim /etc/services
1

db2_hadr1  60001/tcp
db2_hadr2  60002/tcp
1
2

db2网络的配置

su - db2inst1
db2set DB2COMM=TCPIP
db2set -all
1
2
3

主库配置

创建目录

root 用户下面执行

mkdir -p /db2/db2imn/data/dogdb
mkdir -p /db2/db2imn/arch
mkdir  /db2/db2imn/data/dogdb/tbs
chown db2inst1:db2iadm1  -R /db2
1
2
3
4

准备测试表

db2inst1用户执行

##创建数据库
db2 "create db dogdb on /db2/db2imn/data/dogdb using codeset utf8 territory CN"
db2 connect to dogdb

##创建缓冲区
db2 "create bufferpool TB_BP_8k size 4000 pagesize 8k"
db2 "create bufferpool IDX_BP_8k size 4000 pagesize 8k"

创建表空间
db2 "create regular tablespace TB_TS pagesize 8k managed by database using(file '/db2/db2imn/data/dogdb/tbs/TB_TS' 10g) bufferpool TB_BP_8k"  
db2 "create regular tablespace IDX_TS pagesize 8k managed by database using(file '/db2/db2imn/data/dogdb/tbs/IDX_TS' 4g) bufferpool IDX_BP_8k"  

创建表
db2 connect to dogdb

db2 'create table t1(id integer,name varchar(10),sex varchar(10))  IN "TB_TS" INDEX IN "IDX_TS"'
db2 'create table t2(id integer,name varchar(10),sex varchar(10))  IN "TB_TS" INDEX IN "IDX_TS"'

db2 "insert into t1 values(1,'aaaa','bbbbb')"
db2 "insert into t2 values(1,'aaaa','bbbbb')"

db2 'select * from t1'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

配置归档

db2inst1用户执行

db2 update db cfg for dogdb using logarchmeth1 disk:/db2/db2imn/arch
1

备份数据库

root下面执行

mkdir -p /home/db2imn/db2_backup
chown db2inst1:db2iadm1  -R /home/db2imn/db2_backup
1
2

db2inst1用户执行

db2 force applications all
db2 backup database dogdb to "/home/db2imn/db2_backup"
1
2

重启实例

db2inst1用户执行

db2stop
db2start
1
2

Note：配置归档参数需要重启生效。

手动归档

db2inst1用户执行

db2 archive log for db dogdb
1

配置备库只读

db2inst1用户执行

db2set DB2_HADR_ROS=ON
db2set DB2_STANDBY_ISO=UR
1
2

Note：作为备库只读，需要配置以上参数。

配置HADR同步参数

HADR_LOCAL_* 为当前机器参数
HADR_REMOT_* 为对方的参数（主或者备）
HADR_REMOTE_INST 为示例名

db2 update db cfg for dogdb using HADR_LOCAL_HOST db2_node1

db2 update db cfg for dogdb using HADR_LOCAL_SVC db2_hadr1

db2 update db cfg for dogdb using HADR_REMOTE_HOST db2_node2

db2 update db cfg for dogdb using HADR_REMOTE_SVC db2_hadr2

db2 update db cfg for dogdb using HADR_REMOTE_INST db2inst1

db2 update db cfg for dogdb using HADR_SYNCMODE NEARSYNC

db2 update db cfg for dogdb using HADR_TIMEOUT 120

db2 update db cfg for dogdb using LOGINDEXBUILD ON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

拷贝备份集

db2inst1用户执行

scp /home/db2imn/db2_backup/DOGDB.0.db2inst1.DBPART000.20231013052844.001 db2inst1@pear:/home/db2imn/db2_backup
1

备库设置

创建目录

root下面执行

mkdir -p /db2/db2imn/data/dogdb
mkdir -p /db2/db2imn/arch
mkdir  /db2/db2imn/data/dogdb/tbs
chown db2inst1:db2iadm1  -R /db2
1
2
3
4

恢复数据库

db2inst1用户执行

db2 restore database dogdb  from "/home/db2imn/db2_backup" 
1

配置备库只读

db2inst1用户执行

db2set DB2_HADR_ROS=ON
db2set DB2_STANDBY_ISO=UR
1
2

查看备库的状态

db2inst1用户执行

db2 get db cfg for dogdb | grep Rollforward
db2 rollforward db dogdb query status
1
2

[db2inst1@pear ~]$ db2 get db cfg for dogdb | grep Rollforward
Rollforward pending = DATABASE
[db2inst1@pear ~]$ db2 rollforward db dogdb query status

                             Rollforward Status
1

Input database alias = dogdb
Number of members have returned status = 1

Member ID = 0
Rollforward status = DB pending
Next log file to be read = S0000000.LOG
Log files processed = -
Last committed transaction = 2023-10-13-09.28.49.000000 UTC

Note：开启同步前，备库的状态要为pending

配置HADR同步参数

db2inst1用户执行

db2 get db cfg for dogdb | grep -i HADR
1

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) =
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
HADR SSL certificate label (HADR_SSL_LABEL) =

db2 update db cfg for dogdb using HADR_LOCAL_HOST db2_node2

db2 update db cfg for dogdb using HADR_LOCAL_SVC  db2_hadr2

db2 update db cfg for dogdb using HADR_REMOTE_HOST db2_node1

db2 update db cfg for dogdb using HADR_REMOTE_SVC  db2_hadr1

db2 update db cfg for dogdb using HADR_REMOTE_INST db2inst1

db2 update db cfg for dogdb using HADR_SYNCMODE NEARSYNC

db2 update db cfg for dogdb using HADR_TIMEOUT 120

db2 update db cfg for dogdb using LOGINDEXBUILD ON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Note：注意填写主备库的同步节点信息

HADR 启动

db2inst1用户执行

先启动备库

db2 start hadr on database dogdb as standby
1

主库启动

db2 start hadr on database dogdb as primary
1

查看HADR状态

db2pd -db dogdb -hadr 
1

HADR启停顺序

主：
db2 stop hadr on db dogdb

备：
db2 deactivate db dogdb
db2 stop hadr on db dogdb


备：
db2 start hadr on db dogdb as standby

主：
db2 start hadr on db dogdb as primary
1
2
3
4
5
6
7
8
9
10
11
12
13

主备切换

正常接管

db2 takeover hadr on database dogdb 
1

Note：在备库运行，将主库接管过来

故障切换

强制接管

db2 takeover hadr on database dogdb by force
1

Note：故障切换其实就是强制接管主库，接管后会把hadr进程杀掉