搞了好几天总算是把HADR弄好啦，下面分享下 系统环境： OS:SUSE 11sp1-bit DB: db29.7.0.5 DB2server1:192.168.5.151 db2in
搞了好几天总算是把HADR弄好啦，，下面分享下
系统环境：
OS:SUSE 11sp1-bit
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= LINUXAMD
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= LINUXAMD
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
