通过EDBMtk工具从Oracle向PostgreSQL迁移数据

一. 参考资料

postgresql-10-US.pdf

Postgres_Plus_Migration_Guide.pdf

www.postgresql.org

www.enterprisedb.com

*由于技术更新很快，请注意本文的时效性。

二. 环境说明

源数据库：Oracle 12cR2下的一个pdb，服务名aegisdb

目标数据库：postgresql-10.1

三. 准备迁移工具

工具名称: EDB Migration Toolkit（EDB MTK）

EDB MTK可以通过PostgreSQL安装后自带的Application Stack Builder软件下载并安装，也可以单独下载（目前最新的51.0.1下载地址为http://sbp.enterprisedb.com/getfile.jsp?fileid=10872）。该迁移工具并不是必须，但它可以大大简化Oracle的数据库对象向PostgreSQL的转移过程：

数据库中的数据可以很方便的从Oracle迁移到PostgreSQL；

Oracle中的存储过程代码由于PLSQL和PL/PGSQL差异较大，需要逐个手工修改后迁移。

5.1 安装

安装EDB MTK工具的服务器上需预装好JAVA.

我这里把EDB MTK和PostgreSQL装在了同一个服务器上，可以不需要这样。

# ./migrationtoolkit-51.0.1-1-linux-x64.run --mode text
Language Selection
Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] :
----------------------------------------------------------------------------
Welcome to the Migration Toolkit Setup Wizard.
----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.
<<此处省略协议内容>>
Do you accept this license? [y/n]: y
----------------------------------------------------------------------------
Please specify the directory where Migration Toolkit will be installed.
Installation Directory [/opt/edb/mtk]: /PostgreSQL/edbmtk
----------------------------------------------------------------------------
Setup is now ready to begin installing Migration Toolkit on your computer.
Do you want to continue? [Y/n]: Y
----------------------------------------------------------------------------
Please wait while Setup installs Migration Toolkit on your computer.

 Installing Migration Toolkit
 0% ______________ 50% ______________ 100%
 #########################################

----------------------------------------------------------------------------
EnterpriseDB is the leading provider of value-added products and services for
the Postgres community.

Please visit our website at www.enterprisedb.com
 
# chown -R postgres:postgres /PostgreSQL/edbmtk

5.2 配置JDBC库文件

从https://www.enterprisedb.com/advanced-downloads的跳转连接下载对应Oracle版本的第三方JDBC驱动ojdbc8.jar。放置于服务器的JAVA_HOME/jre/lib/ext下。

从https://www.enterprisedb.com/advanced-downloads的跳转链接下载对应PostgreSQL版本的JDBC驱动postgresql-42.2.2.jar。放置于服务器JAVA_HOME/jre/lib/ext下。

5.2 配置迁移工具

$ cd /PostgreSQL/edbmtk/etc/
$ vi toolkit.properties

SRC_DB_URL=jdbc:oracle:thin:@10.19.100.19:1521/aegisdb
SRC_DB_USER=aegisdb
SRC_DB_PASSWORD=123456

 
TARGET_DB_URL=jdbc:postgresql://localhost:5432/aegis
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=123456

配置文件中以JDBC的方式配置目标库（PG）和源库（Oracle）的连接串，因为这里Oracle是12c的一个pdb，所以需要使用服务名不能用SID的方式。

四. 迁移

4.1 生成迁移脚本

虽然EDB MTK工具也提供在线直接迁移的功能，但异构数据库之间的数据库对象迁移会有非常多的问题，因此建议先用离线迁移模式导出源库（Oracle）中所有对象的创建脚本，逐一检查是否符合目标库（PostgreSQL）的SQL语法，修改完毕后再提交到目标库执行。

生成迁移脚本：

 

$ mkdir /PostgreSQL/mtk_scripts
$ mkdir /PostgreSQL/mtk_data
$ cd /PostgreSQL/edbmtk/bin/
$ ./runMTK.sh -targetdbtype postgres -schemaOnly -offlineMigration \
  /PostgreSQL/mtk_scripts AEGISDB

runMTK脚本的最后一个参数表示要转移数据的Oracle schema名称，需要大写。

完成后，在/PostgreSQL/mtk_scripts/ 目录下生成Oracle中所有对象对应的PostgreSQL对象的创建脚本。

 

$ cd /PostgreSQL/mtk_scripts/
$ ls -l
total 96

-rw-rw-r-- 1 postgres postgres  2789 May  9 10:47 mtk_aegisdb_constraint_ddl.sql
-rw-rw-r-- 1 postgres postgres 41829 May  9 10:48 mtk_aegisdb_ddl.sql
-rw-rw-r-- 1 postgres postgres   417 May  9 10:48 mtk_aegisdb_index_ddl.sql
-rw-rw-r-- 1 postgres postgres    24 May  9 10:47 mtk_aegisdb_schema_ddl.sql
-rw-rw-r-- 1 postgres postgres  2750 May  9 10:47 mtk_aegisdb_sequence_ddl.sql
-rw-rw-r-- 1 postgres postgres 32326 May  9 10:47 mtk_aegisdb_table_ddl.sql
-rw-rw-r-- 1 postgres postgres  3619 May  9 10:48 mtk_aegisdb_view_ddl.sql

其中mtk_aegisdb_ddl.sql为所有对象脚本（其内容就是其他所有脚本内容的并集）。其他脚本名称自明，就不做解释了。

脚本在实际导入PostgreSQL前需要进一步校验，确保语法正确。

4.2 表

4.2.1 表结构

表结构位于生成的脚本mtk_aegisdb_table_ddl.sql中，生成的脚本里已经对大部分的数据类型等异构数据库间不兼容项做了映射，但还需要对导出的建表语句进行手工修改以适用于PostgreSQL。如果条件允许可以先在建好的数据库里跑一次这个脚本，检查日志中的报错信息后再做修改：

$ psql -U postgres -d aegis -o ./imp.log -f mtk_aegisdb_table_ddl.sql

可以通过在PG上执行下面的SQL语句来获得一次性清理所有已经导入的表对象的脚本：

select 'drop table aegisdb.'||tablename||';' from pg_tables where schemaname='aegisdb'

其中需要修改的地方包括但不限于：

数据类型

1. date -> timestamp(0) without time zone

Oracle date类型是yyyy-mm-dd hh:mi:ss

PG date类型是yyyy-mm-dd，因此需要换为timestamp(0) without time zone，0表示秒之后的位数有几位

2. default sysdate -> default NOW()

PG无sysdate，使用 NOW()函数替代

分区表

PG的分区是以“主表+子表+规则”的方式实现的（如果试用PG10，则不需要自己写规则），建表语句和Oracle完全不同，EDB MTK无法转换，需要自行修改。

PG分区表建表语句参考如下：

-- 主表
CREATE TABLE MESSAGE_BK_OLD (
    ID NUMERIC(38) NOT NULL,
    MSGCONTENT TEXT,
    STATUS NUMERIC(2) NOT NULL,
    ADDR_TYPE VARCHAR(4) NOT NULL,
    ADDRESS VARCHAR(60) NOT NULL,
    PRIORITY VARCHAR(10) NOT NULL,
    RESEND NUMERIC(10) NOT NULL,
    TIME timestamp(0) without time zone NOT NULL,
    RAWID NUMERIC(38) NOT NULL,
    SENDER_ID NUMERIC(32),
    SOURCE VARCHAR(50) NOT NULL,
    MSGTYPE VARCHAR(20) NOT NULL,
    TAG NUMERIC(2) DEFAULT 0,
    SOURCEADDR VARCHAR(60),
    COPYSTATUS NUMERIC(2),
    FLIGHT_NO VARCHAR(10),
    ORIGIN_DESTADDR VARCHAR(256),
    DOUBLESIGNATURE VARCHAR(10)
);

--子表1
create table MESSAGE_BK_OLD_P1(
  check(TIME<TO_timestamp('2008-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')::timestamp(0) without time zone))
  INHERITS (MESSAGE_BK_OLD);

--子表2
create table MESSAGE_BK_OLD_P2(
  check(TIME>=TO_timestamp('2008-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')::timestamp(0) without time zone))
  INHERITS (MESSAGE_BK_OLD);

--规则1，这里其实不需要规则了，因为数据库是PostgreSQL 10.1
CREATE OR REPLACE RULE MESSAGE_BK_OLD_P1
AS ON INSERT TO MESSAGE_BK_OLD
       WHERE TIME < TO_timestamp('2008-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')::timestamp(0) without time zone
       DO INSTEAD
       INSERT INTO MESSAGE_BK_OLD_P1 VALUES(NEW.*);

--规则2，这里其实不需要规则了，因为数据库是PostgreSQL 10.1
CREATE OR REPLACE RULE MESSAGE_BK_OLD_P2
AS ON INSERT TO MESSAGE_BK_OLD
       WHERE TIME >= TO_timestamp('2008-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')::timestamp(0) without time zone
       DO INSTEAD
       INSERT INTO MESSAGE_BK_OLD_P2 VALUES(NEW.*);

所有建表脚本修改完后，在数据库上执行，生成表对象：

$ psql -U postgres -d aegis -o ./imp.log -f mtk_typeb_table_ddl.sql

如果还有报错，则反复进行修改步骤，直至不报错。　

4.2.2 数据

在4.2.1成功转移所有数据库表对象的基础上，数据迁移也可以通过EDB MTK工具进行。

$ ./runMTK.sh -sourcedbtype oracle -targetdbtype postgres -dataOnly -logDir /PostgreSQL/mtk_scripts/ AEGISDB

通过 dataOnly选项指定只做数据转移，最后一个参数是要转移的Oracle schema，需要大写。有1点需要注意：

CLOB兼容性

Oracle的CLOB字段可以含有不在ASCII码表中的字符，而PG对应CLOB的text类型不支持这种字符。例如，0x00（C里的'\0'）。该问题无法通过数据库解决，只能通过应用想办法绕开，常见办法是吧这类字符替换成其他在ASCII码表里的特定字符。

另外，Offline转移可以把源库的数据做成可以使用postgres COPY命令导入的文件，但是需要自行编写导入脚本；

4.3 索引

索引创建语句位于生成的脚本mtk_aegisdb_index_ddl.sql中。索引一定要等待数据导入后再创建，避免外键约束导致数据无法导入。

已知的问题点有：

函数索引

PG支持函数索引，但函数名称和Oracle不同，需要把函数索引使用的函数修改成对应的PG函数。

分区表的分区索引

PG没有全局/分区索引的概念，由于其分区表实现机制，实际分区表上的所有索引都是局部分区索引。

执行脚本创建索引：

$ psql -U postgres -d aegis -o ./imp.log -f mtk_aegisdb_index_ddl.sql

4.4 约束

约束创建语句位于生成的脚本mtk_aegisdb_constraint_ddl.sql中。脚本可以直接在数据库中执行，可能会报大量的错误，这是由于主键约束，唯一约束在上面建表脚本、创建索引脚本中都已经包含了，再次创建会报对象已经存在；而非空约束在PG中不需要单独创建。

这里我的约束脚本没有其他额外的问题，直接执行并忽略错误即可：

$ psql -U postgres -d aegis -o ./imp.log -f mtk_aegisdb_constraint_ddl.sql

4.5 视图

视图创建语句位于生成的脚本mtk_aegisdb_view_ddl.sql中。PG的视图不兼容Oracle的伪列，含有Oracle伪列的视图需要修改创建语句并在使用上做出相应修改；PG9.3以及以前的版本不支持with check option。

我的视图均为标准语句，直接执行脚本在数据库中创建视图：

$ psql -U postgres -d aegis -o ./imp.log -f mtk_aegisdb_view_ddl.sql

4.6 Sequence

Sequence创建语句位于生成的脚本mtk_aegisdb_sequence_ddl.sql中。需要做序列上限值检查后再在PG里执行。Oracle的Sequence对象的最大值可以写成任意的溢出值（如：999999999999999999999999），但PG的Sequence对象最大值上限为9223372036854775807，超过这个值的创建语句会报错。

$ psql -U postgres -d aegis -o ./imp.log -f mtk_aegisdb_sequence_ddl.sql

4.7 函数、存储过程和包

使用EDB MTK生成离线迁移脚本时是不会产生代码的创建脚本的，而且由于PLSQL和PLPGSQL的语法差异较大，在线迁移迁移的代码对象几乎肯定会报错。代码部分需要逐个手工修改并转移，这也是整个迁移工作中最耗费时间和人力的部分。

参与数据库代码转移的人员需要有Oracle代码和PG代码的编写能力。