Oracle迁移至PostgreSQL工具之Ora2Pg

转自：Oracle迁移至PostgreSQL工具之Ora2Pg

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

3. 小试牛刀

　　3.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

导出数据

[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

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

解决方案：

[root@Postgres201 ora2pg]# yum install -y perl-Time-HiRe

FAQ:导出出现"install_driver(Oracle) failed"错误?

[root@dev ora2pg]# ora2pg -c ora2pg_table.conf
install_driver(Oracle) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libstdc++.so.5: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 200.
 at (eval 14) line 3
Compilation failed in require at (eval 14) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at /usr/local/share/perl5/Ora2Pg.pm line 1486

解决方案：

[root@dev Oracle]# pwd
/usr/local/lib64/perl5/auto/DBD/Oracle
[root@dev Oracle]# ls
dbdimp.h  mk.pm  ocitrace.h  Oracle.bs  Oracle.h  Oracle.so
[root@dev Oracle]# ldd Oracle.so
    linux-vdso.so.1 =>  (0x00007ffe56fdd000)
    libocci.so.10.1 => /usr/lib/oracle/10.2.0.5/client64/lib/libocci.so.10.1 (0x00007fcd5a75b000)
    libclntsh.so.10.1 => /usr/lib/oracle/10.2.0.5/client64/lib/libclntsh.so.10.1 (0x00007fcd59348000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fcd5911b000)
    libc.so.6 => /lib64/libc.so.6 (0x00007fcd58d87000)
    libstdc++.so.5 => not found
    libm.so.6 => /lib64/libm.so.6 (0x00007fcd58b02000)
    libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fcd588ec000)
    libnnz10.so => /usr/lib/oracle/10.2.0.5/client64/lib/libnnz10.so (0x00007fcd5844a000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007fcd58246000)
    libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fcd5802d000)
    /lib64/ld-linux-x86-64.so.2 (0x0000003d0d400000)
[root@dev Oracle]#

从ldd结果看，由于系统缺少libstdc++.so.5导致Oracle.so库文件不能使用

安装libstdc++.so.5

[root@dev Oracle]# yum whatprovides libstdc++.so.5
[root@dev Oracle]# yum install compat-libstdc++-33

Linux下libstdc++.so.5找不到解决方案

posted @ 2018-07-17 15:57 Luis Yang 阅读(1743) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

mxly'blog

天道酬勤！