使用ora2pg迁移mysql到KADB

• ora2pg安装

1. 安装ora2pg依赖

ora2pg依赖项检测是检测安装操作系统(linux，暂不支持windows操作系统搭建，但可使用windows命令行连接搭建好的迁移工具集群)的环境是否满足迁移工具运行。

1. perl-ExtUtils-CBuilder
2. perl-ExtUtils-MakeMaker
3. postgresql-devel
4. postgresql
5. gcc

1. 安装DBI

解压并进行perl

#tar -xvf  DBI-1.637.tar.gz

#cd DBI-1.637

#perl Makefile.PL

编译

#yum install gcc

#make

#make test（可选）

#make install

1. 安装DBD::mysql（4.039）

解压：tar -xvf  DBD-mysql-4.039.tar.gz

cd DBD-mysql-4.039

perl Makefile.PL [options]

make

make test

make install

1. 安装DBD::pg（3.16）

设置变量

export POSTGRES_HOME= /home/mpp_tpcds/mpp

source ~/.bashrc

source /home/mpp_tpcds/.bashrc

tar xvf DBD-Pg-3.16.1.tar.gz

cd DBD-Pg-3.16.1

perl Makefile.PL

make

make install

1. 安装ora2pg

#tar -xvf ora2pg-21.1.tar.bz2

#cd ora2pg-21.1

#perl Makefile.PL

• 分别迁移DDL及数据

1. 准备ora2pg配置文件

备份ora2pg安装目录下的ora2pg.conf.dist文件或者，直接使用下面的文件

PG_VERSION      12 //pg10以下不支持分区，设置了也没有导出分区信息

ORACLE_HOME     /db/app/oracle/product/11.2.0/db_1

ORACLE_DSN   dbi:mysql:host=192.168.85.133;database=test;port=3306      //这里需要修改

ORACLE_USER     root

ORACLE_PWD      zhuyong

USER_GRANTS     0

DEBUG           0

ORA_INITIAL_COMMAND

EXPORT_SCHEMA   0

CREATE_SCHEMA   1

COMPILE_SCHEMA  0

TYPE            TABLE

OUTPUT          output.sql

WHERE           1=1

PG_DSN          dbi:Pg:dbname=test;host=192.168.85.133;port=5432

PG_USER         mppadmin

PG_PWD          123456

1. 导出DDL

ora2pg -c mysql.dist -p -a range_test –d

命令将表range_test的DDL导出为文件：output.sql

cat output.sql

-- Generated by Ora2Pg, the Oracle database Schema converter, version 21.1

-- Copyright 2000-2020 Gilles DAROLD. All rights reserved.

-- DATASOURCE: dbi:mysql:host=192.168.85.133;database=test;port=3306

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

CREATE TABLE range_test (

         a integer,

         b varchar(20)

) ;

1. 数据迁移

修改配置文件，将TYPE类型值从“TABLE”修改为“COPY”进行数据抽取和导入

[root@dm ora2pg-21.1]# ora2pg -c mysql.dist -a range_test

[========================>] 1/1 tables (100.0%) end of scanning.        

[========================>] 10000/9999 rows (100.0%) Table range_test (10000 recs/sec)

[========================>] 10000/9999 total rows (100.0%) - (1 sec., avg: 10000 recs/sec).

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '``,``b`` FROM `range_test` a WHERE (1=1)' at line 1 at /usr/local/share/perl5/Ora2Pg.pm line 15354.

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '``,``b`` FROM `range_test` a WHERE (1=1)' at line 1 at /usr/local/share/perl5/Ora2Pg.pm line 15354.

[root@dm ora2pg-21.1]# ora2pg -c mysql.dist -a range_test

[========================>] 1/1 tables (100.0%) end of scanning.        

[========================>] 10000/9999 rows (100.0%) Table range_test (10000 recs/sec)

[========================>] 10000/9999 total rows (100.0%) - (1 sec., avg: 10000 recs/sec).

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '``,``b`` FROM `range_test` a WHERE (1=1)' at line 1 at /usr/local/share/perl5/Ora2Pg.pm line 15354.

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '``,``b`` FROM `range_test` a WHERE (1=1)' at line 1 at /usr/local/share/perl5/Ora2Pg.pm line 15354.

存在问题：

好像不能正确识别mysql的分区表信息