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切换，业务验证，原库运行状况检查、连接数比对
