DB2 HADR Commands
DB2 HADR and HACMP Commands
Example script for setting up HADR
Update configuration parameters on primary database -
UPDATE DB CFG FOR DB SAMPLE USING LOGINDEXBUILD ON
UPDATE DB CFG FOR DB SAMPLE USING INDEXREC RESTART
Copy backup image from primary to standby system.
Restore database on standby system
RESTORE DATABASE SAMPLE REPLACE HISTORY FILE WITHOUT PROMPTING
OPTIONAL.....Configure databases for client reroute - Primary
UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USING HOSTNAME <Standby ip address> PORT <inst port>
OPTIONAL.....Configure databases for client reroute -Standby
UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USING HOSTNAME <prim ip address> PORT <inst port>
Edit /etc/services file and create a dedicated HADR port on both systems. Admin access required.
To add HADR line on both primary and standby system On Windows:
notepad %SystemRoot%\system32\drivers\etc\services
Update HADR configuration parameters on primary database -
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST <prim ip>
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC DB2_HADR1
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST <sb ip>
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC DB2_HADR1
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST DB2
UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC
UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120
Enable cfg changes by forcing off all connections
CONNECT TO SAMPLE
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
UNQUIESCE DATABASE
CONNECT RESET
Update HADR configuration parameters on standby database - --
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_HOST <sb ip>
UPDATE DB CFG FOR SAMPLE USING HADR_LOCAL_SVC DB2_HADR_14
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_HOST COL27VM
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_SVC DB2_HADR_13
UPDATE DB CFG FOR SAMPLE USING HADR_REMOTE_INST DB2
UPDATE DB CFG FOR SAMPLE USING HADR_SYNCMODE NEARSYNC
UPDATE DB CFG FOR SAMPLE USING HADR_TIMEOUT 120
Start HADR on standby database first
=>db2 DEACTIVATE DATABASE SAMPLE
=>db2 START HADR ON DATABASE SAMPLE AS STANDBY
Start HADR on primary database
=>db2 DEACTIVATE DATABASE SAMPLE
=>db2 START HADR ON DATABASE SAMPLE AS PRIMARY
View status of HADR
=>db2pd -db <dbname> -hadr
Swap roles of Primary and Standby
=>db2 takeover hadr on db <dbname>
Takeover Primary operation on the standby db when the original primary is down
=>db2 takeover hadr on db <dbname> by force
Steps for setting up multiply Standby HADR in an existing HADR environment
On PRODDB22:
db2 "UPDATE DB CFG FOR DBNAME1 USING
HADR_TARGET_LIST 10.5.155.100:50112|10.7.44.100:50111
HADR_REMOTE_HOST 10.5.155.100
HADR_REMOTE_SVC 50112
HADR_LOCAL_HOST 10.5.155.200
HADR_LOCAL_SVC 50220
HADR_SYNCMODE nearsync
HADR_REMOTE_INST db2inst1"
On PRDDB_BK:
db2 "UPDATE DB CFG FOR DBNAME1 USING
HADR_TARGET_LIST 10.5.155.100:50112|10.5.155.200:50220
HADR_REMOTE_HOST 10.5.155.100
HADR_REMOTE_SVC 50112
HADR_LOCAL_HOST 10.7.44.100
HADR_LOCAL_SVC 50111
HADR_SYNCMODE superasync
HADR_REMOTE_INST db2inst1
HADR_REPLAY_DELAY 1800"
On PRODDB11
db2 "UPDATE DB CFG FOR DBNAME1 USING
HADR_TARGET_LIST 10.5.155.200:50220|10.7.44.100:50111
HADR_REMOTE_HOST 10.5.155.200
HADR_REMOTE_SVC 50220
HADR_LOCAL_HOST 10.5.155.100
HADR_LOCAL_SVC 50112
HADR_SYNCMODE nearsync
HADR_REMOTE_INST db2inst1"
On PRODDB11
db2 stop hadr on db dbnam1
PRODDB22
db2 start hadr on db dbname1 as standby
PRODDB-BK
db2 deactivate db dbname1
db2 stop hadr on db dbname1
db2 start hadr on db dbname1 as standby
PRODDB11
db2 start hadr on db dbname1 as primary
Steps for setting up HACMP active/passive
HACMP Active server
1. First create the file <instname>.db2nodes.cfg. in the /etc directory which is local to each server and will NOT be failed over to another server, on both servers.
2. Use the local hostname in the /etc/<instname>.db2nodes.cfg files on both servers.
3. Make sure permissions for file /etc/<instname>.db2nodes.cfg are set to 444 on both servers.
4. As Instance owner, create a link named db2nodes.cfg from the instance sqllib to this new /etc/<instname>.db2nodes.cfg file.
=>ln -fs /etc/<instname>.db2nodes.cfg /db2home/<instname>/sqllib/db2nodes.cfg
5. Make sure you create the <instname>.db2nodes.cfg on the same path on both servers, so that link will still work after failover.
6. Also make sure same TCP port is being used/reserved on both servers for the instance service port.
7. Remove db2 startup line from inittabs and provide your own db2 startup and stop scripts.......as root =>rmitab db2start
HACMP Standby server
1. as root. vi /etc/<instname>.db2nodes.cfg . chmod 444
permissions on /db2home directory should be 775 and owned by db2as:db2asgrp
Copy /etc/services db2 ports from Active server to /etc/services in Standby server.
Make a file called /var/db2/v81/profiles.reg and add instance names on seperate lines
Copy instance id's .profiles over to standby server.