暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MTK-亿级表迁移到mogdb测试

MTK-亿级表迁移到mogdb测试

原创 lqkitten 2022-09-06
2716
上次学习了用mogdb的ptk工具安装三节点mogdb数据库，现在体验一下用mogdb提供的mtk工具从oracle 11.2.0.4迁移一个上亿条数据的表。

一、源表情况
操作系统为虚拟机32C128G，SSD5T
操作系统版本

cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)
内存
free -g
             total        used        free      shared  buff/cache   available
Mem:            125         103          20           1           1          12
Swap:             0           0           0
CPU核数
cat /proc/cpuinfo |grep processor|wc -l
32
硬盘空间 
df -h
Filesystem                  Size  Used Avail Use% Mounted on
/dev/vda2                    98G   17G   78G  18% /
devtmpfs                     63G     0   63G   0% /dev
tmpfs                       110G  156K  110G   1% /dev/shm
tmpfs                        63G  697M   63G   2% /run
tmpfs                        63G     0   63G   0% /sys/fs/cgroup
/dev/vda1                   477M  142M  306M  32% /boot
/dev/mapper/oradata-lvdata  5.1T  4.3T  796G  85% /u01
tmpfs                        13G     0   13G   0% /run/user/0

数据库版本
oracle 11.2.0.4
image.png

表的行数

image.png

表占用空间的大小
image.png
表的分区信息

select partition_name,high_value,num_rows from user_tab_partitions where table_name='WORK_MAINT_ZHXL' 

WORK_MAIN_1	TIMESTAMP' 2016-01-01 00:00:00'	97500
WORK_MAIN_10	TIMESTAMP' 2026-01-01 00:00:00'	0
WORK_MAIN_11	MAXVALUE	0
WORK_MAIN_2	TIMESTAMP' 2017-01-01 00:00:00'	7204103
WORK_MAIN_3	TIMESTAMP' 2018-01-01 00:00:00'	32581194
WORK_MAIN_4	TIMESTAMP' 2019-01-01 00:00:00'	38689180
WORK_MAIN_5	TIMESTAMP' 2020-01-01 00:00:00'	41387049
WORK_MAIN_5_1	TIMESTAMP' 2021-01-01 00:00:00'	39283438
WORK_MAIN_6	TIMESTAMP' 2022-01-01 00:00:00'	47621455
WORK_MAIN_7	TIMESTAMP' 2023-01-01 00:00:00'	1237036
WORK_MAIN_8	TIMESTAMP' 2024-01-01 00:00:00'	0
WORK_MAIN_9	TIMESTAMP' 2025-01-01 00:00:00'	0

用数据泵导出用时，20进程压缩
导出参数
cat mt.par
userid=test/**********
parallel=20
directory=imp
dumpfile=mt%U.dmp
logfile=mt0930.log
compression=all
content=data_only
tables=WORK_MAINT_ZHXL
导出数据
expdp parfile=mt.par

Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 72.40 GB
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
…
elapsed 0 00:02:09
用时2分9秒
导出文件大小

du -sh
7.1G

从远程下载备份文件到本地

time scp 192.168.56.60:/u01/imp/*.dmp .
real    13m3.975s
user    3m0.857s
sys     3m55.604s

这个表异机迁移到oracle,至少用时20分钟。
二、目标环境准备
mogdb用ptk安装在一台物理机上,本机安装过oracle 19c,不用单独安装oracle客户端
操作系统为
cat /etc/centos-release
Oracle Linux Server release 7.6
内存
free -g
total used free shared buff/cache available
Mem: 125 1 47 10 76 95
用ptk安装mogdb,安装后端口使用36000
gsql -r
gsql ((MogDB 3.0.1 build 1a363ea9) compiled at 2022-08-05 17:31:04 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type “help” for help.
MogDB=#create database test dbcompatibility=‘A’;
MogDB=#create user test identified by ‘Test@1234’ login;
MogDB=#alter database test owner to test;
MogDB=#\c test test
test=>create schema test;
参考https://www.modb.pro/db/448177，对mogdb运行参数设置。

安装MTK
wget https://cdn-mogdb.enmotech.com/mtk/v2.5.1/mtk_2.5.1_linux_amd64_db2.tar.gz
tar xvf mtk_2.5.1_linux_amd64_db2.tar.gz
cd mtk_2.5.1_linux_amd64
申请证书
./mtk .license gen
将邮件中的内容复制为license.json
初始化项目
./mtk init-project -n ora2pg -s oracle -t mogdb
编辑参数
vim ora2pg/config/mtk.json

{
 "source": {
  "type": "oracle",
  "connect": {
   "host": "192.168.56.60",
   "user": "TEST",
   "port": 1521,
   "password": "***********",
   "dbName": "orcl"
  },
  "parameter": {
   "charAppendEmptyString": false
  }
 },
 "target": {
  "type": "mogdb",
  "connect": {
   "host": "127.0.0.1",
   "user": "test",
   "port": 36000,
   "password": "************",
   "dbName": "test"
  },
  "parameter": {
   "parallelInsert": 10,
   "dropExistingObject": false,
   "truncTable": false,
   "colKeyWords": {},
   "objKeyWords": {},
   "caseSensitive": 0,
   "quoteMark": false,
   "path": "ora2pg/data",
   "schemaPath": "ora2pg/schema",
   "dataPath": "ora2pg/data",
   "errDataPath": "",
   "fileType": "",
   "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": true,
   "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": "",
   "convertOracleIntegerToNumeric": false,
   "enableOgBlobClob": false,
   "enableConvertSrid": false,
   "defaultSrid": "4326",
   "seqLastNumAddNum": 0,
   "skipColumnType": {},
   "skipColumnName": {},
   "templateSeqName": "",
   "charAppendEmptyString": false,
   "tableOptions": {},
   "indexOptions": {}
  }
 },
 "limit": {
  "parallel": 16,
  "fetchSize": 10000,
  "batchSize": 10000,
  "bufferSize": 8,
  "cpBufferSize": 8,
  "oracleSelectParallel": 20,
  "channelCacheNum": 10000,
  "limit": 0
 },
 "object": {
  "tables": ["TEST.WORK_MAINT_ZHXL"],
  "schemas": [],
  "excludeTable": {},
  "tableSplit": {}
 },
 "dataOnly": false,
 "schemaOnly": false,
 "disableTableDataComp": false,
 "disableCollStatistics": false,
 "reportFile": "ora2pg/report",
 "debug": false,
 "disableIgnoreCase": false,
 "disableSelectPart": false,
 "disableFKCons": false,
 "disableSyncIdxAfterData": false,
 "disablePrintMigDataProgress": false
}

执行迁移
./mtk -c ora2pg/config/mtk.json
完成报告
image.png
总计用时10分

迁移后在mogdb在表行数
image.png
迁移后表大小

image.png

 select relname,parttype,parentid,boundaries from pg_partition where parentid in(select oid from pg_class where relname='work_maint_zhxl');
    relname     | parttype | parentid |       boundaries
----------------+----------+----------+-------------------------
 work_maint_zhxl | r        |    20198 |
 work_main_11    | p        |    20198 | {NULL}
 work_main_10    | p        |    20198 | {"2026-01-01 00:00:00"}
 work_main_9     | p        |    20198 | {"2025-01-01 00:00:00"}
 work_main_8     | p        |    20198 | {"2024-01-01 00:00:00"}
 work_main_7     | p        |    20198 | {"2023-01-01 00:00:00"}
 work_main_6     | p        |    20198 | {"2022-01-01 00:00:00"}
 work_main_5_1   | p        |    20198 | {"2021-01-01 00:00:00"}
 work_main_5     | p        |    20198 | {"2020-01-01 00:00:00"}
 work_main_4     | p        |    20198 | {"2019-01-01 00:00:00"}
 work_main_3     | p        |    20198 | {"2018-01-01 00:00:00"}
 work_main_2     | p        |    20198 | {"2017-01-01 00:00:00"}
 work_main_1     | p        |    20198 | {"2016-01-01 00:00:00"}
(13 rows)

可以看出，mtk在迁移过程中自动建立分区表，用时远小于oracle之间的导出导入，两者占用硬盘空间相差不大。
mtk是一款迁移到mogdb/opengauss/pgsql的称手兵器。

mogdb
最后修改时间：2022-09-07 09:37:38
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论