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切換，業務驗證，原庫運行狀況檢查、連接數比對