In my previous blog, I presented some of the issues you can run into when setting up one auxiliary HADR standby and a potential solution. This blog discusses setting up two auxiliary standby databases in detail.
Let’s start with all the information we need to set the configuration parameters:
Host Info:
Primary: dbpp.xxx.net
Principal standby: dbps.xxx.net
Auxiliary standby 1: aux1-east.xxx.net
Auxiliary standby 2: aux2-east.xxx.net
Prerequisites:
1. Make sure each node can ping every other node. You should be able to ping server 2, server 3 and server 4 from server 1; server2 should be able to ping each of the other servers, and so on.
2. Make sure your /etc/hosts has all the ports defined for HADR for all the nodes.
[db2inst1 ~]$ cat /etc/hosts | grep –i hadr
db2_hadrp 50050/tcp
db2_hadrs 50051/tcp
db2_hadra 50052/tcp
db2_hadrb 50053/tcp
Next, we need to set the HADR configuration parameters on all the boxes. The main parameter we are interested in is HADR_TARGET_LIST. The idea is the same as setting up a single auxiliary box. Leave out the host you are on and put all the other hostnames in order along with the HADR ports.
For example, when setting this parameter for the server aux1-east, HADR_TARGET_LIST would be:
dbpp.xxx.net:50050|dbps.xxx.net:50051|aux2-east.xxx.net:50053
Below is the sample configuration for all the boxes in my set-up.
For the first auxiliary standby (aux1-east.xxx.net):
[[email protected]]$ db2 get db cfg for sample | grep -i hadr
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = aux1-east.xxx.net
HADR local service name (HADR_LOCAL_SVC) = 50052
HADR remote host name (HADR_REMOTE_HOST) = dbpp.xxx.net
HADR remote service name (HADR_REMOTE_SVC) = 50050
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) = dbpp.xxx.net:50050|dbpsxxx.net:50051|aux2-east.xxx.net:50053
HADR log write synchronization mode (HADR_SYNCMODE) = SUPERASYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = 100000
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 120
For the second auxiliary standby (aux2-east.xxx.net):
[[email protected]]$ db2 get db cfg for sample | grep -i hadr
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = aux2-east.xxx.net
HADR local service name (HADR_LOCAL_SVC) = 50053
HADR remote host name (HADR_REMOTE_HOST) = dbpp.xxx.net
HADR remote service name (HADR_REMOTE_SVC) = 50050
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) = dbpp.xxx.net:50050|dbps.xxx.net:50051|aux1-east.xxx.net-01:50052
HADR log write synchronization mode (HADR_SYNCMODE) = SUPERASYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = 100000
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 120
For the principal standby (dbps.xxx.net):
[[email protected]]$ db2 get db cfg for sample | grep -i hadr
HADR database role = STANDBY
HADR local host name (HADR_LOCAL_HOST) = dbps.xxx.net
HADR local service name (HADR_LOCAL_SVC) = 50051
HADR remote host name (HADR_REMOTE_HOST) = dbpp.xxx.net
HADR remote service name (HADR_REMOTE_SVC) = 50050
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) = dbpp.xxx.net:50050|aux1-east.xxx.net-01:50052|aux2-east.xxx.net:50053
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = 100000
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 300
For the primary server (dbpp.xxx.net):
[[email protected]]$ db2 get db cfg for sample |grep -i hadr
HADR database role = PRIMARY
HADR local host name (HADR_LOCAL_HOST) = dbpp.xxx.net
HADR local service name (HADR_LOCAL_SVC) = 50050
HADR remote host name (HADR_REMOTE_HOST) = dbps.xxx.net
HADR remote service name (HADR_REMOTE_SVC) = 50051
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) = dbps.xxx.net:50051|aux1-east.xxx.net:50052|aux2-east.xxx.net
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = 100000
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 300
That’s it. The process to start HADR is the same. Issue this command on the aux2-east; aux1-east and dbps.xxx.net:
[db2inst1 ~]$ db2 start hadr on db sample as standby
Here is an example:
[db2inst1 ~]$ db2 deactivate db sample
DB20000I The DEACTIVATE DATABASE command completed successfully.
[db2inst1 ~]$ db2 start hadr on db sample as standby
DB20000I The START HADR ON DATABASE command completed successfully.
Then start HADR on primary using this command:
[db2inst1 ~]$ db2 start hadr on db sample as primary
Once this is done, you can monitor the HADR_STATE from the primary to watch as all three standby copies connect to the primary and start receiving logs. The principal standby would reach a PEER state once caught up. Both the auxiliary standbys will always show a REMOTE_CATCHUP HADR_STATE.