Shulou(Shulou.com)06/01 Report--

Cbsdb09-JSJKDB V95 Migration upgrade to tms01db01sz V105 step

1. Preparatory work

New machine tms01db01sz environment construction, DB2 media installation and database recovery. Please refer to the document "China Merchants Bank HADR Environment Building Guide".

DB2 V9.5-jsjinst1-JSJKDB

Modify the port number of the database in / etc/services after Instance creation

Modify log-related parameters after database recovery:

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/

At the same time, an empty library of DB2 V10.5 is established for the scheduled construction of CDC.

Jsjinst2-JSJKDB

Check whether there is a problem with the connection between the cdcserver and the source database and the target database. If not, find the DBA to coordinate to solve the firewall problem.

Telnet 10.0.58.18 50020

Telnet 10.2.59.184 50020

Turn on the data capture changes include longvar columns attribute in the source database for all business tables to be synchronized by JSJKDB

Db2 "select 'alter table' | | trim (tabschema) | |'. | | trim (tabname) | | 'datacapture 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

Create a new user cdcuser on the source and destination machines, and join the instance user group with DBADM permission (cbsdb09-jsjinst1,tms01db01sz- jsjinst2)

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

Db2 "grant DBADM on database to user cdcuser"

Modify the source library identity column (must be before db2look, otherwise identity column cannot copy)

Select 'alter table' | | trim (tabschema) | |'. | | TABNAME | | 'alter column' | | COLNAME | | 'set GENERATED by default;' from syscat.columns where left (tabschema,3)' SYS' and generated'' and generatedgeneratedgenerationD'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 backup source library DDL

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

Copy the JSJKDB.ddl to the new machine tms01db01sz and execute it with jsjinst2:

Execute on a springboard machine such as [01057007@szsc-core2 ~] (files are placed in the / tmp directory):

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

Db2-tvf JSJKDB.ddl | tee JSJKDB.out

Check the number of all tables to ensure that the tables to be replicated have been created successfully

Db2 "select substr (tabschema,1,20) tabschema,count (*) as tabnums from syscat.tables where tabschema='FMDBRUN' and type='T' group by tabschema"

two。 Forward CDC build (cbsdb09 v9.5-> tms01db01sz-jsjinst2 v10.5)

Log in to cdcuser@cdcserver, catalog the source and target libraries (jsjinst2-JSJKDB), and use cdcuser to test whether you can connect to the database:

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

Use the following command to create and start the CDC instance sroomjsjkdb (enter CDC as the schema name for the CDC data)

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmconfigurets

Check whether the CDC instance process is started:

Ps-ef | grep dmts64

Start and stop command:

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

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown-I s_jsjkdb

View status:

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmgetstagingstorestatus-I s_jsjkdb

Check the CDC replication log:

/ cdcopt/ReplicationEngineforIBMDB2/instance/t_jsjkdb/log

Log in to the CDC console, create a datastore and book it. For more information, please see the document "CDC installation and configuration Specification".

Select all tables after the reservation is built, right-click, select parktables, and mark the capture point

Mark the start of the external refresh (execute the following command for all tables in the reservation)

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadstart-I s_jsjkdb-s-t ${_ TableName}

You can connect to the source library to generate a script with the following statement:

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 |

The source library is manually archived to ensure that the new library rolls the logs to the external refresh after the start:

Db2 archive log for db JSJKDB

Mark the end of the external refresh (execute the following command for all tables in the reservation)

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmmarkexternalunloadend-I s_jsjkdb-s-t ${_ TableName}

You can connect to the source library to generate a script with the following statement:

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 |

Backing up CDC data with jsjinst2

Db2move jsjkdb export-sn CDC

Copy all the required logs to the new machine tms01db01sz (the files are in the / tmp directory), and use jsjinst1 to roll forward to open the database:

Cp / opsw/Server/@/cbsdb09/files/dbmonusr/tmp/S00033*.LOG / opsw/Server/@/tms01db01sz/files/dbmonusr/tmp/

Db2 "rollforward db JSJKDB to end of logs overflowlog path ('/ db/archm1/overflowlogs') noretrieve"

Check the rollforward status to ensure that the new library rolls the log to the external refresh after the start:

Db2 rollforward db JSJKDB query status using local time

Open the database:

Db2 "rollforward db JSJKDB stop"

Upgrade the database to v10.5.5 and rebind the package

Refer to the document "DB2V9.5-10.5 upgrade Scheme"

Using jsjinst1 to recover CDC data

Db2move jsjkdb import

CDC recataloging points to the newly upgraded v10.5.5 database

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

Restart the instance of the target library to connect to the newly upgraded v10.5.5 database

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown-I t_jsjkdb

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

Operate in the CDC console, start forward booking (v9.5-> v10.5.5), monitor the status of CDC, and check the consistency of source and target database data

Delete jsjinst2-JSJKDB and drop instance, delete user

Db2 drop db JSJKDB

Db2stop

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

Rmuser jsjinst2

Monitoring of new environment, deployment and confirmation of backup configuration-check tivoli monitoring, dbmdb new monitoring, NBU scheduling policy

Construction of new environment HADR

JSJKDB creates a reverse CDC subscription (new library v10.5-> source library v9.5) and deactivates it

Reclaim the CONNECT rights of the application user in the new environment

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. Implementation steps

Source database JSJKDB application health check (collect the number of application connections)

Db2 list applications for db JSJKDB > $HOME/app_FBUDB_ before.`date +% H% M% S`.txt

Reclaim the connect rights of the JSJKDB application user of the source database

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"

Disconnect the application connection of the source database JSJKDB to ensure that there is no application connection.

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

Log in to MC, check cdc synchronization, and stop forward subscriptions

Sequence number and identity column plus 1000 (no sequence) of the seq of the new library JSJKDB-execute the following SQL in the source database and copy the execution result to the new library for execution:

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

Data consistency comparison between the new JSJKDB database and the original database

Start reverse replication

Release the connect rights of application users of FBUDB on the new library fbudb11

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 switch, the new F5 device releases VIP

Notify JSJKDB related systems for development and business verification

New library health check, connection number comparison, CDC synchronization check

Db2 list applications for db JSJKDB > $HOME/app_FBUDB_ room.`date +% H% M% S`.txt

4. Post-change processing

Reclaim permissions and delete CDC users

Db2 "revoke DBADM on database from user cdcuser"

Rmuser cdcuser

Delete reservations in the CDC console, datastore

Delete an instance on cdcserver

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown-I s_jsjkdb

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmshutdown-I t_jsjkdb

/ cdcopt/ReplicationEngineforIBMDB2/bin/dmconfigurets

Cancel database cataloging

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

Full backup and offline of the old database

5. Fallback step

Reclaim the CONNECT rights of the application user in the new environment

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"

Disconnect all applications of the new library

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

Disconnect CDC subscription

Sequence number and identity column plus 1000 (no sequence) of the seq of the source library JSJKDB-execute the following SQL in the new library and copy the execution result to the source database for execution:

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

Data consistency comparison

Release the connect rights of the source database application user

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 switching, business verification, original library health check, connection number comparison

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.