关于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
现在主库和备库已经建立连接，HADR实验环境部署完成.
以下文章点击率最高
Loading…