1、首先在两台DB2 Server上创建相同的用户组,用户名,以及实例,并配置ssh互信(db2实例用户)
创建实例(两台机器同时进行)
ha1:~ # groupadd -g 999 db2iadm1
ha1:~ # groupadd -g 998 db2fadm1
ha1:~ # useradd -u 2014 -g db2iadm1 -m db2inst1 -s /bin/ksh
ha1:~ # useradd -u 2013 -g db2fadm1 -m db2fenc1 -s /bin/ksh
ha1:~ # cd
/opt/ibm/db2/V9.7/instance/
ha1:/opt/ibm/db2/V9.7/instance # ./db2icrt -s ese -u db2fenc1
db2inst1
DBI1070I Program db2icrt completed successfully.
配置db2inst1用户的SSH互信
服务器1:
db2inst1@ha1:/home/db2inst1> ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/db2inst1/.ssh/id_rsa):
Created directory '/home/db2inst1/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/db2inst1/.ssh/id_rsa.
Your public key has been saved in /home/db2inst1/.ssh/id_rsa.pub.
The key fingerprint is:
08:f5:d8:88:52:2d:95:24:bd:b6:b7:60:ed:d0:c2:8c db2inst1@ha1
db2inst1@ha1:/home/db2inst1> ssh-copy-id -i .ssh/id_rsa.pub
db2inst1@ha2
15
The authenticity of host 'ha2 (192.168.56.173)' can't be established.
RSA key fingerprint is
63:42:c3:d4:06:51:dd:2c:86:93:41:df:01:92:80:0d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ha2,192.168.56.173' (RSA) to the list of known
hosts.
Password:
Now try logging into the machine, with "ssh 'db2inst1@ha2'", and check
in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
db2inst1@ha1:/home/db2inst1>
服务器2:
db2inst1@ha2:/home/db2inst1> ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/db2inst1/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/db2inst1/.ssh/id_rsa.
Your public key has been saved in /home/db2inst1/.ssh/id_rsa.pub.
The key fingerprint is:
59:6f:0e:f2:58:a0:0f:6f:41:98:07:ac:d3:68:b8:17 db2inst1@ha2
db2inst1@ha2:/home/db2inst1> ssh-copy-id -i .ssh/id_rsa.pub
db2inst1@ha1
15
The authenticity of host 'ha1 (192.168.56.171)' can't be established.
RSA key fingerprint is
28:02:25:bc:fc:86:ac:52:c5:44:1e:52:12:db:64:dd.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ha1,192.168.56.171' (RSA) to the list of known
hosts.
Password:
Now try logging into the machine, with "ssh 'db2inst1@ha1'", and check
in:
.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.
db2inst1@ha2:/home/db2inst1>
测试互信是否OK:
db2inst1@ha2:/home/db2inst1> ssh db2inst1@ha1
db2inst1@ha1:/home/db2inst1> exit
Connection to ha1 closed.
db2inst1@ha2:/home/db2inst1>
db2inst1@ha1:/home/db2inst1> ssh db2inst1@ha2
db2inst1@ha2:/home/db2inst1> exit
Connection to ha2 closed.
db2inst1@ha1:/home/db2inst1>
2、其次创建测试用数据库(主备机)
由于是测试HADR,因此,将此数据库命名为hadr,同时,直接将此数据库放置到实例用户目录下,创建过程如下:
服务器1:
db2inst1@ha1:/home/db2inst1> db2start
06/20/2013 18:19:17 0 0 SQL1063N DB2START processing was
successful.
SQL1063N DB2START processing was successful.
db2inst1@ha1:/home/db2inst1> db2 create db hadr
DB20000I The CREATE DATABASE command completed successfully.
db2inst1@ha1:/home/db2inst1>
服务器2:
>
db2start
06/20/2013 18:11:27 0 0 SQL1063N DB2START processing was
successful.
SQL1063N DB2START processing was successful.
>
db2 create db hadr
DB20000I The CREATE DATABASE command completed
successfully.
>
修改两个机器的通信端口:
服务器1:
ha1:~ # vi /etc/services
DB2c_db2inst1 50000/tcp
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
-- INSERT --
db2inst1@ha1:/home/db2inst1> db2 update dbm cfg using SVCENAME
DB2c_db2inst1
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
db2inst1@ha1:/home/db2inst1> db2 get dbm cfg | grep -i
SVCENAME
TCP/IP Service name (SVCENAME) =
DB2c_db2inst1
SSL service name (SSL_SVCENAME) =
db2inst1@ha1:/home/db2inst1>
服务器2:
ha2:~ # vi /etc/services
DB2c_db2inst1 50000/tcp
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
-- INSERT --
db2inst1@ha2:/home/db2inst1> db2 update dbm cfg using SVCENAME
DB2c_db2inst1
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
db2inst1@ha2:/home/db2inst1> db2 get dbm cfg | grep -i
SVCENAME
TCP/IP Service name (SVCENAME) =
DB2c_db2inst1
SSL service name (SSL_SVCENAME) =
db2inst1@ha2:/home/db2inst1>
3、修改测试用数据库的日志模式为归档日志模式,并调整日志数量和大小
只修改主机的即可,因为一会会用主机的备份到备机上进行恢复
主备机创建一样的归档日志路径:
db2inst1@ha1:/home/db2inst1> mkdir archlog
db2inst1@ha1:/home/db2inst1> cd archlog/
db2inst1@ha1:/home/db2inst1/archlog> pwd
/home/db2inst1/archlog
db2inst1@ha1:/home/db2inst1/archlog>
调整主机的日志参数:
db2 update db cfg for hadr using softmax 300
db2 update db cfg for hadr using LOGPRIMARY 3
db2 update db cfg for hadr using LOGARCHMETH1
DISK:/home/db2inst1/archlo
备份,完成修改:
>
db2 backup db hadr to /dev/null
Backup successful. The timestamp for this backup image is :
20130620184915
>
4、修改数据库的参数
只修改主机的即可,因为一会会用主机的备份到备机上进行恢复
db2 update db cfg for hadr using HADR_LOCAL_HOST ha1
db2 update db cfg for hadr using HADR_LOCAL_SVC 54321
db2 update db cfg for hadr using HADR_REMOTE_HOST ha2
db2 update db cfg for hadr using HADR_REMOTE_SVC 54321
db2 update db cfg for hadr using HADR_REMOTE_INST db2inst1
db2 update db cfg for hadr using HADR_TIMEOUT 120
db2 update db cfg for hadr using HADR_PEER_WINDOW 300
db2 update db cfg for hadr using HADR_SYNCMODE NEARSYNC
写成脚本,直接执行:
> sh
init_cfg.sh
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
DB20000I The UPDATE DATABASE CONFIGURATION command completed
successfully.
5、备份Primary数据库
创建一张测试表,并加入几条数据:
db2inst1@ha1:/home/db2inst1> db2 "create table test (id int)"
DB20000I The SQL command completed successfully.
>
db2 "insert into test values(1)"
DB20000I The SQL command completed
successfully.
开始备份,使用离线备份:
>
mkdir -p backup_data/offline_bak
> cd
backup_data/offline_bak
>
ls
>
db2 terminate
DB20000I The TERMINATE command completed successfully.
>
db2 deactivate db hadr
SQL1496W Deactivate database is successful, but the
database was not
activated.
>
db2 backup db hadr
Backup successful. The timestamp for this backup image is :
20130620185612
>
ls -l
total 88486
-rw------- 1 db2inst1 db2iadm1 90521600 2013-06-20 18:56
HADR.0.db2inst1.NODE0000.CATN0000.20130620185612.001
>
备份完毕。
6、使用Primary数据库的备份在备机上进行恢复,然后修改参数
将备份上传到备机的某个地方(最好和主机一致):
scp .
>
130620185612.001 .
<
HADR.0.db2inst1.NODE0000.CATN0000.20130620185612.001 100% 86MB
8.6MB/s 00:10
>
>
ls
HADR.0.db2inst1.NODE0000.CATN0000.20130620185612.001
>
进行备份恢复:
db2 restore db hadr taken at 20130620185612
>
at 20130620185612 <
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.
>
查看数据库状态:
db2 rollforward db hadr query status
db2inst1@ha2:/home/db2inst1/backup_data/offline_bak> db hadr query
status <
Rollforward Status
Input database alias = hadr
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB pending
Next log file to be read = S0000001.LOG
Log files processed = -
Last committed transaction = 2013-06-20-10.56.16.000000
UTC
db2inst1@ha2:/home/db2inst1/backup_data/offline_bak>
修改数据库参数:
db2 update db cfg for hadr using HADR_LOCAL_HOST ha2
db2 update db cfg for hadr using HADR_REMOTE_HOST ha1
7、启动hadr
启动hadr时,先在备机启动,然后再去主机启动:
备机启动:
db2inst1@ha2:/home/db2inst1> db2 start hadr on db hadr as standby
SQL1766W The command completed successfully. However, LOGINDEXBUILD was
not
enabled before HADR was started.
db2inst1@ha2:/home/db2inst1>
主机启动:
>
db2 start hadr on db hadr as primary
SQL1766W The command completed
successfully. However, LOGINDEXBUILD was not
enabled before HADR was
started.
>
8、查看hadr状态:
主机:
db2inst1@ha1:/home/db2inst1> db2pd -d hadr -hadr
Database Partition 0 -- Database HADR -- Active -- Up 0 days 00:01:34
HADR Information:
Role State SyncMode HeartBeatsMissed LogGapRunAvg
(bytes)
Primary Peer Nearsync 0
0
ConnectStatus ConnectTime Timeout
Connected Thu Jun 20 19:12:41 2013 (1371726761) 120
PeerWindowEnd PeerWindow
Thu Jun 20 19:19:11 2013 (1371727151) 300
LocalHost LocalService
ha1 54321
RemoteHost RemoteService
RemoteInstance
ha2 54321
db2inst1
PrimaryFile PrimaryPg PrimaryLSN
S0000001.LOG 0 0x0000000002313F01
StandByFile StandByPg StandByLSN
S0000001.LOG 0 0x0000000002313F01
db2inst1@ha1:/home/db2inst1>
备机:
>
db2pd -d hadr -hadr
Database Partition 0 -- Database HADR -- Active -- Up 0 days 00:02:33
HADR Information:
Role State SyncMode
HeartBeatsMissed LogGapRunAvg (bytes)
Standby Peer Nearsync
0 0
ConnectStatus ConnectTime Timeout
Connected Thu Jun 20 19:02:31 2013 (1371726151) 120
PeerWindowEnd PeerWindow
Thu Jun 20 19:19:41
2013 (1371727181) 300
LocalHost LocalService
ha2 54321
RemoteHost RemoteService
RemoteInstance
ha1
54321 db2inst1
PrimaryFile PrimaryPg PrimaryLSN
S0000001.LOG 0
0x0000000002313F01
StandByFile StandByPg StandByLSN
StandByRcvBufUsed
S0000001.LOG 0 0x0000000002313F01 0%
>
状态正常。
9、进行切换实验:
首先在主机插入几条数据:
>
db2 "insert into test values(2)"
DB20000I The SQL command completed
successfully.
>
db2 "select * from test"
ID
-----------
1
2
2 record(s) selected.
>
然后,在备机发出切换命令:
db2inst1@ha2:/home/db2inst1> db2 takeover hadr on db hadr
DB20000I The TAKEOVER HADR ON DATABASE command completed
successfully.
db2inst1@ha2:/home/db2inst1>
切换成功后,进行查看:
db2inst1@ha2:/home/db2inst1> db2pd -d hadr -hadr
Database Partition 0 -- Database HADR -- Active -- Up 0 days 00:06:47
HADR Information:
Role State SyncMode HeartBeatsMissed LogGapRunAvg
(bytes)
Primary Peer Nearsync 0
44
ConnectStatus ConnectTime Timeout
Connected Thu Jun 20 19:02:31 2013 (1371726151) 120
PeerWindowEnd PeerWindow
Thu Jun 20 19:13:47 2013 (1371726827) 300
LocalHost LocalService
ha2 54321
RemoteHost RemoteService
RemoteInstance
ha1 54321
db2inst1
PrimaryFile PrimaryPg PrimaryLSN
S0000001.LOG 0 0x0000000002313F59
StandByFile StandByPg StandByLSN
S0000001.LOG 0 0x0000000002313F01
db2inst1@ha2:/home/db2inst1> db2 connect to hadr
Database Connection Information
Database server = DB2/LINUXX8664 9.7.0
SQL authorization ID = DB2INST1
Local database alias = HADR
db2inst1@ha2:/home/db2inst1> db2 "select * from test"
ID
-----------
1
2
2 record(s) selected.
db2inst1@ha2:/home/db2inst1>
原来的备机变为了Primary
而原来的主机呢,如下,变为了Standby
>
db2pd -d hadr -hadr
Database Partition 0 -- Database HADR -- Active -- Up 0 days 00:08:05
HADR Information:
Role State SyncMode
HeartBeatsMissed LogGapRunAvg (bytes)
Standby Peer Nearsync
0 59
ConnectStatus ConnectTime Timeout
Connected Thu Jun 20 19:12:41 2013 (1371726761) 120
PeerWindowEnd PeerWindow
Thu Jun 20 19:14:47
2013 (1371726887) 300
LocalHost LocalService
ha1 54321
RemoteHost RemoteService
RemoteInstance
ha2
54321 db2inst1
PrimaryFile PrimaryPg PrimaryLSN
S0000001.LOG 0
0x0000000002313F59
StandByFile StandByPg StandByLSN
StandByRcvBufUsed
S0000001.LOG 0 0x0000000002313F01 0%
>
至此,hadr环境搭建完毕。