postgresql|数据库|数据迁移神器ora2pg的安装部署和初步使用

前言：

有的时候有需求需要迁移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的服务器上部署

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.20DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"19.20.0.0\" -DORA_OCI_102 -DORA_OCI_112Checking for functioning wait.phSystem: 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 -lecho $?makeecho $?make installecho $?

命令输出如下：

[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 Ora2PgDone...
------------------------------------------------------------------------------
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 automaticallyB - Migration with code rewrite and a human-days cost up to 5 daysC - Migration with code rewrite and a human-days cost above 5 days
Technical levels:1 = trivial: no stored functions and no triggers2 = easy: no stored functions but with triggers, no manual rewriting3 = simple: stored functions and/or triggers, no manual rewriting4 = manual: no stored functions but with triggers or views with code rewriting5 = 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=53432SET client_encoding TO 'UTF8';\set ON_ERROR_STOP ONSET 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/jtqycp /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.confecho "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