2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Ora2Pg インストールと利用

Posted at
  1. Instant Client Packageのインストール

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html から以下のrpmをダウンロード

 rpm -ivh  oracle-instantclient19.10-basic-19.10.0.0.0-1.x86_64.rpm
 rpm -ihv rpm -ivh oracle-instantclient19.6-devel-19.6.0.0.0-1.x86_64.rpm
 rpm -ihv oracle-instantclient19.10-devel-19.10.0.0.0-1.x86_64.rpm
 rpm -ihv oracle-instantclient19.10-jdbc-19.10.0.0.0-1.x86_64.rpm
 rpm -ihv oracle-instantclient19.10-sqlplus-19.10.0.0.0-1.x86_64.rpm

vi ~/.bash_profile
 export LD_LIBRARY_PATH=/usr/lib/oracle/19.6/client64/lib
 export ORACLE_HOME=/usr/lib/oracle/19.6/client64/lib

source .bash_profile
  1. DBD::Oracleのインストール

yum -y install perl-CPAN
yum -y install libyaml-devel
yum -y install gcc

perl -MCPAN -e shell        #全てEnter
perl -MCPAN -e 'install DBI'
perl -MCPAN -e 'install DBD::Oracle'
  1. ora2pgのインストール

git clone https://github.com/darold/ora2pg.git
cd ./ora2pg
perl Makefile.PL
make && make install
  1. ora2pgの初期セッティング

mkdir migration
ora2pg --project_base ./migration --init_project test_project

4.1 ora2pg.confの設定

vi ./migration/test_project/config/ora2pg.conf
 ORACLE_DSN     dbi:Oracle:host={192.168.84.111};sid={testdb};port=1521
 ORACLE_USER    {username}
 ORACLE_PWD     {userpasswd}
 # Oracle schema/owner to use
 SCHEMA OT      <- Oracleのスキーマ名

sid: SELECT INSTANCE_NAME FROM V$INSTANCE;
--> test for db connect

cd ./migration/test_project
ora2pg -t SHOW_VERSION -c config/ora2pg.conf
... Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

4.2 スキーマ定義の全体export実施

./export_schema.sh
    実行後は「./migration/reports」「./migration/schema」配下を重点的に確認
    reports/report.html : 評価結果
    reports/tables.txt  : テーブル変換結果

./import_all.sh

これでPostgre SQLに入れてくれるが、ForeignKeyなどがあると、入れる順番などでエラになるので、5を参考に個別にやることを推奨します。またデータ投入の際にForeignKeyが邪魔になるので、ForeignKeyを最後に投入するのが良いのではと思います。

  1. コマンド各種

cd migration/test_project

<評価->import postgres>
./export_schema.sh
./import_all.sh

<テーブル定義のみ出力>
 ora2pg -c config/ora2pg.conf -t TABLE [-o output_table.sql]

<データの出力>
 ora2pg -c config/ora2pg.conf -t COPY [-o output_data.sql]

<SQL変換>
 ora2pg -c config/ora2pg.conf -i {input.sql} -o output.sql -t QUERY
2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?