Linux DB2 HADR 双机搭建

搞了好几天总算是把HADR弄好啦，下面分享下 系统环境： OS:SUSE 11sp1-64bit DB: db29.7.0.5 DB2server1:192.168.5.151 db2in

搞了好几天总算是把HADR弄好啦，，下面分享下

系统环境：

OS:SUSE 11sp1-64bit

DB: db29.7.0.5

 

DB2server1:192.168.5.151  db2inst1

DB2server2:192.168.5.152 db2inst2

步骤：

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','2009-12-5')"

db2inst1@DB2server1:~> db2 "insert into certvalues(2, 2, 'cert2','2010-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

 

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/

 

 

重定向恢复

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

db2 rollforward db oga stop  ——这个不需要执行，否则在启动备库的时候会提示SQL1767N  Start HADR cannot complete. Reason code ="1".

将表恢复到了db2inst1下面，保证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

启动standby

db2inst2@DB2server2：~>db2 deactivate database sample

SQL1496W Deactivate database is successful, but the database was not

activated.是断开关闭数据库

db2inst2@DB2server2:~> db2 start hadr on db oga asstandby

SQL1032N  Nostart database manager command was issued.

注意：此时standby不可以连接数据库，否则会造成主库不一致的。

db2inst2@DB2server2:~> db2 get snapshot for db onoga | grep Role

  Role                   = Standby

db2inst2@DB2server2:~>

启动主机

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

 

验证两台机的状态：

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

 

 

停止

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.

测试：

db2inst1@DB2server1:~> db2 "insert into orgvalues (5,'org5')"

DB20000I  The SQLcommand completed successfully.

备库查看

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, 0000000005374584

  Standby logposition(file, page, LSN) = S0000012.LOG, 76, 0000000005374584

接管主库

原来的主库可以停掉也可以不停

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.

查看原来主机的状态

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

原来备库的状态

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/201216:27:38.538201

 

Number of automatic storage paths          = 1