首页 > 代码库 > oracle迁移postgres之-Ora2Pg
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 make make install /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) ) ; -- 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 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 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf [========================>] 1/1 tables (100.0%) end of scanning. [========================>] 1/1 tables (100.0%) end of table export. [root@sdserver40_222 ~]# cat lottu02.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 FOREIGN TABLE oratab ( id bigint, name varchar(20) ) 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 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 ALLOW ORATAB REPLACE_TABLES ORATAB:LOTTU01 MODIFY_TYPE ORATAB:ID:INT,ORATAB:NAME:TEXT OUTPUT lottu03.sql [root@sdserver40_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf [========================>] 1/1 tables (100.0%) end of scanning. [========================>] 1/1 tables (100.0%) end of table export. [root@sdserver40_222 ~]# cat lottu03.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 lottu01 ( id int, name text ) ;
解释：
#参数：REPLACE_TABLES 替换表名；格式REPLACE_TABLES ORIG_TBNAME1:DEST_TBNAME1 ORIG_TBNAME2:DEST_TBNAME2；这个很好理解
#参数：MODIFY_TYPE 指定表中字段类型。
4.Extract procedures/functions/packages
[root@sdserver40_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf [========================>] 2/2 functions (100.0%) end of output. [root@sdserver40_222 ~]# cat lottu04.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 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$ DECLARE v_data bigint; BEGIN v_data := v_in_1+v_in_2; return v_data; exception when others then return null; end; $body$ LANGUAGE PLPGSQL SECURITY DEFINER ; -- REVOKE ALL ON FUNCTION ora2pg_add_1 (v_in_1 bigint,v_in_2 bigint) FROM PUBLIC; [root@sdserver40_222 ~]# cat /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 FUNCTION ALLOW ORA2PG_.* OUTPUT lottu04.sql
备注：
上面提供了两个函数；ora2pg_16_to10函数有postgres不支持的语法；ora2pg_add_1函数是简单的函数。意思是说ora2pg支持简单的函数导出；
其中connect by语法是postgres不支持的语法；导出并不会做修改；这样的代码在postgres执行之前需要修改。这个修改代码需要各位的开发技能。
未完待续.....
若更了解Ora2Pg；可以去看官方文档。
【FAQ】
[root@sdserver40_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf
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
oracle迁移postgres之-Ora2Pg
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。