DB2 9.7 HADR with TSA - Part 06 - Configuring DB2 9.7 HADR between Primary and Standby

In this blog i will present step to configure non-Cluster DB2 9.7 HADR configuration. Following topics are covered in this blog
 a) Pre-Configuration Details
b) Setting up non-cluster DB2 9.7 HADR
c) Manual Starting/Stopping HADR steps
d) Manul Failover/Failback Steps
e) Force Failover/Failback Steps



Part 0 : DB2 9.7 HADR with TSA Part 00 - Introduction

Part 1 : Installing and configuring VMWare environment

Part 2 : Installing IBM tools for querying and monitoring purpose

Part 3 : Installing IBM DB2 9.7 database

Part 4 : Installing DB2 9.7 Fix-pack

Part 5 : Creating Instance and database

A) Pre-Configuation Details

Item #	Description	Command
System On which command will be executed =>		Primary	Standby
1	IP Address and Hostname	a) Public Network IP => 192.168.5.22 Hostname => Mumbai b) Private Network (Optional) IP => 192.168.5.23 Hostname => priv01	a) Public Network IP => 192.168.5.33 Hostname => London b) Private Network (Optional) IP => 192.168.5.34 Hostname => priv02
2	Virtual IP (Configured by TSA)	IP => 192.168.5.55 Virtual Hostname => newyork
3	Create New Mount point (Repet step a to Step i for each new hard disk added to the virtual machine)	a) Add two hard disk to virtual machine and restart the virtual machine b) Log on to the OS to see the hard disk is added ls /dev/sd* -- All the hard disk which show /dev/sd[alphabet] are newly added hard disk. -- All the hard disk which shows /dev/sd[albhabet][number] are the existing hard disk on the server. c) Partition the hard disk using FDISK. The sequence of command to FDISK are "n", "p", "1", "Return(Enter Button on Keyboard)", "Return(Enter Button on Keyboard)","p" and "w" d) New partition get added to the list ls /dev/sd* e) Create the physical Volumne pvcreate /dev/sd[alphabet][number] where : alphabet = Character of new added disk Number = Number of the newly formated disk For Example, pvcreate /dev/sdc1 f) Create the file system on the volumne mkfs -t ext3 -m 1 -v /dev/sd[alphabet][number] For example:- mkfs -t ext3 -m 1 -v /dev/sdc1 g) Check the new FS created tune2fs -l /dev/sd[alphabet][number] For example;- tune2fs -l /dev/sdc1 h) Create the mount directory mkdir /db2fs01 i) Modify the /etc/fstab file to include following line /dev/sdc1 /db2fs01 ext3 defaults 1 1
4	Check OS details	a) OS Version oslevel -a b) Patch details c) Kernal Level
5	Check the Network Details	a) Public IP Address b) Private IP Address c) Virtual IP Address
6	Check DB2 details	a) Check DB2 version db2level b) Mount point for DATA c) Mount point for Logs d) Mount point for archive logs

B) Setting up non-Cluster HADR

Item #	Description	Command
System On which command will be executed =>		Primary	Standby
1	Public Network and hostname	Network device : eth0 Hostname : Mumbai IP => 192.168.5.22	Network device : eth0 Hostname : London IP => 192.168.5.33
2	Virtual IP	Hostname => newyork Virtual IP => 192.168.5.55
3	Add the public/Virtual IP details to /etc/hosts file	192.168.5.22 mumbai 192.168.5.33 london 192.168.5.55 newyork	192.168.5.22 mumbai 192.168.5.33 london 192.168.5.55 newyork
4	Check the connectivity between nodes	ping mumbai ping london	ping mumbai ping london
5	Create DB2 Instance Owner and DB2 Fence Owner Group GID and GROUP name must be same on both node	groupadd -g 1003 db2iadm1 groupadd -g 102 db2fadm1	groupadd -g 1003 db2iadm1 groupadd -g 102 db2fadm1
6	Create DB2 Instance Owner user and DB2 fence owner user	useradd -u 21000 -g 1003 -d /home/db2inst1 -m -c "DB2 Instance Owner user" db2inst1 useradd -u 108 -g 102 -d /home/db2fenc1 -m -c "DB2 Fence Owner user" db2fenc1	useradd -u 21000 -g 1003 -d /home/db2inst1 -m -c "DB2 Instance Owner user" db2inst1 useradd -u 108 -g 102 -d /home/db2fenc1 -m -c "DB2 Fence Owner user" db2fenc1
7	Check DB2 ports in /etc/services files.Must be same on each of the nodes	a)DB2 TCPIP Communication port 6000 b) DB2 HADR port DB2_HADR_MUM=50011/tcp DB2_HADR_LON=50012/tcp	a)DB2 TCPIP Communication port 6000 b) DB2 HADR port DB2_HADR_MUM=50011/tcp DB2_HADR_LON=50012/tcp
8	Check ~/sqllib/db2nodes.cfg under DB2 instance owner user	0 mumbai 0	0 london 0
9	Create directory a) Backup b) Archive log	chmod 777 /db2fs mkdir /db2fs/db2bkp mkdir /db2fs/db2arch mkdir /db2fs/db2log	chmod 777 /db2fs mkdir /db2fs/db2bkp mkdir /db2fs/db2arch mkdir /db2fs/db2log
10	Update DB2 parameters	a) DB2 TCPIP communication parameters db2set DB2COMM=tcpip db2 "update dbm cfg using svcename 60000" db2stop force;db2start b) Enable DB2 to use SSH password less communication db2set -i db2inst1 DB2RSHCMD=/usr/bin/ssh db2stop force;db2start c) Update DB2 HADR database parameters Note Parameter values are differenct on each node update db cfg for sample using logarchmeth1 'DISK:/db2fs/db2arch' update database configuration for sample using LOGINDEXBUILD ON update db cfg for Sample using hadr_local_host mumbai update db cfg for Sample using hadr_remote_host london update db cfg for Sample using hadr_local_svc DB2_HADR_MUM update db cfg for Sample using hadr_remote_svc DB2_HADR_LON update db cfg for Sample using hadr_remote_inst db2inst1 update db cfg for Sample using hadr_timeout 120 update db cfg for Sample using hadr_peer_window 120 update db cfg for Sample using hadr_syncmode nearsync	a) DB2 TCPIP communication parameters db2set DB2COMM=tcpip db2 "update dbm cfg using svcename 60000" db2stop force;db2start b) Enable DB2 to use SSH password less communication db2set -i db2inst1 DB2RSHCMD=/usr/bin/ssh db2stop force;db2start c) Update DB2 HADR database parameters Note Parameter values are differenct on each node. This values are set after RESTORING the database on STANDYBY update db cfg for Sample using hadr_local_host london update db cfg for Sample using hadr_remote_host mumbai update db cfg for Sample using hadr_local_svc DB2_HADR_LON update db cfg for Sample using hadr_remote_svc DB2_HADR_MUM update db cfg for Sample using hadr_remote_inst db2inst1 update db cfg for Sample using hadr_timeout 120 update db cfg for Sample using hadr_peer_window 120 update db cfg for Sample using hadr_syncmode nearsync
11	Backup the database	backup database sample online to /db2fs01/db2bkp	No Operation
12	Copy the backup image to Standby machine	scp SAMPLE.0.db2inst1.NODE0000.CATN0000.20101021100242.001 db2inst1@hadrtest2:/home/db2inst1/bkp	No Operation
13	Create new database on standby from the offline backup	No Operation	restore database sample replace history file This will set the database in rollforward pedning state as we created the database from online full backup of primary database after enabling primary database for archival logging. It will be in ROLLFORWARD PENDING STATE. No changes needed. DO NOT TAKE THE DATABASE OUT OF ROLL FORWARD PENDING STATE
14	Start HADR on Standby	No Operation	start hadr on database sample as standby
15	Start HADR on Primary	start hadr on database sample as primary	No Operation
16	Check the HADR Status (must be same on both nodes)	db2pd -hadr -db sample HADR State = Peer	db2pd -hadr -db sample HADR State = Peer
17	Check if HADR is working	1) Archive current log archive log for db sample 2) Check the first log sequence get db cfg for sample | grep -i first 3) Check if the archive log file is shifted to Standby. (May take some time) db2pd -hadr -db sample	1) Archive current log archive log for db sample 2) Check the first log sequence get db cfg for sample | grep -i first 3) Check if the archive log file is shifted to Standby. (May take some time) db2pd -hadr -db sample

C) Manual Starting/Stopping HADR Steps

Item #	Description	Command
Starting HADR steps		Primary	Standby
1	Start Instance on Primary/Standby	db2start	db2start
2	Start HADR on Standby machine	No Operation	start hadr on database sample as standby
3	Start HADR on Primary Machine	start hadr on database sample as primary	No Operation
4	Check if DB2 HADR is running	db2pd -hadr -db sample	db2pd -hadr -db sample
Stopping HADR steps		Primary	Standby
1	Stop HADR on database	stop hadr on database sample	No Operation
2	Deactivate Database	deactivate database sample	No Operation
3	Stop instance	db2stop force	No Operation
4	Deactivate the standby database	No Operation	deactivate database sample
5	Stop HADR on database	No Operation	stop hadr on database sample
6	Stop Instance	No Operation	db2stop force

D) Manul Failover/Failback Steps

Item #	Description	Command
Failover Operation		Primary	Standby
1	Identify the Standby node	db2pd -db sample -hadr	db2pd -db sample -hadr
2	Execute takeover command on standby	No Operation	takeover hadr on database sample
Failback Operation		Primary	Standby
1	Identify the new Standby node	db2pd -db sample -hadr	db2pd -db sample -hadr
2	Execute takeover command on old Primary which is now new Standby after failover	takeover hadr on database sample	No Operation

E) Force Failover

Item #	Description	Command
Force Failover (Test Scenario)		Primary	Standby
1	Kill the DB2 instance procee to simulate Test Scenario	ps -eaf | grep -i db2sysc kill -9 where PID = Second Column	No Operation
2	Check the status of the standby	No Operation	db2pd -hadr -db sample HADR State will be in Disconnected state
3	Execute the force failover on Standby	No Operation	takeover hadr on database sample by force
4	Bring the primary Server online	db2start start hadr on database sample as standby	No Operation
5	Failback to Primary machine	takeover hadr on database sample	No Operation
6	Check HADR status	db2pd -hadr -db sample	db2pd -hadr -db sample