oracle遷移postgres之-Ora2Pg

ora2pg:甲骨文postgresql資料庫模式轉換器是一個免費的工具用于oracle資料庫遷移到postgresql相容模式。它連接配接oracle資料庫,掃描它自動提取其結構或資料,然後生成sql腳本,您可以加載到postgresql資料庫。

ora2pg從oracle資料庫逆向工程可以使用巨大的企業資料庫遷移或者複制一些oracle資料成一個postgresql資料庫。它很容易使用,不需要任何oracle資料庫知識比提供所需的參數連接配接oracle資料庫。

首先要感謝gilles darold提供了ora2pg工具。目前最新的版本1.74

1. 安裝依賴包

yum install perl-dbi perl-dbd-pg perl-extutils-makemaker gcc

wget http://search.cpan.org/cpan/authors/id/p/py/pythian/dbd-oracle-1.74.tar.gz

tar -zxvf dbd-oracle-1.74.tar.gz

cd dbd-oracle-1.74

source /home/oracle/.bash_profile

perl makefile.pl -l

make

make install

2.安裝 ora2pg包

下載下傳位址：https://github.com/darold/ora2pg/tree/v17.4

unzip ora2pg-17.4.zip

cd ora2pg-17.4

perl makefile.pl

/usr/local/bin/ora2pg -v --預設安裝在/usr/local/bin/目錄下；版本為v17.4。

ora2pg v17.4

ora2pg配置檔案預設位置：/etc/ora2pg/ora2pg.conf； 這個在安裝ora2pg會有提示。

/usr/local/bin/ora2pg -c /etc/ora2pg/ora2pg.conf

下面針對配置做下簡單講解

1. limiting object to export。

#允許或者（排除）某個對象或者某些對象導出；

/etc/ora2pg/ora2pg.conf

oracle_home /u01/app/oracle

#this directive is used to set the data source name in the form standard dbi dsn

oracle_dsn dbi:oracle:ora229

oracle_user lottu

oracle_pwd li0924

logfile /tmp/ora2pg.log

schema lottu

type table insert

allow oratab

output lottu01.sql

[root@sdserver40_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf

[========================>] 1/1 tables (100.0%) end of scanning.

[> ] 0/1 tables (0.0%) end of scanning.

[========================>] 1/1 tables (100.0%) end of table export.

[====> ] 1/6 rows (16.7%) table oratab (1 recs/sec)

[========================>] 6/6 rows (100.0%) on total estimated data (1 sec., avg: 6 recs/sec)

[root@sdserver40_222 ~]# cat lottu01.sql

-- generated by ora2pg, the oracle database schema converter, version 17.4

-- copyright 2000-2016 gilles darold. all rights reserved.

-- datasource: dbi:oracle:ora229

set client_encoding to 'utf8';

\set on_error_stop on

create table oratab (

id bigint,

name varchar(20)

) ;

begin;

insert into oratab (id,name) values (1001,e'li0924');

alter sequence if exists ggs_marker_seq restart with 7117;

alter sequence if exists ggs_ddl_seq restart with 7117;

commit

解釋：

#參數 allow：指導出比對的對象；例如：allow employees sale_.* countries .*_geom_seq ：導出的對象名稱有 employees, countries, 以 'sale_'開頭 以 '_geom_seq'結尾的。

#參數 exclude就是排除比對的對象；與allow相反。

#參數 where：對象記錄排除；這個隻針對type table.

#上例還可以這樣執行；配置檔案注釋allow/exclude；在執行指令用-a : allow|-e : exclude來替代。

#執行指令 ora2pg -a "oratab" -c /etc/ora2pg/ora2pg.conf

2.export oracle tables as foreign table for oracle_fdw

#做oracle遷移到postgres;其中oracle_fdw也是一個很好的方案. 那上例隻需改下參數type fdw;并把參數where注釋；這對oracle_fdw是一大助力。避免手寫外部表腳本。

[root@sdserver40_222 ~]# cat lottu02.sql

create foreign table oratab (

) server orcl options(schema 'lottu', table 'oratab');

3. modifying object structure

#由上面導出的oratab.id類型是bigint；可是我隻需要int就行；不需要字段類型為長整型；name類型由varchar(20)改為text類型。

#同時導出表的名字我也想修改為：lottu

[root@sdserver40_222 ~]# cat /etc/ora2pg/ora2pg.conf

type table

replace_tables oratab:lottu01

modify_type oratab:id:int,oratab:name:text

output lottu03.sql

[root@sdserver40_222 ~]# cat lottu03.sql

create table lottu01 (

id int,

name text

#參數：replace_tables 替換表名；格式replace_tables orig_tbname1:dest_tbname1 orig_tbname2:dest_tbname2；這個很好了解

#參數：modify_type 指定表中字段類型。

4.extract procedures/functions/packages

[========================>] 2/2 functions (100.0%) end of output.

[root@sdserver40_222 ~]# cat lottu04.sql

create or replace function ora2pg_16_to10 (v_16_data text) returns bigint as $body$

declare

v_data bigint;

v_char varchar(18);

begin

v_char:=substring(v_16_data from 2);

select sum(data) into v_data from

(

select (

case substr(upper(v_char),rownum,1)

when 'a' then '10'

when 'b' then '11'

when 'c' then '12'

when 'd' then '13'

when 'e' then '14'

when 'f' then '15'

else substring(v_char from rownum for 1) end

)*power(16,length(v_char)-rownum) data

connect by rownum<=length(v_char)

);

return v_data;

exception

when others then

return null;

end;

$body$

language plpgsql

security definer

;

-- revoke all on function ora2pg_16_to10 (v_16_data text) from public;

create or replace function ora2pg_add_1 (v_in_1 bigint,v_in_2 bigint) returns bigint as $body$

v_data := v_in_1+v_in_2;

-- revoke all on function ora2pg_add_1 (v_in_1 bigint,v_in_2 bigint) from public;

type function

allow ora2pg_.*

output lottu04.sql

備注：

上面提供了兩個函數；ora2pg_16_to10函數有postgres不支援的文法；ora2pg_add_1函數是簡單的函數。意思是說ora2pg支援簡單的函數導出；

其中connect by文法是postgres不支援的文法；導出并不會做修改；這樣的代碼在postgres執行之前需要修改。這個修改代碼需要各位的開發技能。

未完待續.....

若更了解ora2pg；可以去看官方文檔。

【faq】

install_driver(oracle) failed: can't load '/usr/local/lib64/perl5/auto/dbd/oracle/oracle.so' for module dbd::oracle: libclntsh.so.11.1: cannot open shared object file: no such file or directory at /usr/lib64/perl5/dynaloader.pm line 200.

at (eval 13) line 3

compilation failed in require at (eval 13) line 3.

perhaps a required shared library or dll isn't installed where expected

at /usr/local/share/perl5/ora2pg.pm line 1323

[root@sdserver40_222 ~]# source /home/oracle/.bash_profile

[root@sdserver40_222 ~]# echo $oracle_home/lib >> /etc/ld.so.conf