一步一个脚印 DB2 10.5 HADR 主备库配置

原创阿林哥 2021-07-08

1877

前言

我们都知道Oracle数据库的容灾技术DG（dataguard），当然db2也有自己的容灾技术HADR，也就是High Availability Disaster Recovery，Oracle从11g版本开始就出现了dg的新特性adg（active dataguard）备库就可以read only，而db2呢则在9.7版本的时候备库就出现了read only，也就是RoS。

数据库的安装

主备节点安装好数据库软件并且创建好实例，可以参考我前面的文章进行安装。

主节点：fwl-node03
备节点：fwl-node04

db2网络的配置

su - db2imn
db2set DB2COMM=TCPIP
db2set -all

Note：配置TCP协议，主备节点均要配置

配置服务端口

[db2imn@fwl-node03 ~]$ cat /etc/services| grep db2
ibm-db2         523/tcp                 # IBM-DB2
ibm-db2         523/udp                 # IBM-DB2
questdb2-lnchr  5677/tcp                # Quest Central DB2 Launchr
questdb2-lnchr  5677/udp                # Quest Central DB2 Launchr
qdb2service     45825/tcp               # Qpuncture Data Access Service
qdb2service     45825/udp               # Qpuncture Data Access Service
DB2_db2imn	60000/tcp
DB2_db2imn_1	60001/tcp
DB2_db2imn_2	60002/tcp
DB2_db2imn_3	60003/tcp
DB2_db2imn_4	60004/tcp
DB2_db2imn_END	60005/tcp

Note：这里可以看到db2的服务端口为60000，下面我们修改db2的参数

db2 update dbm cfg using SVCENAME DB2_db2imn
db2 get dbm cfg | grep SVCENAME
db2start

添加HADR的专用服务

vi /etc/services
db2_hadr01     65110/tcp
db2_hadr02     65111/tcp

Note：主备库均需要添加

主库配置

创建目录

mkdir -p /db2/db2imn/data/dogdb
mkdir -p /db2/db2imn/arch
mkdir  /db2/db2imn/data/dogdb/tbs
chown db2imn.db2iadm -R /db2

创建数据库

Note：这里创建一个数据库作为案例。

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'

配置归档

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

备份数据库

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

Note：以此备份集恢复出来一个备库

重启实例

db2stop
db2start

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

手动归档

db2 archive log for db dogdb

配置备库只读

db2set DB2_HADR_ROS=ON
db2set DB2_STANDBY_ISO=UR

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

配置HADR同步参数

db2 update db cfg for dogdb using HADR_LOCAL_HOST fwl-node03

db2 update db cfg for dogdb using HADR_LOCAL_SVC db2_hadr01

db2 update db cfg for dogdb using HADR_REMOTE_HOST fwl-node04

db2 update db cfg for dogdb using HADR_REMOTE_SVC db2_hadr02

db2 update db cfg for dogdb using HADR_REMOTE_INST db2imn

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

拷贝备份集

scp /home/db2imn/db2_backup/DOGDB.0.db2imn.DBPART000.20210701202133.001 fwl-node04:/home/db2imn/db2_backup

Note：前面开启归档已对数据库进行备份，可以以此备份集恢复出来一个备库。

备库配置

创建目录

mkdir -p /db2/db2imn/data/dogdb
mkdir -p /db2/db2imn/arch
mkdir  /db2/db2imn/data/dogdb/tbs
chown db2imn.db2iadm -R /db2

恢复数据库

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

[db2imn@fwl-node04 db2_backup]$ db2 restore database dogdb  from "/home/db2imn/db2_backup" 
DB20000I  The RESTORE DATABASE command completed successfully.
[db2imn@fwl-node04 db2_backup]$

[db2imn@fwl-node04 db2_backup]$ db2 list db DIRECTORY

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = DOGDB
 Database name                        = DOGDB
 Local database directory             = /home/db2imn
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

[db2imn@fwl-node04 db2_backup]$

配置备库只读

db2set DB2_HADR_ROS=ON
db2set DB2_STANDBY_ISO=UR

查看备库的状态

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

[db2imn@fwl-node04 db2_backup]$ db2 get db cfg for dogdb | grep Rollforward
 Rollforward pending                                     = DATABASE

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

配置HADR同步参数

db2 get db cfg for dogdb | grep -i HADR

db2 update db cfg for dogdb using HADR_LOCAL_HOST fwl-node04

db2 update db cfg for dogdb using HADR_LOCAL_SVC  db2_hadr02

db2 update db cfg for dogdb using HADR_REMOTE_HOST fwl-node03

db2 update db cfg for dogdb using HADR_REMOTE_SVC  db2_hadr01

db2 update db cfg for dogdb using HADR_REMOTE_INST db2imn

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

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

HADR 启动

备库先启动

db2 start hadr on database dogdb as standby

主库启动

db2 start hadr on database dogdb as primary

查看

db2pd -db dogdb -hadr

主库：

[db2imn@fwl-node03 ~]$ db2pd -db dogdb -hadr 

Database Member 0 -- Database DOGDB -- Active -- Up 0 days 21:47:41 -- Date 2021-07-08-11.32.56.469563

                            HADR_ROLE = PRIMARY
                          REPLAY_TYPE = PHYSICAL
                        HADR_SYNCMODE = NEARSYNC
                           STANDBY_ID = 1
                        LOG_STREAM_ID = 0
                           HADR_STATE = PEER
                           HADR_FLAGS = 
                  PRIMARY_MEMBER_HOST = fwl-node03
                     PRIMARY_INSTANCE = db2imn
                       PRIMARY_MEMBER = 0
                  STANDBY_MEMBER_HOST = fwl-node04
                     STANDBY_INSTANCE = db2imn
                       STANDBY_MEMBER = 0
                  HADR_CONNECT_STATUS = CONNECTED
             HADR_CONNECT_STATUS_TIME = 07/07/2021 13:45:24.102263 (1625636724)
          HEARTBEAT_INTERVAL(seconds) = 30
                     HEARTBEAT_MISSED = 0
                   HEARTBEAT_EXPECTED = 2612
                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.000255
   LOG_HADR_WAIT_ACCUMULATED(seconds) = 2.022
                  LOG_HADR_WAIT_COUNT = 4683
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 87040
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 374400
            PRIMARY_LOG_FILE,PAGE,POS = S0000029.LOG, 172, 175024279
            STANDBY_LOG_FILE,PAGE,POS = S0000029.LOG, 172, 175024279
                  HADR_LOG_GAP(bytes) = 0
     STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000029.LOG, 172, 175024279
       STANDBY_RECV_REPLAY_GAP(bytes) = 0
                     PRIMARY_LOG_TIME = 07/08/2021 10:35:15.000000 (1625711715)
                     STANDBY_LOG_TIME = 07/08/2021 10:35:15.000000 (1625711715)
              STANDBY_REPLAY_LOG_TIME = 07/08/2021 10:35:15.000000 (1625711715)
         STANDBY_RECV_BUF_SIZE(pages) = 4298
             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

[db2imn@fwl-node03 ~]$

备库：

[db2imn@fwl-node04 ~]$ db2pd -db dogdb -hadr 

Database Member 0 -- Database DOGDB -- Active Standby -- Up 0 days 21:48:53 -- Date 2021-07-08-11.34.14.406197

                            HADR_ROLE = STANDBY
                          REPLAY_TYPE = PHYSICAL
                        HADR_SYNCMODE = NEARSYNC
                           STANDBY_ID = 0
                        LOG_STREAM_ID = 0
                           HADR_STATE = PEER
                           HADR_FLAGS = 
                  PRIMARY_MEMBER_HOST = fwl-node03
                     PRIMARY_INSTANCE = db2imn
                       PRIMARY_MEMBER = 0
                  STANDBY_MEMBER_HOST = fwl-node04
                     STANDBY_INSTANCE = db2imn
                       STANDBY_MEMBER = 0
                  HADR_CONNECT_STATUS = CONNECTED
             HADR_CONNECT_STATUS_TIME = 07/07/2021 13:45:23.682451 (1625636723)
          HEARTBEAT_INTERVAL(seconds) = 30
                     HEARTBEAT_MISSED = 0
                   HEARTBEAT_EXPECTED = 2617
                HADR_TIMEOUT(seconds) = 120
        TIME_SINCE_LAST_RECV(seconds) = 30
             PEER_WAIT_LIMIT(seconds) = 0
           LOG_HADR_WAIT_CUR(seconds) = 0.000
    LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000255
   LOG_HADR_WAIT_ACCUMULATED(seconds) = 2.022
                  LOG_HADR_WAIT_COUNT = 4683
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 87040
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 372480
            PRIMARY_LOG_FILE,PAGE,POS = S0000029.LOG, 172, 175024279
            STANDBY_LOG_FILE,PAGE,POS = S0000029.LOG, 172, 175024279
                  HADR_LOG_GAP(bytes) = 0
     STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000029.LOG, 172, 175024279
       STANDBY_RECV_REPLAY_GAP(bytes) = 0
                     PRIMARY_LOG_TIME = 07/08/2021 10:35:15.000000 (1625711715)
                     STANDBY_LOG_TIME = 07/08/2021 10:35:15.000000 (1625711715)
              STANDBY_REPLAY_LOG_TIME = 07/08/2021 10:35:15.000000 (1625711715)
         STANDBY_RECV_BUF_SIZE(pages) = 4298
             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

[db2imn@fwl-node04 ~]$

Note：可以看到同步状态为HADR_STATE = PEER，主备节点之间是无延迟的。

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

主备切换

正常接管

db2 takeover hadr on database dogdb

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

故障切换

强制接管

db2 takeover hadr on database dogdb by force

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

db2

最后修改时间：2021-07-08 11:52:01

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

IBM db2 luw（共3篇）

db2入门与精通

一步一个脚印 DB2 10.5 HADR 主备库配置

前言

数据库的安装

db2网络的配置

配置服务端口

添加HADR的专用服务

主库配置

创建目录

创建数据库

配置归档

备份数据库

重启实例

手动归档

配置备库只读

配置HADR同步参数

拷贝备份集

备库配置

创建目录

恢复数据库

配置备库只读

查看备库的状态

配置HADR同步参数

HADR 启动

备库先启动

主库启动

查看

HADR启停顺序

主备切换

故障切换

文章被以下合辑收录

评论