1、MTK工具介紹

MTK–異構數據遷移工具

MTK全稱為 Database Migration Toolkit，是一個可以將Oracle/DB2/MySQL/openGauss/SqlServer/Informix數據庫的數據結構，全量數據高速導入到MogDB的工具。
1.多數據庫類型支持
支持 Oracle,DB2,openGauss,SqlServer,MySQL,Informix 等數據庫之間的互相遷移 (互為源和目標)。
支持將數據庫內容導出成可執行的 SQL 腳本 （源數據庫內容遷移到文本）

2.遷移性能調整

支持調整數據遷移過程中的批量查詢、批量插入大小等細粒度參數，來調整數據遷移的性能。

支持數據遷移時的多并發，并行和數據分片。

3.結構和數據分離

支持同步遷移對象結構和數據；也支持僅遷移結構或者僅遷移數據（在結構已經遷移完之后）。

支持表級和 Schema 級的遷移范圍限定，允許指定schema下全部對象或者某些對象進行遷移 。

支持遷移過程中的 Schema 重映射，也就是支持將對象從源Schema遷移到目標端的不同名Schema下 。

4.程序遷移（支持Oracle/MySQL為源,openGauss/Mogdb為目標）

支持Oracle/MySQL->openGauss/Mogdb的存儲過程，函數，觸發器，包遷移。

自動根據openGauss/Mogdb的語法規則，對Oracle/MySQL的程序進行改寫，之后再在目標端openGauss/Mogdb數據庫中創建

2、MTK工具安裝 和 oracle需要的客戶端安裝

[omm@db1 ~]$ su - root
Password: 
Last login: Thu Jun 30 14:21:42 CST 2022 from 192.168.3.100 on pts/0
[root@db1 ~]# 
[root@db1 ~]# 
[root@db1 ~]# cd /home
[root@db1 home]# ls
omm  roo
[root@db1 home]# cd omm/.
[root@db1 omm]# ls
mtk_2.4.2_linux_amd64.tar.gz                              oracle-instantclient19.12-devel-19.12.0.0.0-1.x86_64.rpm  oracle-instantclient19.12-sqlplus-19.12.0.0.0-1.x86_64.rpm
oracle-instantclient19.12-basic-19.12.0.0.0-1.x86_64.rpm  oracle-instantclient19.12-jdbc-19.12.0.0.0-1.x86_64.rpm   tb_rp_ct_crso_out_call_list_mon201803_1.txt
[root@db1 omm]# rpm -ivh oracle-instantclient19.12-*.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-instantclient19.12-basic-1################################# [ 25%]
   2:oracle-instantclient19.12-devel-1################################# [ 50%]
   3:oracle-instantclient19.12-jdbc-19################################# [ 75%]
   4:oracle-instantclient19.12-sqlplus################################# [100%]
[root@db1 omm]# 


[root@db1 ~]# cd /home/omm/
[root@db1 omm]# ll
total 233604
-rw------- 1 omm dbgrp  12352226 Jul  5 16:54 mtk_2.4.2_linux_amd64.tar.gz
-rw------- 1 omm dbgrp  54501080 Jul  6 14:50 oracle-instantclient19.12-basic-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm dbgrp    613488 Jul  6 14:50 oracle-instantclient19.12-devel-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm dbgrp   1524732 Jul  6 14:50 oracle-instantclient19.12-jdbc-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm dbgrp    703176 Jul  6 15:06 oracle-instantclient19.12-sqlplus-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm dbgrp 169508765 Jun 28 11:33 tb_rp_ct_crso_out_call_list_mon201803_1.txt
[root@db1 omm]# tar -xf mtk_2.4.2_linux_amd64.tar.gz 
[root@db1 omm]# ll
total 233604
drwxr-xr-x 3 root root         69 Jul  7 09:12 mtk_2.4.2_linux_amd64
-rw------- 1 omm  dbgrp  12352226 Jul  5 16:54 mtk_2.4.2_linux_amd64.tar.gz
-rw------- 1 omm  dbgrp  54501080 Jul  6 14:50 oracle-instantclient19.12-basic-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm  dbgrp    613488 Jul  6 14:50 oracle-instantclient19.12-devel-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm  dbgrp   1524732 Jul  6 14:50 oracle-instantclient19.12-jdbc-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm  dbgrp    703176 Jul  6 15:06 oracle-instantclient19.12-sqlplus-19.12.0.0.0-1.x86_64.rpm
-rw------- 1 omm  dbgrp 169508765 Jun 28 11:33 tb_rp_ct_crso_out_call_list_mon201803_1.txt
[root@db1 omm]# cd mtk_2.4.2_linux_amd64/
[root@db1 mtk_2.4.2_linux_amd64]# ll
total 35096
-rw-r--r-- 1 root root    43629 Jul  4 09:43 CHANGELOG.md
drwxr-xr-x 2 root root      209 Jul  7 09:12 example
-rwxr-xr-x 1 root root 35885568 Jul  4 09:43 mtk
-rw-r--r-- 1 root root     2051 Jan 11 16:51 README.md

3、獲取MTK license

查看版本

./mtk -v

申請License 請聯系恩墨的小墨！！！

./mtk license gen

查看命令行幫助

./mtk -h

4、配置 ora2mog.json

{
  "taskID": "1544967622372626432",
  "source": {
    "type": "oracle",
    "connect": {
      "version": "19.7.0.0.0",
      "host": "192.168.3.59",
      "user": "dbmt",
      "port": 1521,
      "password": "******",
      "dbName": "wxoadb",
      "timeout": 30000000000,
      "charset": "ZHS16GBK"
    },
    "parameter": {
      "parallelInsert": 1,
      "dropExistingObject": false,
      "truncTable": false,
      "caseSensitive": 0,
      "colKeyWords": null,
      "objKeyWords": null,
      "quoteMark": false,
      "path": "",
      "schemaPath": "",
      "dataPath": "",
      "fileType": "",
      "fileSize": "",
      "csvHeader": false,
      "csvNullValue": "",
      "csvFieldDelimiter": "",
      "csvOptionallyEnclosed": "",
      "excludeSysTable": null,
      "remapSchema": null,
      "remapTable": null,
      "remapTablespace": null,
      "enableSyncTabTbsPro": false,
      "enableSyncCompTabPro": false,
      "timeFormat": "",
      "dateFormat": "",
      "dateTimeFormat": "",
      "noSupportPartTabToNormalTab": false,
      "ignoreDB2PartInclusive": false,
      "igNotSupportIntervalPart": false,
      "igErrorData": false,
      "enableBatchCommit": false,
      "ignoreTabPartition": false,
      "autoAddMaxvaluePart": false,
      "autoAddMySQLAutoIncr": false,
      "autoAddMySQLAutoIncrTabList": null,
      "ignoreNotSupportDefault": false,
      "replaceZeroDate": "",
      "virtualColToNormalCol": false,
      "virtualColConv": null,
      "mySQLSkipErrorDateTimeData": false,
      "ignoreTableDDLCompErr": false,
      "convertPackageMethod": "",
      "enableOgBlobClob": false,
      "enableConvertSrid": false,
      "defaultSrid": "",
      "seqLastNumAddNum": 0,
      "skipColumnType": null,
      "skipColumnName": null,
      "templateSeqName": "",
      "charAppendEmptyString": false,
      "tableOptions": null,
      "indexOptions": null
    }
  },
  "target": {
    "type": "mogdb",
    "connect": {
      "version": "2.1.1",
      "vendor": "MogDB",
      "host": "192.168.3.25",
      "user": "dbmt",
      "port": 26000,
      "password": "******",
      "dbName": "miao",
      "timeout": 30000000000,
      "charset": "UTF8",
      "datCompatibility": "A"
    },
    "parameter": {
      "parallelInsert": 4,
      "dropExistingObject": false,
      "truncTable": false,
      "caseSensitive": 0,
      "colKeyWords": {},
      "objKeyWords": {},
      "quoteMark": false,
      "path": "./data",
      "schemaPath": "data/schema",
      "dataPath": "data/data",
      "fileType": "sql",
      "fileSize": "",
      "csvHeader": false,
      "csvNullValue": "",
      "csvFieldDelimiter": ",",
      "csvOptionallyEnclosed": "\"",
      "excludeSysTable": [],
      "remapSchema": {},
      "remapTable": {},
      "remapTablespace": {},
      "enableSyncTabTbsPro": false,
      "enableSyncCompTabPro": false,
      "timeFormat": "HH:MI:SS",
      "dateFormat": "YYYY-MM-DD",
      "dateTimeFormat": "YYYY-MM-DD HH24:MI:SS",
      "noSupportPartTabToNormalTab": false,
      "ignoreDB2PartInclusive": false,
      "igNotSupportIntervalPart": false,
      "igErrorData": false,
      "enableBatchCommit": false,
      "ignoreTabPartition": false,
      "autoAddMaxvaluePart": false,
      "autoAddMySQLAutoIncr": false,
      "autoAddMySQLAutoIncrTabList": [],
      "ignoreNotSupportDefault": false,
      "replaceZeroDate": "",
      "virtualColToNormalCol": false,
      "virtualColConv": {},
      "mySQLSkipErrorDateTimeData": false,
      "ignoreTableDDLCompErr": false,
      "convertPackageMethod": "",
      "enableOgBlobClob": false,
      "enableConvertSrid": false,
      "defaultSrid": "",
      "seqLastNumAddNum": 0,
      "skipColumnType": {},
      "skipColumnName": {},
      "templateSeqName": "SEQ_{{.TabName}}_{{.ColName}}",
      "charAppendEmptyString": false,
      "tableOptions": {},
      "indexOptions": {}
    }
  },
  "limit": {
    "parallel": 4,
    "fetchSize": 1000,
    "batchSize": 1000,
    "bufferSize": 8,
    "cpBufferSize": 8,
    "oracleSelectParallel": 2,
    "channelCacheNum": 10000,
    "limit": 0
  },
  "object": {
    "tables": [],
    "schemas": [
      "DBMT"
    ],
    "excludeTable": {},
    "tableSplit": {},
    "objects": {
      "DBMT": []
    }
  },
  "dataOnly": false,
  "schemaOnly": false,
  "disableTableDataComp": false,
  "disableCollStatistics": false,
  "reportFile": "mtk_report.html",
  "debug": false,
  "preRun": false,
  "test": false,
  "disableIgnoreCase": false,
  "disableSelectPart": false,
  "disableFKCons": false,
  "disableSyncIdxAfterData": false,
  "disablePrintMigDataProgress": false
}

5、擴文件系統

[root@db1 ~]# mount /dev/sdb1 /mogdb/data/db1/pg_location
[root@db1 ~]# chown omm:dbgrp /mogdb/data/db1/pg_location
[root@db1 /]# vi /etc/fstab

#
# /etc/fstab
# Created by anaconda on Sat Mar  7 09:58:11 2020
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=133cf253-8e46-4fb9-bbba-a18965938533 /                       xfs     defaults        0 0
UUID=cd9c7c10-e67c-4204-a76e-af44f841fd7f swap                    swap    defaults        0 0
/dev/sdb1     /mogdb/data/db1/pg_location     ext4     defaults    0 0

[root@db1 ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        70G   24G   47G  35% /
devtmpfs        904M     0  904M   0% /dev
tmpfs           920M   12K  920M   1% /dev/shm
tmpfs           920M  9.2M  910M   1% /run
tmpfs           920M     0  920M   0% /sys/fs/cgroup
tmpfs           184M   12K  184M   1% /run/user/42
tmpfs           184M     0  184M   0% /run/user/1001
/dev/sdb1       2.0T   71M  1.9T   1% /mogdb/data/db1/pg_location/db_tbs

7、執行mtk

 ./mtk -c ora2mog.json --reportFile mtk_report.html --logfile mtk_report.log

8、執行后結果

-----------------------
ObjectName Type Summary
-----------------------

+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+-------------+
|       Type       |     StartTime     |      EndTime      | Status | Total Num | Success Num | Warring Num | Failed  Num | Time        |
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+-------------+
|Schema            |2022-08-08 06:49:46|2022-08-08 06:49:46|finish  |1          |1            |0            |0            |162 ms       ｜
|Sequence          |2022-08-08 06:49:46|2022-08-08 06:49:46|finish  |0          |0            |0            |0            |225 ms       ｜
|ObjectType        |2022-08-08 06:49:46|2022-08-08 06:49:47|finish  |0          |0            |0            |0            |953 ms       ｜
|Queue             |2022-08-08 06:49:47|2022-08-08 06:49:47|finish  |0          |0            |0            |0            |245 ms       ｜
|Table             |2022-08-08 06:49:47|2022-08-08 06:50:06|finish  |1804       |1697         |107          |0            |19 s 290 ms  ｜
|TableData         |2022-08-08 06:50:06|2022-08-08 19:15:24|finish  |1854       |1808         |43           |3            |12 h 25 m 17 s 655 ms｜
|Index             |2022-08-08 19:15:24|2022-08-08 19:15:25|finish  |458        |451          |6            |1            |740 ms       ｜
|Constraint        |2022-08-08 19:15:25|2022-08-08 19:15:33|finish  |0          |0            |0            |0            |8 s 444 ms   ｜
|DBLink            |2022-08-08 19:15:33|2022-08-08 19:15:33|finish  |0          |0            |0            |0            |55 ms        ｜
|View              |2022-08-08 19:15:33|2022-08-08 19:15:34|finish  |0          |0            |0            |0            |179 ms       ｜
|MaterializedView  |2022-08-08 19:15:34|2022-08-08 19:15:34|finish  |0          |0            |0            |0            |528 ms       ｜
|Function          |2022-08-08 19:15:34|2022-08-08 19:15:34|finish  |0          |0            |0            |0            |304 ms       ｜
|Procedure         |2022-08-08 19:15:34|2022-08-08 19:15:35|finish  |0          |0            |0            |0            |147 ms       ｜
|Package           |2022-08-08 19:15:35|2022-08-08 19:15:35|finish  |0          |0            |0            |0            |166 ms       ｜
|Trigger           |2022-08-08 19:15:35|2022-08-08 19:15:35|finish  |0          |0            |0            |0            |160 ms       ｜
|Synonym           |2022-08-08 19:15:35|2022-08-08 19:15:35|finish  |0          |0            |0            |0            |74 ms        ｜
|TableDataCom      |2022-08-08 19:15:35|2022-08-08 19:27:07|finish  |1804       |1761         |43           |0            |11 m 31 s 999 ms｜
|AlterSequence     |2022-08-08 19:27:07|2022-08-08 19:27:08|finish  |0          |0            |0            |0            |648 ms       ｜
|CollStatistics    |2022-08-08 19:27:08|2022-08-08 19:32:12|finish  |1804       |1761         |43           |0            |5 m 4 s 185 ms｜
+------------------+-------------------+-------------------+--------+-----------+-------------+-------------+-------------+-------------+

time="2022-08-08 19:32:13.852987" level=info msg="reportDir: mtk_report" function=PrintReport line=236 file="mtk/cmd/mtk/services/cmd.go"
time="2022-08-08 19:32:26.077751" level=info msg="the text report : mtk_report.txt" function=HTMLReportToFIle line=123 file="mtk/pkg/report/report.go"
time="2022-08-08 19:32:27.060807" level=info msg="the warring report : mtk_report.warring" function=HTMLReportToFIle line=130 file="mtk/pkg/report/report.go"
time="2022-08-08 19:32:27.083022" level=info msg="the error report : mtk_report.err" function=HTMLReportToFIle line=137 file="mtk/pkg/report/report.go"

9、可以查看html文件

10、錯誤處理
從oracle庫導出csv文件 ，然后copy mogdb庫里