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.