oracle to mogdb 迁移---mtk工具
一、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数据库中创建
二、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
三、获取MTK license
3.1查看版本
./mtk -v
3.2 申请license
./mtk license gen
3.3查看命令行帮助
./mtk -h
四、配置 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
}
五、扩文件系统
[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
六、执行mtk
./mtk -c ora2mog.json --reportFile mtk_report.html --logfile mtk_report.log
七、执行后结果
-----------------------
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"
八、查看html文件
九、错误处理
9.1 表的处理
经过检查 第一个表和第三个表 源端是空表 ，表结构都过去了。
第二张表源端和目标端的数据都是一致的。
处理方法： 暂无处理
9.2 索引的处理
源库检查aaa的对象，发现索引和表是同名的
处理方法： 修改索引的名字。
