DB2 V95 数据库 迁移升级至 V105 实战案例

发布时间:2020-08-09 19:42:51 作者:大漠孤鸿
来源:ITPUB博客 阅读:227

cbsdb09-JSJKDB V95迁移升级至tms01db01sz V105步骤

1.       准备工作

新机器tms01db01sz的环境搭建,DB2介质安装及数据库恢复,具体请参考文档《招行HADR环境搭建指南》

DB2 V9.5 - jsjinst1-JSJKDB

Instance创建后修改/etc/services中关于数据库的端口号

数据库恢复之后修改log相关参数:

db2 get db cfg for JSJKDB|grep -i log

db2 update db cfg for JSJKDB using MIRRORLOGPATH /db/mirlog/jsjinst1/JSJKDB

db2 update db cfg for JSJKDB using LOGARCHMETH1 DISK:/db/archm1/

db2 update db cfg for JSJKDB using LOGARCHMETH2 DISK:/db/archm2/

同时建立一个DB2 V10.5 的空库用于CDC预定的搭建

jsjinst2-JSJKDB

 

检查cdcserver和源库,目标库的连接是否有问题,如不通则找行方DBA协调解决防火墙问题

telnet 10.0.58.18 50020

telnet 10.2.59.184 50020

 

在源库对JSJKDB所有要同步的业务表开启data capture changes include longvar columns 属性

db2 "select 'alter table '||trim(tabschema)||'.'||trim(tabname)||' data capture changes include longvar columns ;' from syscat.tables where type='T' and tabschema not like 'SYS%' and datacapture<>'L'"|tee alter_table_fbudb.sql

db2 -tvf alter_table_fbudb.sql|tee alter_table_fbudb.out

 

在源和目标机器上新建用户cdcuser,并加入实例用户组,赋DBADM权限(cbsdb09-jsjinst1tms01db01sz- jsjinst2

mkuser pgrp=jsjigrp1 shell=/usr/bin/ksh cdcuser

db2 "grant DBADM on database to user cdcuser"

 

修改源库identity column(必须在db2look之前,否则identity column无法复制)

select 'alter table '||trim(tabschema)||'.'||TABNAME||' alter column '||COLNAME||' set GENERATED by default;' from syscat.columns where left(tabschema,3)<>'SYS' and generated<>'' and generated!='D' and  tabschema='FMDBRUN'

 

alter table FMDBRUN.SYBRADTAP alter column BRDSEQNBR set GENERATED by default;

alter table FMDBRUN.ACFIXCUR alter column CURDTLSEQ set GENERATED by default;

alter table FMDBRUN.DT_LOTRSPAYP alter column LPYROWNUM set GENERATED by default;

alter table FMDBRUN.ACTRSDTLP alter column ATSBUSNBR set GENERATED by default;

 

db2look备份源库DDL

db2look -d JSJKDB -a -e -l -x -o JSJKDB.ddl

 

JSJKDB.ddl拷贝到到新机器tms01db01sz上,用jsjinst2执行:

在跳板机器如:[01057007@szsc-core2 ~]上执行(文件都放在/tmp目录):

cp /opsw/Server/@/cbsdb09/files/dbmonusr/tmp/JSJKDB.ddl /opsw/Server/@/tms01db01sz/files/dbmonusr/tmp/

db2 -tvf JSJKDB.ddl | tee JSJKDB.out

 

查看所有表数量,确保要复制的表都已经创建成功

db2 "select substr(tabschema,1,20) tabschema,count(*) as tabnums from syscat.tables where tabschema='FMDBRUN' and type='T' group by tabschema"

 

2.   正向CDC搭建(cbsdb09 v9.5->tms01db01sz-jsjinst2 v10.5)

登录cdcuser@cdcserver,编目源库和目标库( jsjinst2-JSJKDB)并用cdcuser测试能否连接数据库:

. ~srcinst1/sqllib/db2profile

db2 catalog tcpip node S_JSJKDB remote 10.0.58.18 server 50020

db2 catalog db JSJKDB as S_JSJKDB at node S_JSJKDB

. ~tgtinst1/sqllib/db2profile

db2 catalog tcpip node T_JSJKDB remote 10.2.59.184 server 50001

db2 catalog db JSJKDB as T_JSJKDB at node T_JSJKDB

 

使用如下命令创建并启动CDC实例s_jsjkdbt_jsjkdb(输入CDC作为CDC数据的模式名)

/cdcopt/ReplicationEngineforIBMDB2/bin/dmconfigurets

检查CDC实例进程是否启动:

ps -ef|grep dmts64

启停命令:

nohup /cdcopt/ReplicationEngineforIBMDB2/bin/dmts64 -I s_jsjkdb &

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I s_jsjkdb

查看状态:

/cdcopt/ReplicationEngineforIBMDB2/bin/dmgetstagingstorestatus -I s_jsjkdb

检查CDC复制日志:

/cdcopt/ReplicationEngineforIBMDB2/instance/t_jsjkdb/log

 

登录CDC控制台,创建datastore及预定,具体过程参考文档《CDC安装配置规范》

预定建好后选定所有的表,点右键,选择parktables,标记捕获点

 

标记外部刷新开始(对预定中的所有表执行如下命令)

/cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadstart -I s_jsjkdb -s <预定名,如:sub_jsjkdb> -t ${_TableName}

可连接源库用如下语句生成脚本:

db2 "select '/cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadstart -I s_jsjkdb -s sub_jsjkdb -t '||trim(tabschema)||'.'||trim(tabname) from syscat.tables where type='T' and tabschema='FMDBRUN'"|tee markstart.sh

 

源库手工归档,确保新库能滚日志到外部刷新开始之后:

db2 archive log for db JSJKDB

 

标记外部刷新结束(对预定中的所有表执行如下命令)

/cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadend -I s_jsjkdb -s <预定名,如:s_jsjkdb> -t ${_TableName}

可连接源库用如下语句生成脚本:

db2 "select '/cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadend -I s_jsjkdb -s s_jsjkdb -t '||trim(tabschema)||'.'||trim(tabname) from syscat.tables where type='T' and tabschema='FMDBRUN'"|tee markend.sh

 

jsjinst2备份CDC数据

db2move jsjkdb export -sn CDC

 

拷贝所有需要的日志到新机器tms01db01sz(文件都放在/tmp目录),使用jsjinst1前滚打开数据库:

cp /opsw/Server/@/cbsdb09/files/dbmonusr/tmp/S00033*.LOG /opsw/Server/@/tms01db01sz/files/dbmonusr/tmp/

db2 "rollforward db JSJKDB to end of logs overflow log path ('/db/archm1/overflowlogs') noretrieve"

 

查看rollforward状态,确保新库能滚日志到外部刷新开始之后:

db2 rollforward db JSJKDB query status using local time

 

打开数据库:

db2 "rollforward db JSJKDB stop"

 

将数据库升级至v10.5.5,重绑定包

参考文档《DB2V9.5-10.5升级方案》

 

jsjinst1恢复CDC数据

db2move jsjkdb import

 

CDC重编目指向新升级的v10.5.5数据库

. ~tgtinst1/sqllib/db2profile

db2 uncatalog node T_JSJKDB

db2 uncatalog db T_JSJKDB

db2 catalog tcpip node T_JSJKDB remote 10.2.59.184 server 50000

db2 catalog db JSJKDB as T_JSJKDB at node T_JSJKDB

 

重启目标库的实例使之连接新升级的v10.5.5数据库

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I t_jsjkdb

nohup /cdcopt/ReplicationEngineforIBMDB2/bin/dmts64 -I t_jsjkdb &

 

CDC控制台操作,启动正向预定(v9.5->v10.5.5),监控CDC的状态,检查源库和目标库数据的一致性

 

删除jsjinst2-JSJKDBdrop instance,删除用户

db2 drop db JSJKDB

db2stop

/opt/IBM/db2/V10.5.5/instance/db2idrop jsjinst2

rmuser jsjinst2

 

新环境的监控、备份配置的部署和确认-检查tivoli监控,dbmdb新监控,NBU调度策略

 

新环境HADR搭建

 

JSJKDB 创建反向CDC预订(新库v10.5->源库v9.5)并停用

 

回收新环境的应用用户CONNECT权限

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

db2 -v "revoke connect on database from user FMDBRUN,ITMUSER,FMQRYNJ,DBMUID";

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

 

3.       实施步骤

源库JSJKDB应用运行状况检查(收集应用连接数)

db2 list applications for db JSJKDB > $HOME/app_FBUDB_before.`date +%H%M%S`.txt

 

回收源库JSJKDB应用用户的connect权限

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

db2 -v "revoke connect on database from user FMDBRUN,ITMUSER,FMQRYNJ,DBMUID";

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

 

断开源库JSJKDB的应用连接,确保没有应用连接上来

db2 list applications|grep -iw JSJKDB|grep -iv cdcuser|awk '{print "db2 \"force application ("$3")\";"}'|tee force_JSJKDB_app.sql

 

登陆MC,检查cdc同步情况,停止正向订阅

 

新库JSJKDBseq的序列号和identity column1000(无序列)-在源库执行下列SQL并将执行结果拷贝到新库执行:

select 'alter table ' ||trim(tabschema)|| '.' ||tabname || ' alter column '|| colname || ' restart with '||trim(char(bigint(NEXTCACHEFIRSTVALUE + 1000))) || ';'  from syscat.colidentattributes

 

JSJKDB 新库与原库数据一致性比对

 

启动反向复制

 

放开新库fbudb11FBUDB的应用用户的connect权限

db2 "grant connect on database to user FMDBRUN,ITMUSER,FMQRYNJ,DBMUID"

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

 

F5切换,新F5设备对外释放VIP

 

通知JSJKDB相关系统进行开发、业务验证

 

新库运行状况检查、连接数比对、CDC同步情况检查

db2 list applications for db JSJKDB > $HOME/app_FBUDB_after.`date +%H%M%S`.txt

 

4.       变更后处理

回收权限并删除CDC用户

db2 "revoke DBADM on database from user cdcuser"

rmuser cdcuser

 

CDC控制台删除预定,datastore

 

cdcserver上删除实例

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I s_jsjkdb

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I t_jsjkdb

/cdcopt/ReplicationEngineforIBMDB2/bin/dmconfigurets

 

取消数据库编目

. ~srcinst1/sqllib/db2profile

db2 uncatalog node S_JSJKDB

db2 uncatalog db S_JSJKDB

. ~tgtinst1/sqllib/db2profile

db2 uncatalog node T_JSJKDB

db2 uncatalog db T_JSJKDB

 

旧库全库备份及下线

 

5.       回退步骤

回收新环境的应用用户CONNECT权限

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

db2 -v "revoke connect on database from user FMDBRUN,ITMUSER,FMQRYNJ,DBMUID";

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

 

断开新库所有应用连接

db2 list applications|grep -iw JSJKDB|grep -iv cdcuser|awk '{print "db2 \"force application ("$3")\";"}'|tee force_JSJKDB_app.sql

 

断开CDC订阅

 

源库JSJKDBseq的序列号和identity column1000(无序列)-在新库执行下列SQL并将执行结果拷贝到源库执行:

select 'alter table ' ||trim(tabschema)|| '.' ||tabname || ' alter column '|| colname || ' restart with '||trim(char(bigint(NEXTCACHEFIRSTVALUE + 1000))) || ';'  from syscat.colidentattributes

 

数据一致性比对

 

放开源库应用用户的connect权限

db2 "grant connect on database to user FMDBRUN,ITMUSER,FMQRYNJ,DBMUID"

db2 "select substr(grantor,1,10) grantor,substr(grantee,1,10) grantee,connectauth from syscat.dbauth where connectauth='Y' with ur";

 

F5切换,业务验证,原库运行状况检查、连接数比对

推荐阅读:
  1. db2 v10安装
  2. DB2 SQL Replication 配置方法

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

db2 v95 v105

上一篇:Flink State 有可能代替数据库吗?

下一篇:关于窗体右上角的关闭按钮

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》