Dual-host setup for Linux DB2 HADR

After several days, I have completed HADR. Next, let's share it with you.

System Environment:

OS: SUSE 11sp1-64bit

DB: db29.7.0.5

 

DB2server1: 192.168.5.151 db2inst1

DB2server2: 192.168.5.152 db2inst2

Steps:

Operations on DB2server1:

Db2inst1 @ DB2server1: ~> Db2 create database oga;

Db2inst1 @ DB2server1: ~> Db2 get dbm cfg | grep SVC

Db2inst1 @ DB2server1: ~> Db2set db2comm = tcpip

Db2inst1 @ DB2server1: ~> Db2 update dbm cfg usingSVCENAME 50001

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing logretain on

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing trackmod on;

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing logindexbuild on;

Db2inst2 @ DB2server1: ~> Db2 backup db oga

Db2inst1 @ DB2server1: ~> Db2 "create tablecert (OrgID int not null, EntId int not null, certnum char (20) not null primarykey, issuedate date )"

Db2inst1 @ DB2server1: ~> Db2 "alter table certdata capture changes"

Db2inst1 @ DB2server1: ~> Db2 "create tableorg (OrgId int not null primary key, OrgName char (20) date capturechanges"

 

Db2inst1 @ DB2server1: ~> Db2 "insert into orgvalues (1, 'org1 ')"

Db2inst1 @ DB2server1: ~> Db2 "insert into orgvalues (2, 'org2 ')"

Db2inst1 @ DB2server1: ~> Db2 "insert into orgvalues (3, 'org3 ')"

Db2inst1 @ DB2server1: ~> Db2 "insert into certvalues (1, 2, 'cert1', '2017-12-5 ')"

Db2inst1 @ DB2server1: ~> Db2 "insert into certvalues (2, 2, 'cert2', '2014-3-5 ')"

Db2inst1 @ DB2server1: ~> Db2 "insert into certvalues (3, 2, 'cert3 ', current date )"

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing hadr_local_svc 44455

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing hadr_remote_host 192.168.5.152

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing hadr_local_host 192.168.5.151

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing hadr_remote_svc 33344

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing hadr_remote_inst db2inst2

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst1 @ DB2server1: ~> Db2 update db cfg for ogausing hadr_syncmode nearsync

DB20000I TheUPDATE database configuration command completed successfully.

Db2 update alternate server for database sample usinghostname 192.168.5.151 PORT 50001

Db2inst1 @ DB2server1: ~> Db2 get db cfg for oga | grep-I hadr

 

Operations on DB2server2

Db2inst1 @ DB2server2: ~> Db2 create database oga;

Db2inst1 @ DB2server2: ~> Db2 get dbm cfg | grep SVC

Db2inst1 @ DB2server2: ~> Db2set db2comm = tcpip

Db2inst1 @ DB2server2: ~> Db2 update dbm cfg usingSVCENAME 50001

Db2inst1 @ DB2server2: ~> Db2 update db cfg for ogausing logretain on

Db2inst1 @ DB2server2: ~> Db2 update db cfg for ogausing trackmod on;

Db2inst1 @ DB2server2: ~> Db2 update db cfg for ogausing logindexbuild on;

Db2inst1 @ DB2server1:/opt/bak> db2 backup db oga to/opt/bak

 

 

Db2inst1 @ DB2server2: ~> Db2 "create tablecert (OrgID int not null, EntId int not null, certnum char (20) not null primarykey, issuedate date )"

Db2inst1 @ DB2server2: ~> Db2 "alter table certdata capture changes"

Db2inst1 @ DB2server2: ~> Db2 "create tableorg (OrgId int not null primary key, OrgName char (20) date capturechanges"

Db2inst2 @ DB2server2: ~> Db2 update db cfg for ogausing hadr_local_host 192.168.5.152

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst2 @ DB2server2: ~> Db2 update db cfg for ogausing hadr_local_svc 44455

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst2 @ DB2server2: ~> Db2 update db cfg for ogausing hadr_remote_host 192.168.5.151

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst2 @ DB2server2: ~> Db2 update db cfg for ogausing hadr_remote_svc 33344

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst2 @ DB2server2: ~> Db2 update db cfg for ogausing hadr_syncmode nearsync

DB20000I TheUPDATE database configuration command completed successfully.

Db2inst2 @ DB2server2: ~> Db2 update db cfg for ogausing HADR_REMOTE_INST db2inst1

DB20000I TheUPDATE database configuration command completed successfully.

Db2 update alternate server for database sample usinghostname 192.168.5.152 PORT 50001

Db2inst1 @ DB2server2: ~> Db2 get db cfg for oga | grep-I hadr

Db2inst2 @ DB2server2:/opt/bak> cd/opt/bak/

 

 

Redirect recovery

Db2 restore db oga on/data_inst2/db2inst2/oga/dbpathon/data_inst2/db2inst2/into oga

Db2 rollforward db oga stop -- this does not need to be executed; otherwise, SQL1767N Start HADR cannot complete. Reason code = "1" will be prompted when the slave database is started ".

Restore the table to the db2inst1 directory. Make sure that the tables are displayed in db2inst2.

Db2inst2 @ DB2server2: ~> Db2 connect to sample userdb2inst1 using db2inst

Db2inst2 @ DB2server2:/opt/bak> db2 connect to sample

B2inst1 @ DB2server1:/opt/bak> db2 grant dbadm ondatabase to db2inst2

DB20000I The SQLcommand completed successfully.

Db2inst1 @ DB2server1:/opt/bak> db2 grant secadm ondatabase to db2inst2

DB20000I The SQLcommand completed successfully.

Db2inst2 @ DB2server2: ~> Db2 "select * from db2inst1. cert"

 

ORGID ENTID CERTNUM ISSUEDATE

----------------------------------------------------

1 2 cert1 12/05/2009

2 2 cert2 03/05/2010

3 2 cert3 03/23/2012

Start standby

Db2inst2 @ DB2server2: ~> Db2 deactivate database sample

SQL1496W Deactivate database is successful, but the database was not

Activated. Disconnected to close the database

Db2inst2 @ DB2server2: ~> Db2 start hadr on db oga asstandby

SQL1032N Nostart database manager command was issued.

Note: standby cannot connect to the database at this time. Otherwise, the master database may be inconsistent.

Db2inst2 @ DB2server2: ~> Db2 get snapshot for db onoga | grep Role

Role = Standby

Db2inst2 @ DB2server2: ~>

Start host

Db2inst1 @ DB2server1:/opt/bak> db2 activate db oga

DB20000I TheACTIVATE DATABASE command completed successfully.

Db2inst2 @ DB2server1: ~> Db2 start hadr on db oga as primary

Db2inst1 @ DB2server1: ~> Db2 get snapshot for db onsample | grep Role

Role = Primary

 

Verify the status of the two machines:

Db2inst1 @ DB2server1: ~> Db2 get snapshot for db onoga | grep state

Commit statements attempted = 16

Rollback statements attempted = 0

Dynamic statements attempted= 479

Static statements attempted = 30

Failed statement operations = 0

Select SQL statements executed= 152

Xquery statements executed = 0

Update/Insert/Delete statements executed = 9

DDL statements executed = 0

 

 

Stop

Db2inst2 @ DB2server2: ~> Db2 deactivate database oga

DB20000I TheDEACTIVATE DATABASE command completed successfully.

Db2inst2 @ DB2server2: ~> Db2 stop hadr on database oga

DB20000I TheSTOP hadr on database command completed successfully.

Db2inst2 @ DB2server1: ~> Db2 stop hadr on database oga

DB20000I TheSTOP hadr on database command completed successfully.

Test:

Db2inst1 @ DB2server1: ~> Db2 "insert into orgvalues (5, 'org5 ')"

DB20000I The SQLcommand completed successfully.

Standby database view

Db2inst2 @ DB2server2:/opt/bak> db2 get snapshot fordb on oga | grep-I file

Database files closed = Not Collected

File number of first active log = Not applicable

File number of last active log = Not applicable

File number of current active log = 12

File number of log being archived = Not applicable

Rollforward log file being processed = 7

Primary logposition (file, page, LSN) = S0000012.LOG, 76,000 records 005374584

Standby logposition (file, page, LSN) = S0000012.LOG, 76,000 records 005374584

Take over master database

The original master database can be stopped or stopped.

Db2inst2 @ DB2server2:/opt/bak> db2 takeover hadr ondatabase oga user db2inst1 using db2inst1

DB20000I TheTAKEOVER hadr on database command completed successfully.

Db2inst2 @ DB2server2:/opt/bak> db2 connect to oga userdb2inst1 using db2inst1

Db2inst2 @ DB2server2:/opt/bak> db2 "select * from org"

 

ORGID ORGNAME

-------------------------------

1org1

2org2

3org3

4 org4

5org5

 

5 record (s) selected.

View the status of the original host

Db2inst1 @ DB2server1: ~> Db2 get snapshot for db onoga | more

 

Database Snapshot

 

Database name = OGA

Database path =/data_inst1/db2inst1/db2inst1/NODE0000/SQL00001/

Input database alias = OGA

Database status = Standby

Catalog database partition number = 0

Catalog network node name =

Operating system running at database server = LINUXAMD64

Location of the database = Local

First database connect timestamp = 2012-03-28 15:21:16. 354049

Last reset timestamp =

Last backup timestamp = 2012-03-2715: 20: 54.000000

Snapshot timestamp = 2012-03-2816: 26: 47.497005

 

Number of automatic storage paths = 1

Status of the original slave Database

Db2inst2 @ DB2server2:/opt/bak> db2 get snapshot fordb on oga | more

 

Database Snapshot

 

Database name = OGA

Database path =/data_inst2/db2inst2/db2inst2/NODE0000/SQL00001/

Input database alias = OGA

Database status = Active

Catalog database partition number = 0

Catalog network node name =

Operating system running at database server = LINUXAMD64

Location of the database = Local

First database connect timestamp = 03/28/2012 15:20:41. 342208

Last reset timestamp =

Last backup timestamp =

Snapshot timestamp = 03/28/. 538201

 

Number of automatic storage paths = 1