DB2 V10.1 HADR快速部署手册

时间：2022-09-19 08:45:41
关于DB2 HADR就不做多的解释了，和oracle的DataGuard类似
这里记录一下平时实验的一个快速部署手册

CentOS6.5 x64位

192.168.122.101 kvm110

192.168.122.102 kvm111


目录准备

  1. mkdir -p /home/db2inst2/db2_backup
  2. mkdir -p /home/db2inst2/db2_archive
  3. mkdir -p /home/db2inst2/db2_log
  4.  
  5. chmod -R 775 /home/db2inst2/db2_backup
  6. chmod -R 775 /home/db2inst2/db2_archive
  7. chmod -R 775 /home/db2inst2/db2_log
  8.  
  9. chown -R db2inst2:db2iadm2 /home/db2inst2/db2_backup
  10. chown -R db2inst2:db2iadm2 /home/db2inst2/db2_archive
  11. chown -R db2inst2:db2iadm2 /home/db2inst2/db2_log



安装db2-略

创建测试库

 

db2 create db hadb01

 

下面新增一些数据，只在主库添加：

 


  1. db2 connect to hadb01
  2. db2 "create table t1(id int)"
  3. db2 "insert into t1 values(1)"
  4. db2 "insert into t1 values(2)"
  5.  
  6. db2 "create table t2(id int)"
  7. db2 "insert into t2 values(1)"
  8. db2 "insert into t2 values(2)"

开启归档模式


 

主库和备库都操作

先修改归档参数，做离线备份，重启数据库后，手工测试归档

 


点击(此处)折叠或打开
  1. db2 update db cfg for hadb01 using logarchmeth1 disk:/home/db2inst2/db2_archive/
  2. db2 update db cfg for hadb01 using NEWLOGPATH /home/db2inst2/db2_log
  3.  
  4. db2 force applications all
  5. db2 backup db hadb01 to /home/db2inst2/db2_backup/
  6. db2stop force;db2start
  7. db2 archive log for db hadb01



备库:

 


  1. db2 update db cfg for hadb01 using logarchmeth1 disk:/home/db2inst2/db2_archive/
  2. db2 update db cfg for hadb01 using NEWLOGPATH /home/db2inst2/db2_log
  3.  
  4. db2 force applications all
  5. db2 backup db hadb01 to /home/db2inst2/db2_backup/
  6. db2stop force;db2start
  7. db2 archive log for db hadb01


主库离线全备份


  1. db2 backup database hadb01 to /home/db2inst2/db2_backup

  3. scp /home/db2inst2/db2_backup/hadb01.0.db2inst2.NODE0000.CATN0000.20150522091531.001 db2inst2@192.168.122.102:/home/db2inst2/db2_backup/


备库还原数据


  1. [db2inst2@kvm111 ~]$ db2 restore database hadb01 from "/home/db2inst2/db2_backup" taken at 20150522091531 replace history file
  2. SQL2523W Warning! Restoring to an existing database that is different from
  3. the database on the backup image, but have matching names. The target database
  4. will be overwritten by the backup version. The Roll-forward recovery logs
  5. associated with the target database will be deleted.
  6. Do you want to continue ? (y/n) y
  7. DB20000I The RESTORE DATABASE command completed successfully.



服务和端口配置

配置HADR服务 主备都设置

 

vi /etc/services加入

 


点击(此处)折叠或打开
  1. DB2_HADR_1 55110/tcp
  2. DB2_HADR_2 55111/tcp


 

后面加入了两行,用于配置下面的HADR_REMOTE_SVC

注意有没有和已有的端口冲突

 

主备参数配置

 

主库参数配置


  1. db2 get db cfg for hadb01 | grep -i HADR
  2. db2 update db cfg for hadb01 using HADR_LOCAL_HOST 192.168.122.101
  3. db2 update db cfg for hadb01 using HADR_LOCAL_SVC DB2_HADR_1
  4.  
  5. db2 update db cfg for hadb01 using HADR_REMOTE_HOST 192.168.122.102
  6. db2 update db cfg for hadb01 using HADR_REMOTE_SVC DB2_HADR_2
  7. db2 update db cfg for hadb01 using HADR_REMOTE_INST db2inst2
  8.  
  9. db2 update db cfg for hadb01 using HADR_SYNCMODE NEARSYNC
  10. db2 update db cfg for hadb01 using HADR_TIMEOUT 120
  11. db2 get db cfg for hadb01 | grep -i HADR


执行前：

 


  1. [db2inst2@kvm110 ~]$ db2 get db cfg for hadb01 | grep -i HADR
  2.  HADR database role = STANDARD
  3.  HADR local host name (HADR_LOCAL_HOST) =
  4.  HADR local service name (HADR_LOCAL_SVC) =
  5.  HADR remote host name (HADR_REMOTE_HOST) =
  6.  HADR remote service name (HADR_REMOTE_SVC) =
  7.  HADR instance name of remote server (HADR_REMOTE_INST) =
  8.  HADR timeout value (HADR_TIMEOUT) = 120
  9.  HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
  10.  HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0


 

执行后：

 


  1. [db2inst2@kvm110 ~]$ db2 get db cfg for hadb01 | grep -i HADR
  2.  HADR database role = STANDARD
  3.  HADR local host name (HADR_LOCAL_HOST) = 192.168.122.101
  4.  HADR local service name (HADR_LOCAL_SVC) = DB2_HADR_1
  5.  HADR remote host name (HADR_REMOTE_HOST) = 192.168.122.102
  6.  HADR remote service name (HADR_REMOTE_SVC) = DB2_HADR_2
  7.  HADR instance name of remote server (HADR_REMOTE_INST) = db2inst2
  8.  HADR timeout value (HADR_TIMEOUT) = 120
  9.  HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
  10.  HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0


备库参数配置

 


  1. db2 get db cfg for hadb01 | grep -i HADR
  2. db2 update db cfg for hadb01 using HADR_LOCAL_HOST 192.168.122.102
  3. db2 update db cfg for hadb01 using HADR_LOCAL_SVC DB2_HADR_2
  4.  
  5. db2 update db cfg for hadb01 using HADR_REMOTE_HOST 192.168.122.101
  6. db2 update db cfg for hadb01 using HADR_REMOTE_SVC DB2_HADR_1
  7. db2 update db cfg for hadb01 using HADR_REMOTE_INST db2inst2
  8.  
  9. db2 update db cfg for hadb01 using HADR_SYNCMODE NEARSYNC
  10. db2 update db cfg for hadb01 using HADR_TIMEOUT 120
  11. db2 get db cfg for hadb01 | grep -i HADR

 

执行后：

 


  1. [db2inst2@kvm111 ~]$ db2 get db cfg for hadb01 | grep -i HADR
  2.  HADR database role = STANDARD
  3.  HADR local host name (HADR_LOCAL_HOST) = 192.168.122.102
  4.  HADR local service name (HADR_LOCAL_SVC) = DB2_HADR_2
  5.  HADR remote host name (HADR_REMOTE_HOST) = 192.168.122.101
  6.  HADR remote service name (HADR_REMOTE_SVC) = DB2_HADR_1
  7.  HADR instance name of remote server (HADR_REMOTE_INST) = db2inst2
  8.  HADR timeout value (HADR_TIMEOUT) = 120
  9.  HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
  10.  HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0



 启动HADR

 先启动备库

 

standby端的数据库通过primary端的数据库恢复来,恢复后必须是roll forward-pending状态，restore中不能使用without rolling forward，启动备库之前先确定这个状态

 

[db2inst2@kvm111 ~]$ db2 get db cfg for hadb01 | grep Rollforward

 Rollforward pending                                     = DATABASE

 

相比于主库:

[db2inst2@kvm110 ~]$ db2 get db cfg for hadb01 | grep Rollforward

 Rollforward pending                                     = NO

 

启动备库

 

db2 start hadr on database hadb01 as standby


[db2inst2@kvm111 ~]$ db2 start hadr on database hadb01 as standby

SQL1766W  The command completed successfully. However, LOGINDEXBUILD was not

enabled before HADR was started.


查看HADR状态-目前只有备库，状态是disconnected


  1. [db2inst2@kvm102 ~]$ db2 get snapshot for db on hadb01 | grep -A 15 "HADR Status"
  2. HADR Status
  3.   Role = Standby
  4.   State = Remote catchup pending
  5.   Synchronization mode = Nearsync
  6.   Connection status = Disconnected, 12/17/2015 00:57:27.251629
  7.   Heartbeats missed = 0
  8.   Local host = 192.168.122.102
  9.   Local service = DB2_HADR_2
  10.   Remote host = 192.168.122.101
  11.   Remote service = DB2_HADR_1
  12.   Remote instance = db2inst2
  13.   timeout(seconds) = 120
  14.   Primary log position(file, page, LSN) = S0000000.LOG, 0, 0000000000000000
  15.   Standby log position(file, page, LSN) = S0000002.LOG, 0, 000000000366BA41
  16.   Log gap running average(bytes) = 0
  17.  
  18. [db2inst2@kvm102 ~]$ db2pd -d hadb01 -hadr
  19.  
  20. Database Member 0 -- Database HADB01 -- Standby -- Up 0 days 00:04:42 -- Date 2015-12-17-01.02.08.288233
  21.  
  22.                             HADR_ROLE = STANDBY
  23.                           REPLAY_TYPE = PHYSICAL
  24.                         HADR_SYNCMODE = NEARSYNC
  25.                            STANDBY_ID = 0
  26.                         LOG_STREAM_ID = 0
  27.                            HADR_STATE = REMOTE_CATCHUP_PENDING
  28.                   PRIMARY_MEMBER_HOST =
  29.                      PRIMARY_INSTANCE =
  30.                        PRIMARY_MEMBER = 0
  31.                   STANDBY_MEMBER_HOST = 192.168.122.102
  32.                      STANDBY_INSTANCE = db2inst2
  33.                        STANDBY_MEMBER = 0
  34.                   HADR_CONNECT_STATUS = DISCONNECTED
  35.              HADR_CONNECT_STATUS_TIME = 12/17/2015 00:57:27.251629 (1450285047)
  36.           HEARTBEAT_INTERVAL(seconds) = 30
  37.                 HADR_TIMEOUT(seconds) = 120
  38.         TIME_SINCE_LAST_RECV(seconds) = 0
  39. SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 16384
  40. SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380
  41.             PRIMARY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 0
  42.             STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
  43.                   HADR_LOG_GAP(bytes) = 0
  44.      STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
  45.        STANDBY_RECV_REPLAY_GAP(bytes) = 0
  46.                      PRIMARY_LOG_TIME = NULL
  47.                      STANDBY_LOG_TIME = 12/17/2015 00:22:15.000000 (1450282935)
  48.               STANDBY_REPLAY_LOG_TIME = 12/17/2015 00:22:15.000000 (1450282935)
  49.          STANDBY_RECV_BUF_SIZE(pages) = 16
  50.              STANDBY_RECV_BUF_PERCENT = 0
  51.            STANDBY_SPOOL_LIMIT(pages) = 0
  52.                  PEER_WINDOW(seconds) = 0
  53.              READS_ON_STANDBY_ENABLED = N


启动主库


  1. db2 deactivate database hadb01
  2. db2 start hadr on database hadb01 as primary


状态

 

  1. [db2inst2@kvm101 ~]$ db2 get snapshot for db on hadb01 | grep -A 15 "HADR Status"
  2. HADR Status
  3.   Role = Primary
  4.   State = Peer
  5.   Synchronization mode = Nearsync
  6.   Connection status = Connected, 12/17/2015 01:03:31.996832
  7.   Heartbeats missed = 0
  8.   Local host = 192.168.122.101
  9.   Local service = DB2_HADR_1
  10.   Remote host = 192.168.122.102
  11.   Remote service = DB2_HADR_2
  12.   Remote instance = db2inst2
  13.   timeout(seconds) = 120
  14.   Primary log position(file, page, LSN) = S0000002.LOG, 0, 000000000366BA41
  15.   Standby log position(file, page, LSN) = S0000002.LOG, 0, 000000000366BA41
  16.   Log gap running average(bytes) = 0
  17.  
  18. [db2inst2@kvm101 ~]$
  19. [db2inst2@kvm101 ~]$ db2pd -d hadb01 -hadr
  20.  
  21. Database Member 0 -- Database HADB01 -- Active -- Up 0 days 00:01:30 -- Date 2015-12-17-01.04.59.855546
  22.  
  23.                             HADR_ROLE = PRIMARY
  24.                           REPLAY_TYPE = PHYSICAL
  25.                         HADR_SYNCMODE = NEARSYNC
  26.                            STANDBY_ID = 1
  27.                         LOG_STREAM_ID = 0
  28.                            HADR_STATE = PEER
  29.                   PRIMARY_MEMBER_HOST = 192.168.122.101
  30.                      PRIMARY_INSTANCE = db2inst2
  31.                        PRIMARY_MEMBER = 0
  32.                   STANDBY_MEMBER_HOST = 192.168.122.102
  33.                      STANDBY_INSTANCE = db2inst2
  34.                        STANDBY_MEMBER = 0
  35.                   HADR_CONNECT_STATUS = CONNECTED
  36.              HADR_CONNECT_STATUS_TIME = 12/17/2015 01:03:31.996832 (1450285411)
  37.           HEARTBEAT_INTERVAL(seconds) = 30
  38.                 HADR_TIMEOUT(seconds) = 120
  39.         TIME_SINCE_LAST_RECV(seconds) = 27
  40.              PEER_WAIT_LIMIT(seconds) = 0
  41.            LOG_HADR_WAIT_CUR(seconds) = 0.000
  42.     LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
  43.    LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
  44.                   LOG_HADR_WAIT_COUNT = 0
  45. SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 19800
  46. SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380
  47.             PRIMARY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
  48.             STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
  49.                   HADR_LOG_GAP(bytes) = 0
  50.      STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
  51.        STANDBY_RECV_REPLAY_GAP(bytes) = 0
  52.                      PRIMARY_LOG_TIME = 12/17/2015 00:34:24.000000 (1450283664)
  53.                      STANDBY_LOG_TIME = 12/17/2015 00:34:24.000000 (1450283664)
  54.               STANDBY_REPLAY_LOG_TIME = 12/17/2015 00:34:24.000000 (1450283664)
  55.          STANDBY_RECV_BUF_SIZE(pages) = 512
  56.              STANDBY_RECV_BUF_PERCENT = 0
  57.            STANDBY_SPOOL_LIMIT(pages) = 0
  58.                  PEER_WINDOW(seconds) = 0
  59.              READS_ON_STANDBY_ENABLED = N
现在主库和备库已经建立连接，HADR实验环境部署完成
标签：

相关文章