ora2pg安装和使用

官方文档

http://ora2pg.darold.net/

http://ora2pg.darold.net/documentation.html

https://github.com/darold/ora2pg

特性

MySQL和Oracle数据迁移到PostgreSQL使用

        - Export full database schema (tables, views, sequences, indexes), with
          unique, primary, foreign key and check constraints.
        - Export grants/privileges for users and groups.
        - Export range/list partitions and sub partitions.
        - Export a table selection (by specifying the table names).
        - Export Oracle schema to a PostgreSQL 8.4+ schema.
        - Export predefined functions, triggers, procedures, packages and
          package bodies.
        - Export full data or following a WHERE clause.
        - Full support of Oracle BLOB object as PG BYTEA.
        - Export Oracle views as PG tables.
        - Export Oracle user defined types.
        - Provide some basic automatic conversion of PLSQL code to PLPGSQL.
        - Works on any platform.
        - Export Oracle tables as foreign data wrapper tables.
        - Export materialized view.
        - Show a  report of an Oracle database content.
        - Migration cost assessment of an Oracle database.
        - Migration difficulty level assessment of an Oracle database.
        - Migration cost assessment of PL/SQL code from a file.
        - Migration cost assessment of Oracle SQL queries stored in a file.
        - Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
        - Export Oracle locator and spatial geometries into PostGis.
        - Export DBLINK as Oracle FDW.
        - Export SYNONYMS as views.
        - Export DIRECTORY as external table or directory for external_file extension.
        - Full MySQL export just like Oracle database.
        - Dispatch a list of SQL orders over multiple PostgreSQL connections
        - Perform a diff between Oracle and PostgreSQL database for test purpose.

安装（ubuntu18.04/Redhat7.5）

链接：https://pan.baidu.com/s/1LlRilUM8ygrHFjAI4DjMww
提取码：1qaz

安装说明

1. perl要求必须在5.8以上
2. 依赖安装
   • oracle-instantclient
   • DBI
   • DBD::Oracle
   • DBD::mysql
   • DBD::Pg
   • Compress::Zlib
3. 安装ora2pg

说明：perl包安装有多种方式（cpan，apt，源码编译，下面安装三种方式都有用到。

依赖安装

前提

gcc、make等工具都已经安装。此外下面的包编译安装时，部分包会提示不存在对应的lib库，只要安装对应的包的devel包即可。

安装perl

系统默认已经安装了perl，版本号为5.26

安装DBI

cpan
install DBI

安装oracle-instantclient

#下载 InstantClient（Basic、SDK和Sqlplus的zip包）
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

#解压后放到 /opt/user/lib 目录下
unzip instantclient-basic-linux.x64-19.9.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-19.9.0.0.0dbru.zip
unzip instantclient-sqlplus-linux.x64-19.9.0.0.0dbru.zip
mkdir /opt/user/lib -p
mv instantclient_19_9 /opt/user/lib
#修改权限并配置环境变量
cd /opt/user/lib
chmod 755 instantclient_19_9/

vi /etc/profile
export PATH=/opt/user/lib/instantclient_19_9/:$PATH
export LD_LIBRARY_PATH=/opt/user/lib/instantclient_19_9/:$LD_LIBRARY_PATH

source /etc/profile

安装DBD::Oracle

#下载软件包
https://metacpan.org/pod/DBD::Oracle

#安装
tar -xzvf DBD-Oracle-1.80.tar.gz
cd DBD-Oracle-1.80
#编译
export PATH=/opt/user/lib/instantclient_19_9/:$PATH
export LD_LIBRARY_PATH=/opt/user/lib/instantclient_19_9/:$LD_LIBRARY_PATH
perl Makefile.PL -l
make
make install

##另一种方法
perl -MCPAN -e shell
install DBD::Oracle

安装DBD::Pg

#类似DBD:Oracle
#前提：已经安装了PostgreSQL
#下载软件包 https://metacpan.org/
#编译安装
tar -xzvf DBD-Pg-3.14.2.tar.gz
cd DBD-Pg-3.14.2
perl Makefile.PL -l
make
make install

##另一种方法
perl -MCPAN -e shell
install DBD::Pg

安装DBD::Mysql

#https://metacpan.org/pod/distribution/DBD-mysql/lib/DBD/mysql/INSTALL.pod
sudo apt-get install libdbd-mysql-perl

#centos
yum install perl-DBD-MySQL

安装Compress::Zlib（可选）

perl -MCPAN -e shell
install Compress::Zlib

安装Ora2pg

#下载软件包
https://github.com/darold/ora2pg/releases

#编译安装
tar -xzvf ora2pg-21.0.tar.gz
cd ora2pg-21.0
perl Makefile.PL -l
make
make install

#简单检查
ora2pg --help

Oracle迁移

导出SQL文件

说明

• ora2pg已经在pro环境安装
• oracle数据库已经在pro环境配置（只安装ORACLE客户端也可以）
• 在root用户下操作
• 如果是用navicat导入的，先删除表 project_struct
• 库中没有存储过程、触发器等，所以配置文件中也没有设置要导出这些内容

执行

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