从网上找的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@]

ITPub博客

hadr操作

最新文章