db2主备部署(hadr)
环境:
OS:Centos 7
DB:v11.5.6
主库:192.168.1.135 host135
从库:192.168.1.134 host134
说明:
a.主库已经运行并有数据库DB_HXL,DB_HXL01
可以使用如下命令查看:
db2 list database directory
b.主数据库已经处于归档模式
db2 get db cfg for db_hxl |grep -i LOGARCHMETH1
db2 get db cfg for db_hxl01 |grep -i LOGARCHMETH1
c.从库已经创建了实例,但是不建库
实例名与主库保持一致,并启动实例
[db2inst1@host134 ~]$ ss -nlp|grep 50000
1.主库配置HADR同步参数
db2inst1用户下执行
注意每个库都需要指定不同的端口:db_hxl使用60006,db_hxl01使用60007
db2 update db cfg for db_hxl using logretain on ##去掉
db2 update db cfg for db_hxl using trackmod on ##好像需要重启动
db2 update db cfg for db_hxl using logindexbuild on
db2 update db cfg for db_hxl using indexrec restart
db2 update db cfg for db_hxl using HADR_LOCAL_HOST 192.168.1.135 ##好像需要重启动
db2 update db cfg for db_hxl using HADR_LOCAL_SVC 60006 (当启动HADR后，这个端口就会打开，负责复制的通信，端口可自定义)
db2 update db cfg for db_hxl using HADR_REMOTE_HOST 192.168.1.134
db2 update db cfg for db_hxl using HADR_REMOTE_SVC 60006
db2 update db cfg for db_hxl using HADR_REMOTE_INST db2inst1
db2 update db cfg for db_hxl using HADR_TIMEOUT 120
db2 update db cfg for db_hxl using HADR_PEER_WINDOW 10
db2 update db cfg for db_hxl using HADR_SYNCMODE sync
db2 update db cfg for db_hxl01 using logretain on ##去掉
db2 update db cfg for db_hxl01 using trackmod on
db2 update db cfg for db_hxl01 using logindexbuild on
db2 update db cfg for db_hxl01 using indexrec restart
db2 update db cfg for db_hxl01 using HADR_LOCAL_HOST 192.168.1.135
db2 update db cfg for db_hxl01 using HADR_LOCAL_SVC 60007 (当启动HADR后，这个端口就会打开，负责复制的通信，端口可自定义)
db2 update db cfg for db_hxl01 using HADR_REMOTE_HOST 192.168.1.134
db2 update db cfg for db_hxl01 using HADR_REMOTE_SVC 60007
db2 update db cfg for db_hxl01 using HADR_REMOTE_INST db2inst1
db2 update db cfg for db_hxl01 using HADR_TIMEOUT 120
db2 update db cfg for db_hxl01 using HADR_PEER_WINDOW 10
db2 update db cfg for db_hxl01 using HADR_SYNCMODE sync
尝试重启数据库
db2 restart db db_hxl
db2 restart db db_hxl01
[db2inst1@host135 ~]$ db2 restart db db_hxl
DB20000I The RESTART DATABASE command completed successfully.
[db2inst1@host135 ~]$ db2 restart db db_hxl01
SQL1116N A connection to or activation of database "DB_HXL01" failed because the database is in BACKUP PENDING state. SQLSTATE=57019
解决办法:
db2 backup db db_hxl01 to /dev/null
再次重启动db_hxl01
查看配置:
db2 get db cfg for db_hxl|grep HADR_LOCAL_HOST
db2 get db cfg for db_hxl01|grep HADR_LOCAL_HOST
db2 get db cfg for db_hxl|grep HADR_LOCAL_SVC
db2 get db cfg for db_hxl01|grep HADR_LOCAL_SVC
2.备份数据库
在主库的db2inst1用户下执行,2个库都要进行备份
db2 force applications all
db2 backup database db_hxl to /home/db2inst1/backup
[db2inst1@host135 backup]$ db2 backup database db_hxl to /home/db2inst1/backup
SQL1433N The application is already connected to "DB_HXL01" while the command
issued requires a connection to "DB_HXL" for successful execution.
解决办法:
退出后重新登录执行
db2 backup database db_hxl01 to /home/db2inst1/backup
整个执行命令
[db2inst1@host135 ~]$ db2 backup database db_hxl to /home/db2inst1/backup
Backup successful. The timestamp for this backup image is : 20240130091247
[db2inst1@host135 ~]$ db2 backup database db_hxl01 to /home/db2inst1/backup
Backup successful. The timestamp for this backup image is : 20240130091344
3.备份文件拷贝到备库
拷贝到目的库相应的目录
scp DB_HXL01.0.db2inst1.DBPART000.20240130091344.001 db2inst1@192.168.1.134:/home/db2inst1/backup/
scp DB_HXL.0.db2inst1.DBPART000.20240130091247.001 db2inst1@192.168.1.134:/home/db2inst1/backup/
4.备库创建数据库库并恢复
在备库上执行
db2 create db db_hxl using codeset utf-8 territory CN --done
db2 restore db db_hxl from /home/db2inst1/backup
[db2inst1@host134 backup]$ db2 restore db db_hxl from /home/db2inst1/backup
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 create db db_hxl01 using codeset utf-8 territory CN --done
db2 restore db db_hxl01 from /home/db2inst1/backup
[db2inst1@host134 backup]$ db2 restore db db_hxl01 from /home/db2inst1/backup
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.
5.开启备库可读
db2set DB2_HADR_ROS=ON
db2set DB2_STANDBY_ISO=UR
6.修改备库参数
注意每个库都需要指定不同的端口:db_hxl使用60006,db_hxl01使用60007
配置备库参数：
db2 update db cfg for db_hxl using logretain on ##取消
db2 update db cfg for db_hxl using trackmod on
db2 update db cfg for db_hxl using logindexbuild on
db2 update db cfg for db_hxl using indexrec restart
db2 update db cfg for db_hxl using HADR_LOCAL_HOST 192.168.1.134
db2 update db cfg for db_hxl using HADR_LOCAL_SVC 60006
db2 update db cfg for db_hxl using HADR_REMOTE_HOST 192.168.1.135
db2 update db cfg for db_hxl using HADR_REMOTE_SVC 60006
db2 update db cfg for db_hxl using HADR_REMOTE_INST db2inst1
db2 update db cfg for db_hxl using HADR_TIMEOUT 120
db2 update db cfg for db_hxl using HADR_PEER_WINDOW 10
db2 update db cfg for db_hxl using HADR_SYNCMODE sync
db2 update db cfg for db_hxl01 using logretain on ##取消
db2 update db cfg for db_hxl01 using trackmod on
db2 update db cfg for db_hxl01 using logindexbuild on
db2 update db cfg for db_hxl01 using indexrec restart
db2 update db cfg for db_hxl01 using HADR_LOCAL_HOST 192.168.1.134
db2 update db cfg for db_hxl01 using HADR_LOCAL_SVC 60007
db2 update db cfg for db_hxl01 using HADR_REMOTE_HOST 192.168.1.135
db2 update db cfg for db_hxl01 using HADR_REMOTE_SVC 60007
db2 update db cfg for db_hxl01 using HADR_REMOTE_INST db2inst1
db2 update db cfg for db_hxl01 using HADR_TIMEOUT 120
db2 update db cfg for db_hxl01 using HADR_PEER_WINDOW 10
db2 update db cfg for db_hxl01 using HADR_SYNCMODE sync
7.启动备库，必须先启动备库，再启动主库
db2stop
db2start
若实例已经启动的情况下,不需要重启动
db2 "deactivate database db_hxl"
db2 "deactivate database db_hxl01"
db2 start hadr on db db_hxl as standby
db2 start hadr on db db_hxl01 as standby
8.启动主库
db2 "deactivate database db_hxl"
db2 "deactivate database db_hxl01"
db2 start hadr on db db_hxl as primary
db2 start hadr on db db_hxl01 as primary
9.监控主备数据库状态:
db2pd -hadr -db db_hxl
db2pd -hadr -db db_hxl01
10.数据验证
主库建表,并写入数据
[db2inst1@host135 ~]$ db2 connect to db_hxl
db2 "
create table tb_hxl01(id bigint not null generated by default as identity (start with 1,increment by 1),name varchar(20),user_ages int)
"
这种模式好像不好换行
[db2inst1@host135 bin]$ db2
db2 => connect to db_hxl
db2 => create table tb_hxl01(id bigint not null generated by default as identity (start with 1,increment by 1),name varchar(20),user_ages int)
db2 => insert into tb_hxl01(name,user_ages) values('hxl',20);
DB20000I The SQL command completed successfully.
db2 => insert into tb_hxl01(name,user_ages) values('hxl01',30);
DB20000I The SQL command completed successfully.
11.登录备库查看
