您好,登录后才能下订单哦!
cbsdb09-JSJKDB V95迁移升级至tms01db01sz V105步骤
新机器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"
登录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";
源库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
回收权限并删除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
旧库全库备份及下线
回收新环境的应用用户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切换,业务验证,原库运行状况检查、连接数比对
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。