验证 DB2 v9.7 FP1 HADR 备机可读性

控制 DB2 v9.7 FP1 HADR Standby 核心参数其实就是在 Standby 上面设置 db2set DB2_HADR_ROS=ON


HADR 配置环境如下:

==============>Primary:

[db2inst4@CentOS5 ~]$ db2 get db cfg for hadrdb |grep -i hadr
       Database Configuration for Database hadrdb
HADR database role                                      = PRIMARY
HADR local host name                  (HADR_LOCAL_HOST) = CentOS5
HADR local service name                (HADR_LOCAL_SVC) = 8494
HADR remote host name                (HADR_REMOTE_HOST) = CentOS5
HADR remote service name              (HADR_REMOTE_SVC) = 8495
HADR instance name of remote server  (HADR_REMOTE_INST) = db2inst5
HADR timeout value                       (HADR_TIMEOUT) = 120
HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0

==============>Standby:

[db2inst5@CentOS5 ~]$ db2set -all
[i] DB2_HADR_ROS=ON
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=CentOS5
[g] DB2INSTDEF=db2inst5


[db2inst5@CentOS5 ~]$ db2 get db cfg for hadrdb |grep -i hadr
       Database Configuration for Database hadrdb
HADR database role                                      = STANDBY
HADR local host name                  (HADR_LOCAL_HOST) = CentOS5
HADR local service name                (HADR_LOCAL_SVC) = 8495
HADR remote host name                (HADR_REMOTE_HOST) = CentOS5
HADR remote service name              (HADR_REMOTE_SVC) = 8494
HADR instance name of remote server  (HADR_REMOTE_INST) = db2inst4
HADR timeout value                       (HADR_TIMEOUT) = 120
HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0


[db2inst4@CentOS5 ~]$ db2 get snapshot for db on hadrdb |grep -B 15 "Log gap running average(bytes)"

HADR Status
  Role                   = Primary
  State                  = Peer
  Synchronization mode   = Nearsync
  Connection status      = Connected, 03/02/2010 16:32:31.612992
  Heartbeats missed      = 0
  Local host             = CentOS5
  Local service          = 8494
  Remote host            = CentOS5
  Remote service         = 8495
  Remote instance        = db2inst5
  timeout(seconds)       = 120
  Primary log position(file, page, LSN) = S0000004.LOG, 23, 0000000002EBC872
  Standby log position(file, page, LSN) = S0000004.LOG, 23, 0000000002EBC872
  Log gap running average(bytes) = 0


[db2inst5@CentOS5 ~]$ db2 get snapshot for db on hadrdb |grep -B 15 "Log gap running average(bytes)"

HADR Status
  Role                   = Standby
  State                  = Peer
  Synchronization mode   = Nearsync
  Connection status      = Connected, 03/02/2010 16:32:31.613554
  Heartbeats missed      = 0
  Local host             = CentOS5
  Local service          = 8495
  Remote host            = CentOS5
  Remote service         = 8494
  Remote instance        = db2inst4
  timeout(seconds)       = 120
  Primary log position(file, page, LSN) = S0000004.LOG, 23, 0000000002EBC872
  Standby log position(file, page, LSN) = S0000004.LOG, 23, 0000000002EBC872
  Log gap running average(bytes) = 0

测试效果:

==============>Primary:

>connect to hadrdb
>CREATE TABLE T1(C1 INT NOT NULL PRIMARY KEY, C2 int, C3 char(10))
>insert into t1 values(1,1,'Chanel')
>insert into t1 values(2,2,'Helen')
>insert into t1 values(3,3,'David')
>select * from t1

[db2inst4@CentOS5 ~]$ db2 "select * from t1"

C1          C2          C3
----------- ----------- ----------
          1           1 Chanel
          2           2 Helen
          3           3 David

  3 record(s) selected.

==============>Standby:

[db2inst5@CentOS5 ~]$ db2 connect to hadrdb

   Database Connection Information

Database server        = DB2/LINUX 9.7.1
SQL authorization ID   = DB2INST5
Local database alias   = HADRDB


[db2inst5@CentOS5 ~]$ db2 "select * from db2inst4.t1"

C1          C2          C3
----------- ----------- ----------
          1           1 Chanel
          2           2 Helen
          3           3 David

  3 record(s) selected.


这个时候 Standby DB 处于 Not Pending 状态了, 所有在主数据库上的 Tansation 将不会在 Standby 上 Replay, 想要恢复 Standby 状态只需要设置备机上的 db2set DB2_HADR_ROS=OFF 然后 recycle Standby instance 即可重放 所有主机上的更改.收起