Ora2Pg 是一个免费的工具，用于将 Oracle 数据库迁移到 PostgreSQL 兼容的模式。它连接您的 Oracle 数据库，自动扫描并提取它的结构或数据，然后生成可以装载到 PostgreSQL 数据库的 SQL 脚本。Ora2Pg 可以从逆向工
1.简介
Ora2Pg 是一个免费的工具，用于将 Oracle 数据库迁移到 PostgreSQL 兼容的模式。它连接您的 Oracle 数据库，自动扫描并提取它的结构或数据，然后生成可以装载到 PostgreSQL 数据库的 SQL 脚本。Ora2Pg 可以从逆向工程 Oracle 数据库到大型企业数据库迁移，或者简单地将一些 Oracle 数据复制到 PostgreSQL 数据库中。它非常容易使用，并且不需要任何 Oracle 数据库知识，而不需要提供连接到 Oracle 数据库所需的参数。
Ora2Pg 由一个 Perl 脚本（ora2pg）以及一个 Perl 模块（Ora2Pg.pm）组成，唯一需要做的事情就是修改它的配置文件 ora2pg.conf，设置连接 Oracle 数据库的 DSN 和一个可选的模式名称。完成之后，只需要设置导出的类型：TABLE（包括约束）、VIEW、MVIEW、TABLESPACE、SEQUENCE、INDEXES、TRIGGER、GRANT、FUNCTION、PROCEDURE、PACKAGE、PARTITION、TYPE、INSERT 或 COPY、FDW、QUERY、KETTLE 以及 SYNONYM。
默认情况下，Ora2Pg 导出一个文件，可以通过 psql 客户端将文件加载到 PostgreSQL 数据库；但是也可以在配置文件中设置一个数据库的 DSN，直接导入 PostgreSQL 数据库。通过 ora2pg.conf 中的配置选项，可以控制导出的内容和方式。
| 对象 | ora2pg 是否支持 |
|---|---|
| view | 是 |
| trigger | 是，某些情况下需要手工修改脚本 |
| package | 是，某些情况下需要手工修改脚本 |
| sequence | 是 |
| function | 是 |
| procedure | 是，某些情况下需要手工修改脚本 |
| type | 是，某些情况下需要手工修改脚本 |
| materialized view | 是，某些情况下需要手工修改脚 |
1.1 环境描述
以 ORACLE 11.2.0.3 on LINUX sample 数据为例，迁移 psql (12.8) on LINUX .
| 内容 | 源端 | 目标端 |
|---|---|---|
| IP | 10.85.10.6 | 10.85.10.5 |
| 数据库 | oracle 11.2.3 | postgres 12.8 |
| 实例名 | PROD1 | orcl |
| 操作系统 | redhat 5.4 | redhat 7.4 |
| 迁移数据 | SH SCOTT HR | SH SCOTT HR |
2.安装
2.1依赖环境要求
2.1.1 perl版本5.10以上
yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
[root@test01 ~]# perl -v
This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi
(with 33 registered patches, see perl -V for more detail)
2.1.2 安装DBI
# tar -zxvf DBI-1.641.tar.gz
# cd DBI-1.641
# perl Makefile.PL
# make
# make install
2.1.3 安装Oracle客户端
按顺序安装oracle客户端的三个rpm包 （安装包地址（包含oracle的rpm客户端和DBI DBD二进制包
[root@localhost tmp]# rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]
[root@localhost tmp]# rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]
[root@localhost tmp]# rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
Preparing... ########################################### [100%]
1:oracle-instantclient11.########################################### [100%]
2.1.4 安装DBD-Oracle
# tar -zxvf DBD-Oracle-1.74.tar.gz加载环境变量;因为必须定义ORACLE_HOME环境变量;本例在postgres用户下配置环境变量
# source /home/postgres/.bashrc
# cd DBD-Oracle-1.74
# perl Makefile.PL
# make
# make install
2.1.5 安装DBD::Pg[可选]
# tar -zxvf DBD-Pg-1.32.tar.gz
# source /home/postgres/.bashrc
# cd DBD-Pg-1.32
# perl Makefile.PL
# make
# make install
2.2安装Ora2Pg
[root@Postgres201 ~]# tar -zxvf ora2pg-18.2.tar.gz
ora2pg-18.2/
ora2pg-18.2/INSTALL
ora2pg-18.2/LICENSE
ora2pg-18.2/MANIFEST
ora2pg-18.2/Makefile.PL
ora2pg-18.2/README
ora2pg-18.2/changelog
ora2pg-18.2/doc/
ora2pg-18.2/doc/Ora2Pg.pod
ora2pg-18.2/doc/ora2pg.3
ora2pg-18.2/lib/
ora2pg-18.2/lib/Ora2Pg.pm
ora2pg-18.2/lib/Ora2Pg/
ora2pg-18.2/lib/Ora2Pg/GEOM.pm
ora2pg-18.2/lib/Ora2Pg/MySQL.pm
ora2pg-18.2/lib/Ora2Pg/PLSQL.pm
ora2pg-18.2/packaging/
ora2pg-18.2/packaging/README
ora2pg-18.2/packaging/RPM/
ora2pg-18.2/packaging/RPM/ora2pg.spec
ora2pg-18.2/packaging/debian/
ora2pg-18.2/packaging/debian/create-deb-tree.sh
ora2pg-18.2/packaging/debian/ora2pg/
ora2pg-18.2/packaging/debian/ora2pg/DEBIAN/
ora2pg-18.2/packaging/debian/ora2pg/DEBIAN/control
ora2pg-18.2/packaging/debian/ora2pg/DEBIAN/copyright
ora2pg-18.2/packaging/slackbuild/
ora2pg-18.2/packaging/slackbuild/Ora2Pg.SlackBuild
ora2pg-18.2/packaging/slackbuild/Ora2Pg.info
ora2pg-18.2/packaging/slackbuild/README
ora2pg-18.2/packaging/slackbuild/doinst.sh
ora2pg-18.2/packaging/slackbuild/slack-desc
ora2pg-18.2/scripts/
ora2pg-18.2/scripts/ora2pg
ora2pg-18.2/scripts/ora2pg_scanner
[root@Postgres201 ~]# cd ora2pg-18.2
[root@Postgres201 ora2pg-18.2]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Ora2Pg
Done...
------------------------------------------------------------------------------
Please read documentation at http://ora2pg.darold.net/ before asking for help
------------------------------------------------------------------------------
Now type: make && make install
[root@Postgres201 ora2pg-18.2]# make && make install
cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm
cp lib/Ora2Pg/GEOM.pm blib/lib/Ora2Pg/GEOM.pm
cp lib/Ora2Pg/PLSQL.pm blib/lib/Ora2Pg/PLSQL.pm
cp lib/Ora2Pg/MySQL.pm blib/lib/Ora2Pg/MySQL.pm
cp scripts/ora2pg blib/script/ora2pg
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg
cp scripts/ora2pg_scanner blib/script/ora2pg_scanner
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg_scanner
Manifying blib/man3/ora2pg.3
Installing /usr/local/share/perl5/Ora2Pg.pm
Installing /usr/local/share/perl5/Ora2Pg/MySQL.pm
Installing /usr/local/share/perl5/Ora2Pg/GEOM.pm
Installing /usr/local/share/perl5/Ora2Pg/PLSQL.pm
Installing /usr/local/share/man/man3/ora2pg.3
Installing /usr/local/bin/ora2pg
Installing /usr/local/bin/ora2pg_scanner
Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg
Appending installation info to /usr/lib64/perl5/perllocal.pod
2.3查看所有软件是否安装成功
[root@Postgres201 ~]# vi 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@Postgres201 ~]# perl check.pl
DBD::Oracle -- 1.74
DBD::Pg -- 1.32
DBI -- 1.641
Ora2Pg -- 18.2
Perl -- 5.10.1
3.小试牛刀
3.1配置文件
3.1.1导出表结构
[postgres@Postgres201 config]$ vi ora2pg_table.conf
ORACLE_HOME /u01/app/oracle
ORACLE_DSN dbi:Oracle:ora221
#ORACLE_DSN dbi:Oracle:host=192.168.1.221;sid=orcl
ORACLE_USER lottu
ORACLE_PWD li0924
SCHEMA lottu
TYPE TABLE
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT table.sql
3.1.2导出数据
[postgres@Postgres201 config]$ vi ora2pg_data.conf
ORACLE_HOME /u01/app/oracle
ORACLE_DSN dbi:Oracle:ora221
#ORACLE_DSN dbi:Oracle:host=192.168.1.221;sid=orcl
ORACLE_USER lottu
ORACLE_PWD li0924
SCHEMA lottu
TYPE COPY
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT data.sql
3.2 导出数据
[postgres@Postgres201 config]$ ora2pg -c ora2pg_table.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
[postgres@Postgres201 config]$ ora2pg -c ora2pg_data.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[========================>] 4/1 rows (400.0%) Table DEPT (4 recs/sec)
[===================> ] 4/5 total rows (80.0%) - (0 sec., avg: 4 recs/sec).
[========================>] 4/4 rows (100.0%) Table ORATAB (4 recs/sec)
[========================>] 8/5 total rows (160.0%) - (0 sec., avg: 8 recs/sec).
[========================>] 5/5 rows (100.0%) on total estimated data (1 sec., avg: 5 recs/sec)
3.3 导入PostgreSQL数据库中
[postgres@Postgres201 config]$ psql lottu lottu -f table.sql
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
[postgres@Postgres201 config]$ psql lottu lottu -f data.sql
SET
SET
BEGIN
COPY 4
COPY 4
COMMIT
4. FAQ:导出出现"Can't locate Time/HiRes.pm in"错误?
[root@Postgres201 ora2pg]# ora2pg -c ora2pg.conf
Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/share/perl5/Ora2Pg.pm line 33.
BEGIN failed--compilation aborted at /usr/local/share/perl5/Ora2Pg.pm line 33.
Compilation failed in require at /usr/local/bin/ora2pg line 30.
BEGIN failed--compilation aborted at /usr/local/bin/ora2pg line 30.
**解决方案：**
[root@Postgres201 ora2pg]# yum install -y perl-Time-HiRes
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
