Configuring HADR in DB2

Configuring HADR

1. db2level should be same on both the server.

Primary:

DB21085I  This instance or install (instance name, where applicable: "db2inst2")
uses "64" bits and DB2 code release "SQL10014" with level identifier "0205010E".
Informational tokens are "DB2 v10.1.0.4", "s140509", "IP23584", and Fix Pack "4".
Product is installed at "/opt/IBM/DB2".

Standby:

DB21085I  This instance or install (instance name, where applicable:"db2inst2")
uses "64" bits and DB2 code release "SQL10014" with level identifier "0205010E".
Informational tokens are "DB2 v10.1.0.4", "s140509", "IP23584", and Fix Pack "4".
Product is installed at "/opt/IBM/DB2".

2. Create database on Primary

Create a sample database or restore db from any offline backup

3. Enable  Archive logging mode

db2 "update db cfg using LOGARCHMETH1 'DISK:/datahome/db2inst2/archive_test_1'"

take offline backup after the archival logging mode is enabled.

4. Set the below DB cfg parameters on primary database

   db2 update db cfg for test_1 using HADR_LOCAL_HOST mopyz6160139.pssc.mop.fr.ibm.com
  
   Specifies the local host for HADR TCP/IP communication. Either a host
   name or an IP address can be used.
  
   db2 update db cfg for test_1 using HADR_LOCAL_SVC 60008

   Specifies the TCP/IP service name or port number for which the HADR
   process accepts connections on the local host. This port cannot be the
   same as the SVCENAME or SVCENAME +1 of the HADR instance.

   db2 update db cfg for test_1 using HADR_REMOTE_HOST mopyz6160202.pssc.mop.fr.ibm.com

   Specifies the TCP/IP host name or IP address of the remote HADR node.

   db2 update db cfg for test_1 using HADR_REMOTE_SVC 60008

   Specifies the TCP/IP service name or port number for which HADR
   process accepts connections on the remote node. This port cannot be the
   same as the SVCENAME or SVCENAME +1 of the remote instance.

   db2 update db cfg for test_1 using HADR_REMOTE_INST db2inst2

   Specifies the instance name of the remote server. Administration tools,
   such as the DB2 Control Center, use this parameter to contact the remote
   server.

   db2 update db cfg for test_1 using LOGINDEXBUILD ON

5. Take offline backup of database

   db2 backup db test_1

   Transfer backup file to standby server

6. Restore the database with the transferred backup.

   use normal restore process or redirect restore.

   Note: restored db will be rollforward state let it be

7. Setup the below HADR configuration parameters on standby
  
   db2 update db cfg for test_1 using HADR_LOCAL_HOST mopyz6160202.pssc.mop.fr.ibm.com
   db2 update db cfg for test_1 using HADR_LOCAL_SVC 60008
   db2 update db cfg for test_1 using HADR_REMOTE_HOST mopyz6160139.pssc.mop.fr.ibm.com
   db2 update db cfg for test_1 using HADR_REMOTE_SVC 60008
   db2 update db cfg for test_1 using HADR_REMOTE_INST db2inst2

8. Start HADR on standby

   db2 start hadr on database test_1 as standby

9. Start HADR on primary

    db2 start hadr on database test_1 as primary


10. db2pd -db test_1 -hadr (test HADR)

Database Member 0 -- Database TEST_1 -- Active -- Up 0 days 00:01:13 -- Date 2016-06-06-11.37.02.867729

                            HADR_ROLE = PRIMARY
                          REPLAY_TYPE = PHYSICAL
                        HADR_SYNCMODE = NEARSYNC
                           STANDBY_ID = 1
                        LOG_STREAM_ID = 0
                           HADR_STATE = PEER
                  PRIMARY_MEMBER_HOST = mopyz6160139.pssc.mop.fr.ibm.com
                     PRIMARY_INSTANCE = db2inst2
                       PRIMARY_MEMBER = 0
                  STANDBY_MEMBER_HOST = mopyz6160202.pssc.mop.fr.ibm.com
                     STANDBY_INSTANCE = db2inst2
                       STANDBY_MEMBER = 0
                  HADR_CONNECT_STATUS = CONNECTED
             HADR_CONNECT_STATUS_TIME = 06/06/2016 11:35:51.056868 (1465205751)
          HEARTBEAT_INTERVAL(seconds) = 30
                HADR_TIMEOUT(seconds) = 120
        TIME_SINCE_LAST_RECV(seconds) = 11
             PEER_WAIT_LIMIT(seconds) = 0
           LOG_HADR_WAIT_CUR(seconds) = 0.000
    LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
   LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
                  LOG_HADR_WAIT_COUNT = 0
SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 19800
SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380
            PRIMARY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 54751342817
            STANDBY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 54751342817
                  HADR_LOG_GAP(bytes) = 0
     STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 54751342817
       STANDBY_RECV_REPLAY_GAP(bytes) = 3735724
                     PRIMARY_LOG_TIME = 06/06/2016 11:15:09.000000 (1465204509)
                     STANDBY_LOG_TIME = 06/06/2016 11:15:09.000000 (1465204509)
              STANDBY_REPLAY_LOG_TIME = 06/06/2016 11:15:09.000000 (1465204509)
         STANDBY_RECV_BUF_SIZE(pages) = 4298
             STANDBY_RECV_BUF_PERCENT = 0
           STANDBY_SPOOL_LIMIT(pages) = 0
                 PEER_WINDOW(seconds) = 0
             READS_ON_STANDBY_ENABLED = N

11. TEST HADR

On primary database create table and insert rows like below

create table fep_data.test_hadr(id int,name varchar(20));

insert into fep_data.test_hadr values(4,'fgt')

stop db2 on primary.

on the standby server

db2 takeover hadr on database test_1 by force

Now stanby should be primary

db2pd -db test_1 -hadr (the ROLE should state: PRIMARY)

check the table that was created on primary. SHould be available here.