cdc目標端資料庫初始化（backup restore）
1、 backup database to establish table mapping
[db2inst1@mongo1 ~]$ db2 backup db sample online include logs
Backup successful. The timestamp for this backup image is : 20181030093222
[db2inst1@mongo1 ~]$ ll
total 182968
-rw-rw-r-- 1 db2inst1 db2inst1 1715 Oct 30 00:43 1.sql
-rw-rw-r-- 1 db2inst1 db2inst1 1668 Oct 30 00:47 2.sql
drwxrwxr-x 3 db2inst1 db2inst1 22 Oct 29 22:53 archlog
-rw-rw-r-- 1 db2inst1 db2inst1 1474496 Oct 29 14:23 date.log
drwxrwxr-x 3 db2inst1 db2inst1 22 Oct 23 23:40 db2inst1
drwxrwxr-x 5 db2inst1 db2inst1 180 Oct 24 10:30 mon
-rw------- 1 db2inst1 db2inst1 185872384 Oct 30 09:32 SAMPLE.0.db2inst1.DBPART000.20181030093222.001
drwxrwsr-t 24 db2inst1 db2inst1 4096 Oct 29 22:54 sqllib
[db2inst1@mongo1 ~]$ scp SAMPLE.0.db2inst1.DBPART000.20181030093222.001 db2inst1@mongo2:/home/db2inst1
db2inst1@mongo2's password:
SAMPLE.0.db2inst1.DBPART000.20181030093222.001 100% 177MB 26.9MB/s 00:06
[db2inst1@mongo1 ~]$
2、 target create cdc instance
[db2inst1@mongo2 ~]$ cd /opt/ibm/InfoSphereDataReplication/ReplicationEngineforIBMDB2/bin/
[db2inst1@mongo2 bin]$ ./dmconfigurets
Initializing. Please wait...
MAIN MENU
---------
1. List Current Instances
2. Add an Instance
3. Edit an Instance
4. Delete an Instance
5. Consolidate Instances
6. Exit
Enter your selection:4
DELETING AN INSTANCE
--------------------
List of current Instances:
1. target
2. sample
Enter the number of the instance you want to delete or press ENTER to return to the Main menu:2
Are you sure you want to delete instance sample (y/n):y
Instance sample successfully deleted.
Press ENTER to return to the Main menu...
MAIN MENU
---------
1. List Current Instances
2. Add an Instance
3. Edit an Instance
4. Delete an Instance
5. Consolidate Instances
6. Exit
Enter your selection:2
ADDING A NEW INSTANCE
---------------------
Enter the name of the new instance: sample
Enter the server port number [10902]:
Staging Store Disk Quota is used to limit the disk space used by IBM InfoSphere Data Replication staging Store. If this space is exhausted, this instance may run at a
lower speed. The minimum value allowed is 1 GB.
Enter the Staging Store Disk Quota for this instance (GB) [100]: 2
Enter the Maximum Memory Allowed for this instance (MB) [1024]: 256
Select y to use JMS or TCP/IP engine communication connection, select n to use TCP only engine communication connection (y/n) [n]:
Select a DB2 Instance
1. db2inst1
2. Other...
Select a DB2 Instance [db2inst1]:
Select a database name
1. TARGET
2. SAMPLE
3. Other...
Select a database name: 2
Would you like to configure advanced parameters (y/n) [n]:
Enter the username [db2inst1]:
Enter the password:
Retrieving schema list...
Metadata schema:
1. CDC
2. DB2INST1
3. Other...
Select a database schema for metadata tables: 1
Would you like to specify a refresh loader path (y/n) [y]: n
Creating a new instance. Please wait...
You are about to overwrite metadata for a previous instance of IBM InfoSphere Data Replication that appears to be removed from the system. If you overwrite the metadat
a, you will not be able to use previous instance of IBM InfoSphere Data Replication. Do you want to proceed (y/n)?y
Instance sample was successfully created.
Would you like to START instance sample now (y/n)?y
Starting instance sample. Please wait...
Instance sample started successfully. Press ENTER to go to the Main menu...
MAIN MENU
---------
1. List Current Instances
2. Add an Instance
3. Edit an Instance
4. Delete an Instance
5. Consolidate Instances
6. Exit
Enter your selection:6
Exiting...
[db2inst1@mongo2 bin]$
3、 add table mapping
4、 target export cdc metadata tables
[db2inst1@mongo2 bin]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 10.5.10
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[db2inst1@mongo2 bin]$ db2 list tables for schema cdc
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TS_AUTH CDC T 2018-10-30-09.37.27.518036
TS_BOOKMARK CDC T 2018-10-30-09.37.28.682909
TS_CONFAUD CDC T 2018-10-30-09.37.29.051967
TS_DDLAUD CDC T 2018-10-30-09.37.29.574086
4 record(s) selected.
[db2inst1@mongo2 bin]$ cd
[db2inst1@mongo2 ~]$ db2 "export cdc.ts_auth.del of del select * from cdc.ts_auth"
SQL0104N An unexpected token "cdc.ts_auth.del" was found following "EXPORT".
Expected tokens may include: "TO". SQLSTATE=42601
[db2inst1@mongo2 ~]$ db2 "export to cdc.ts_auth.del of del select * from cdc.ts_auth"
SQL3104N The Export utility is beginning to export data to file
"cdc.ts_auth.del".
SQL3105N The Export utility has finished exporting "6" rows.
Number of rows exported: 6
[db2inst1@mongo2 ~]$ db2 "export to cdc.ts_bookmark.del of del select * from cdc.ts_bookmark"
SQL3104N The Export utility is beginning to export data to file
"cdc.ts_bookmark.del".
SQL3105N The Export utility has finished exporting "0" rows.
Number of rows exported: 0
[db2inst1@mongo2 ~]$ db2 "export to cdc.ts_confaud.del of del select * from cdc.ts_confaud"
SQL3104N The Export utility is beginning to export data to file
"cdc.ts_confaud.del".
SQL3105N The Export utility has finished exporting "0" rows.
Number of rows exported: 0
[db2inst1@mongo2 ~]$ db2 "export to cdc.ts_ddlaud.del of del select * from cdc.ts_ddlaud"
SQL3132W The character data in column "STATEMENT" will be truncated to size
"32700".
SQL3104N The Export utility is beginning to export data to file
"cdc.ts_ddlaud.del".
SQL3105N The Export utility has finished exporting "0" rows.
Number of rows exported: 0
5、 source external unload start
開始之前，模擬一個事務持續執行插入資料 :
i=1
while (($i < 10000))
do
db2 "insert into xpf1 values(int(rand()*1000), char(int(rand()*1000)||'abc'||int(rand()*1000)))"
db2 "insert into xpf2 values(int(rand()*1000), char(int(rand()*1000)||'abc'||int(rand()*1000)))"
db2 "insert into xpf3 values(int(rand()*1000), char(int(rand()*1000)||'abc'||int(rand()*1000)))"
i=$((i=i+1))
sleep 1
done
[db2inst1@mongo1 ~]$ cd /opt/ibm/InfoSphereDataReplication/ReplicationEngineforIBMDB2/bin
[db2inst1@mongo1 bin]$ ./dmmarkexternalunloadstart -I source -s SAMPLE -t DB2INST1.XPF1
Successfully marked the unload start point.
[db2inst1@mongo1 bin]$ ./dmmarkexternalunloadstart -I source -s SAMPLE -t DB2INST1.XPF2
Successfully marked the unload start point.
[db2inst1@mongo1 bin]$ ./dmmarkexternalunloadstart -I source -s SAMPLE -t DB2INST1.XPF3
Successfully marked the unload start point.
[db2inst1@mongo1 bin]$
6、 source backup database online
[db2inst1@mongo1 bin]$ cd ~
[db2inst1@mongo1 ~]$ db2 backup db sample online include logs
Backup successful. The timestamp for this backup image is : 20181030094634
[db2inst1@mongo1 ~]$ ll
total 364484
-rw-rw-r-- 1 db2inst1 db2inst1 1715 Oct 30 00:43 1.sql
-rw-rw-r-- 1 db2inst1 db2inst1 1668 Oct 30 00:47 2.sql
drwxrwxr-x 3 db2inst1 db2inst1 22 Oct 29 22:53 archlog
-rw-rw-r-- 1 db2inst1 db2inst1 1474496 Oct 29 14:23 date.log
drwxrwxr-x 3 db2inst1 db2inst1 22 Oct 23 23:40 db2inst1
drwxrwxr-x 5 db2inst1 db2inst1 180 Oct 24 10:30 mon
-rw------- 1 db2inst1 db2inst1 185872384 Oct 30 09:32 SAMPLE.0.db2inst1.DBPART000.20181030093222.001
-rw------- 1 db2inst1 db2inst1 185872384 Oct 30 09:46 SAMPLE.0.db2inst1.DBPART000.20181030094634.001
drwxrwsr-t 24 db2inst1 db2inst1 4096 Oct 29 22:54 sqllib
7、 source external unload end
[db2inst1@mongo1 ~]$ cd /opt/ibm/InfoSphereDataReplication/ReplicationEngineforIBMDB2/bin
[db2inst1@mongo1 bin]$ ./dmmarkexternalunloadend -I source -s SAMPLE -t DB2INST1.XPF1
Successfully marked the unload stop point.
[db2inst1@mongo1 bin]$ ./dmmarkexternalunloadend -I source -s SAMPLE -t DB2INST1.XPF2
Successfully marked the unload stop point.
[db2inst1@mongo1 bin]$ ./dmmarkexternalunloadend -I source -s SAMPLE -t DB2INST1.XPF3
Successfully marked the unload stop point.
8、 target restore database
[db2inst1@mongo1 ~]$ scp SAMPLE.0.db2inst1.DBPART000.20181030094634.001 db2inst1@mongo2:/home/db2inst1
db2inst1@mongo2's password:
SAMPLE.0.db2inst1.DBPART000.20181030094634.001 100% 177MB 29.2MB/s 00:06
[db2inst1@mongo1 ~]$
[db2inst1@mongo2 ~]$ db2 restore db sample logtarget /home/db2inst1/logtarget/
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@mongo2 ~]$ db2 "rollforward db sample to end of logs and stop overflow log path ('/home/db2inst1/logtarget')"
Rollforward Status
Input database alias = sample
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000016.LOG - S0000016.LOG
Last committed transaction = 2018-10-30-02.00.34.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@mongo2 ~]$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 10.5.10
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[db2inst1@mongo2 ~]$ db2 "import from cdc.ts_auth.del of del replace into cdc.ts_auth"
SQL3109N The utility is beginning to load data from file "cdc.ts_auth.del".
SQL3110N The utility has completed processing. "6" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "6".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "6" rows were processed from the input file. "6" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read = 6
Number of rows skipped = 0
Number of rows inserted = 6
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 6
9、 management console restart subscription
10、 compare source and target
source:
[db2inst1@mongo1 ~]$ db2 "select count(*) from xpf3"
1
-----------
808
1 record(s) selected.
[db2inst1@mongo1 ~]$ db2 "select count(*) from xpf2"
1
-----------
808
1 record(s) selected.
[db2inst1@mongo1 ~]$ db2 "select count(*) from xpf1"
1
-----------
809
1 record(s) selected.
[db2inst1@mongo1 ~]$ db2 "select count(*) from xpf3"
1
-----------
808
1 record(s) selected.
[db2inst1@mongo1 ~]$ db2 "select count(*) from xpf2"
1
-----------
808
1 record(s) selected.
[db2inst1@mongo1 ~]$ db2 "select count(*) from xpf1"
1
-----------
809
1 record(s) selected.
target:
[db2inst1@mongo2 bin]$ db2 "select count(*) from xpf1"
1
-----------
809
1 record(s) selected.
[db2inst1@mongo2 bin]$ db2 "select count(*) from xpf2"
1
-----------
808
1 record(s) selected.
[db2inst1@mongo2 bin]$ db2 "select count(*) from xpf3"
1
-----------
808
1 record(s) selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26224914/viewspace-2218139/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE用於特殊情況下的恢復REST
- [20190228]Backup Restore Throttle sleep.txtREST
- SQL Server資料庫遠端更新目標表資料的儲存過程SQLServer資料庫儲存過程
- db2備份恢復（backup restore）DB2REST
- 用begin backup的方式遷移資料庫資料庫
- PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.16.00.00 in TARGET database isSQLPackageRESTDatabase
- [Flink/CDC/資料整合] 資料增量整合方案：Flink CDC
- python將目標檢測資料匯入到指定資料庫中Python資料庫
- 資料庫表初始化資料庫
- PG資料庫初始化資料庫
- You may fail to backup log or restore log after TDE certification/key rotation.AIREST
- DTS-077100 向目標庫同步資料時出錯
- Oracle rac使用nid和dbms_backup_restore包修改dbid和dbnameOracleREST
- DBMS_BACKUP_RESTORE用於特殊情況下的恢復(轉)REST
- 目標檢測資料集分析
- 達夢7資料庫初始化資料庫
- 雲資料庫逐漸成熟，阿里雲提出“去O”小目標資料庫阿里
- 【MySQL 資料庫】MySQL目錄MySql資料庫
- 融合資料庫生態：利用 EventBridge 構建 CDC 應用資料庫
- python標準庫目錄Python
- 目標自主安全可控 中國銀聯分散式資料庫實踐分散式資料庫
- 目標檢測資料集，全部有標註
- 12 爬取目標的資料分析
- 如何對資料目標進行分析
- 資料治理的目標和原則
- SQL Server 2014如何使用遊標迴圈向遠端資料庫插入資料SQLServer資料庫
- MySQL 更改資料庫資料儲存目錄MySql資料庫
- ABP預設模板修改預設資料庫型別並初始化資料庫資料資料庫型別
- Flink CDC 採集MySQL 初始化或者指定時間戳時，沒有采集到資料MySql時間戳
- 資料庫大戰,AWS又將目標瞄準了微軟SQL Server資料庫微軟SQLServer
- 【12c】12c RMAN新特性之通過網路遠端恢復資料庫（RESTORE/Recover from Service）資料庫REST
- MySQL資料庫部署及初始化相關MySql資料庫
- SAR目標檢測資料集彙總
- 【PG結構】Postgresql資料庫資料目錄說明SQL資料庫
- Ubuntu 上更改 MySQL 資料庫資料儲存目錄UbuntuMySql資料庫
- hive初始化mysql資料庫失敗的原因HiveMySql資料庫
- Docker容器啟動時初始化Mysql資料庫DockerMySql資料庫
- SpringBoot系列之資料庫初始化-datasource配置方式Spring Boot資料庫