1. 描述
Ora2Pg是一個免費的工具，用于將Oracle數(shù)據(jù)庫遷移到PostgreSQL兼容的模式搔谴。它連接您的Oracle數(shù)據(jù)庫魁袜，自動掃描并提取它的結(jié)構(gòu)或數(shù)據(jù)，然后生成可以裝載到PostgreSQL數(shù)據(jù)庫的SQL腳本敦第。Ora2Pg可以從逆向工程Oracle數(shù)據(jù)庫到大型企業(yè)數(shù)據(jù)庫遷移峰弹，或者簡單地將一些Oracle數(shù)據(jù)復制到PostgreSQL數(shù)據(jù)庫中。它非常容易使用芜果，并且不需要任何Oracle數(shù)據(jù)庫知識鞠呈，而不需要提供連接到Oracle數(shù)據(jù)庫所需的參數(shù)。
2. 安裝
2.1. 依賴環(huán)境要求
- 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
# 加載環(huán)境變量;因為必須定義ORACLE_HOME環(huán)境變量;本例在postgres用戶下配置環(huán)境變量
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
檢查所有軟件是否安裝成功？
[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. 小試牛刀
配置配置文件
- 導出表結(jié)構(gòu)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
- 導出數(shù)據(jù)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
導出數(shù)據(jù)
[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)
導入Postgres數(shù)據(jù)庫中
[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:導出出現(xiàn)"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.
解決方案
yum install -y perl-Time-HiRes
更多詳細查考http://ora2pg.darold.net/documentation.html#installation
