- ora2pg安装
- 安装ora2pg依赖
ora2pg依赖项检测是检测安装操作系统(linux,暂不支持windows操作系统搭建,但可使用windows命令行连接搭建好的迁移工具集群)的环境是否满足迁移工具运行。
- perl-ExtUtils-CBuilder
- perl-ExtUtils-MakeMaker
- postgresql-devel
- postgresql
- gcc
- 安装DBI
解压并进行perl
#tar -xvf DBI-1.637.tar.gz
#cd DBI-1.637
#perl Makefile.PL
编译
#yum install gcc
#make
#make test(可选)
#make install
- 安装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
- 安装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
- 安装ora2pg
#tar -xvf ora2pg-21.1.tar.bz2
#cd ora2pg-21.1
#perl Makefile.PL
- 分别迁移DDL及数据
- 准备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
- 导出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)
) ;
- 数据迁移
修改配置文件,将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的分区表信息