代码改变世界

【HADR】搭建实战

2015-01-29 15:57  Ivan的一亩三分地  阅读(729)  评论(0编辑  收藏  举报

Summary:

简单的HADR,只用一台虚拟机,两个实例间搭建。工作量不大,一般5分钟左右能够完成。
步骤:
1.设定归档模式
2.使用备份建立standby数据库
3.设定hadr相关的参数
4.启动并测试

 

测试环境:

OS:Red hat 6
Server: 192.168.122.17  
Primary instance: hadr1pri 
Primary service/port: 43099  
Standby instance: hadr1std 
Standby service/port: 44099  
DB name: ORG
--注意,切勿使用与DBM SVCENAME 太接近的端口,因为实例会默认使用那端口之后的连续几个端口,所以应尝试更远一些的端口

 

vi /etc/services
DB2_hadrpri     60008/tcp 
DB2_hadrpri_1   60009/tcp 
DB2_hadrpri_2   60010/tcp 
DB2_hadrpri_END 60011/tcp 
DB2_hadrstd     60012/tcp 
DB2_hadrstd_1   60013/tcp 
DB2_hadrstd_2   60014/tcp 
DB2_hadrstd_END 60015/tcp
43099    43099/tcp
44099   44099/tcp

 

搭建开始

step 1: 创建组和用户

create primary instance id
To create groups on Linux operating systems, enter the following commands:

groupadd -g 1999 db2iadm2
groupadd -g 1998 db2fadm2
groupadd -g 1997 dasadm2

Create users for each group:

useradd -u 1014 -g db2iadm2 -m -d /home/hadrpri  hadrpri 
useradd -u 1013 -g db2fadm2 -m -d /home/hadrfenc  hadrfenc
useradd -u 1012 -g dasadm2 -m -d /home/hadrdas hadrdas

create standby instance id
To create groups on Linux operating systems, enter the following commands:

useradd -u 1020 -g db2iadm2 -m -d /home/hadr1std  hadr1std
useradd -u 1019 -g db2fadm2 -m -d /home/hadr1sfc  hadr1sfc
useradd -u 1018 -g dasadm2 -m -d /home/std1das std1das

step 2:创建实例

cd /opt/IBM/db2/V9.7/instance 
./db2icrt -s ese -u hadr1fc  hadr1pri 
./db2icrt -s ese -u hadr1sfc hadr1std

 

step3: 主节点归档模式设置,备节点使用restore方式创建数据库,主备节点HADR设置

--在Primary: 

--启用归档模式
--启用LOGINDEXBUILD,以便日志有关索引的操作   

db2 update db cfg for org  using LOGRETAIN on
db2 update db cfg for org using LOGINDEXBUILD on 

--Backup DB
db2 backup db org to /data

 

----在standby

--启动数据

--使用primary db 的备份文件进行数据库恢复

db2start 
db2 restore db org from   /home/hadrstd  taken at 20150120040252 on /home/hadrstd dbpath on /home/hadrstd

--这时候standby的数据库应该是roll-forward pedning的状态,切勿手动roll-forward 
   db2 connect to org
   SQL1117N  A connection to or activation of database "SAMPLE" cannot be made  
   because of ROLL-FORWARD PENDING.  SQLSTATE=57019 

--在Primary: 

db2 update dbm cfg using svcename DB2_hadrpri

db2 update db cfg for org using hadr_local_svc  41099
db2 update db cfg for org using hadr_remote_host  192.168.122.17
db2 update db cfg for org using hadr_local_host  192.168.122.17
db2 update db cfg for org using hadr_remote_svc 42099
db2 update db cfg for org using hadr_remote_inst  hadrstd
db2 update db cfg for org using hadr_syncmode sync

 
db2 connect to org
db2 quiesce database immediate force connections  
db2 unquiesce database  
db2 connect reset


 
--在Standby: 

db2 update dbm cfg using svcename DB2_hadrstd
db2 update db cfg for org using hadr_remote_host  192.168.122.17
db2 update db cfg for org using hadr_local_svc  42099
db2 update db cfg for org using hadr_local_host  192.168.122.17
db2 update db cfg for org using hadr_remote_svc  41099
db2 update db cfg for org using hadr_remote_inst  hadrpri
db2 update db cfg for org using hadr_syncmode sync
db2 update db cfg for org using HADR_TIMEOUT 3
db2 update db cfg for org using HADR_PEER_WINDOW 120

 

4.启动并测试

    --先启动standby 
    --在Standby: 
    db2 start hadr on db sample as standby 
    DB20000I  The START HADR ON DATABASE command completed successfully. 
     
    --这时候应该是remote catchup pending的状态: 
  

[hadrstd@oc0644314035 db2dump]$ db2pd -d org -hadr

Database Partition 0 -- Database ORG -- Standby -- Up 0 days 00:00:05 -- Date 2015-01-20-22.00.09.169952

HADR Information:
Role    State                SyncMode   HeartBeatsMissed   LogGapRunAvg (bytes)
Standby RemoteCatchupPending Sync     0                  0                  

ConnectStatus ConnectTime                           Timeout  
Disconnected  Tue Jan 20 22:00:05 2015 (1421809205) 120      

LocalHost                                LocalService     
192.168.122.17                           42099            

RemoteHost                               RemoteService      RemoteInstance   
192.168.122.17                           41099              hadrstd          

PrimaryFile  PrimaryPg  PrimaryLSN       
S0000000.LOG 0          0x0000000002728010

StandByFile  StandByPg  StandByLSN         StandByRcvBufUsed


     
    --再启动Primary 
    --在Primary: 
[hadr1pri@oc0644314035 data]$ db2 start hadr on database org as primary
DB20000I  The START HADR ON DATABASE command completed successfully.

[hadrstd@oc0644314035 ~]$ db2pd -d org -hadr

Database Partition 0 -- Database ORG -- Standby -- Up 0 days 00:11:53 -- Date 2015-01-21-00.59.58.732725

HADR Information:
Role    State                SyncMode   HeartBeatsMissed   LogGapRunAvg (bytes)
Standby Peer                 Sync     0                  0                  

ConnectStatus ConnectTime                           Timeout  
Connected     Wed Jan 21 00:48:39 2015 (1421819319) 3        

PeerWindowEnd                         PeerWindow
Wed Jan 21 01:01:57 2015 (1421820117) 120      

LocalHost                                LocalService     
192.168.122.17                           42099            

RemoteHost                               RemoteService      RemoteInstance   
192.168.122.17                           41099              hadrpri          

PrimaryFile  PrimaryPg  PrimaryLSN       
S0000001.LOG 0          0x0000000002728010

StandByFile  StandByPg  StandByLSN         StandByRcvBufUsed
S0000001.LOG 0          0x0000000002728010 0% 

 

    --可以看到一旦Primary也起来了,hadr的状态就会变成peer 

--这时候尝试手动归档,看日志是否能够顺利传递到standby 
[hadr1pri@oc0644314035 data]$  db2 archive log for DB sample
DB20000I  The ARCHIVE LOG command completed successfully.
     
    --在Standby进行role 切换

[hadrstd@oc0644314035 ~]$ db2 takeover hadr on database org
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.

[hadrstd@oc0644314035 ~]$ db2pd -d org -hadr

Database Partition 0 -- Database ORG -- Active -- Up 0 days 00:12:16 -- Date 2015-01-21-01.00.21.098460

HADR Information:
Role    State                SyncMode   HeartBeatsMissed   LogGapRunAvg (bytes)
Primary Peer                 Sync     0                  0                  

ConnectStatus ConnectTime                           Timeout  
Connected     Wed Jan 21 00:48:39 2015 (1421819319) 3        

PeerWindowEnd                         PeerWindow
Wed Jan 21 01:02:20 2015 (1421820140) 120      

LocalHost                                LocalService     
192.168.122.17                           42099            

RemoteHost                               RemoteService      RemoteInstance   
192.168.122.17                           41099              hadrpri          

PrimaryFile  PrimaryPg  PrimaryLSN       
S0000001.LOG 0          0x0000000002728010

StandByFile  StandByPg  StandByLSN       
S0000001.LOG 0          0x0000000002728010

 

References

搭建案例:

http://guoyanxi.iteye.com/blog/1173906

http://blog.csdn.net/dream19881003/article/details/7417285

 

自己总结的ISSUES

http://www.cnblogs.com/DBA-Ivan/p/4260264.html

 

Issues:

https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014850278
http://bytes.com/topic/db2/answers/496482-unable-start-hadr-reason-code-7-a

http://www.dbforums.com/showthread.php?1665366-DB2-9-7-HADR-setup