Linux DB2 HADR 双机搭建

搞了好几天总算是把HADR弄好啦，下面分享下系统环境：OS:SUSE 11sp1-64bitDB: db29.7.0.5 DB2server1:192.168.5.151 db2inst1DB2server2:192.168.5.152 db2inst2步骤：DB2server1上操作：db2inst1@DB2server1:~> db2 create database oga;db2inst1@DB2server1:~> db2 get dbm cfg | grep SVCdb2inst1@DB2server1:~> db2set db2comm=tcpipdb2inst1@DB2server1:~> db2 update dbm cfg usingSVCENAME 50001db2inst1@DB2server1:~> db2 update db cfg for ogausing logretain ondb2inst1@DB2server1:~> db2 update db cfg for ogausing trackmod on;db2inst1@DB2server1:~> db2 update db cfg for ogausing logindexbuild on;db2inst2@DB2server1:~> db2 backup db ogadb2inst1@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 44455DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_remote_host 192.168.5.152DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_local_host 192.168.5.151DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_remote_svc 33344DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_remote_inst db2inst2DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst1@DB2server1:~> db2 update db cfg for ogausing hadr_syncmode nearsyncDB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2 UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USINGHOSTNAME 192.168.5.151 PORT 50001db2inst1@DB2server1:~> db2 get db cfg for oga | grep-i hadr DB2server2上的操作db2inst1@DB2server2:~> db2 create database oga;db2inst1@DB2server2:~> db2 get dbm cfg | grep SVCdb2inst1@DB2server2:~> db2set db2comm=tcpipdb2inst1@DB2server2:~> db2 update dbm cfg usingSVCENAME 50001db2inst1@DB2server2:~> db2 update db cfg for ogausing logretain ondb2inst1@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.152DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_local_svc 44455DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_remote_host 192.168.5.151DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_remote_svc 33344DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst2@DB2server2:~> db2 update db cfg for ogausing hadr_syncmode nearsyncDB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2inst2@DB2server2:~> db2 update db cfg for ogausing HADR_REMOTE_INST db2inst1DB20000I TheUPDATE DATABASE CONFIGURATION command completed successfully.db2 UPDATE ALTERNATE SERVER FOR DATABASE SAMPLE USINGHOSTNAME 192.168.5.152 PORT 50001db2inst1@DB2server2:~> db2 get db cfg for oga | grep-i hadrdb2inst2@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 db2instdb2inst2@DB2server2:/opt/bak> db2 connect to sampleb2inst1@DB2server1:/opt/bak> db2 grant dbadm ondatabase to db2inst2DB20000I The SQLcommand completed successfully.db2inst1@DB2server1:/opt/bak> db2 grant secadm ondatabase to db2inst2DB20000I 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启动standbydb2inst2@DB2server2：~>db2 deactivate database sampleSQL1496W Deactivate database is successful, but the database was not activated.是断开关闭数据库db2inst2@DB2server2:~> db2 start hadr on db oga asstandbySQL1032N Nostart database manager command was issued.注意：此时standby不可以连接数据库，否则会造成主库不一致的。db2inst2@DB2server2:~> db2 get snapshot for db onoga | grep Role Role = Standbydb2inst2@DB2server2:~> 启动主机db2inst1@DB2server1:/opt/bak> db2 activate db ogaDB20000I TheACTIVATE DATABASE command completed successfully.db2inst2@DB2server1:~> db2 start hadr on db oga as primarydb2inst1@DB2server1:~> db2 get snapshot for db onsample | grep Role Role = Primary 验证两台机的状态：db2inst1@DB2server1:~> db2 get snapshot for db onoga | grep stateCommit statements attempted = 16Rollback statements attempted = 0Dynamic statements attempted = 479Static statements attempted = 30Failed statement operations = 0Select SQL statements executed = 152Xquery statements executed = 0Update/Insert/Delete statements executed = 9DDL statements executed = 0 停止db2inst2@DB2server2:~> db2 deactivate database ogaDB20000I TheDEACTIVATE DATABASE command completed successfully.db2inst2@DB2server2:~> db2 stop hadr on database ogaDB20000I TheSTOP HADR ON DATABASE command completed successfully.db2inst2@DB2server1:~> db2 stop hadr on database ogaDB20000I 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 fileDatabase files closed = Not CollectedFile number of first active log = Not applicableFile number of last active log = Not applicableFile number of current active log = 12File number of log being archived = Not applicableRollforward 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 db2inst1DB20000I TheTAKEOVER HADR ON DATABASE command completed successfully.db2inst2@DB2server2:/opt/bak> db2 connect to oga userdb2inst1 using db2inst1db2inst2@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 = OGADatabase path =/data_inst1/db2inst1/db2inst1/NODE0000/SQL00001/Input database alias = OGADatabase status = StandbyCatalog database partition number = 0Catalog network node name =Operating system running at database server= LINUXAMD64Location of the database = LocalFirst database connect timestamp = 2012-03-28 15:21:16.354049Last reset timestamp =Last backup timestamp = 2012-03-2715:20:54.000000Snapshot 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 = OGADatabase path =/data_inst2/db2inst2/db2inst2/NODE0000/SQL00001/Input database alias = OGADatabase status = ActiveCatalog database partition number = 0Catalog network node name =Operating system running at database server= LINUXAMD64Location of the database = LocalFirst database connect timestamp = 03/28/2012 15:20:41.342208Last reset timestamp =Last backup timestamp =Snapshot timestamp = 03/28/201216:27:38.538201 Number of automatic storage paths = 1ORA-01618: redo thread 2 is not enabled - cannot mount 错误Oracle 11g 中的SCN多长时间增加一次相关资讯 DB2

RHEL5 下安装 DB2 V9.7 数据库（今 09:39）
DB2常用脚本整理（01月19日）
Linux下DB2SQL1024N A database （01月12日）

DB2中REVERSE函数的实现（01月19日）
使用 IBM Data Studio 创建和管理（01月12日）
TOAD连接DB2报错SQL1460N解决（01月12日）

本文评论查看全部评论（0）