DB2 V95 數(shù)據(jù)庫(kù) 遷移升級(jí)至 V105 實(shí)戰(zhàn)案例

cbsdb09-JSJKDB V95遷移升級(jí)至tms01db01sz V105步驟

1.       準(zhǔn)備工作

新機(jī)器tms01db01sz的環(huán)境搭建，DB2介質(zhì)安裝及數(shù)據(jù)庫(kù)恢復(fù)，具體請(qǐng)參考文檔《招行HADR環(huán)境搭建指南》

DB2 V9.5 - jsjinst1-JSJKDB

Instance創(chuàng)建后修改/etc/services中關(guān)于數(shù)據(jù)庫(kù)的端口號(hào)

數(shù)據(jù)庫(kù)恢復(fù)之后修改log相關(guān)參數(shù)：

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/

同時(shí)建立一個(gè)DB2 V10.5 的空庫(kù)用于CDC預(yù)定的搭建

jsjinst2-JSJKDB

檢查cdcserver和源庫(kù)，目標(biāo)庫(kù)的連接是否有問(wèn)題，如不通則找行方DBA協(xié)調(diào)解決防火墻問(wèn)題

telnet 10.0.58.18 50020

telnet 10.2.59.184 50020

在源庫(kù)對(duì)JSJKDB所有要同步的業(yè)務(wù)表開啟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

在源和目標(biāo)機(jī)器上新建用戶cdcuser，并加入實(shí)例用戶組，賦DBADM權(quán)限（cbsdb09-jsjinst1，tms01db01sz- jsjinst2）

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

db2 "grant DBADM on database to user cdcuser"

修改源庫(kù)identity column(必須在db2look之前，否則identity column無(wú)法復(fù)制)

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備份源庫(kù)DDL

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

將JSJKDB.ddl拷貝到到新機(jī)器tms01db01sz上，用jsjinst2執(zhí)行：

在跳板機(jī)器如：[01057007@szsc-core2 ~]上執(zhí)行（文件都放在/tmp目錄）：

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

db2 -tvf JSJKDB.ddl | tee JSJKDB.out

查看所有表數(shù)量，確保要復(fù)制的表都已經(jīng)創(chuàng)建成功

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，編目源庫(kù)和目標(biāo)庫(kù)（ jsjinst2-JSJKDB）并用cdcuser測(cè)試能否連接數(shù)據(jù)庫(kù)：

. ~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

使用如下命令創(chuàng)建并啟動(dòng)CDC實(shí)例s_jsjkdb，t_jsjkdb(輸入CDC作為CDC數(shù)據(jù)的模式名)

/cdcopt/ReplicationEngineforIBMDB2/bin/dmconfigurets

檢查CDC實(shí)例進(jìn)程是否啟動(dòng)：

ps -ef|grep dmts64

啟停命令：

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

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I s_jsjkdb

查看狀態(tài)：

/cdcopt/ReplicationEngineforIBMDB2/bin/dmgetstagingstorestatus -I s_jsjkdb

檢查CDC復(fù)制日志：

/cdcopt/ReplicationEngineforIBMDB2/instance/t_jsjkdb/log

登錄CDC控制臺(tái)，創(chuàng)建datastore及預(yù)定，具體過(guò)程參考文檔《CDC安裝配置規(guī)范》

預(yù)定建好后選定所有的表，點(diǎn)右鍵，選擇parktables，標(biāo)記捕獲點(diǎn)

標(biāo)記外部刷新開始（對(duì)預(yù)定中的所有表執(zhí)行如下命令）

/cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadstart -I s_jsjkdb -s <預(yù)定名，如:sub_jsjkdb> -t ${_TableName}

可連接源庫(kù)用如下語(yǔ)句生成腳本：

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

源庫(kù)手工歸檔，確保新庫(kù)能滾日志到外部刷新開始之后：

db2 archive log for db JSJKDB

標(biāo)記外部刷新結(jié)束（對(duì)預(yù)定中的所有表執(zhí)行如下命令）

/cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadend -I s_jsjkdb -s <預(yù)定名，如:s_jsjkdb> -t ${_TableName}

可連接源庫(kù)用如下語(yǔ)句生成腳本：

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數(shù)據(jù)

db2move jsjkdb export -sn CDC

拷貝所有需要的日志到新機(jī)器tms01db01sz（文件都放在/tmp目錄），使用jsjinst1前滾打開數(shù)據(jù)庫(kù)：

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狀態(tài)，確保新庫(kù)能滾日志到外部刷新開始之后：

db2 rollforward db JSJKDB query status using local time

打開數(shù)據(jù)庫(kù)：

db2 "rollforward db JSJKDB stop"

將數(shù)據(jù)庫(kù)升級(jí)至v10.5.5，重綁定包

參考文檔《DB2V9.5-10.5升級(jí)方案》

用 jsjinst1恢復(fù)CDC數(shù)據(jù)

db2move jsjkdb import

CDC重編目指向新升級(jí)的v10.5.5數(shù)據(jù)庫(kù)

. ~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

重啟目標(biāo)庫(kù)的實(shí)例使之連接新升級(jí)的v10.5.5數(shù)據(jù)庫(kù)

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I t_jsjkdb

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

在CDC控制臺(tái)操作，啟動(dòng)正向預(yù)定(v9.5->v10.5.5)，監(jiān)控CDC的狀態(tài)，檢查源庫(kù)和目標(biāo)庫(kù)數(shù)據(jù)的一致性

刪除jsjinst2-JSJKDB并drop instance，刪除用戶

db2 drop db JSJKDB

db2stop

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

rmuser jsjinst2

新環(huán)境的監(jiān)控、備份配置的部署和確認(rèn)-檢查tivoli監(jiān)控，dbmdb新監(jiān)控，NBU調(diào)度策略

新環(huán)境HADR搭建

JSJKDB 創(chuàng)建反向CDC預(yù)訂（新庫(kù)v10.5->源庫(kù)v9.5）并停用

回收新環(huán)境的應(yīng)用用戶CONNECT權(quán)限

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.       實(shí)施步驟

源庫(kù)JSJKDB應(yīng)用運(yùn)行狀況檢查（收集應(yīng)用連接數(shù)）

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

回收源庫(kù)JSJKDB應(yīng)用用戶的connect權(quán)限

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";

斷開源庫(kù)JSJKDB的應(yīng)用連接，確保沒(méi)有應(yīng)用連接上來(lái)

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

登陸MC,檢查cdc同步情況，停止正向訂閱

新庫(kù)JSJKDB的seq的序列號(hào)和identity column加1000（無(wú)序列）-在源庫(kù)執(zhí)行下列SQL并將執(zhí)行結(jié)果拷貝到新庫(kù)執(zhí)行：

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

JSJKDB 新庫(kù)與原庫(kù)數(shù)據(jù)一致性比對(duì)

啟動(dòng)反向復(fù)制

放開新庫(kù)fbudb11上FBUDB的應(yīng)用用戶的connect權(quán)限

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設(shè)備對(duì)外釋放VIP

通知JSJKDB相關(guān)系統(tǒng)進(jìn)行開發(fā)、業(yè)務(wù)驗(yàn)證

新庫(kù)運(yùn)行狀況檢查、連接數(shù)比對(duì)、CDC同步情況檢查

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

4.       變更后處理

回收權(quán)限并刪除CDC用戶

db2 "revoke DBADM on database from user cdcuser"

rmuser cdcuser

在CDC控制臺(tái)刪除預(yù)定，datastore

在cdcserver上刪除實(shí)例

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I s_jsjkdb

/cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown -I t_jsjkdb

/cdcopt/ReplicationEngineforIBMDB2/bin/dmconfigurets

取消數(shù)據(jù)庫(kù)編目

. ~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

舊庫(kù)全庫(kù)備份及下線

5.       回退步驟

回收新環(huán)境的應(yīng)用用戶CONNECT權(quán)限

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";

斷開新庫(kù)所有應(yīng)用連接

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

斷開CDC訂閱

源庫(kù)JSJKDB的seq的序列號(hào)和identity column加1000（無(wú)序列）-在新庫(kù)執(zhí)行下列SQL并將執(zhí)行結(jié)果拷貝到源庫(kù)執(zhí)行：

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

數(shù)據(jù)一致性比對(duì)

放開源庫(kù)應(yīng)用用戶的connect權(quán)限

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切換，業(yè)務(wù)驗(yàn)證，原庫(kù)運(yùn)行狀況檢查、連接數(shù)比對(duì)