hadr操作
從網上找的hadr操作配置，寫的還不錯。
1.建立hadr
目標：將83設定為paimary 213設定為standby
按照要求首先將83上的sample資料庫全備份出來。
[db2inst1@FxsTest-2 db2backup]$ db2 backup db sample
Backup successful. The timestamp for this backup image is : 20060419004013
得到全備份檔案 SAMPLE.0.db2inst1.NODE0000.CATN0000.20060419004013.001
將該備份檔案恢復為db2dyp
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
[db2inst1@FxsTest-2 db2backup]$ db2 restore db SAMPLE taken at 20060419004013 into db2dyp
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@FxsTest-2 db2backup]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@FxsTest-2 db2backup]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
12 record(s) selected.
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
接下來的步驟是為了生成從服務所需要的暫掛狀態的資料庫備份檔案，然後傳到從伺服器上進行驗證為暫掛狀態
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
83
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using LOGRETAIN ON
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 stop
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "STOP".
Expected tokens may include: "DATABASE". SQLSTATE=42601
[db2inst1@FxsTest-2 ~]$ db2stop
04/19/2006 00:50:20 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@FxsTest-2 ~]$ db2start
04/19/2006 00:50:28 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@FxsTest-2 ~]$ db2 backup db db2dyp online （注意該位置由於剛改完引數所以必須進行一次非線上的備份，這樣防止出現錯誤）
SQL2413N Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
[db2inst1@FxsTest-2 ~]$ db2 backup db db2dyp
Backup successful. The timestamp for this backup image is : 20060419005052
[db2inst1@FxsTest-2 ~]$ db2 backup db db2dyp online
Backup successful. The timestamp for this backup image is : 20060419005056
[db2inst1@FxsTest-2 ~]$ scp DB2DYP.0.db2inst1.NODE0000.CATN0000.20060419005056.001 db2inst1@10.4.5.213:/home/db2inst1
db2inst1@10.4.5.213's password:
DB2DYP.0.db2inst1.NODE0000.CATN0000.20060419005056.001 100% 29MB 7.1MB/s 00:04
[db2inst1@FxsTest-2 ~]$
213
[db2inst1@fxstest2 ~]$ db2 restore db db2dyp taken at 20060419005056 into db2dyp
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@fxstest2 ~]$ db2 connect to db2dyp
SQL1117N A connection to or activation of database "DB2DYP" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
下面進行db2 hadr引數的設定。
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
primary 83
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_HOST 10.4.5.83
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_SVC DB2DYPP
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_HOST 10.4.5.213
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_SVC DB2DYPS
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_INST db2inst1
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = 10.4.5.83
HADR local service name (HADR_LOCAL_SVC) = DB2DYPP
HADR remote host name (HADR_REMOTE_HOST) = 10.4.5.213
HADR remote service name (HADR_REMOTE_SVC) = DB2DYPS
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using LOGINDEXBUILD ON
standby 213
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_HOST 10.4.5.213
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp HADR_LOCAL_SVC DB2DYPS
SQL0104N An unexpected token "HADR_LOCAL_SVC" was found following
"". Expected tokens may include: "USING". SQLSTATE=42601
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_SVC DB2DYPS
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_HOST 10.4.5.83
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_SVC DB2DYPP
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_INST db2inst1
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using LOGINDEXBUILD 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, all
applications must disconnect from this database before the changes become
effective.
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = 10.4.5.213
HADR local service name (HADR_LOCAL_SVC) = DB2DYPS
HADR remote host name (HADR_REMOTE_HOST) = 10.4.5.83
HADR remote service name (HADR_REMOTE_SVC) = DB2DYPP
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
啟動hadr
[db2inst1@fxstest2 ~]$ db2 start hadr on db db2dyp as standby
DB20000I The START HADR ON DATABASE command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 start hadr on db db2dyp as primary
DB20000I The START HADR ON DATABASE command completed successfully.
primary的狀態
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:02:17
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 4095964
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:16:29 2006 (1145380589) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 0 0x000000FA000080AB3C44895C0 S0000000.LOG 0 0x000000BB802780AB3C44895C0
standby的狀態
[db2inst1@fxstest2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:03:41
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 4095962
ConnectStatus ConnectTime Timeout
Connected Tue Feb 7 15:12:45 2006 (1139296365) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 0 0x000000FA000080AB3C48BA5C0 S0000000.LOG 0 0x000000BB802780AB3C48BA5C0
hadr建立成功
2.順利切換：
1）HADR ROLE的確認
83
[db2inst1@FxsTest-2 ~]$ db2 get db cfg for db2dyp |grep role
HADR database role = PRIMARY
213
[db2inst1@fxstest2 root]$ db2 get db cfg for db2dyp|grep role
HADR database role = STANDBY
2）使用Snap shot monitor，確認在stand by 節點上HADR正確起動。
83
[db2inst1@fxstest2 root]$ db2 get snapshot for db on db2dyp|more
Database Snapshot
HADR Status
Role = Standby
State = Peer
Synchronization mode = Nearsync
Connection status = Connected, 02/07/2006 15:12:45.717741
Heartbeats missed = 0
Local host = 10.4.5.213
Local service = DB2DYPS
Remote host = 10.4.5.83
Remote service = DB2DYPP
Remote instance = db2inst1
timeout(seconds) = 120
Primary log position(file, page, LSN) = S0000001.LOG, 0, 0000000000FA0000
Standby log position(file, page, LSN) = S0000000.LOG, 0, 0000000000BB8027
Log gap running average(bytes) = 4095956
..........................................
需要知道的是HADR的roll為Standby，HADR的狀態為State= Peer，接續狀態為 connected。
213
HADR Status
Role = Primary
State = Peer
Synchronization mode = Nearsync
Connection status = Connected, 04/19/2006 01:16:29.356048
Heartbeats missed = 0
Local host = 10.4.5.83
Local service = DB2DYPP
Remote host = 10.4.5.213
Remote service = DB2DYPS
Remote instance = db2inst1
timeout(seconds) = 120
Primary log position(file, page, LSN) = S0000001.LOG, 0, 0000000000FA0000
Standby log position(file, page, LSN) = S0000000.LOG, 0, 0000000000BB8027
Log gap running average(bytes) = 4095956
....................
需要知道的是HADR的roll為Primary，HADR的狀態為State= Peer，接續狀態為 connected
下面進行hadr的試驗，首先將兩個資料庫同時down掉。
83
[db2inst1@FxsTest-2 ~]$ db2stop force
04/19/2006 01:37:34 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
213
[db2inst1@fxstest2 root]$ db2stop force
02/07/2006 15:33:59 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
啟動
83
[db2inst1@FxsTest-2 ~]$ db2start
04/19/2006 01:39:06 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
213
[db2inst1@fxstest2 root]$ db2start
02/07/2006 15:35:17 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
啟動hadr (切記為了資料的完整應該先啟動從伺服器)
213
[db2inst1@fxstest2 root]$ db2 activate db db2dyp
DB20000I The ACTIVATE DATABASE command completed successfully.
83
[db2inst1@FxsTest-2 ~]$ db2 activate db db2dyp
DB20000I The ACTIVATE DATABASE command completed successfully.
對83 primary的db 實施更新
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
[db2inst1@FxsTest-2 ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
12 record(s) selected.
[db2inst1@FxsTest-2 ~]$ db2 "create table hadr1(name char(7) not null,time timestamp not null with default current timestamp)"
DB20000I The SQL command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 "insert into hadr1 values ('InsertA',current timestamp)"
DB20000I The SQL command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
1 record(s) selected.
[db2inst1@FxsTest-2 ~]$ db2list tables
-bash: db2list: command not found
[db2inst1@FxsTest-2 ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@FxsTest-2 ~]$
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
對從伺服器進行takeover
[db2inst1@fxstest2 root]$ db2 "takeover hadr on db db2dyp"
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
主服務83的狀態
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:06:47
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:41:05 2006 (1145382065) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA15D080AB3C44C15C0 S0000001.LOG 1 0x000000FA15D080AB3C44C15C0
從服務213的狀態：
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:08:32
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Tue Feb 7 15:37:21 2006 (1139297841) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA15D080AB3C48BA5C0 S0000001.LOG 1 0x000000FA15D080AB3C48BA5C0
切換之後213為primary，執行如下命令繼續新增資料。
[db2inst1@fxstest2 root]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@fxstest2 root]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@fxstest2 root]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
1 record(s) selected.
[db2inst1@fxstest2 root]$ db2 "insert into hadr1 values ('InsertB',current timestamp)"
DB20000I The SQL command completed successfully.
[db2inst1@fxstest2 root]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
InsertB 2006-02-07-15.54.38.032607
2 record(s) selected.
[db2inst1@fxstest2 root]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
對原primary現standby的83進行操作：
[db2inst1@FxsTest-2 ~]$ db2 "takeover hadr on db db2dyp"
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:20:06
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:41:05 2006 (1145382065) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C428F5C0 S0000001.LOG 1 0x000000FA162B80AB3C428F5C0
可以看出83經歷的狀態為 P -> S ->P
進行上述操作之後檢視213
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:23:24
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Tue Feb 7 15:37:21 2006 (1139297841) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C456E5C0 S0000001.LOG 1 0x000000FA162B80AB3C456E5C0
可以看出213經歷的狀態為 s -> p ->s
最後檢視現在的primary83的資料：
[db2inst1@FxsTest-2 ~]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@FxsTest-2 ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@FxsTest-2 ~]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
InsertB 2006-02-07-15.54.38.032607
2 record(s) selected.
出現異常時 強制 切換 hadr
首先檢視primary的狀態
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:26:18
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:41:05 2006 (1145382065) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C44BC5C0 S0000001.LOG 1 0x000000FA162B80AB3C44BC5C0
將主伺服器 kill掉
[db2inst1@FxsTest-2 ~]$ db2_kill
ipclean: Removing DB2 engine and client's IPC resources for db2inst1.
從伺服器狀態為：
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:29:24
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby RemoteCatchupPending Nearsync 0 0
ConnectStatus ConnectTime Timeout
Disconnected Tue Feb 7 16:06:25 2006 (1139299585) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C44DD5C0 S0000001.LOG 1 0x000000FA162B80AB3C44DD5C0
[db2inst1@fxstest2 root]$ db2 "takeover hadr on db db2dyp"
SQL1770N Takeover HADR cannot complete. Reason code = "1".
返回SQL1770N（RC=1）的錯誤。
這是因為HADR的狀態不是”Peer”的時候，就想實施take-over，所以發生的。
強制切換
[db2inst1@fxstest2 root]$ db2 "takeover hadr on db db2dyp by force"
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:31:50
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Disconnected Nearsync 0 0
ConnectStatus ConnectTime Timeout
Disconnected Tue Feb 7 16:07:53 2006 (1139299673) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C48BA5C0 S0000000.LOG 0 0x00000000000080AB3C48BA5C0
原主伺服器的操作及檢視：
[db2inst1@FxsTest-2 ~]$ db2start
04/19/2006 02:13:41 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@FxsTest-2 ~]$ db2 get db cfg for db2dyp|grep role
HADR database role = PRIMARY
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database DB2DYP not activated on database partition 0.
Option -hadr requires -db or -alldbs option and active database.
[db2inst1@FxsTest-2 ~]$ db2 "start hadr on db db2dyp as standby"
DB20000I The START HADR ON DATABASE command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:00:06
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 5676
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 02:16:11 2006 (1145384171) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C42A65C0 S0000001.LOG 0 0x000000FA000080AB3C42A65C0
[db2inst1@FxsTest-2 ~]$ db2 connect to db2dyp
SQL1776N The command cannot be issued on an HADR standby database. Reason
code = "1".
現主伺服器的檢視：
[db2inst1@fxstest2 root]$ db2 get db cfg for db2dyp |grep role
HADR database role = PRIMARY
[db2inst1@fxstest2 root]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@fxstest2 root]$ db2 list tables;
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@fxstest2 root]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
InsertB 2006-02-07-15.54.38.032607
2 record(s) selected.
以上 hadr強制切換成功。
[@more@]
1.建立hadr
目標：將83設定為paimary 213設定為standby
按照要求首先將83上的sample資料庫全備份出來。
[db2inst1@FxsTest-2 db2backup]$ db2 backup db sample
Backup successful. The timestamp for this backup image is : 20060419004013
得到全備份檔案 SAMPLE.0.db2inst1.NODE0000.CATN0000.20060419004013.001
將該備份檔案恢復為db2dyp
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
[db2inst1@FxsTest-2 db2backup]$ db2 restore db SAMPLE taken at 20060419004013 into db2dyp
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@FxsTest-2 db2backup]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@FxsTest-2 db2backup]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
12 record(s) selected.
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
接下來的步驟是為了生成從服務所需要的暫掛狀態的資料庫備份檔案，然後傳到從伺服器上進行驗證為暫掛狀態
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
83
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using LOGRETAIN ON
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 stop
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "STOP".
Expected tokens may include: "DATABASE". SQLSTATE=42601
[db2inst1@FxsTest-2 ~]$ db2stop
04/19/2006 00:50:20 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@FxsTest-2 ~]$ db2start
04/19/2006 00:50:28 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@FxsTest-2 ~]$ db2 backup db db2dyp online （注意該位置由於剛改完引數所以必須進行一次非線上的備份，這樣防止出現錯誤）
SQL2413N Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
[db2inst1@FxsTest-2 ~]$ db2 backup db db2dyp
Backup successful. The timestamp for this backup image is : 20060419005052
[db2inst1@FxsTest-2 ~]$ db2 backup db db2dyp online
Backup successful. The timestamp for this backup image is : 20060419005056
[db2inst1@FxsTest-2 ~]$ scp DB2DYP.0.db2inst1.NODE0000.CATN0000.20060419005056.001 db2inst1@10.4.5.213:/home/db2inst1
db2inst1@10.4.5.213's password:
DB2DYP.0.db2inst1.NODE0000.CATN0000.20060419005056.001 100% 29MB 7.1MB/s 00:04
[db2inst1@FxsTest-2 ~]$
213
[db2inst1@fxstest2 ~]$ db2 restore db db2dyp taken at 20060419005056 into db2dyp
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@fxstest2 ~]$ db2 connect to db2dyp
SQL1117N A connection to or activation of database "DB2DYP" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
下面進行db2 hadr引數的設定。
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
primary 83
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_HOST 10.4.5.83
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_SVC DB2DYPP
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_HOST 10.4.5.213
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_SVC DB2DYPS
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_INST db2inst1
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = 10.4.5.83
HADR local service name (HADR_LOCAL_SVC) = DB2DYPP
HADR remote host name (HADR_REMOTE_HOST) = 10.4.5.213
HADR remote service name (HADR_REMOTE_SVC) = DB2DYPS
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
[db2inst1@FxsTest-2 ~]$ db2 update db cfg for db2dyp using LOGINDEXBUILD ON
standby 213
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_HOST 10.4.5.213
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp HADR_LOCAL_SVC DB2DYPS
SQL0104N An unexpected token "HADR_LOCAL_SVC" was found following
"
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_LOCAL_SVC DB2DYPS
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_HOST 10.4.5.83
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_SVC DB2DYPP
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using HADR_REMOTE_INST db2inst1
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, all
applications must disconnect from this database before the changes become
effective.
[db2inst1@fxstest2 ~]$ db2 update db cfg for db2dyp using LOGINDEXBUILD 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, all
applications must disconnect from this database before the changes become
effective.
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) = 10.4.5.213
HADR local service name (HADR_LOCAL_SVC) = DB2DYPS
HADR remote host name (HADR_REMOTE_HOST) = 10.4.5.83
HADR remote service name (HADR_REMOTE_SVC) = DB2DYPP
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst1
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
啟動hadr
[db2inst1@fxstest2 ~]$ db2 start hadr on db db2dyp as standby
DB20000I The START HADR ON DATABASE command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 start hadr on db db2dyp as primary
DB20000I The START HADR ON DATABASE command completed successfully.
primary的狀態
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:02:17
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 4095964
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:16:29 2006 (1145380589) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 0 0x000000FA000080AB3C44895C0 S0000000.LOG 0 0x000000BB802780AB3C44895C0
standby的狀態
[db2inst1@fxstest2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:03:41
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 4095962
ConnectStatus ConnectTime Timeout
Connected Tue Feb 7 15:12:45 2006 (1139296365) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 0 0x000000FA000080AB3C48BA5C0 S0000000.LOG 0 0x000000BB802780AB3C48BA5C0
hadr建立成功
2.順利切換：
1）HADR ROLE的確認
83
[db2inst1@FxsTest-2 ~]$ db2 get db cfg for db2dyp |grep role
HADR database role = PRIMARY
213
[db2inst1@fxstest2 root]$ db2 get db cfg for db2dyp|grep role
HADR database role = STANDBY
2）使用Snap shot monitor，確認在stand by 節點上HADR正確起動。
83
[db2inst1@fxstest2 root]$ db2 get snapshot for db on db2dyp|more
Database Snapshot
HADR Status
Role = Standby
State = Peer
Synchronization mode = Nearsync
Connection status = Connected, 02/07/2006 15:12:45.717741
Heartbeats missed = 0
Local host = 10.4.5.213
Local service = DB2DYPS
Remote host = 10.4.5.83
Remote service = DB2DYPP
Remote instance = db2inst1
timeout(seconds) = 120
Primary log position(file, page, LSN) = S0000001.LOG, 0, 0000000000FA0000
Standby log position(file, page, LSN) = S0000000.LOG, 0, 0000000000BB8027
Log gap running average(bytes) = 4095956
..........................................
需要知道的是HADR的roll為Standby，HADR的狀態為State= Peer，接續狀態為 connected。
213
HADR Status
Role = Primary
State = Peer
Synchronization mode = Nearsync
Connection status = Connected, 04/19/2006 01:16:29.356048
Heartbeats missed = 0
Local host = 10.4.5.83
Local service = DB2DYPP
Remote host = 10.4.5.213
Remote service = DB2DYPS
Remote instance = db2inst1
timeout(seconds) = 120
Primary log position(file, page, LSN) = S0000001.LOG, 0, 0000000000FA0000
Standby log position(file, page, LSN) = S0000000.LOG, 0, 0000000000BB8027
Log gap running average(bytes) = 4095956
....................
需要知道的是HADR的roll為Primary，HADR的狀態為State= Peer，接續狀態為 connected
下面進行hadr的試驗，首先將兩個資料庫同時down掉。
83
[db2inst1@FxsTest-2 ~]$ db2stop force
04/19/2006 01:37:34 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
213
[db2inst1@fxstest2 root]$ db2stop force
02/07/2006 15:33:59 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
啟動
83
[db2inst1@FxsTest-2 ~]$ db2start
04/19/2006 01:39:06 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
213
[db2inst1@fxstest2 root]$ db2start
02/07/2006 15:35:17 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
啟動hadr (切記為了資料的完整應該先啟動從伺服器)
213
[db2inst1@fxstest2 root]$ db2 activate db db2dyp
DB20000I The ACTIVATE DATABASE command completed successfully.
83
[db2inst1@FxsTest-2 ~]$ db2 activate db db2dyp
DB20000I The ACTIVATE DATABASE command completed successfully.
對83 primary的db 實施更新
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
[db2inst1@FxsTest-2 ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
12 record(s) selected.
[db2inst1@FxsTest-2 ~]$ db2 "create table hadr1(name char(7) not null,time timestamp not null with default current timestamp)"
DB20000I The SQL command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 "insert into hadr1 values ('InsertA',current timestamp)"
DB20000I The SQL command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
1 record(s) selected.
[db2inst1@FxsTest-2 ~]$ db2list tables
-bash: db2list: command not found
[db2inst1@FxsTest-2 ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@FxsTest-2 ~]$
----------------------------------------------------------------------------------------------------------------------
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
----------------------------------------------------------------------------------------------------------------------
對從伺服器進行takeover
[db2inst1@fxstest2 root]$ db2 "takeover hadr on db db2dyp"
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
主服務83的狀態
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:06:47
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:41:05 2006 (1145382065) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA15D080AB3C44C15C0 S0000001.LOG 1 0x000000FA15D080AB3C44C15C0
從服務213的狀態：
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:08:32
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Tue Feb 7 15:37:21 2006 (1139297841) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA15D080AB3C48BA5C0 S0000001.LOG 1 0x000000FA15D080AB3C48BA5C0
切換之後213為primary，執行如下命令繼續新增資料。
[db2inst1@fxstest2 root]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@fxstest2 root]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@fxstest2 root]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
1 record(s) selected.
[db2inst1@fxstest2 root]$ db2 "insert into hadr1 values ('InsertB',current timestamp)"
DB20000I The SQL command completed successfully.
[db2inst1@fxstest2 root]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
InsertB 2006-02-07-15.54.38.032607
2 record(s) selected.
[db2inst1@fxstest2 root]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
對原primary現standby的83進行操作：
[db2inst1@FxsTest-2 ~]$ db2 "takeover hadr on db db2dyp"
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:20:06
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:41:05 2006 (1145382065) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C428F5C0 S0000001.LOG 1 0x000000FA162B80AB3C428F5C0
可以看出83經歷的狀態為 P -> S ->P
進行上述操作之後檢視213
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:23:24
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Tue Feb 7 15:37:21 2006 (1139297841) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C456E5C0 S0000001.LOG 1 0x000000FA162B80AB3C456E5C0
可以看出213經歷的狀態為 s -> p ->s
最後檢視現在的primary83的資料：
[db2inst1@FxsTest-2 ~]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@FxsTest-2 ~]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@FxsTest-2 ~]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
InsertB 2006-02-07-15.54.38.032607
2 record(s) selected.
出現異常時 強制 切換 hadr
首先檢視primary的狀態
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:26:18
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Peer Nearsync 0 0
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 01:41:05 2006 (1145382065) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C44BC5C0 S0000001.LOG 1 0x000000FA162B80AB3C44BC5C0
將主伺服器 kill掉
[db2inst1@FxsTest-2 ~]$ db2_kill
ipclean: Removing DB2 engine and client's IPC resources for db2inst1.
從伺服器狀態為：
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:29:24
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby RemoteCatchupPending Nearsync 0 0
ConnectStatus ConnectTime Timeout
Disconnected Tue Feb 7 16:06:25 2006 (1139299585) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C44DD5C0 S0000001.LOG 1 0x000000FA162B80AB3C44DD5C0
[db2inst1@fxstest2 root]$ db2 "takeover hadr on db db2dyp"
SQL1770N Takeover HADR cannot complete. Reason code = "1".
返回SQL1770N（RC=1）的錯誤。
這是因為HADR的狀態不是”Peer”的時候，就想實施take-over，所以發生的。
強制切換
[db2inst1@fxstest2 root]$ db2 "takeover hadr on db db2dyp by force"
DB20000I The TAKEOVER HADR ON DATABASE command completed successfully.
[db2inst1@fxstest2 root]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:31:50
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Primary Disconnected Nearsync 0 0
ConnectStatus ConnectTime Timeout
Disconnected Tue Feb 7 16:07:53 2006 (1139299673) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.213 DB2DYPS 10.4.5.83 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C48BA5C0 S0000000.LOG 0 0x00000000000080AB3C48BA5C0
原主伺服器的操作及檢視：
[db2inst1@FxsTest-2 ~]$ db2start
04/19/2006 02:13:41 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@FxsTest-2 ~]$ db2 get db cfg for db2dyp|grep role
HADR database role = PRIMARY
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database DB2DYP not activated on database partition 0.
Option -hadr requires -db
[db2inst1@FxsTest-2 ~]$ db2 "start hadr on db db2dyp as standby"
DB20000I The START HADR ON DATABASE command completed successfully.
[db2inst1@FxsTest-2 ~]$ db2pd -hadr -db db2dyp
Database Partition 0 -- Database DB2DYP -- Active -- Up 0 days 00:00:06
HADR Information:
Role State SyncMode HeartBeat LogGapRunAvg (bytes)
Standby Peer Nearsync 0 5676
ConnectStatus ConnectTime Timeout
Connected Wed Apr 19 02:16:11 2006 (1145384171) 120
LocalHost LocalService RemoteHost RemoteService RemoteInstance
10.4.5.83 DB2DYPP
10.4.5.213 DB2DYP db2inst1
PrimaryFile PrimaryPg PrimaryLSN StandByFile StandByPg StandByLSN
S0000001.LOG 1 0x000000FA162B80AB3C42A65C0 S0000001.LOG 0 0x000000FA000080AB3C42A65C0
[db2inst1@FxsTest-2 ~]$ db2 connect to db2dyp
SQL1776N The command cannot be issued on an HADR standby database. Reason
code = "1".
現主伺服器的檢視：
[db2inst1@fxstest2 root]$ db2 get db cfg for db2dyp |grep role
HADR database role = PRIMARY
[db2inst1@fxstest2 root]$ db2 connect to db2dyp
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = DB2DYP
[db2inst1@fxstest2 root]$ db2 list tables;
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CL_SCHED DB2INST1 T 2006-02-04-19.46.02.234842
DEPARTMENT DB2INST1 T 2006-02-04-19.46.00.956747
EMPLOYEE DB2INST1 T 2006-02-04-19.46.00.988795
EMP_ACT DB2INST1 T 2006-02-04-19.46.01.103928
EMP_PHOTO DB2INST1 T 2006-02-04-19.46.01.487664
EMP_RESUME DB2INST1 T 2006-02-04-19.46.01.982587
HADR1 DB2INST1 T 2006-04-19-01.43.36.577364
IN_TRAY DB2INST1 T 2006-02-04-19.46.02.258845
ORG DB2INST1 T 2006-02-04-19.46.00.546165
PROJECT DB2INST1 T 2006-02-04-19.46.01.344174
SALES DB2INST1 T 2006-02-04-19.46.02.157892
STAFF DB2INST1 T 2006-02-04-19.46.00.689629
STAFFG DB2INST1 T 2006-02-04-19.46.00.804813
13 record(s) selected.
[db2inst1@fxstest2 root]$ db2 "select * from hadr1"
NAME TIME
------- --------------------------
InsertA 2006-04-19-01.43.56.510199
InsertB 2006-02-07-15.54.38.032607
2 record(s) selected.
以上 hadr強制切換成功。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9893498/viewspace-1015742/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- db2 hadr 限制DB2
- DB2 HADR效能分析DB2
- db2 hadr_spool_limitDB2MIT
- DB2 HADR搭建過程DB2
- DB2 HADR的heartbeat檢測DB2
- DB2 HADR對效能的影響DB2
- DB2 HADR相關引數1DB2
- DB2 HADR takeover 問題 SQL1387WDB2SQL
- DB2 HADR環境下，應用的改變DB2
- [AlwaysOn2017] AlwaysOn的DMV和DMF - Sys.dm_hadr_auto_page_repairAI
- [AlwaysOn2017] AlwaysOn的DMV和DMF - Sys.dm_hadr_database_replica_statesDatabase
- [AlwaysOn2017] AlwaysOn的DMV - Sys.dm_hadr_availability_replica_cluster_statesAI
- [AlwaysOn2017] AlwaysOn的DMV和DMF - Sys.dm_hadr_availability_group_statesAI
- [AlwaysOn2017] AlwaysOn的DMV和DMF - Sys.dm_hadr_availability_replica_statesAI
- [AlwaysOn2017] AlwaysOn的DMV和DMF - Sys.dm_hadr_database_replica_cluster_statesDatabase
- [AlwaysOn2017] AlwaysOn的DMV和DMF - Sys.fn_hadr_distributed_ag_database_replicaDatabase
- [AlwaysOn2017] AlwaysOn的DMV和DMF-Sys.dm_hadr_availability_replica_cluster_nodesAI
- sqlite相關--------adb shell操作，工具操作，程式操作SQLite
- Go 操作 Redis 的基本操作GoRedis
- 常用操作 / 資料庫操作資料庫
- 原子操作 vs 非原子操作
- 操作
- 坐下坐下，基本操作（ZooKeeper 操作篇）
- JavaScript騷操作之操作符JavaScript
- Java檔案操作 讀寫操作Java
- 使用java操作ranger，hdfs ranger授權操作，hive ranger授權操作JavaRangerHive
- Go 語言操作 MySQL 之 CURD 操作GoMySql
- Oracle並行操作——並行DML操作Oracle並行
- js操作JS
- gorm 操作GoORM
- firewalld操作
- 流操作
- DOM 操作
- 位操作
- docker操作Docker
- Jedis操作
- Git 操作Git
- BOM操作