Back to Blog
PostgreSQL

Installing Ora2Pg and migration cost assessment

Vadim Iatsenko
Vadim Iatsenko
December 6, 2017

image

OVERVIEW

To migrate the data from Oracle to Postgres, we using the utility Ora2Pg. Ora2Pg is a free tool used to migrate an Oracle or MySQL database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates.

Ora2Pg can be used from reverse engineering Oracle database to huge enterprise database migration or simply to replicate some Oracle data into a PostgreSQL database. It is really easy to use and does not require any additional DB.

SPECIFICATIONS

We will install the utility on the same server where Oracle is installed.

The server spec is:

  • CentOS 7
  • Oracle 12.2
  • Ora2Pg 18.2

REQUIREMENTS

To install ora2pg we need following:

  • perl-devel
  • DBD-Oracle-1.74.tar.gz : Oracle database driver for the DBI module
  • DBD-Pg-3.7.0.tar.gz : PostgreSQL database driver for the DBI module (Do not need to install if we do not connect to Postgres)
  • DBI-1.637.tar.gz : Database independent interface for Perl
  • ora2pg-18.2.tar.gz : ora2pg archive

INSTALLATION

perl-devel:

yum install perl-devel

DBD-Oracle-1.74.tar.gz:

wget http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gztar xvzf DBD-Oracle-1.74.tar.gzcd DBD-Oracle-1.74/export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1export LD_LIBRARY_PATH=/u01/app/oracle/product/12.2.0/dbhome_1/libperl Makefile.PLmakemake install

DBD-Pg-3.7.0.tar.gz :

wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-3.7.0.tar.gztar xvzf DBD-Pg-3.7.0.tar.gzcd DBD-Pg-3.7.0perl Makefile.PL/usr/bin/pg_configmakemake install

Ora2Pg 18.2

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

/usr/local/bin/ora2pg —version cd /etc/ora2pg cp ora2pg.conf.dist ora2pg.conf cd /etc/ora2pg/ vi ora2pg.conf ORACLE_DSN dbi:Oracle:host= ;sid=;port=1521 ORACLE_USER ORACLE_PWD TYPE TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION

GENERATE REPORT

ora2pg -t SHOW_REPORT —estimate_cost —cost_unit_value 10 > /home/oracle/report.txt

We set one unit of cost as 10 minutes of work.

An example of the report can be viewed on the link.

The last line shows the total estimated migration code in man-days following the number of migration units estimated for each object. By default, this migration unit represent around five minutes for a PostgreSQL expert. In our example, we increased this cost to 10 minutes, because if the migration is done by Oracle DBA, then it will take them longer.