前言：
有的时候有需求需要迁移Oracle数据库的数据到postgresql，那么，其实可供选择的工具是比较多的，但从迁移效率，准确度这些角度来选择的话，无疑还是GitHub上的开源免费工具ora2pg比较合适的。
ora2pg主要是由perl语言编写的，因此，部署使用的前提是需要有perl语言环境，这里需要特别注意哦。
ora2pg的工作原理是连接到 Oracle，根据提前准备的配置文件，主动扫描并抽取oracle数据库的结构和数据，然后生成用于迁移的SQL脚本，利用该脚本可将数据库结构和数据加载到postgresql数据库中
ora2pg的优点是免费，迁移速度快，迁移数据准确，部署简单，快速，可以在任意的可以同时连接postgresql和oracle数据库的服务器上安装，简单说就是灵活自由。
主要由DBI，DBD::ORACLE，DBD::PG，ora2pg 这些组件组成，如果需要直接将oracle的数据迁移入库到postgresql数据库而不经过任何中间库的话，那么，是需要安装的
DBD::PG下载地址：http://www.cpan.org/authors/id/T/TU/TURNSTEP/
https://metacpan.org/pod/DBD::OracleDBD::ORACLE下载地址：https://metacpan.org/pod/DBD::Oracle
ora2pg下载地址：https://github.com/darold/ora2pg/releases
下面将介绍如何在A服务器 centos7下面部署安装ora2pg，在另一台服务器B上通过docker安装部署一个Oracle，并简单的写一些数据
######注：CPAN是perl语言的资料库
环境简介：
A服务器的IP是192.168.123.11，操作系统是centos，安装有postgresql数据库
B服务器的IP是192.168.123.12，操作系统是centos，安装有docker启动的Oracle数据库
一，
postgresql的概况
pg数据库所在服务器的IP地址是 192.168.123.11
计划在此服务器上安装ora2pg
二，
ora2pg的安装部署
####注：这一部分相关部署都是在192.168.123.11上的哦，当然，也可以在任意一台可同时连接postgresql和oracle的服务器上部署
相关文件下载：
相关文件在百度网盘上，地址如下：
链接：https://pan.baidu.com/s/1ixb6Vi7aCvNXfzWOqM13rw?pwd=ora2
提取码：ora2
--来自百度网盘超级会员V5的分享
版本说明：
ora2pg的版本选用的比较高，考虑到pg数据库版本是12，Oracle数据库的版本是11g，还算比较新，并且ora2pg版本高一点，功能会稍微多一点。
架构说明:
ora2pg的工作原理是利用Oracle的客户端，通过dbd插件和dbi插件连接Oracle数据库，读取预设的自定义规则（自定义规则定义在ora2pg的配置文件内），按照设定规则扫描Oracle数据库内的目标表，逆向生成可供postgresql数据库直接使用的SQL存储语句
1，
dbi的安装
yum install -y perl-DBI perl-DBD-Pg perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder perl-CPAN perl-ExtUtils-eBuilder
yum install perl perl-devel make gcc gcc-c++ -y
yum install perl-Time-HiRes perl-tests cpan perl-Time-HiRes -y
该插件基本不需要考虑太多版本问题，直接本地仓库yum安装即可，该插件的主要用途是oracle客户端连接oracle服务器的驱动
####注意，perl的版本必须是5.0以上！！！！！！！！！！
2，
oracle的客户端安装
#####注，必须先安Oracle的客户端，否则后面的dbd：：oracle会安装不了报错
unzip instantclient-basic-linux.x64-19.20.0.0.0dbru.zip unzip instantclient-sdk-linux.x64-19.20.0.0.0dbru.zip unzip instantclient-sqlplus-linux.x64-19.20.0.0.0dbru.zip mkdir -p /opt/user/lib mv instantclient_19_20 /opt/user/lib cd /opt/user/lib chmod 755 /opt/user/lib/instantclient_19_20/ chmod 755 -Rf /opt/user/lib/instantclient_19_20/ echo "export PATH=/opt/user/lib/instantclient_19_20/:$PATH">>/etc/profile echo "export LD_LIBRARY_PATH=/opt/user/lib/instantclient_19_20/:$LD_LIBRARY_PATH">>/etc/profile source /etc/profile
3，
dbd::oracle的安装
####注：再次重申，必须先安装oracle的客户端
cd DBD-Oracle-1.83/ perl Makefile.PL -l make make install
大体输出如下；
[root@centos9 DBD-Oracle-1.83]# perl Makefile.PL -l Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/ Configuring DBD::Oracle for perl 5.016003 on linux (x86_64-linux-thread-multi) If you encounter any problem, a collection of troubleshooting guides are available under lib/DBD/Oracle/Troubleshooting. 'DBD::Oracle::Troubleshooting' is the general troubleshooting guide, while platform-specific troubleshooting hints live in their labelled sub-document (e.g., Win32 hints are gathered in 'lib/DBD/Oracle/Troubleshooting/Win32.pod'). Trying to find an ORACLE_HOME Found /opt/user/lib/instantclient_19_20/ WARNING: Setting ORACLE_HOME env var to /opt/user/lib/instantclient_19_20/ for you. WARNING: If these tests fail you may have to set ORACLE_HOME yourself! Installing on a linux, Ver#3.10 Using Oracle in /opt/user/lib/instantclient_19_20/ DEFINE _SQLPLUS_RELEASE = "1920000000" (CHAR) Oracle Version 19.20.0.0 (19.20) Looks like an Instant Client installation, okay Your LD_LIBRARY_PATH env var is set to '/opt/user/lib/instantclient_19_20/:' Oracle sysliblist: Found header files in /opt/user/lib/instantclient_19_20//sdk/include. client_version=19.20 DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"19.20.0.0\" -DORA_OCI_102 -DORA_OCI_112 Checking for functioning wait.ph System: perl5.016003 linux x86-01.bsys.centos.org 3.10.0-693.17.1.el7.x86_64 #1 smp thu jan 25 20:13:58 utc 2018 x86_64 x86_64 x86_64 gnulinux Compiler: gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 Linker: /usr/bin/ld Sysliblist: Linking with -lclntsh. Checking if your kit is complete... Looks good Unrecognized argument in LIBS ignored: '-Wl,-rpath,/opt/user/lib/instantclient_19_20/' LD_RUN_PATH=/opt/user/lib/instantclient_19_20 Using DBD::Oracle 1.83. Using DBD::Oracle 1.83. Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/
基本没什么好说的，基础的编译环境具备就可以了
4,
ora2pg的安装
该安装类似DBD::oracle的安装，没什么好说的
####注：ora2pg安装完毕后会自动将可执行文件加入环境变量，无需过多设置
cd ora2pg-23.1/ perl Makefile.PL -l echo $? make echo $? make install echo $?
命令输出如下：
[root@centos9 ora2pg]# cd ora2pg-23.1 [root@centos9 ora2pg-23.1]# ls changelog doc INSTALL lib LICENSE Makefile.PL MANIFEST packaging README scripts [root@centos9 ora2pg-23.1]# perl Makefile.PL -l 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@centos9 ora2pg-23.1]# make && make install cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm cp lib/Ora2Pg/GEOM.pm blib/lib/Ora2Pg/GEOM.pm cp lib/Ora2Pg/Oracle.pm blib/lib/Ora2Pg/Oracle.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/GEOM.pm Installing /usr/local/share/perl5/Ora2Pg/Oracle.pm Installing /usr/local/share/perl5/Ora2Pg/PLSQL.pm Installing /usr/local/share/perl5/Ora2Pg/MySQL.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@centos9 ora2pg-23.1]# echo $?
最终测试以上组件是否正确安装：
cat > /root/check.pl <<"EOF" #!/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; EOF
[root@centos9 ~]# perl check.pl DBD::Oracle -- 1.83 Ora2Pg -- 23.1 Perl -- 5.16.3
三，
Oracle数据库的部署
Oracle数据库部署非常简单，因为是测试性质，因此，使用docker结合docker-compose拉起Oracle数据库实例即可
#####注：此数据库安装在192.168.123.12服务器上，启动数据库命令为：
[root@oula2 ~]# cat oracle.yaml
version: '3'
services:
oracle:
restart: always
image: hub.c.163.com/springwen/oracle12c
container_name: oracle
volumes:
- /usr/local/oracle/data:/u01/app/oracle
- /usr/local/oracle/source:/docker-entrypoint-initdb.d
environment:
- "TZ=Asia/Shanghai"
- "DBCA_TOTAL_MEMORY=16192"
- "IMPORT_FROM_VOLUME=true"
ports:
- 53432:1521
- 36888:8080
logging:
driver: "json-file"
options:
max-size: "1g"
使用sqldeveloper连接该数据库：
####注：sys用户的密码是oracle
创建emp测试表：
CREATE TABLE emp ( emp_id NUMBER(10) PRIMARY KEY, emp_name VARCHAR2(50), emp_age NUMBER(3), emp_sal NUMBER(10, 2) );
创建测试用户：
####注，该用户的密码是gzmpc，此用户是普通用户
create tablespace gzmoc_wk datafile '/u01/app/oracle/gzmoc_wk.DBF' size 100m autoextend on next 10m maxsize 500m permanent extent management local;
CREATE USER gzmpc IDENTIFIED BY PASSWORD DEFAULT TABLESPACE gzmoc_wk; grant create session to gzmpc; grant create table to gzmpc; grant unlimited tablespace to gzmpc; ALTER USER gzmpc IDENTIFIED BY gzmpc;
四，
迁移配置
主要的最为关键的就是ora2pg的配置文件了，此文件是有模版的，该模版文件在/etc/ora2pg目录下，大概是1300行，内容比较多的
下面将就此配置文件做一个详细的解读
OK，根据上面提到的文件，编写如下配置：
这里的oracle_home 是指的oracle服务器的真实路径，由于是docker部署的，因此，写成这样
oracle_dsn 是oracle服务器的连接信息，port是docker定义的53432
schema指的是oracle数据库的工作用户，这里使用的是斯考特这个用户
TYPE 可以有很多个，比如，COPY，TABLE，INSERT table指的是表结构，INSERT指的是表数据，view是视图
type option must be (TABLE, VIEW, GRANT, SEQUENCE, TRIGGER, PACKAGE, FUNCTION, PROCEDURE, PARTITION, TYPE, INSERT, COPY, TABLESPACE, SHOW_REPORT, SHOW_VERSION, SHOW_SCHEMA, SHOW_TABLE, SHOW_COLUMN, SHOW_ENCODING, FDW, MVIEW, QUERY, KETTLE, DBLINK, SYNONYM, DIRECTORY, LOAD, TEST, TEST_COUNT, TEST_VIEW, TEST_DATA)
下面是导出表结构
ORACLE_HOME /u01/app/oracle/ ORACLE_DSN dbi:Oracle:host=192.168.123.12;sid=XE;port=53432 ORACLE_USER sys ORACLE_PWD oracle SCHEMA scott TYPE TABLE DEFAULT_NUMERIC float SKIP fkeys pkeys ukeys indexes checks NLS_LANG AMERICAN_AMERICA.UTF8 OUTPUT /opt/ora2pg/table.sql
测试配置文件是否可以正常连接oracle服务器：
####注，打印的是oracle服务器的版本号
[root@centos9 ~]# ora2pg -t SHOW_VERSION -c oracle_pg.conf WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 Oracle Database 12c Standard Edition Release 12.1.0.2.0
迁移成本报告：
[root@centos9 ~]# ora2pg -t SHOW_REPORT --estimate_cost -c oracle_pg.conf WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 [========================>] 4/4 tables (100.0%) end of scanning. [========================>] 6/6 objects types (100.0%) end of objects auditing. ------------------------------------------------------------------------------- Ora2Pg v23.1 - Database Migration Report ------------------------------------------------------------------------------- Version Oracle Database 12c Standard Edition Release 12.1.0.2.0 Schema SCOTT Size 0.38 MB ------------------------------------------------------------------------------- Object Number Invalid Estimated cost Comments Details ------------------------------------------------------------------------------- DATABASE LINK 0 0 0.00 Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw. GLOBAL TEMPORARY TABLE 0 0 0.00 Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior. INDEX 2 0 1.20 2 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es). Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns. 2 b-tree index(es). JOB 0 0 0.00 Job are not exported. You may set external cron job with them. SYNONYM 0 0 0.00 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema. TABLE 4 0 1.00 Total number of rows: 4. Top 10 of tables sorted by number of rows:. dept has 1 rows. bonus has 1 rows. emp has 1 rows. salgrade has 1 rows. Top 10 of largest tables:. ------------------------------------------------------------------------------- Total 6 0 2.20 2.20 cost migration units means approximatively 1 person-day(s). The migration unit was set to 5 minute(s) ------------------------------------------------------------------------------- Migration level : A-1 ------------------------------------------------------------------------------- Migration levels: A - Migration that might be run automatically B - Migration with code rewrite and a human-days cost up to 5 days C - Migration with code rewrite and a human-days cost above 5 days Technical levels: 1 = trivial: no stored functions and no triggers 2 = easy: no stored functions but with triggers, no manual rewriting 3 = simple: stored functions and/or triggers, no manual rewriting 4 = manual: no stored functions but with triggers or views with code rewriting 5 = difficult: stored functions and/or triggers with code rewriting -------------------------------------------------------------------------------
OK，以上报告说我们这一次迁移等级是A-1的等级，也就是可以由ora2pg全自动处理，无需人工干预
等级分类：
A 无需人工干预，全自动
B 需要部分代码重构，也就是说部分SQL语句需要人工调整，大概5天时间可完成的工作量
C 需要部分代码重构，也就是说部分SQL语句需要人工调整，超过5天时间才可以完成的工作量
1，迁移任务非常简单，无触发器，函数
2，迁移任务简单，有函数，无触发器
3，稍有难度的迁移任务，有函数或者触发器，无需重构
4，正常难度的迁移任务，有函数，触发器，视图，可能需要重构
5， 非常困难的迁移任务，大部分函数和触发器需要重构
SO，如果迁移成本报告是A-1，那么就非常简单了，如果是C-5的迁移任务，那么你应该考虑一下是否有足够的技术来做迁移任务。
执行迁移任务：
[root@centos9 ~]# mkdir /opt/ora2pg/ [root@centos9 ~]# ora2pg -c oracle_pg.conf WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 [========================>] 4/4 tables (100.0%) end of scanning. [========================>] 4/4 tables (100.0%) end of table export. Fixing function calls in output files...
查看迁移成果：
[root@centos9 ~]# cat /opt/ora2pg/table.sql -- Generated by Ora2Pg, the Oracle database Schema converter, version 23.1 -- Copyright 2000-2022 Gilles DAROLD. All rights reserved. -- DATASOURCE: dbi:Oracle:host=192.168.123.12;sid=XE;port=53432 SET client_encoding TO 'UTF8'; \set ON_ERROR_STOP ON SET check_function_bodies = false; CREATE TABLE bonus ( ename varchar(10), job varchar(9), sal float, comm float ) ; CREATE TABLE dept ( deptno smallint NOT NULL, dname varchar(14), loc varchar(13) ) ; CREATE TABLE emp ( empno smallint NOT NULL, ename varchar(10), job varchar(9), mgr smallint, hiredate timestamp, sal decimal(7,2), comm decimal(7,2), deptno smallint ) ; CREATE TABLE salgrade ( grade float, losal float, hisal float ) ;
使用迁移成果：
[postgres@centos9 ~]$ psql -Upostgres -f table.sql SET SET CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE
另一种方式：
mkdir -p /opt/tmp/jtqy cd /opt/tmp/jtqy cp /etc/ora2pg/ora2pg.conf.dist ora2pg_oracle.conf sed -i 's/^ORACLE_HOME/# ORACLE_HOME/g' ora2pg_oracle.conf sed -i 's/^ORACLE_DSN/# ORACLE_DSN/g' ora2pg_oracle.conf sed -i 's/^ORACLE_USER/# ORACLE_USER/g' ora2pg_oracle.conf sed -i 's/^ORACLE_PWD/# ORACLE_PWD/g' ora2pg_oracle.conf sed -i 's/^TYPE/# TYPE/g' ora2pg_oracle.conf sed -i 's/^INDEXES_RENAMING/# INDEXES_RENAMING/g' ora2pg_oracle.conf sed -i 's/^NLS_LANG/# NLS_LANG/g' ora2pg_oracle.conf sed -i 's/^SCHEMA/# SCHEMA/g' ora2pg_oracle.conf sed -i 's/^DEFAULT_NUMERIC/# DEFAULT_NUMERIC/g' ora2pg_oracle.conf sed -i 's/^PG_NUMERIC_TYPE/# PG_NUMERIC_TYPE/g' ora2pg_oracle.conf sed -i 's/^PG_INTEGER_TYPE/# PG_INTEGER_TYPE/g' ora2pg_oracle.conf echo "ORACLE_HOME /opt/user/lib/instantclient_19_9" >> ora2pg_oracle.conf echo "ORACLE_DSN dbi:Oracle:host=localhost;sid=ORCL;port=1521" >> ora2pg_oracle.conf echo "ORACLE_USER PROJECT" >> ora2pg_oracle.conf echo "ORACLE_PWD PROJECT" >> ora2pg_oracle.conf echo "INDEXES_RENAMING 1" >> ora2pg_oracle.conf echo "TYPE TABLE,INSERT,VIEW,SEQUENCE" >> ora2pg_oracle.conf echo "NLS_LANG AMERICAN_AMERICA.AL32UTF8" >> ora2pg_oracle.conf echo "SCHEMA PROJECT" >> ora2pg_oracle.conf echo "DEFAULT_NUMERIC numeric" >> ora2pg_oracle.conf echo "PG_NUMERIC_TYPE 1" >> ora2pg_oracle.conf echo "PG_INTEGER_TYPE 1" >> ora2pg_oracle.conf
在文件末尾根据自己的迁移计划进行修改就可以了，末尾内容如下；
ORACLE_HOME /u01/app/oracle/ ORACLE_DSN dbi:Oracle:host=192.168.123.12;sid=xe;port=53432 ORACLE_USER gzmpc ORACLE_PWD gzmpc INDEXES_RENAMING 1 TYPE TABLE,INSERT,VIEW,SEQUENCE NLS_LANG AMERICAN_AMERICA.AL32UTF8 SCHEMA scott DEFAULT_NUMERIC numeric
