关于DB2 HADR就不做多的解释了,和oracle的DataGuard类似
这里记录一下平时实验的一个快速部署手册
CentOS6.5 x64位
192.168.122.101 kvm110
192.168.122.102 kvm111
目录准备
- mkdir -p /home/db2inst2/db2_backup
- mkdir -p /home/db2inst2/db2_archive
- mkdir -p /home/db2inst2/db2_log
-
- chmod -R 775 /home/db2inst2/db2_backup
- chmod -R 775 /home/db2inst2/db2_archive
- chmod -R 775 /home/db2inst2/db2_log
-
- chown -R db2inst2:db2iadm2 /home/db2inst2/db2_backup
- chown -R db2inst2:db2iadm2 /home/db2inst2/db2_archive
- chown -R db2inst2:db2iadm2 /home/db2inst2/db2_log
安装db2-略
创建测试库
db2 create db hadb01
下面新增一些数据,只在主库添加:
- db2 connect to hadb01
- db2 "create table t1(id int)"
- db2 "insert into t1 values(1)"
- db2 "insert into t1 values(2)"
-
- db2 "create table t2(id int)"
- db2 "insert into t2 values(1)"
- db2 "insert into t2 values(2)"
开启归档模式
主库和备库都操作
先修改归档参数,做离线备份,重启数据库后,手工测试归档
点击(此处)折叠或打开
- db2 update db cfg for hadb01 using logarchmeth1 disk:/home/db2inst2/db2_archive/
- db2 update db cfg for hadb01 using NEWLOGPATH /home/db2inst2/db2_log
-
- db2 force applications all
- db2 backup db hadb01 to /home/db2inst2/db2_backup/
- db2stop force;db2start
- db2 archive log for db hadb01
备库:
- db2 update db cfg for hadb01 using logarchmeth1 disk:/home/db2inst2/db2_archive/
- db2 update db cfg for hadb01 using NEWLOGPATH /home/db2inst2/db2_log
-
- db2 force applications all
- db2 backup db hadb01 to /home/db2inst2/db2_backup/
- db2stop force;db2start
- db2 archive log for db hadb01
主库离线全备份
- db2 backup database hadb01 to /home/db2inst2/db2_backup
-
- scp /home/db2inst2/db2_backup/hadb01.0.db2inst2.NODE0000.CATN0000.20150522091531.001 db2inst2@192.168.122.102:/home/db2inst2/db2_backup/
备库还原数据
- [db2inst2@kvm111 ~]$ db2 restore database hadb01 from "/home/db2inst2/db2_backup" taken at 20150522091531 replace history file
- SQL2523W Warning! Restoring to an existing database that is different from
- the database on the backup image, but have matching names. The target database
- will be overwritten by the backup version. The Roll-forward recovery logs
- associated with the target database will be deleted.
- Do you want to continue ? (y/n) y
- DB20000I The RESTORE DATABASE command completed successfully.
服务和端口配置
配置HADR服务 主备都设置
vi /etc/services加入
点击(此处)折叠或打开
- DB2_HADR_1 55110/tcp
- DB2_HADR_2 55111/tcp
后面加入了两行,用于配置下面的HADR_REMOTE_SVC
注意有没有和已有的端口冲突
主备参数配置
主库参数配置
- db2 get db cfg for hadb01 | grep -i HADR
- db2 update db cfg for hadb01 using HADR_LOCAL_HOST 192.168.122.101
- db2 update db cfg for hadb01 using HADR_LOCAL_SVC DB2_HADR_1
-
- db2 update db cfg for hadb01 using HADR_REMOTE_HOST 192.168.122.102
- db2 update db cfg for hadb01 using HADR_REMOTE_SVC DB2_HADR_2
- db2 update db cfg for hadb01 using HADR_REMOTE_INST db2inst2
-
- db2 update db cfg for hadb01 using HADR_SYNCMODE NEARSYNC
- db2 update db cfg for hadb01 using HADR_TIMEOUT 120
- db2 get db cfg for hadb01 | grep -i HADR
执行前:
- [db2inst2@kvm110 ~]$ db2 get db cfg for hadb01 | grep -i HADR
- HADR database role = STANDARD
- HADR local host name (HADR_LOCAL_HOST) =
- HADR local service name (HADR_LOCAL_SVC) =
- HADR remote host name (HADR_REMOTE_HOST) =
- HADR remote service name (HADR_REMOTE_SVC) =
- HADR instance name of remote server (HADR_REMOTE_INST) =
- HADR timeout value (HADR_TIMEOUT) = 120
- HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
- HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
执行后:
- [db2inst2@kvm110 ~]$ db2 get db cfg for hadb01 | grep -i HADR
- HADR database role = STANDARD
- HADR local host name (HADR_LOCAL_HOST) = 192.168.122.101
- HADR local service name (HADR_LOCAL_SVC) = DB2_HADR_1
- HADR remote host name (HADR_REMOTE_HOST) = 192.168.122.102
- HADR remote service name (HADR_REMOTE_SVC) = DB2_HADR_2
- HADR instance name of remote server (HADR_REMOTE_INST) = db2inst2
- HADR timeout value (HADR_TIMEOUT) = 120
- HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
- HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
备库参数配置
- db2 get db cfg for hadb01 | grep -i HADR
- db2 update db cfg for hadb01 using HADR_LOCAL_HOST 192.168.122.102
- db2 update db cfg for hadb01 using HADR_LOCAL_SVC DB2_HADR_2
-
- db2 update db cfg for hadb01 using HADR_REMOTE_HOST 192.168.122.101
- db2 update db cfg for hadb01 using HADR_REMOTE_SVC DB2_HADR_1
- db2 update db cfg for hadb01 using HADR_REMOTE_INST db2inst2
-
- db2 update db cfg for hadb01 using HADR_SYNCMODE NEARSYNC
- db2 update db cfg for hadb01 using HADR_TIMEOUT 120
- db2 get db cfg for hadb01 | grep -i HADR
执行后:
- [db2inst2@kvm111 ~]$ db2 get db cfg for hadb01 | grep -i HADR
- HADR database role = STANDARD
- HADR local host name (HADR_LOCAL_HOST) = 192.168.122.102
- HADR local service name (HADR_LOCAL_SVC) = DB2_HADR_2
- HADR remote host name (HADR_REMOTE_HOST) = 192.168.122.101
- HADR remote service name (HADR_REMOTE_SVC) = DB2_HADR_1
- HADR instance name of remote server (HADR_REMOTE_INST) = db2inst2
- HADR timeout value (HADR_TIMEOUT) = 120
- HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
- HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
启动HADR
先启动备库
standby端的数据库通过primary端的数据库恢复来,恢复后必须是roll forward-pending状态,restore中不能使用without rolling forward,启动备库之前先确定这个状态
[db2inst2@kvm111 ~]$ db2 get db cfg for hadb01 | grep Rollforward
Rollforward pending = DATABASE
相比于主库:
[db2inst2@kvm110 ~]$ db2 get db cfg for hadb01 | grep Rollforward
Rollforward pending = NO
启动备库
db2 start hadr on database hadb01 as standby
[db2inst2@kvm111 ~]$ db2 start hadr on database hadb01 as standby
SQL1766W The command completed successfully. However, LOGINDEXBUILD was not
enabled before HADR was started.
查看HADR状态-目前只有备库,状态是disconnected
- [db2inst2@kvm102 ~]$ db2 get snapshot for db on hadb01 | grep -A 15 "HADR Status"
- HADR Status
- Role = Standby
- State = Remote catchup pending
- Synchronization mode = Nearsync
- Connection status = Disconnected, 12/17/2015 00:57:27.251629
- Heartbeats missed = 0
- Local host = 192.168.122.102
- Local service = DB2_HADR_2
- Remote host = 192.168.122.101
- Remote service = DB2_HADR_1
- Remote instance = db2inst2
- timeout(seconds) = 120
- Primary log position(file, page, LSN) = S0000000.LOG, 0, 0000000000000000
- Standby log position(file, page, LSN) = S0000002.LOG, 0, 000000000366BA41
- Log gap running average(bytes) = 0
-
- [db2inst2@kvm102 ~]$ db2pd -d hadb01 -hadr
-
- Database Member 0 -- Database HADB01 -- Standby -- Up 0 days 00:04:42 -- Date 2015-12-17-01.02.08.288233
-
- HADR_ROLE = STANDBY
- REPLAY_TYPE = PHYSICAL
- HADR_SYNCMODE = NEARSYNC
- STANDBY_ID = 0
- LOG_STREAM_ID = 0
- HADR_STATE = REMOTE_CATCHUP_PENDING
- PRIMARY_MEMBER_HOST =
- PRIMARY_INSTANCE =
- PRIMARY_MEMBER = 0
- STANDBY_MEMBER_HOST = 192.168.122.102
- STANDBY_INSTANCE = db2inst2
- STANDBY_MEMBER = 0
- HADR_CONNECT_STATUS = DISCONNECTED
- HADR_CONNECT_STATUS_TIME = 12/17/2015 00:57:27.251629 (1450285047)
- HEARTBEAT_INTERVAL(seconds) = 30
- HADR_TIMEOUT(seconds) = 120
- TIME_SINCE_LAST_RECV(seconds) = 0
- SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 16384
- SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380
- PRIMARY_LOG_FILE,PAGE,POS = S0000000.LOG, 0, 0
- STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
- HADR_LOG_GAP(bytes) = 0
- STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
- STANDBY_RECV_REPLAY_GAP(bytes) = 0
- PRIMARY_LOG_TIME = NULL
- STANDBY_LOG_TIME = 12/17/2015 00:22:15.000000 (1450282935)
- STANDBY_REPLAY_LOG_TIME = 12/17/2015 00:22:15.000000 (1450282935)
- STANDBY_RECV_BUF_SIZE(pages) = 16
- STANDBY_RECV_BUF_PERCENT = 0
- STANDBY_SPOOL_LIMIT(pages) = 0
- PEER_WINDOW(seconds) = 0
- READS_ON_STANDBY_ENABLED = N
启动主库
- db2 deactivate database hadb01
- db2 start hadr on database hadb01 as primary
状态
- [db2inst2@kvm101 ~]$ db2 get snapshot for db on hadb01 | grep -A 15 "HADR Status"
- HADR Status
- Role = Primary
- State = Peer
- Synchronization mode = Nearsync
- Connection status = Connected, 12/17/2015 01:03:31.996832
- Heartbeats missed = 0
- Local host = 192.168.122.101
- Local service = DB2_HADR_1
- Remote host = 192.168.122.102
- Remote service = DB2_HADR_2
- Remote instance = db2inst2
- timeout(seconds) = 120
- Primary log position(file, page, LSN) = S0000002.LOG, 0, 000000000366BA41
- Standby log position(file, page, LSN) = S0000002.LOG, 0, 000000000366BA41
- Log gap running average(bytes) = 0
-
- [db2inst2@kvm101 ~]$
- [db2inst2@kvm101 ~]$ db2pd -d hadb01 -hadr
-
- Database Member 0 -- Database HADB01 -- Active -- Up 0 days 00:01:30 -- Date 2015-12-17-01.04.59.855546
-
- HADR_ROLE = PRIMARY
- REPLAY_TYPE = PHYSICAL
- HADR_SYNCMODE = NEARSYNC
- STANDBY_ID = 1
- LOG_STREAM_ID = 0
- HADR_STATE = PEER
- PRIMARY_MEMBER_HOST = 192.168.122.101
- PRIMARY_INSTANCE = db2inst2
- PRIMARY_MEMBER = 0
- STANDBY_MEMBER_HOST = 192.168.122.102
- STANDBY_INSTANCE = db2inst2
- STANDBY_MEMBER = 0
- HADR_CONNECT_STATUS = CONNECTED
- HADR_CONNECT_STATUS_TIME = 12/17/2015 01:03:31.996832 (1450285411)
- HEARTBEAT_INTERVAL(seconds) = 30
- HADR_TIMEOUT(seconds) = 120
- TIME_SINCE_LAST_RECV(seconds) = 27
- PEER_WAIT_LIMIT(seconds) = 0
- LOG_HADR_WAIT_CUR(seconds) = 0.000
- LOG_HADR_WAIT_RECENT_AVG(seconds) = 0.000000
- LOG_HADR_WAIT_ACCUMULATED(seconds) = 0.000
- LOG_HADR_WAIT_COUNT = 0
- SOCK_SEND_BUF_REQUESTED,ACTUAL(bytes) = 0, 19800
- SOCK_RECV_BUF_REQUESTED,ACTUAL(bytes) = 0, 87380
- PRIMARY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
- STANDBY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
- HADR_LOG_GAP(bytes) = 0
- STANDBY_REPLAY_LOG_FILE,PAGE,POS = S0000002.LOG, 0, 57064001
- STANDBY_RECV_REPLAY_GAP(bytes) = 0
- PRIMARY_LOG_TIME = 12/17/2015 00:34:24.000000 (1450283664)
- STANDBY_LOG_TIME = 12/17/2015 00:34:24.000000 (1450283664)
- STANDBY_REPLAY_LOG_TIME = 12/17/2015 00:34:24.000000 (1450283664)
- STANDBY_RECV_BUF_SIZE(pages) = 512
- STANDBY_RECV_BUF_PERCENT = 0
- STANDBY_SPOOL_LIMIT(pages) = 0
- PEER_WINDOW(seconds) = 0
- READS_ON_STANDBY_ENABLED = N
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29023300/viewspace-2117996/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29023300/viewspace-2117996/