關於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…