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

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-jsjinst1，tms01db01sz- 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_jsjkdb，t_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-JSJKDB并drop 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同步情况，停止正向订阅

新库JSJKDB的seq的序列号和identity column加1000（无序列）-在源库执行下列SQL并将执行结果拷贝到新库执行：

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

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

启动反向复制

放开新库fbudb11上FBUDB的应用用户的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订阅

源库JSJKDB的seq的序列号和identity column加1000（无序列）-在新库执行下列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切换，业务验证，原库运行状况检查、连接数比对