Installing Ora2Pg and migration cost assessment

by 6 December 2017

OVERVIEW

To migrate the data from Oracle to Postgres, we using the utility Ora2PgOra2Pg 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.gz
tar xvzf DBD-Oracle-1.74.tar.gz
cd DBD-Oracle-1.74/
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/12.2.0/dbhome_1/lib
perl Makefile.PL
make
make 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.gz
tar xvzf DBD-Pg-3.7.0.tar.gz
cd DBD-Pg-3.7.0
perl Makefile.PL
/usr/bin/pg_config
make
make 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= <your host>;sid=<your DB>;port=1521
ORACLE_USER     <your oracle user>
ORACLE_PWD      <your oracle passwd>
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.

Comments

  • No comment

Leave a Reply

Your email address will not be published. Required fields are marked *