1. 描述
   　　Ora2Pg是一个免费的工具，用于将Oracle数据库迁移到PostgreSQL兼容的模式。它连接您的Oracle数据库，自动扫描并提取它的结构或数据，然后生成可以装载到PostgreSQL数据库的SQL脚本。Ora2Pg可以从逆向工程Oracle数据库到大型企业数据库迁移，或者简单地将一些Oracle数据复制到PostgreSQL数据库中。它非常容易使用，并且不需要任何Oracle数据库知识，而不需要提供连接到Oracle数据库所需的参数。

2. 安装
   2.1. 依赖环境要求
   perl版本5.10以上
   yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
   安装DBI
   　　下载地址：https://metacpan.org/release/DBI

tar -zxvf DBI-1.641.tar.gz

cd DBI-1.641

perl Makefile.PL

make

make install

安装Oracle客户端
　　安装Oracle的客户端软件；步骤略

安装DBD-Oracle
　　下载地址：http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm

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

安装DBD::Pg[可选]
　　下载地址：http://search.cpan.org/~turnstep/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
　　下载地址 ：https://sourceforge.net/projects/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

1. 小试牛刀
   　　3.1 配置配置文件
   导出表结构
   [postgres@Postgres201 config]$ vi ora2pg_table.conf
   ORACLE_HOME /u01/app/oracle
   ORACLE_DSN dbiora221

   host=192.168.1.221;sid=orcl" class="reference-link">ORACLE_DSN dbihost=192.168.1.221;sid=orclORACLE_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
   导出数据
   [postgres@Postgres201 config]$ vi ora2pg_data.conf
   ORACLE_HOME /u01/app/oracle
   ORACLE_DSN dbiora221

   host=192.168.1.221;sid=orcl" class="reference-link">ORACLE_DSN dbihost=192.168.1.221;sid=orclORACLE_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.