-ORA2PG of Oracle Migration Postgres

Description ORA2PG: Oracle PostgreSQL Database Mode Converter is a free tool for Oracle Database migration to PostgreSQL compatibility mode. It connects to the Oracle database, scans it to automatically extract its structure or data, and generates SQL scripts that you can load into the PostgreSQL database. ORA2PG Reverse Engineering from Oracle database you can use a huge enterprise database to migrate or copy some Oracle data into a PostgreSQL database. It is easy to use and does not require any Oracle database knowledge to connect to the Oracle database with the required parameters. Installation first of all thanks to Gilles Darold provides the ORA2PG tool. Current version 1.741. Install dependent Packages

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. Install the ORA2PG package

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

Unzip ORA2PG-17.4.ZIPCD Ora2pg-17.4perl makefile.plmakemake install/usr/local/bin/ora2pg-v--installed by default in/usr/local/bin/ directory; the version is v17.4. ORA2PG v17.4

Configure ORA2PG configuration file default location:/etc/ora2pg/ora2pg.conf; This will be prompted for the installation ora2pg. /usr/local/bin/ora2pg-c/etc/ora2pg/ora2pg.conf below for configuration to do a brief explanation of 1. limiting object to export. #允许或者 (exclude) An object or some 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 orata B (id bigint,name varchar);--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

Explain:

#参数 allow: directs the matching object; For example: Allow EMPLOYEES sale_.* countries. *_geom_seq: The exported object name has EMPLOYEES, countries, with ' sale_ ' beginning with ' _geom_s ' EQ ' ends. #参数 exclude is the exclusion of matching objects; #参数 where: Object record exclusions; This is only for type TABLE. #上例还可以这样执行; config file comment allow/exclude;-a:allow|-e:exclude in the execution of commands. #执行命令 ora2pg-a "Oratab"-c/etc/ora2pg/ora2pg.conf 2.Export Oracle Tables as foreign table for oracle_fdw# do Oracle migration to POSTG Res; ORACLE_FDW is also a good solution. The above example simply changes the parameter type FDW; and takes the parameter where annotation; this is a big boost for ORACLE_FDW. Avoid handwritten external table scripts.

[Email protected]_222 ~]# ora2pg-c/etc/ora2pg/ora2pg.conf[========================>] 1/1 tables (100.0%) End of SCA Nning. [========================>] 1/1 tables (100.0%) end of table export.[ [Email protected]_222 ~]# cat lottu02.sql--Generated by ORA2PG, Oracle database Schema Converter, version 17.4--Copy Right 2000-2016 Gilles Darold. All rights reserved.--DATASOURCE:dbi:Oracle:ora229SET client_encoding to ' UTF8 '; \set on_error_stop ONCREATE FOREIGN TAB LE oratab (id bigint,name varchar) SERVER ORCL OPTIONS (schema ' lottu ', table ' Oratab ');

3. Modifying object structure

#由上面导出的oratab. The ID type is bigint; but I just need int on the line; No field type is long; the name type is changed from varchar (20) to the text type. #同时导出表的名字我也想修改为: Lottu

[Email protected]_222 ~]# cat/etc/ora2pg/ora2pg.conforacle_home/u01/app/oracle#this directive is used to set the data s Ource 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, Oracle database Schema Converter, version 17.4--Copy Right 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);

Explain:

#参数: Replace_tables replacement table name; format Replace_tables orig_tbname1:dest_tbname1 orig_tbname2:dest_tbname2; This is a good understanding. # Parameters: MODIFY_ Type specifies the field type in the table. 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 F Unction 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) to V_data from (select (Case substr (Upper (V_char), rownum,1) When ' A ' and ' when ', ' B ' then ' once ', ' C ' then ' when ' and ' when ' and ' when ' E ' and ' then ' if ' F ' then ' ' else substring (V_char from rownum to 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 the 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 in 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/or A2pg.logschema lottutype Functionallow ora2pg_.*output lottu04.sql

Note:

The above provides two functions; the ORA2PG_16_TO10 function has postgres unsupported syntax; the Ora2pg_add_1 function is a simple function. This means that ORA2PG supports simple function export, where the Connect by syntax is a syntax not supported by Postgres; The export does not modify; Such code needs to be modified before Postgres executes. This revision code requires your development skills. To be continued ..... If you know more about ORA2PG, you can visit the official documentation. "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) 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 1 323[[email protected]_222 ~]# source/home/oracle/.bash_profile[[email protected]_222 ~]# echo $ORACLE _home/lib > >/etc/ld.so.conf

-ORA2PG of Oracle Migration Postgres