迁移工具MTK和ora2pg迁移BLOB字段数据到MogDB性能对比

原创张凡 2022-01-21

1928

迁移工具MTK和ora2pg迁移BLOB字段数据到MogDB性能对比

背景介绍：
mtk全称为 The Database Migration Toolkit，是一个云和恩墨自主研发的可以将Oracle/DB2/MySQL/openGauss数据库的数据结构，全量数据高速导入到MogDB的工具。ora2pg是一款免费迁移工具，能将oracle迁移到pg。以下是迁移数据说明,在Oracle中创建25张带有BLOB字段的表，每张表数据50000条，用迁移工具MTK和ora2pg，分别对比迁移1张表、5张表、10张表、20张表的迁移时间，从而对比其迁移性能。数据库磁盘使用的是nvme磁盘，写入速度高达1400M/s,不用考虑i/o对其性能的影响。

一、容器版oracle安装部署

1、Oracle容器部署

docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/oracle_11g_ee_lhr_11.2.0.4:1.0
docker run -itd --name oracle -h oracle --privileged=true -p 1521:1521 -p 222:22 -p 1158:1158 lhrbest/oracle_11g_ee_lhr_11.2.0.4:1.0 init

2、安装Oracle客户端

wget https://download.oracle.com/otn_software/linux/instantclient/214000/oracle-instantclient-basic-21.4.0.0.0-1.el8.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/214000/oracle-instantclient-sqlplus-21.4.0.0.0-1.el8.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/214000/oracle-instantclient-devel-21.4.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/214000/oracle-instantclient-jdbc-21.4.0.0.0-1.x86_64.rpm
[root@ecs-1b06 oracle]# rpm -ivh oracle-instantclient-basic-21.4.0.0.0-1.x86_64.rpm
[root@ecs-1b06 oracle]# rpm -ivh oracle-instantclient-sqlplus-21.4.0.0.0-1.x86_64.rpm
[root@ecs-1b06 oracle]# rpm -iv oracle-instantclient-jdbc-21.4.0.0.0-1.x86_64.rpm
[root@ecs-1b06 oracle]# rpm -iv oracle-instantclient-devel-21.4.0.0.0-1.x86_64.rpm
[root@ecs-1b06 oracle]# export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib
[root@ecs-1b06 oracle]# export ORACLE_HOME=/usr/lib/oracle/21/client64

二、安装ora2pg

1、安装依赖

[root@ecs-1b06 ora2pg]# yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

2、安装DBI模块

[root@ecs-1b06 local]# wget https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.643.tar.gz
[root@ecs-1b06 local]# pwd
[root@ecs-1b06 local]# tar -xf DBI-1.643.tar.gz
[root@ecs-1b06 local]# cd DBI-1.643/
[root@ecs-1b06 DBI-1.643]# perl Makefile.PL
[root@ecs-1b06 DBI-1.643]#make
[root@ecs-1b06 DBI-1.643]#make insatll

3、安装DBD:oracle模块

[root@ecs-1b06 DBD-Oracle-1.80]# wget https://cpan.metacpan.org/authors/id/M/MJ/MJEVANS/DBD-Oracle-1.80.tar.gz
[root@ecs-1b06 dbd]# tar -xf DBD-Oracle-1.80.tar.gz
[root@ecs-1b06 oracle]# export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib/
[root@ecs-1b06 oracle]# export ORACLE_HOME=/usr/lib/oracle/21/client64
[root@ecs-1b06 dbd]#cd DBD-Oracle-1.80
[root@ecs-1b06 dbd]perl Makefile.PL
[root@ecs-1b06 dbd]make && make

4、安装DBD:pg模块

[root@ecs-1b06 DBI-1.643]# yum install -y postgresql*
[root@ecs-1b06 local]# wget  https://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/DBD-Pg-3.15.0.tar.gz
[root@ecs-1b06 DBD-Pg-3.15.0]# tar -xf DBD-Pg-3.15.0.tar.gz
[root@ecs-1b06 DBD-Pg-3.15.0]#  cd DBD-Pg-3.15.0
[root@ecs-1b06 DBD-Pg-3.15.0]# perl Makefile.PL
[root@ecs-1b06 DBD-Pg-3.15.0]# make && make install

5、安装ORA2PG

[root@ecs-1b06 local]# wget https://sourceforge.net/projects/ora2pg/files/23.0/ora2pg-23.0.tar.bz2  --no-check-certificate
[root@ecs-1b06 ora2pg-23.0]#  perl Makefile.PL
[root@ecs-1b06 ora2pg-23.0]# make && make install

6、检查是否安装成功

[root@mogdb-kernel-0004 ~]# cat check.pl
#!/usr/bin/perl
use strict;
use ExtUtils::Installed;
my $inst=ExtUtils::Installed->new();
my @modules = $inst->modules();
foreach(@modules){
       my $ver = $inst->version($_) || "???";
       printf("%-12s -- %s\n",$_,$ver);
       }
exit;
[root@ecs-1b06 dbd]# perl check.pl
DBD::Oracle  -- 1.80
DBD::Pg      -- 3.15.0
DBI          -- 1.643
Ora2Pg       -- 23.0
Perl         -- 5.16.3

三、MTK安装

1.下载软件

根据系统架构选择对应的版本
wget https://cdn-mogdb.enmotech.com//mtk/v2.2.1/mtk_2.2.1_linux_arm64.tar.gz
tar -xf mtk_2.2.1_linux_arm64.tar.gz

2.申请license

生成license.json，即可使用
[root@node151 mtk_2.2.1_linux_arm64]# ./mtk license gen
License File Not Found (default license.json)
许可证无效,开始申请
✗ Email: █
[root@node151 mtk_2.2.1_linux_arm64]# vi license.json
[root@node151 mtk_2.2.1_linux_arm64]# ll
总用量 33M
-rw-r--r-- 1 root root  29K  1月 21 10:09 CHANGELOG.md
drwxr-xr-x 2 root root 4.0K  1月 24 13:52 example
-rw-r--r-- 1 root root  531  1月 24 13:57 license.json
-rwxr-xr-x 1 root root  33M  1月 21 10:08 mtk
-rw-r--r-- 1 root root 2.1K  1月 11 16:51 README.md

四、Oracle准备数据

1、docker进入oracle

[root@ecs-1b06 ~]# docker ps
CONTAINER ID        IMAGE                                    COMMAND             CREATED             STATUS              PORTS                                                                 NAMES
52dcc856bf99        lhrbest/oracle_11g_ee_lhr_11.2.0.4:1.0   "init"              36 minutes ago      Up 36 minutes       0.0.0.0:1158->1158/tcp, 0.0.0.0:1521->1521/tcp, 0.0.0.0:222->22/tcp   oracle
[root@ecs-1b06 ~]# docker exec -it 52dcc856bf99 bash
[root@oracle /]# su - oracle
[oracle@oracle ~]$ sqlplus /nolog
@> conn / as sysdba
Connected to an idle instance.
SYS@LHR11G> startup
ORACLE instance started.
SYS@LHR11G> create user test identified by test123;
User created.
SYS@LHR11G> grant dba to test;
Grant succeeded.

2、生成数据

[oracle@oracle image]$ ll|wc -l
50000
[oracle@oracle image]$ pwd
/home/oracle/image
[oracle@oracle image]$ du -sh .
7.3G	.
[oracle@oracle ~]$ sqlplus / as sysdba
SYS@LHR11G> conn test/test123
Connected.
SYS@LHR11G> create table testimg1(id int,photo blob);
SYS@LHR11G> create or replace directory imgpath as '/home/oracle/image';
Directory created.
declare
l_blob blob;
l_bfile bfile;
begin
for i in 1..5000 loop
insert into testimg1(id,photo)
values(1,empty_blob())
returning photo into l_blob;
l_bfile :=bfilename('IMGPATH',i||'.jpg');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
end loop;
commit;
end;
 16  /
PL/SQL procedure successfully completed.
.....省略生成数据的部分内容
SQL> SELECT  TABLE_NAME,NUM_ROWS FROM USER_TABLES;

TABLE_NAME			 NUM_ROWS
------------------------------ ----------
TESTIMG1				50000
TESTIMG2				50000
TESTIMG3				50000
TESTIMG4				50000
TESTIMG5				50000
TESTIMG6				50000
TESTIMG7				50000
TESTIMG8				50000
TESTIMG9				50000
TESTIMG10				50000
共循环生成25张表。每张表数据50000条