oracle遷移postgres之-Ora2Pg

標籤：sha   this   set   convert   case   函數匯出   not   sql   cti   

描述Ora2Pg:甲骨文PostgreSQL資料庫模式切換器是一個免費的工具用於Oracle資料庫遷移到PostgreSQL相容模式。它串連Oracle資料庫,掃描它自動提取其結構或資料,然後產生SQL指令碼,您可以載入到PostgreSQL資料庫。Ora2Pg從Oracle資料庫逆向工程可以使用巨大的企業資料庫遷移或者複製一些Oracle資料成一個PostgreSQL資料庫。它很容易使用,不需要任何Oracle資料庫知識比提供所需的參數串連Oracle資料庫。 安裝首先要感謝Gilles Darold提供了Ora2Pg工具。目前最新的版本1.741. 安裝依賴包

yum install perl-DBI perl-DBD-Pg perl-ExtUtils-MakeMaker gccwget http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gztar -zxvf DBD-Oracle-1.74.tar.gzcd DBD-Oracle-1.74source /home/oracle/.bash_profileperl Makefile.PL -lmakemake install

2.安裝 ora2pg包

：https://github.com/darold/ora2pg/tree/v17.4

unzip ora2pg-17.4.zipcd ora2pg-17.4perl Makefile.PLmakemake 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.confORACLE_HOME /u01/app/oracle#This directive is used to set the data source name in the form standard DBI DSNORACLE_DSN dbi:Oracle:ora229ORACLE_USER lottuORACLE_PWD li0924LOGFILE /tmp/Ora2Pg.logSCHEMA LOTTUTYPE TABLE INSERTALLOW ORATABOUTPUT lottu01.sql[[email protected]_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)[[email protected]_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:ora229SET client_encoding TO ‘UTF8‘;\set ON_ERROR_STOP ONCREATE 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:ora229SET 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是一大助力。避免手寫外部表格指令碼。

[[email protected]_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf[========================>] 1/1 tables (100.0%) end of scanning.[========================>] 1/1 tables (100.0%) end of table export.[[email protected]_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:ora229SET client_encoding TO ‘UTF8‘;\set ON_ERROR_STOP ONCREATE 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

[[email protected]_222 ~]# cat /etc/ora2pg/ora2pg.confORACLE_HOME /u01/app/oracle#This directive is used to set the data source name in the form standard DBI DSNORACLE_DSN dbi:Oracle:ora229ORACLE_USER lottuORACLE_PWD li0924LOGFILE /tmp/Ora2Pg.logSCHEMA LOTTUTYPE TABLEALLOW ORATABREPLACE_TABLES ORATAB:LOTTU01MODIFY_TYPE ORATAB:ID:INT,ORATAB:NAME:TEXTOUTPUT lottu03.sql[[email protected]_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf[========================>] 1/1 tables (100.0%) end of scanning.[========================>] 1/1 tables (100.0%) end of table export.[[email protected]_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

[[email protected]_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.conf[========================>] 2/2 functions (100.0%) end of output.[[email protected]_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:ora229SET client_encoding TO ‘UTF8‘;\set ON_ERROR_STOP ONCREATE OR REPLACE FUNCTION ora2pg_16_to10 (v_16_data text) RETURNS bigint AS $body$DECLAREv_data bigint;v_char varchar(18);BEGINv_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;exceptionwhen others thenreturn null;end;$body$LANGUAGE PLPGSQLSECURITY 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; BEGINv_data := v_in_1+v_in_2;return v_data;exceptionwhen others thenreturn null;end;$body$LANGUAGE PLPGSQLSECURITY DEFINER;-- REVOKE ALL ON FUNCTION ora2pg_add_1 (v_in_1 bigint,v_in_2 bigint) FROM PUBLIC; [[email protected]_222 ~]# cat /etc/ora2pg/ora2pg.confORACLE_HOME /u01/app/oracle#This directive is used to set the data source name in the form standard DBI DSNORACLE_DSN dbi:Oracle:ora229ORACLE_USER lottuORACLE_PWD li0924LOGFILE /tmp/Ora2Pg.logSCHEMA LOTTUTYPE FUNCTIONALLOW ORA2PG_.*OUTPUT lottu04.sql

備忘：

上面提供了兩個函數；ora2pg_16_to10函數有postgres不支援的文法；ora2pg_add_1函數是簡單的函數。意思是說ora2pg支援簡單的函數匯出；其中connect by文法是postgres不支援的文法；匯出並不會做修改；這樣的代碼在postgres執行之前需要修改。這個修改代碼需要各位的開發技能。未完待續.....若更瞭解Ora2Pg；可以去看官方文檔。 【FAQ】[[email protected]_222 ~]# ora2pg -c /etc/ora2pg/ora2pg.confinstall_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 3Compilation failed in require at (eval 13) line 3.Perhaps a required shared library or dll isn‘t installed where expectedat /usr/local/share/perl5/Ora2Pg.pm line 1323[[email protected]_222 ~]# source /home/oracle/.bash_profile[[email protected]_222 ~]# echo $ORACLE_HOME/lib >> /etc/ld.so.conf 

oracle遷移postgres之-Ora2Pg