DB2 high availability setup made easy (DB2 HADR)

DB2 HADR Configuration :

Steps to start HADR service for DB2 :-

Firstly Lets Start with creating a database in DB using commands:

db2 create database ISIMDB using codeset UTF-8 territory us
db2 connect to ISIMDB user dbadmin_name using dbadmin_password
db2 create bufferpool ENROLEBP size automatic pagesize 32k
db2 update db cfg for ISIMDB using logsecond 12
db2 update db cfg for ISIMDB using logfilsiz 10000
db2 update db cfg for ISIMDB using auto_runstats off
db2 disconnect current

The below command is used to give complete rights to an user on that database (In this example i am using a user called timuser)

GRANT DBADM WITH DATAACCESS WITH ACCESSCTRL, SECADM, CONNECT, QUIESCE_CONNECT, LOAD, EXPLAIN, CREATETAB, IMPLICIT_SCHEMA, BINDADD, CREATE_NOT_FENCED_ROUTINE, CREATE_EXTERNAL_ROUTINE, SQLADM, WLMADM ON DATABASE TO USER TIMUSER;
COMMIT;

Click on start menu>>go to run and type db2cmd.

Run these commands in the new db2 command prompt.

ON THE PRIMARY Machine:

                                                                        

1. Connect to the Database .

  db2 connect to <DBNAME>

2. Enable it for log archiving.

  db2 update db cfg for <DBNAME> using LOGRETAIN recovery

  db2 update db cfg for <DBNAME> using LOGARCHMETH1 DISK:D:\

3. Take an offline backup.

   db2 "backup database <DBNAME> to NUL"

4. Setting up HADR cfg parameters on Primary database.

   db2 update db cfg for <DBNAME> using HADR_LOCAL_HOST <IP ADDRESS OF PRIM>

   db2 update db cfg for <DBNAME> using HADR_LOCAL_SVC <PORT # on PRIM>

   db2 update db cfg for <DBNAME> using HADR_REMOTE_HOST <IP ADDRESS OF STNDBY>

   db2 update db cfg for <DBNAME> using HADR_REMOTE_SVC <PORT # on STNDBY>

   db2 update db cfg for <DBNAME> using HADR_REMOTE_INST <INSTNAME OF STNDBY>

   db2 update db cfg for <DBNAME> using LOGINDEXBUILD ON

5. Take an offline backup to be used for setting HADR.

   db2 "backup database <DBNAME> to C:\BACKUP"

ON THE STANDBY MACHINE:

Ensure both the servers are on the same db2level so that a mismatch situation does not occur. Run "db2level" command on both the servers to check whether they are on the same DB2 Version and Fix Pack.

6. FTP the backup image (from the primary machine) to the STANDBY MACHINE

7. db2 "restore database DBNAME from C:\BACKUP"

8. Setting up HADR cfg parameters on standby database"

   db2 update db cfg for <DBNAME> using HADR_LOCAL_HOST <IP ADDRESS ON STANDBY>

   db2 update db cfg for <DBNAME> using HADR_LOCAL_SVC <PORT # ON STANDBY>

   db2 update db cfg for <DBNAME> using HADR_REMOTE_HOST <IP ADDRESS ON PRIM>

   db2 update db cfg for <DBNAME> using HADR_REMOTE_SVC <PORT # ON PRIM>

   db2 update db cfg for <DBNAME> using HADR_REMOTE_INST <INSTNAME ON PRIM>

 

9. db2 start hadr on database <DBNAME> as standby

ON THE PRIMARY MACHINE:

10. Starting up HADR on the primary server

   db2 start hadr on database <DBNAME> as primary

   --Verifing HADR is up and running"

   db2pd -db <DBNAME> -hadr

==================================================================

Here's the steps on switching roles (PRIMARY/STANDBY) between the two   

machine (CM01 and CM11).

1. ON PRIMARY (CM01): db2 connect to <dbname>

2. ON PRIMARY (CM01) db2 "create table tab1 (col1 int)

3. ON PRIMARY (CM01): db2 "insert into tab1 values (1) -insert 20 rows

4. ON PRIMARY (CM01): powered down the Primary --> db2stop force

5. ON the standby (CM11) -> db2 takeover hadr on database <dbname> by force

6. ON the STANDBY instance on CM11 (DB2) is now the primary

7. ON the standby CM11: db2pd -db <dbname> -hadr (the ROLE should state: PRIMARY)

8. ON the standby CM11: db2 connect to <dbname>

9. ON the standby CM11: db2 "select * from tab1" -You should see the 20 rows inserted

10. ON the standby CM11: db2 "create table tab2 (col1 int)"

11. ON the standby CM11 db2 "insert into tab2 values (1) -insert about 20 rows

12. ON PRIMARY CM01: db2 start hadr on database <dbname> as standby

13. ON PRIMARY CM01: db2pd -db <dbname> -hadr (the ROLE should state: STANDBY)

14. ON PRIMARY CM01: db2 takeover hadr on database <dbname>

15. ON PRIMARY CM01: db2pd -db <dbname> -hadr (the ROLE should state: PRIMARY)

16. ON PRIMARY CM01: db2 "select * from tab2" -you should be able to see the 20 rows inserted

17. ON the standby CM11: db2pd -db <dbname> -hadr (the ROLE should state; STANDBY) 

Note: Hostname of the HADR pair cannot be the same on both the servers.

    DB2 Issues:

·         Unable to start Hadr on standby/primary.

1.     Deactivate the database by using the command db2 deactivate db <db name>.

2.     Then start hadr as standby by using the command db2 start hadr on db <db name> as standby/primary.

·         Unable to switch standby database to primary.

1.     Deactivate the database by using the command db2 deactivate db <db name>.

2.     Stop the hadr on primary database usint the command db2 stop hadr on db <db name>.

3.     Then start hadr as standby by using the command db2 start hadr on db <db name> as primary by force.

·         Unable to takeover hadr on standby.

1.     Deactivate the database by using the command db2 deactivate db <db name>.

2.     Then start hadr as standby by using the command db2 takeover hadr on db <db name> by force.

·         Roll forward pending error on standby.

1.     Stop Hadr on standby by using the command db2 stop hadr on db <db name>.

2.     Type the command db2 rollforward on db <db name> complete.

3.     Then take a backup of the primary database and copy it to the secondary machine and then restore the database on the standby.

4.     Then start hadr as standby by using the command db2 start hadr on db <db name> as standby.

·         When both the database shows status as Disconnected.

1.     Stop hadr on standby database using the command db2 stop hadr on db <db name>.

2.     Then take a backup of the primary database and copy it to the secondary machine and then restore the database on the standby.

3.     Then start hadr as standby by using the command db2 start hadr on db <db name> as standby.

·         Unable to stop Hadr on standby:

1.     Deactivate the database by using the command db2 deactivate db <db name>.

2.     Then stop hadr as standby by using the command db2 stop hadr on db <db name>.

·         Unable to switch primary database to standby.

1.     Stop the hadr on primary database usint the command db2 stop hadr on db <db name>.

2.     Go to start menu>> open run console and type services.msc.

3.     In the services management console stop the db2 instance and stop the service named DB2DAS.

4.     Now start the DB2DAS service and the db2 instance from the console.

5.     Now type the command db2 deactivate db <db name>.

6.     Activate the database using command db2 activate db <db name>.

7.     Start the hadr on database  db2 start hadr on db <db name> as primary.