【环境准备】
操作系统:centos 7.9
db2:11.1
db2数据库压缩包
链接:https://pan.baidu.com/s/1EQnmpah9HNoC38U2y6aBwg?pwd=xs8e
提取码:xs8e
【安装db2数据库】
一、安装前准备
1.解压压缩包
mkdir -p /home/software
cd /home/software
将压缩包上传至/home/software,解压
tar -zxvf v11.1_linuxx64_expc.tar.gz
2.安装依赖
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
yum install pam-1.1.8-23.el7.i686 -y
yum install libstdc++.so.6 -y
3.安装前环境检测
cd /home/software/expc
./db2prereqcheck
二、安装数据库
1.在/home/software/expc目录下安装数据库
cd /home/software/expc
./db2_install
2.创建用户、用户组
groupadd -g 2000 db2iadm1
groupadd -g 2001 db2fadm1
useradd -m -g db2iadm1 -d /home/db2inst1 db2inst1
useradd -m -g db2fadm1 -d /home/db2fenc1 db2fenc1
passwd db2inst1 ##设置db2inst1用户密码
3.添加证书
cd /opt/ibm/db2/V11.1/adm
chmod -R 755 *
./db2licm -a /home/software/expc/db2/license/db2expc_uw.lic
4.开启数据库监听
cd /opt/ibm/db2/V11.1/instance/
chmod -R 775 *
./db2icrt -p 50000 -u db2fenc1 db2inst1
5.切换用户
su -l db2inst1
6.创建样本数据库
db2sampl
7.启动数据库
db2start
三、测试连接数据库
使用DBeaver测试连接
【配置hadr】
前提条件:源备端机器均成功安装db2数据库
一、环境配置(源备端均需配置)
1.配置/etc/hosts
vi /etc/hosts
在末尾添加源备端机器ip以及机器名
xx.xx.78.192 db2pri
xx.xx.78.193 db2std
2.配置/etc/services
vi /etc/services
在末尾添加
db2_hadr01 63001/tcp
db2_hadr02 63002/tcp
3.网络配置
su - db2inst1
db2set DB2COMM=TCPIP
db2set -all
二、主库配置
1.创建目录
[db2inst1@db2pri ~]$ su root
[root@db2pri db2inst1]# mkdir -p /db2/db2inst1/data/testdb
[root@db2pri db2inst1]# mkdir -p /db2/db2inst1/arch
[root@db2pri db2inst1]# mkdir /db2/db2inst1/data/testdb/tbs
[root@db2pri db2inst1]# chown db2inst1:db2iadm1 -R /db2
2.创建测试表
##创建数据库
su - db2inst1
db2 "create db testdb on /db2/db2inst1/data/testdb using codeset utf8 territory CN"
db2 connect to testdb
##创建缓冲区
db2 "create bufferpool TB_BP_8k size 4000 pagesize 8k"
db2 "create bufferpool IDX_BP_8k size 4000 pagesize 8k"
##创建表空间
[db2inst1@db2pri ~]$ db2 "create regular tablespace TB_TS pagesize 8k managed by database using(file '/db2/db2inst1/data/testdb/tbs/TB_TS' 10g) bufferpool TB_BP_8k"
[db2inst1@db2pri ~]$ db2 "create regular tablespace IDX_TS pagesize 8k managed by database using(file '/db2/db2inst1/data/testdb/tbs/IDX_TS' 4g) bufferpool IDX_BP_8k"
##创建表
[db2inst1@db2pri ~]$ db2 connect to testdb
[db2inst1@db2pri ~]$ db2 'create table product(id integer,name varchar(10)) IN "TB_TS" INDEX IN "IDX_TS"'
##新增表数据
[db2inst1@db2pri ~]$ db2 "insert into product values(1,'drm')"
[db2inst1@db2pri ~]$ db2 'select * from product'
ID NAME
----------- ----------
1 drm
1 record(s) selected.
3.配置归档
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using logarchmeth1 disk:/db2/db2inst1/arch
4.备份数据库
[db2inst1@db2pri ~]$ mkdir -p /home/db2inst1/db2_backup
[db2inst1@db2pri ~]$ chown db2inst1:db2iadm1 -R /home/db2inst1/db2_backup
[db2inst1@db2pri ~]$ db2 force applications all
[db2inst1@db2pri ~]$ db2 backup database testdb to "/home/db2inst1/db2_backup"
5.重启实例
[db2inst1@db2pri ~]$ db2stop
[db2inst1@db2pri ~]$ db2start
6.手动归档
[db2inst1@db2pri ~]$ db2 archive log for db testdb
7.配置备库只读
[db2inst1@db2pri ~]$ db2set DB2_HADR_ROS=ON
[db2inst1@db2pri ~]$ db2set DB2_STANDBY_ISO=UR
8.配置hadr同步参数
##HADR_LOCAL_* 为当前机器参数 HADR_REMOT_* 为对方的参数(主或者备) HADR_REMOTE_INST 为示例名
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_LOCAL_HOST db2pri
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_LOCAL_SVC db2_hadr01
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_REMOTE_HOST db2std
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_REMOTE_SVC db2_hadr02
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_REMOTE_INST db2inst1
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_SYNCMODE NEARSYNC
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using HADR_TIMEOUT 120
[db2inst1@db2pri ~]$ db2 update db cfg for testdb using LOGINDEXBUILD ON
9.拷贝备份集
[root@db2pri ~]# scp /home/db2inst1/db2_backup/TESTDB.0.db2inst1.DBPART000.20231129105054.001 db2inst1@db2std:/home/db2inst1/db2_backup
三、备库配置
1.备库/home/db2inst1/db2_backup目录下查看
[root@db2std db2_backup]# ll
总用量 196688
-rw-------. 1 db2inst1 db2iadm1 201408512 11月 29 11:29 TESTDB.0.db2inst1.DBPART000.20231129105054.001
2.创建目录
[root@db2std db2_backup]# mkdir -p /db2/db2inst1/data/testdb
[root@db2std db2_backup]# mkdir -p /db2/db2inst1/arch
[root@db2std db2_backup]# mkdir /db2/db2inst1/data/testdb/tbs
[root@db2std db2_backup]# chown db2inst1:db2iadm1 -R /db2
3.恢复数据库
[db2inst1@db2std ~]$ db2 restore database testdb from "/home/db2inst1/db2_backup"
4.配置备库只读
[db2inst1@db2std ~]$ db2set DB2_HADR_ROS=ON
[db2inst1@db2std ~]$ db2set DB2_STANDBY_ISO=UR
5.查看备库状态
[db2inst1@db2std ~]$ db2 get db cfg for testdb | grep Rollforward
Rollforward pending = DATABASE
[db2inst1@db2std ~]$ db2 rollforward db testdb query status
Rollforward Status
Input database alias = testdb
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-11-29-02.50.59.000000 UTC
6.配置HADR同步参数
[db2inst1@db2std ~]$ db2 get db cfg for testdb | grep -i HADR
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) =
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_LOCAL_HOST db2std
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_LOCAL_SVC db2_hadr02
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_REMOTE_HOST db2pri
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_REMOTE_SVC db2_hadr01
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_REMOTE_INST db2inst1
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_SYNCMODE NEARSYNC
[db2inst1@db2std ~]$ db2 update db cfg for testdb using HADR_TIMEOUT 120
[db2inst1@db2std ~]$ db2 update db cfg for testdb using LOGINDEXBUILD ON
四、启动hadr
1.先启动备库
[db2inst1@db2std ~]$ db2 start hadr on database testdb as standby
2.再启动主库
[db2inst1@db2pri ~]$ db2 start hadr on database testdb as primary
3.查看hadr状态
##主库
[db2inst1@db2pri ~]$ db2pd -db testdb -hadr
Database Member 0 -- Database TESTDB -- Active -- Up 0 days 00:00:36 -- Date 2023-11-29-13.52.29.823298
HADR_ROLE = PRIMARY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 1
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = db2pri
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = db2std
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 2023-11-29 13:51:56.301931 (1701237116)
HEARTBEAT_INTERVAL(seconds) = 30
HEARTBEAT_MISSED = 0
HEARTBEAT_EXPECTED = 1
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 3
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
LOG_HADR_WAIT_COUNT = 0
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 87040
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 369280
PRIMARY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61531297
STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61531297
HADR_LOG_GAP(bytes) = 0
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61531297
STANDBY_RECV_REPLAY_GAP(bytes) = 0
PRIMARY_LOG_TIME = 2023-11-29 10:50:59.000000 (1701226259)
STANDBY_LOG_TIME = 2023-11-29 10:50:59.000000 (1701226259)
STANDBY_REPLAY_LOG_TIME = 2023-11-29 10:50:59.000000 (1701226259)
STANDBY_RECV_BUF_SIZE(pages) = 4300
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 25600
STANDBY_SPOOL_PERCENT = 0
##备库
[db2inst1@db2std ~]$ db2pd -db testdb -hadr
Database Member 0 -- Database TESTDB -- Active Standby -- Up 0 days 00:20:53 -- Date 2023-11-29-13.58.31.780621
HADR_ROLE = STANDBY
REPLAY_TYPE = PHYSICAL
HADR_SYNCMODE = NEARSYNC
STANDBY_ID = 0
LOG_STREAM_ID = 0
HADR_STATE = PEER
HADR_FLAGS = TCP_PROTOCOL
PRIMARY_MEMBER_HOST = db2pri
PRIMARY_INSTANCE = db2inst1
PRIMARY_MEMBER = 0
STANDBY_MEMBER_HOST = db2std
STANDBY_INSTANCE = db2inst1
STANDBY_MEMBER = 0
HADR_CONNECT_STATUS = CONNECTED
HADR_CONNECT_STATUS_TIME = 2023-11-29 13:54:20.279629 (1701237260)
HEARTBEAT_INTERVAL(seconds) = 30
HEARTBEAT_MISSED = 1
HEARTBEAT_EXPECTED = 12
HADR_TIMEOUT(seconds) = 120
TIME_SINCE_LAST_RECV(seconds) = 8
PEER_WAIT_LIMIT(seconds) = 0
LOG_HADR_WAIT_CUR(seconds) = 0.000
LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000354
LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.001
LOG_HADR_WAIT_COUNT = 3
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 16384
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380
PRIMARY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61532050
STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61532050
HADR_LOG_GAP(bytes) = 0
STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 61532050
STANDBY_RECV_REPLAY_GAP(bytes) = 0
PRIMARY_LOG_TIME = 2023-11-29 13:57:48.000000 (1701237468)
STANDBY_LOG_TIME = 2023-11-29 13:57:48.000000 (1701237468)
STANDBY_REPLAY_LOG_TIME = 2023-11-29 13:57:48.000000 (1701237468)
STANDBY_RECV_BUF_SIZE(pages) = 4300
STANDBY_RECV_BUF_PERCENT = 0
STANDBY_SPOOL_LIMIT(pages) = 25600
STANDBY_SPOOL_PERCENT = 0
STANDBY_ERROR_TIME = NULL
PEER_WINDOW(seconds) = 0
READS_ON_STANDBY_ENABLED = Y
STANDBY_REPLAY_ONLY_WINDOW_ACTIVE = N
4.测试
##主库新增数据
[db2inst1@db2pri ~]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 11.1.4.4
SQL authorization ID = DB2INST1
Local database alias = TESTDB
[db2inst1@db2pri ~]$ db2 'select * from product'
ID NAME
----------- ----------
1 drm
1 record(s) selected.
[db2inst1@db2pri ~]$ db2 "insert into product values(2,'up')"
DB20000I The SQL command completed successfully.
[db2inst1@db2pri ~]$ db2 'select * from product'
ID NAME
----------- ----------
1 drm
2 up
2 record(s) selected.
##备库查看
[db2inst1@db2std ~]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 11.1.4.4
SQL authorization ID = DB2INST1
Local database alias = TESTDB
[db2inst1@db2std ~]$ db2 'select * from product'
ID NAME
----------- ----------
1 drm
2 up
2 record(s) selected.
注:HADR启停顺序
##停止
主:
db2 stop hadr on db testdb
备:
db2 deactivate db testdb
db2 stop hadr on db testdb
##启动
备:
db2 start hadr on db testdb as standby
主:
db2 start hadr on db testdb as primary
五、切换
1.主备切换(备库执行)
db2 takeover hadr on database testdb
2.故障切换(备库执行)
db2 takeover hadr on database testdb by force