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