db2 hadr

主库 db2-test1 192.168.23.221 60000 secs
备库 db2-test2 192.168.23.222 60000 secs


1.设置主库
[db2inst1@db2-test1 ~]$ db2 connect to secs


   Database Connection Information


 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = SECS


[db2inst1@db2-test1 ~] db2 quiesce database immediate
DB20000I  The QUIESCE DATABASE command completed successfully.


[db2inst1@db2-test1 ~]$ db2 quiesce database immediate
DB20000I  The QUIESCE DATABASE command completed successfully.


[db2inst1@db2-test1 ~]$ db2 update db cfg for secs using logarchmeth1 "disk:/opt/archivelog"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, the database 
must be shutdown and reactivated before the configuration parameter changes 
become effective.


[db2inst1@db2-test1 ~]$ db2 update db cfg for secs using trackmod on
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, the database 
must be shutdown and reactivated before the configuration parameter changes 
become effective.


[可选]以下日志部分是为了解决SQL0964C  The transaction log for the database is full.
[db2inst1@dev05 ~]$ db2 update db cfg for secs using logprimary 20
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, the database 
must be shutdown and reactivated before the configuration parameter changes 
become effective.


[可选]
[db2inst1@dev05 ~]$ db2 update db cfg for secs using logsecond 5
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, the database 
must be shutdown and reactivated before the configuration parameter changes 
become effective.


[可选]
[db2inst1@dev05 ~]$ db2 update db cfg for secs using logfilsiz 2048
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, the database 
must be shutdown and reactivated before the configuration parameter changes 
become effective.


[可选]
[db2inst1@dev05 ~]$ db2 update db cfg for secs using logbufsz 1024
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification 
were not changed dynamically. For these configuration parameters, the database 
must be shutdown and reactivated before the configuration parameter changes 
become effective.


[db2inst1@db2-test1 ~]$ db2 unquiesce database
DB20000I  The UNQUIESCE DATABASE command completed successfully.


[db2inst1@db2-test1 ~]$ db2 update db cfg for secs using logindexbuild on
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[db2inst1@db2-test1 ~]$ db2 update db cfg for secs using indexrec restart
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


2.备份主库
[db2inst1@db2-test1 ~]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
[db2inst1@db2-test1 ~]$ db2 backup database secs to /home/db2inst1/dbbak/


Backup successful. The timestamp for this backup image is : 20151009230558


3.复制备份到备库
[db2inst1@db2-test1 dbbak]$ scp SECS.0.db2inst1.NODE0000.CATN0000.20151009230558.001  db2inst1@db2-test2:/home/db2inst1/dbbak
db2inst1@db2-test2's password: 
SECS.0.db2inst1.NODE0000.CATN0000.20151009230558.001                                       100%  144MB  72.0MB/s   00:02   


4.在备库做恢复
[db2inst1@db2-test2 dbbak]$ db2 restore database secs from /home/db2inst1/dbbak/ taken at 20151009230558
DB20000I  The RESTORE DATABASE command completed successfully.


5.设置hadr通讯端口
主备库都要设置
[root@db2-test1 ~]# vi /etc/services
在最后添加
DB2_HADR_SECS1 60006/tcp
DB2_HADR_SECS2 60007/tcp


6.设置主库hadr参数
[db2inst1@db2-test1 dbbak]$ db2 update db cfg for secs using hadr_local_host db2-test1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[db2inst1@db2-test1 dbbak]$ db2 update db cfg for secs using hadr_remote_host db2-test2
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[db2inst1@db2-test1 dbbak]$ db2 update db cfg for secs using hadr_local_svc DB2_HADR_SECS1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[db2inst1@db2-test1 dbbak]$ db2 update db cfg for secs using hadr_remote_svc DB2_HADR_SECS2 deferred
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[db2inst1@db2-test1 dbbak]$ db2 update db cfg for secs using hadr_remote_inst db2inst1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[db2inst1@db2-test1 dbbak]$ db2 update db cfg for secs using hadr_syncmode async deferred
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[可选]
[db2inst1@db2-test1 dbbak]$ db2 update db cfg for secs using hadr_timeout 10
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


[db2inst1@db2-test1 dbbak]$ db2 deactivate database secs
SQL1496W  Deactivate database is successful, but the database was not 
activated.


[db2inst1@db2-test1 dbbak]$ db2 activate database secs
DB20000I  The ACTIVATE DATABASE command completed successfully.


7.配置备库hadr参数
[db2inst1@db2-test2 dbbak]$ db2 update db cfg for secs using hadr_local_host db2-test2
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 update db cfg for secs using hadr_remote_host db2-test1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 update db cfg for secs using hadr_local_svc DB2_HADR_SECS2
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 update db cfg for secs using hadr_remote_svc DB2_HADR_SECS1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 update db cfg for secs using hadr_remote_inst db2inst1
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 update db cfg for secs using hadr_syncmode async
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 update db cfg for secs using hadr_timeout 10
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.


8.在备库启动hadr
[db2inst1@db2-test2 dbbak]$ db2 start hadr on database secs as standby
DB20000I  The START HADR ON DATABASE command completed successfully.


9.在主库启动hadr
[db2inst1@db2-test1 dbbak]$ db2 start hadr on database secs as primary
DB20000I  The START HADR ON DATABASE command completed successfully.


10.切换
在备机上操作,类似于switch
[db2inst1@db2-test2 dbbak]$ db2 takeover hadr on database secs
DB20000I  The TAKEOVER HADR ON DATABASE command completed successfully.


强制切换,类似于takeover
db2 takeover hadr on database secs by force


11.配置自动客户端重新路由[可选]
在主库上操作
[db2inst1@db2-test1 dbbak]$ db2 update alternate server for database secs using hostname db2-test2 port 60000
DB20000I  The UPDATE ALTERNATE SERVER FOR DATABASE command completed 
successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.
[db2inst1@db2-test1 dbbak]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.


在备库上操作
[db2inst1@db2-test2 dbbak]$ db2 update alternate server for database secs using hostname db2-test1 port 60000
DB20000I  The UPDATE ALTERNATE SERVER FOR DATABASE command completed 
successfully.
DB21056W  Directory changes may not be effective until the directory cache is 
refreshed.
[db2inst1@db2-test2 dbbak]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.


12.配置备库只读模式[可选]
配置备库可连接
[db2inst1@db2-test2 dbbak]$ db2set DB2_HADR_ROS=ON
[db2inst1@db2-test2 dbbak]$ db2 deactivate database secs
DB20000I  The DEACTIVATE DATABASE command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2stop
10/10/2015 07:39:07     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@db2-test2 dbbak]$ db2start
10/10/2015 07:39:12     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@db2-test2 dbbak]$ db2 activate database secs
DB20000I  The ACTIVATE DATABASE command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 connect to secs


   Database Connection Information


 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = SECS


只读方式1
[db2inst1@db2-test2 dbbak]$ db2 "select * from secs.test1 with ur"


ID          NAME                
----------- --------------------


  0 record(s) selected.
  
只读方式2
[db2inst1@db2-test2 dbbak]$ db2set DB2_STANDBY_ISO=UR
[db2inst1@db2-test2 dbbak]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 deactivate database secs
DB20000I  The DEACTIVATE DATABASE command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 stop hadr on database secs
DB20000I  The STOP HADR ON DATABASE command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2stop
10/10/2015 07:47:03     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@db2-test2 dbbak]$ db2start
10/10/2015 07:47:08     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst1@db2-test2 dbbak]$ db2 start hadr on database secs as standby
DB20000I  The START HADR ON DATABASE command completed successfully.
[db2inst1@db2-test2 dbbak]$ db2 connect to secs


   Database Connection Information


 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = DB2INST1
 Local database alias   = SECS


[db2inst1@db2-test2 dbbak]$ db2 "select * from secs.test1"


ID          NAME                
----------- --------------------


  0 record(s) selected.
  
13.监控
通过数据库参数查看
[db2inst1@db2-test2 ~]$ db2 get db cfg for secs | grep -i hadr
 HADR database role                                      = STANDBY
 HADR local host name                  (HADR_LOCAL_HOST) = db2-test2
 HADR local service name                (HADR_LOCAL_SVC) = DB2_HADR_SECS2
 HADR remote host name                (HADR_REMOTE_HOST) = db2-test1
 HADR remote service name              (HADR_REMOTE_SVC) = DB2_HADR_SECS1
 HADR instance name of remote server  (HADR_REMOTE_INST) = db2inst1
 HADR timeout value                       (HADR_TIMEOUT) = 10
 HADR log write synchronization mode     (HADR_SYNCMODE) = ASYNC
 HADR peer window duration (seconds)  (HADR_PEER_WINDOW) = 0
 
 通过数据库快照信息查看
 [db2inst1@db2-test2 ~]$ db2 get snapshot for db on secs | grep -A 17 "HADR Status"
HADR Status
  Role                   = Standby
  State                  = Peer
  Synchronization mode   = Async
  Connection status      = Connected, 10/10/2015 07:47:28.855065
  Heartbeats missed      = 0
  Local host             = db2-test2
  Local service          = DB2_HADR_SECS2
  Remote host            = db2-test1
  Remote service         = DB2_HADR_SECS1
  Remote instance        = db2inst1
  timeout(seconds)       = 10
  Primary log position(file, page, LSN) = S0000000.LOG, 56, 0000000002760EDA
  Standby log position(file, page, LSN) = S0000000.LOG, 56, 0000000002760EDA
  Log gap running average(bytes) = 0


Memory usage for database:


通过db2pd获取hadr运行情况
[db2inst1@db2-test2 ~]$ db2pd -d secs -hadr


Database Partition 0 -- Database SECS -- Active Standby -- Up 0 days 00:07:27 -- Date 2015-10-10-07.54.55.920680


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


ConnectStatus ConnectTime                           Timeout   
Connected     Sat Oct 10 07:47:28 2015 (1444434448) 10        


ReplayOnlyWindowStatus ReplayOnlyWindowStartTime             MaintenanceTxCount
Inactive               N/A                                   0         


LocalHost                                LocalService      
db2-test2                                DB2_HADR_SECS2    


RemoteHost                               RemoteService      RemoteInstance    
db2-test1                                DB2_HADR_SECS1     db2inst1          


PrimaryFile  PrimaryPg  PrimaryLSN        
S0000000.LOG 56         0x0000000002760EDA


StandByFile  StandByPg  StandByLSN         StandByRcvBufUsed
S0000000.LOG 56         0x0000000002760EDA 0%  


角色（Role）：标志数据库是主数据库还是从数据库。


状态（State）：HADR 当前的状态。包括 Local Catchup、Remote Catchup、Remote Catchup、Pending、Peer、Disconnect Peer。
Local Catchup: 如果备机在这种状态下，表明备机这在从本地的磁盘上读取日志文件，并且对日志进行重新重做；如果主机在这种状态下，表明它正在等待备机的连接。HADR 的主机并没有从本地读日志并重做的过程，我们之所以让主机显示这个状态，就是通过主机上的这个状态告诉用户，备机正在做本地日志的重做。

Remote Catchup: 处于这个状态的 HADR 的主机正在从本地读日志，并且将这些日志发送给备机；而备机会从主机接受日志，并且将这些日志写入它本地的磁盘，并且对这些日志进行重做。

Remote Catchup Pending: 如果备机出于这种状态，表明它正在尝试连接主机。出现这种状态，一般是因为主机不存在或者主机还没有完全的启动起来，导致连接没有成功。

Peer: 如果 HADR 的主备机器处于这种状态，表明主机和备机的网络连接良好。日志可以顺利的从主机发送到备机。
Disconnect Peer: 如果 HADR 的主备机器处于这种状态，表明主机和备机的网络已经断开，但是连接断开的时间并没有超过 PEER_WINDOW。这个状态内，主机上的事务不可以提交。如果这个时候网络恢复，主机和备机重新建立连接，主备机器会重新回到 PEER 状态；如果双方进入这个状态的原因是主机出现了故障，当在备机上做接管（takeover）操作时，不会发生数据丢失。就是说不会出现主机提交了某个事务，但是备机没有提交这个事务的情况。

Disconnected: 如果主机处于这种状态，表明主机没有收到来自备机的连接。如果备机处于这种状态，表明备机不能连接到主机。


同步方式（SyncMode）：HADR 传输日志的三种方式，同步模式(SYNC)，近同步模式(NEARSYNC)，异步模式(ASYNC)。


心跳丢失数量（HeartBeatMissed）：主机会不断地向备机发送心跳，以确认仍然可以和对方通信。这个值表明了有多少心跳信号没有发送成功或者没有接收成功。


日志 LSN 差异（LogGapRunAvg）：这个值表示一段时间以内，主机和备机日志差异的平均值。如果这个值一直很大，可能表明网络状况比较差，或者备机的性能和主机差异太大，以至于日志不能及时的从主机传到备机。


连接状态（ConnectStatus）：包括三种 CONNECTED, DISCONNECTED, CONGESTED。
CONNECTED 表示连接状况良好；DISCONNECTED 表示主机和备机已经断开连接；CONGESTED 表示当前的网络状况不太好，日志或者消息的发送遇到拥塞。


连接时间（ConnectTime）：如果主机和备机是连接的，表示连接建立起来的时间；如果连接时断开的，表示的是连接断开的时间；如果发生了网络拥塞，则表示上次网络拥塞的时间。


超时时间（Timeout）：如果 HADR 在这段时间内没有收到来自同伴的任何消息，它就会断开网络连接。需要注意的是，这个时间并一定是网络出现错误以后的等待时间。HADR 可以发现网络上的大部分错误，当这些错误发生时，HADR 会立刻断开和对方的连接，而并不会等待。另外，这项配置还有另外两个作用：
1. 心跳的时间间隔为 HADR_TIMEOUT/4 和 30 秒钟两者之间的较小的那个值
2. 如果首先在主机上执行启动 HADR 的操作，如果主机没有在 HADR_TIMEOUT 时间以内没有收到备机的连接，主机上的数据库就会停掉，以防止两台主数据库的存在而导致脑裂。

同伴窗口（PeerWindow）：相对应 DB2 配置文件中的 HADR_PEER_WINDOW，该参数只对SYNC和NEARSYNC 两种同步模式有效。如果该参数不为零，当主机和备机断开连接时，在HADR_PEER_WINDOW 这段时间以内，数据库处于 DISCONNECTED PEER 状态。主机在这段时间内不能提交任何事务。所以，这段时间内，如果备机做了接管，备机不会丢失任何事务。这个参数通对于在TSA 对 HADR 自动做接管的环境中特别重要，因为 TSA 执行“takeover hard on db dbname by force peer window only”这个命令进行接管。


同伴窗口结束时间（PeerWindowEnd）：显示了同伴窗口的结束时间。过了这个时间以后，HADR 将处于DISCONNECTED 状态。


本地主机名（LocalHost）：本地 HADR 所在的主机名或者 IP 地址。


本地服务名（LocalService）：本地 HADR 所使用的服务名称或者端口号。


远程主机名（RemoteHost）：对端 HADR 所在机器的的主机名或者 IP 地址。


远程服务名（RemoteService）：对端 HADR 使用的服务名称或者端口号。


远程实例（RemoteInstance）：对端 HADR 数据库所在的实例的名字。


主机日志文件（PrimaryFile）：主机目前正在写的日志文件。


主机日志页号（PrimaryPg）：主机目前正在写的日志文件中的页号。


主机日志序号（PrimaryLSN）：主机正在处理的日志记录的序列号。


备机日志文件（StandByFile）：备机目前正在写的日志文件。


备机日志页号（StandByPg）：备机目前正在写的日志文件中的页号。


备机日志序号（StandByLSN）：备机正在处理的日志记录的序列号。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20777547/viewspace-1815586/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20777547/viewspace-1815586/