How to use Ora2pg for Oracle to Postgres Migration

We will review Ora2pg, great open source solution for migrations to PostgreSQL. All companies have requests to migrate their small databases to postgres. Ora2pg is a great utility full of features.

Prerequisites

Oracle client
Oracle Database
Postgres Database
Perl modules DBI DBD:Oracle DBD:PG
Latest version of ora2pg

Establish

You can install oracle client for first connection

Or rpm client install

rpm -ivh oracle-instantclient-basic-21.6.0.0.0-1.x86_64.rpm

rpm -ivh oracle-instantclient-devel-21.6.0.0.0-1.x86_64.rpm

rpm -ivh oracle-instantclient-jdbc-21.6.0.0.0-1.x86_64.rpm

rpm -ivh oracle-instantclient-sqlplus-21.6.0.0.0-1.x86_64.rpm

You can install DBI for oracle connection

yum install perl-DBD-Pg perl perl-devel perl-DBI perl-CPAN -y

https://www.cpan.org/modules/by-module/DBI/DBI-1.643.tar.gz

tar -zxvf DBI-1.643.tar.gz

cd DBI-1.643

perl Makefile.PL

make

make install

export ORACLE_HOME=/usr/lib/oracle/21/client64/bin/

export LD_LIBRARY_PATH=/u01/install/lib

export PATH=$PATH:$ORACLE_HOME/bin

perl -MCPAN -e shell

Warning: You do not have write permission for Perl library directories.

Running get for module ‘DBD::Oracle’
Checksum for /root/.cpan/sources/authors/id/Z/ZA/ZARQUON/DBD-Oracle-1.83.tar.gz ok
Scanning cache /root/.cpan/build for sizes
………………………………………………………………….DONE

What approach do you want? (Choose ‘local::lib’, ‘sudo’ or ‘manual’)
manual
get DBD::Oracle

Fetching with LWP:
http://www.cpan.org/modules/03modlist.data.gz
Reading ‘/root/.cpan/sources/modules/03modlist.data.gz’
DONE
Writing /root/.cpan/Metadata
Running get for module ‘DBD::Oracle’
Fetching with LWP:
http://www.cpan.org/authors/id/Z/ZA/ZARQUON/DBD-Oracle-1.83.tar.gz
Fetching with LWP:
http://www.cpan.org/authors/id/Z/ZA/ZARQUON/CHECKSUMS
Checksum for /root/.cpan/sources/authors/id/Z/ZA/ZARQUON/DBD-Oracle-1.83.tar.gz ok
Scanning cache /root/.cpan/build for sizes
DONE

cd /root/.cpan/build/DBD-Oracle-1.83-YenE6h

perl Makefile.PL

make

make install

You can install DBD for postgres connection

wget https://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/DBD-Pg-3.14.2.tar.gz

tar -zxvf DBD-Pg-3.14.2.tar.gz

cd /u01/rpm/DBD-Pg-3.14.2

sudo find / -name “pg_config” -print

perl Makefile.PL

make

make install

Ora2Pg 18.2

git clone https://github.com/darold/ora2pg.git
cd ora2pg/
perl Makefile.PL
make
make install

Set Oracle database connection (host,user,password)

cd /data/ora2pg_your_project/config

vi ora2pg.conf

ORACLE_DSN dbi:Oracle:host=192.168.1.75;sid=orcl;port=1521
ORACLE_USER bugra
ORACLE_PWD 123456

ora2pg -t SHOW_VERSION -c ora2pg.conf

Set Postgres database connection (host,user,password)

Define the following directive to send export directly to a PostgreSQL
database. This will disable file output.
PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
PG_USER test
PG_PWD test

PG_DSN dbi:Pg:dbname=postgres;host=localhost;port=5432
PG_USER postgres
PG_PWD 12345

Oracle to Postgres Migration Schema

Oracle schema/owner to use

vi ora2pg.conf

#Oracle schema/owner to use

SCHEMA bugra
PG_SCHEMA bugra

CREATE DATABASE bugracomak;
\c bugracomak
CREATE SCHEMA bugra;

You can run migration output

ora2pg -d

-------------------------------------------------------------------------------
Ora2Pg v18.2 - Database Migration Report
-------------------------------------------------------------------------------
Version  Oracle Database 21c Enterprise Edition Release 21.0.0.0.0
Schema   bugra
Size     5.25 MB

Create DDL to Scripts

./export_schema.sh

Create DDL to Postgres

./import_all.sh -U postgres -d hr -p 5432 -o postgres

Congrats, have a nice day.

Source: https://ora2pg.darold.net/documentation.html