ora2pg

https://www.sraoss.co.jp/tech-blog/pgsql/ora2pg/
https://qiita.com/mkyz08/items/89771d11b73d2a2d58f3
https://ora2pg.darold.net/documentation.html#Oracle-database-connection

移行元サーバ: CentOS 7 (Oracle 19c)
ora2pgサーバ: CentOS 7 (ora2pg 20.0)
移行先サーバ: Rocky Linux 8 (PostgreSQL15) 

-- 1. ora2pgをソースコードからインストール

-- 1.1 OracleInstantClientのインストール

yum install -y libaio

rpm -ivh oracle-instantclient19.20-basic-19.20.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.20-devel-19.20.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.20-jdbc-19.20.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.20-sqlplus-19.20.0.0.0-1.x86_64.rpm

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

-- 1.2 Perlのインストール

perl -v

yum install -y perl-CPAN

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

yum install -y gcc

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

yum install -y perl-ExtUtils-MakeMaker

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

cd
mkdir workdir
cd workdir

tar xzf ora2pg-20.0.tar.gz

-- 1.4 Ora2Pgのインストール

cd ora2pg-20.0
perl Makefile.PL
make && make install

ls /usr/local/bin/ora2pg

-- 2. Ora2Pgの実行

-- 2.1 ora2pg.confの編集
cp /etc/ora2pg/ora2pg.conf.dist /root/workdir/ora2pg.conf

cd /root/workdir

vim ora2pg.conf

ORACLE_DSN      dbi:Oracle:host=192.168.137.65;service_name=pdb1.example.com;port=1521
ORACLE_USER     test
ORACLE_PWD      test

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

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

cat ora2pg_TABLE.sql

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

cat ora2pg_PROCEDURE.sql

-- 2.3 データ移行用のDMLの作成

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

cat ora2pg_INSERT.sql | head -n 100

-- 2.4 変換レポートの出力

ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > SHOW_REPORT.html

firefox SHOW_REPORT.html

-- 3. PostgreSQLへのインポート

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

yum search postgresql14
yum install -y postgresql14

PGPASSWORD=postgres psql -h 192.168.137.55 -p 5432 -U postgres -d test 

PGPASSWORD=postgres psql -h 192.168.137.55 -p 5432 -U postgres -d test -f ora2pg_TABLE.sql
PGPASSWORD=postgres psql -h 192.168.137.55 -p 5432 -U postgres -d test -f ora2pg_PROCEDURE.sql
PGPASSWORD=postgres psql -h 192.168.137.55 -p 5432 -U postgres -d test -f ora2pg_INSERT.sql