Ora2Pg简介和安装
内容简介
本文将介绍一款从oracle数据库迁移数据至postgres、greenplum数据库的工具的简介和安装。
Ora2Pg是什么
Ora2Pg是一个免费工具，用于将Oracle或MySQL数据库中数据迁移到PostgreSQL。它连接Oracle数据库，自动扫描并提取其结构或数据，然后生成可以加载到PostgreSQL数据库中的SQL脚本。由于Greenplum与PostgreSQL兼容，所以也可以用来迁移到Greenplum。
Ora2Pg可用于从逆向工程Oracle数据库到大型企业数据库迁移，或者只是将一些Oracle数据复制到PostgreSQL数据库中。它非常易于使用，除了提供连接Oracle数据库所需的参数之外，不需要任何Oracle数据库知识。更多信息可以参看官网的说明。
安装所需的准备
我是在CentOS上安装的Ora2Pg。其他系统如Windows系统的安装，应该安装Strawberry Perl（http://strawberryperl.com/）和操作系统对应的Oracle客户端，本文就不做赘述。
系统上需要安装Oracle Instant Client或完整的Oracle instaaltion。Ora2Pg的官网上说可以从Oracle下载中心下载数据库对应版本或是更高版的RPM：
oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
地址是：
http://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html
由于下载需要先登录，所以先要在oracle上先申请帐号后才能下载。但经过本人的点击注册oracle的帐号后，并不能登录进去以及下载对应软件。
后面是在百度网盘上搜索才找到对应的安装包，若有更好的下载方式，可以留言告诉我。
发现官网有个不用帐号就能下载的网址入口：
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
其他的依赖可以在centos中下载安装，相关要求：
- 1、 perl版本5.10以上
- 2、安装DBD::Oracle
- 3、安装DBI
- 4、安装DBD::Pg 【可选，需要直接导入postgres数据库时安装】
安装步骤
1.安装perf和cpan
yum install -y perf cpan
2.安装oracle-instantclient安装包，安装basic、devel、sqlplus三个rpm包
rpm -ivh oracle-instantclient*.rpm
3.安装oracle-instantclient完毕后，添加oracle-instantclient配置文件
echo "/usr/lib/oracle/12.2/client64/lib" > /etc/ld.so.conf.d/oracle_client.conf
ldconfig
ldconfig -p|grep oracle
libsqlplusic.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libsqlplusic.so
libsqlplus.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libsqlplus.so
liboramysql12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/liboramysql12.so
libons.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libons.so
libocijdbc12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocijdbc12.so
libociei.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libociei.so
libocci.so.12.1 (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocci.so.12.1
libocci.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocci.so
libnnz12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libnnz12.so
libmql1.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libmql1.so
...
注意版本替换成下载的版本
4.测试oracle-instantclient是否安装成功
sqlplus64 test/test@xxx.xxx.xxx.xxx:1521/ora11g
5.安装 DBI
cpan install DBI
6.安装 DBD-Oracle
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
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
perl Makefile.PL -l
make && make test
make install
7.安装Ora2pg
wget https://github.com/darold/ora2pg/archive/v18.2.tar.gz
tar -zxvf v18.2.tar.gz
cd ora2pg-18.2/
perl Makefile.PL
make && make install
8.导出数据测试，编辑导出配置文件
vi ora.conf
#---------------------------------#
#---------------------------------#
# Set the Oracle home directory
ORACLE_HOME /usr/lib/oracle/12.2/client64
# Set Oracle database connection (data source, user, password)
ORACLE_DSN dbi:Oracle:host=xxx.xxx.xxx.xxx;sid=ora11g;port=1521
ORACLE_USER test
ORACLE_PWD test
# Oracle schema/owner to use
#SCHEMA SCHEMA_NAME
SCHEMA test
#--------------------------
# EXPORT SECTION (Export type and filters)
#--------------------------
# Type of export. Values can be the following keyword:
# TABLE Export tables, constraints, indexes, …
# PACKAGE Export packages
# INSERT Export data from table as INSERT statement
# COPY Export data from table as COPY statement
# VIEW Export views
# GRANT Export grants
# SEQUENCE Export sequences
# TRIGGER Export triggers
# FUNCTION Export functions
# PROCEDURE Export procedures
# TABLESPACE Export tablespace (PostgreSQL >= 8 only)
# TYPE Export user-defined Oracle types
# PARTITION Export range or list partition (PostgreSQL >= v8.4)
# FDW Export table as foreign data wrapper tables
# MVIEW Export materialized view as snapshot refresh view
# QUERY Convert Oracle SQL queries from a file.
# KETTLE Generate XML ktr template files to be used by Kettle.
TYPE INSERT
# By default all output is dump to STDOUT if not send directly to PostgreSQL
# database (see above). Give a filename to save export to it. If you want
# a Gzip’d compressed file just add the extension .gz to the filename (you
# need perl module Compress::Zlib from CPAN). Add extension .bz2 to use Bzip2
# compression.
OUTPUT output.sql
# Base directory where all dumped files must be written
#OUTPUT_DIR /var/tmp
OUTPUT_DIR /tmp
9.使用配置文件导出
ora2pg -c ./ora.conf
10.检查导出结果
cat /tmp/output.sql
参考资料
[1] 源码github地址 https://github.com/darold/ora2pg
[2] https://www.cnblogs.com/lottu/p/9114959.html
[3] https://www.jianshu.com/p/08abf07f57be
[4] https://github.com/simapple/postgresdigal/blob/master/201804/20180423_02.md