Ora2Pg (Oracle/PostgreSQLマイグレーションツール)

本記事では、このOra2Pgを実行できる環境の構築方法と、実際にOra2Pgを実行し、Oracle12cからPostgreSQL用に変換されたオブジェクト定義など作成した結果をお伝えしたいと思います。

Ora2Pgの概要

Ora2Pgとは、Perlで実装されているオープンソースソフトウェアで、OracleまたはMySQLからPostgreSQLへのマイグレーションツールです。
最新バージョンは2019/01/18にリリースされたVersion20.0となっています(2020年6月時点)。

公式サイトはこちらです。

Ora2Pgでできること

Ora2Pgで具体的にできることは下記の3つです。

1. オブジェクト定義の移行

変換元データベースに接続してオブジェクト情報を参照し、PostgreSQL用のDDLを作成できます。
また、変換元データベースに接続してオブジェクト情報を参照し、変換先のPostgreSQLへ直接(ファイルなどを作成せずに)オブジェクトを作成することもできます。

2. データの移行

変換元データベースに接続して情報を参照し、データ移行用のDMLを作成できます。
また、変換元データベースに接続してオブジェクト情報を参照し、変換先のPostgreSQLへ直接(ファイルなどを作成せずに)データを移行することもできます。

3. SQLの変換

変換元データベースで実行できる形式のSQLを、PostgreSQLで実行できる形式のSQLに変換できます。

Ora2Pg稼働環境の構築

CentOS7にOra2Pgをインストールし、Oracle12cからPostgreSQL12へオブジェクト定義とデータの移行をおこなってみます。

環境の用意

Vagrantを利用してOracle Virtual Box上に3つの仮想環境を用意しました。

  1. Oracle12c 稼働環境(マイグレーションを想定して少し古いバージョンのOracleを用意しています)
  2. PostgreSQL12 稼働環境
  3. Ora2Pg 稼働環境

今回はOra2Pgの紹介ですので、Oracle12c稼働環境とPostgreSQL12稼働環境の構築については、対象外とさせていただいています。
また、Oracle12cにはオラクル社が用意しているサンプルスキーマHuman Resource (HR)が格納されています。

Ora2Pgをソースコードからインストール

Ora2Pgは公式サイトからソースコードをダウンロードし、インストールを行います。
公式サイト上に詳しいインストール方法が記載されていますので、まずはそのドキュメントに沿ってOra2Pgが動作する環境を構築します。

OracleInstantClientのインストール

最初に、Ora2Pg本体をインストールするために必要となるライブラリ群をインストールします。
今回はOracle12cを変換元のデータベースとしているため、OracleInstantClientをインストールして、Oracle12cデータベースへ接続できるようにします。
OracleInstantClientはOracle Technology Network(OTN)サイトからダウンロードします。
※ダウンロードにはOTNサイトへのユーザ登録が必要です。

変換元のOracle12cとバージョンを合わせるため、OracleInstantClientも12cの各種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-jdbc-12.2.0.1.0-1.x86_64.rpm
  • oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

次に、OracleInstantClientのインストールに必要なライブラリをインストールします。

# yum install libaio

その後、OracleInstantClientをインストールします。

# rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

最後にインストールしたOracleInstantClientのライブラリを共有ライブラリの検索パスに含めます。

# echo '/usr/lib/oracle/12.2/client64/lib' > /etc/ld.so.conf.d/oracle.conf
# ldconfig

これでOra2Pg実行端末からOracleデータベースへ接続できるようになりました。

Perlのインストール

次はPerl関連のインストールです。Ora2PgをインストールするにはPerl(5.10以上)が必要となります。

# perl -v

This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi
(with 40 registered patches, see perl -V for more detail)

Copyright 1987-2012, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

CentOs7にデフォルトでインストール済みのものが5.16であったため、そのまま使います。
※ 5.10未満の場合はPerlのアップデートが必要です。

Perlモジュールのインストール

Ora2PgからOracleデータベースへ接続するためにDBD::Oracleをインストールすることから始めます。
DBD::OracleはCPANからインストールするので、先にCPANをインストールします。

# yum install perl-CPAN

次に、DBD::Oracleのインストールに必要な環境変数を設定します。

# export PERL5LIB=/usr/local/bin
# export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
# export ORACLE_HOME=/usr/lib/oracle/12.2/client64/lib

その後、必要なモジュールをCPANからインストールします。

# perl -MCPAN -e 'install Test::NoWarnings'
# perl -MCPAN -e 'install DBI'
# perl -MCPAN -e 'install DBD::Oracle'

※ CPANの初回実行時に「設定についてどうするか?」を聞かれますが、すべてデフォルトで進め、自動的に設定してもらいました。

最後に、PerlでMakefileを作成するのに必要なライブラリをインストールします。

# yum install perl-ExtUtils-MakeMaker

Ora2Pgのソースコードのダウンロード

まずは作業ディレクトリを作成し、Ora2Pg本体をダウンロードします。
今回はora2pgというユーザーを作成して、作業ユーザーとしています。

# useradd ora2pg
# su - ora2pg
$ mkdir workdir
$ cd workdir

Ora2Pgのgithubから最新版のVer20.0をダウンロードしました。
作業ディレクトリにダウンロードしたファイルを格納し、解凍します。

$ tar xzf ora2pg-20.0.tar.gz

Ora2Pgのインストール

root権限で解凍したディレクトリに移動して、インストールを実行します。

# cd /home/ora2pg/workdir/ora2pg-20.0/
# perl Makefile.PL
# make && make install

/usr/local/bin ディレクトリにOra2Pgが無事にインストールされました。

$ ls /usr/local/bin/ora2pg
/usr/local/bin/ora2pg

Ora2Pgの実行

Ora2Pgのインストールが無事に完了しましたので、さっそく実行してみたいと思います。

ora2pg.confの編集

Ora2Pgはora2pg.confという設定ファイルを読み込んで実行されます。
設定ファイルのひな型は/etc/ora2pg/ora2pg.conf.distにありますので、作業フォルダにora2pg.confとしてコピーして編集します。

$ cp /etc/ora2pg/ora2pg.conf.dist /home/ora2pg/workdir/ora2pg.conf

Oracleへの接続設定

ora2pg.conf内の下記3項目にOracle接続のための情報を設定します(viなどでora2pg.confを編集します)。

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN      dbi:Oracle:host=oracle_host;sid=orcl;port=1521
ORACLE_USER     system
ORACLE_PWD      system_pass

※ ORACLE_USERにはDBA_OBJECTSを参照できる権限をもつユーザーを指定しないと、Ora2Pg実行時にエラーとなります。

データ定義移行用のDDLの作成

データ定義移行用のDDLを出力してみます。
ora2pgコマンドに-cオプションをつけることでora2pg.confの場所を指定します。-cをつけない場合は/etc/ora2pg/ora2pg.confが自動的に参照されます。
その他のオプションについては後述します。

ora2pg -c ora2pg.conf -t TABLE -b ~/workdir/ -o ora2pg_TABLE.sql

出力結果の確認

出力ファイルの中身は下記のようなSQL文となっていました。

-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0
-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=ora;sid=orcl;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE countries (
        country_id char(2) NOT NULL,
        country_name varchar(40),
        region_id bigint
) ;
COMMENT ON TABLE countries IS E'country table. Contains 25 rows. References with locations table.';
COMMENT ON COLUMN countries.country_id IS E'Primary key of countries table.';
COMMENT ON COLUMN countries.country_name IS E'Country name';
COMMENT ON COLUMN countries.region_id IS E'Region ID for the country. Foreign key to region_id column in the departments table.';
ALTER TABLE countries ADD PRIMARY KEY (country_id);
.
.
.

プライマリキーの設定や、コメントなども作成されるようになっています(ora2pg.confで制御することもできます)。

プロシージャの変換

ストアドプロシージャも変換してみます。OracleのPL/SQL言語とPostgreSQLのPL/PgSQL言語の相違点についてはマニュアルにもまとめられていますので、参考にしてください。

ora2pg -c ora2pg.conf -t PROCEDURE-b ~/workdir/ -o ora2pg_PROCEDURE.sql

出力結果を確認

出力ファイルの中身は下記のようなSQL文となっていました。

-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0
-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=ora;sid=orcl;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON



CREATE OR REPLACE FUNCTION add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type ) RETURNS VOID AS $body$
BEGIN
  INSERT INTO job_history(employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES (p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON FUNCTION add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type ) FROM PUBLIC;
.
.
.

PostgreSQL11からPostgreSQLでもプロシージャを作成することができるようになっていますが、FUNCTIONとして作成されるようです。

データ移行用のDMLの作成

次にデータ移行用のDMLを出力してみます。

ora2pg -c ora2pg.conf -t INSERT -b ~/workdir/ -o ora2pg_INSERT.sql

出力されたファイルの確認

出力ファイルの中身は下記のようなINSERT文となっていました。

BEGIN;
INSERT INTO countries (country_id,country_name,region_id) VALUES (E'AR',E'Argentina',2);
INSERT INTO countries (country_id,country_name,region_id) VALUES (E'AU',E'Australia',3);
INSERT INTO countries (country_id,country_name,region_id) VALUES (E'BE',E'Belgium',1);
INSERT INTO countries (country_id,country_name,region_id) VALUES (E'BR',E'Brazil',2);
INSERT INTO countries (country_id,country_name,region_id) VALUES (E'CA',E'Canada',2);
INSERT INTO countries (country_id,country_name,region_id) VALUES (E'CH',E'Switzerland',1);
INSERT INTO countries (country_id,country_name,region_id) VALUES (E'CN',E'China',3);
.
.
.

変換レポートの出力

-t オプションにSHOW_REPORTを設定すると、変換レポートを出力できます。

ora2pg -c ora2pg.conf -t SHOW_REPORT

出力結果

[========================>] 8/8 tables (100.0%) end of scanning.
[========================>] 10/10 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v20.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 12c Standard Edition Release 12.2.0.1.0
Schema  HR
Size    1.62 MB

-------------------------------------------------------------------------------
Object  Number  Invalid Comments        Details
-------------------------------------------------------------------------------
DATABASE LINK   0       0       Database links will be exported as SQL/MED PostgreSQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
GLOBAL TEMPORARY TABLE  0       0       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   19      0       11 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) and hash index(es) will be exported as b-tree index(es) if any. 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.   11 b-tree index(es).
JOB     0       0       Job are not exported. You may set external cron job with them.
PROCEDURE       2       0       Total size of procedure code: 772 bytes.
SEQUENCE        3       0       Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').
SYNONYM 0       0       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   8       0        2 check constraint(s). Total number of rows: 216. Top 10 of tables sorted by number of rows:. employees has 107 rows. departments has 27 rows. countries has 25 rows. locations has 23 rows. jobs has 19 rows. job_history has 10 rows. regions has 4 rows. test has 1 rows. Top 10 of largest tables:.
TRIGGER 2       0       Total size of trigger code: 123 bytes.
VIEW    2       0       Views are fully supported but can use specific functions.
-------------------------------------------------------------------------------
Total   36      0
-------------------------------------------------------------------------------

画面では見づらいので–dump_as_htmlをつけることでHTMLとして出力することもできます(その他、SHOW_REPORTでは変換に必要な推定コストを表示する–estimate_costオプションも指定できるようです)。

ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ~/workdir/SHOW_REPORT.html

今回はサンプルスキーマHRが変換対象ということもあり、変換対象36オブジェクト中、無効なオブジェクトは0個という結果となりました。

設定値の説明

ora2pg.conf内の主な設定値は下記の通りです。

設定値 意味 備考
TYPE 変換するオブジェクトの種類 実行時に -t オプションとして指定できます。
OUTPUT_DIR 出力先のディレクトリ 実行時に -b オプションとして指定できます。
OUTPUT 出力するファイル名 実行時に -o オプションとして指定できます。
ORACLE_DSN Oracleデータベースの接続情報
ORACLE_USER Oracleデータベースの接続ユーザ
ORACLE_PWD Oracleデータベースの接続ユーザのパスワード
PG_DSN PostgreSQLの接続情報
PG_USER PostgreSQLの接続ユーザ
PG_PWD PostgreSQLの接続ユーザのパスワード
PG_SCHEMA PostgreSQLの接続スキーマ インポート先のスキーマです。
SCHEMA 変換元のスキーマ 指定しないとシステムスキーマ以外の全スキーマが対象となります。
FILE_PER_FKEYS 外部キーを別ファイルに出力するかどうか

他にも

  • テーブルごとに出力されるSQLファイルを分割する「FILE_PER_TABLE」
  • 数値データ型をnumericに変換するかどうかを決定する「PG_NUMERIC_TYPE」
  • データ移行の並列度を決定する「JOBS」

など多数の設定値が用意されており、細かく挙動を制御できるようになっています。

Ora2PgをPostgreSQL公式サイトのリポジトリからインストール

Ora2Pgの公式サイトには記載されていないのですが、実はPostgreSQL公式サイトに用意されているPostgreSQLリポジトリ内にOra2Pgが含まれています。
ご参考までに、このPostgreSQLリポジトリからOra2Pgをインストールする方法もご紹介します。

PostgreSQLリポジトリの追加

最初にPostgreSQLリポジトリを追加します。

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

これでインストール可能なパッケージリスト内にora2pgが存在するようになりました。

# yum list ora2pg
Available Packages
ora2pg.noarch                                        20.0-1.rhel7                                         pgdg-common

OracleInstantClientのインストール

早速、Ora2Pgのインストールを行いたいところですが、やはり事前にインストールしておくものがあります。
Oracle Technology Network(OTN)サイトからOracleInstantClient(※)をダウンロードしてインストールします。
※バージョン19cをダウンロードしてください。19c以前のバージョンでは、Ora2Pgをインストールする際にエラーとなります。

# rpm -ivh oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient19.6-devel-19.6.0.0.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient19.6-jdbc-19.6.0.0.0-1.x86_64.rpm

Perlモジュールのインストール

次に、epelリポジトリに含まれているString::Randomモジュールをインストールします。

# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# yum install perl-String-Random

今度は、non-freeのPostgreSQLリポジトリからDBD::Oracleモジュールをインストールします。

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/non-free/EL-7-x86_64/pgdg-redhat-nonfree-repo-latest.noarch.rpm
# yum install perl-DBD-Oracle

Ora2Pgのインストール

これでOra2Pgをインストールする準備ができましたので、続けてインストールを行います。

# yum install ora2pg

/bin ディレクトリにOra2Pgが無事にインストールされました。

# ora2pg -v
Ora2Pg v20.0

# which ora2pg
/bin/ora2pg

Ora2Pgの注意点

最後にOra2Pgを利用する上での注意するべき点を挙げさせていただきます。

繰り返しとなりますが今回はシンプルなサンプルスキーマHRが変換対象としましたので、検証した中ではとくに手作業での修正などは必要としませんでした。しかし、実際に運用されているOracleデータベースを変換する際には、OracleとPostgreSQLの違いにより、変換不能なオブジェクトや手作業での修正が必要となることが予想されます。

PostgreSQLはOracleへの互換性が高いといわれておりますが、それでも多くの相違点があります。
たとえば

  • オブジェクト名をダブルコーテーションでくくらない場合、Oracleは大文字に変換されるが、PostgreSQLでは小文字に変換される。
  • 文字列型を定義する際のvarchar(n)などのnは、Oracle(のデフォルト設定)ではバイト数、PostgreSQLでは文字数となる。

などを解消できる設定値などはora2pg.conf内に確認することはできませんでした。
Ora2Pgを利用することで移行作業の工数を確かに削減できますが、Ora2Pgでは完全にOracleからPostgreSQLへ移行できるとはいえず、何かしらの修正作業が発生いたします。
加えて、PostgreSQLへ変換した際には変換前と同じ機能が提供されているかどうかの検証作業も必要となることにご注意ください。

以上、Ora2Pgを実行できる環境の構築方法と、簡単ではありましたがOra2Pgを実行した結果を紹介させていただきました。