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库里
最后修改时间：2022-08-09 09:54:39
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
