Installing Ora2Pg

An Oracle Client is required on the machine:

./runInstaller -silent -noconfig -responseFile /tmp/client/client_install.rsp

Run your root scripts and configuration assistant scripts in separate window, post install.

Reference the installation document.  Like any other Perl Module Ora2Pg can be installed with the following commands:

        tar xzf ora2pg-18.x.tar.gz
        cd ora2pg-18.x/
        perl Makefile.PL
        make && make install

you may need to run:
       sudo make install 
if permission issues arise

This will install Ora2Pg.pm into your site Perl repository, ora2pg into /usr/local/bin/ and ora2pg.conf.dist into /etc/ora2pg/.

Installing DBD::Oracle

Ora2Pg need perl module DBD::Oracle for connectivity to an Oracle database from perl DBI. To get DBD::Oracle get it from CPAN a perl module repository.

After setting ORACLE_HOME and LD_LIBRARY_PATH environment variables as root user, install DBD::Oracle. Proceed as follow:

        yum install perl-CPAN        
        export LD_LIBRARY_PATH=/data/oracle/client/lib
        export ORACLE_HOME=/data/oracle/client
        perl -MCPAN -e 'install DBD::Oracle'
I was getting an error for missing perl module:
        yum install perl-Time-HiRes

 

Configure ora2pg parameter file

vi /etc/ora2pg/ora2pg.conf <<— Configure some mandatory and optional parameters

#————————————————————————————————–#

#————————————————————————————————–#

# Set the Oracle home directory
ORACLE_HOME /data/oracle/client

# Set Oracle database connection (data source, user, password)
ORACLE_DSN dbi:Oracle:host=xx.xx.xxx.net;sid=SID
ORACLE_USER username
ORACLE_PWD password
# Oracle schema/owner to use
#SCHEMA SCHEMA_NAME
SCHEMA  ORACLE_SCHEMA
#——————————————————————————
# EXPORT SECTION (Export type and filters)
#——————————————————————————

# Type of export. Values can be the following keyword:
# TABLE Export tables, constraints, indexes, …
# PACKAGE Export packages
# INSERT Export data from table as INSERT statement
# COPY Export data from table as COPY statement
# VIEW Export views
# GRANT Export grants
# SEQUENCE Export sequences
# TRIGGER Export triggers
# FUNCTION Export functions
# PROCEDURE Export procedures
# TABLESPACE Export tablespace (PostgreSQL >= 8 only)
# TYPE Export user-defined Oracle types
# PARTITION Export range or list partition (PostgreSQL >= v8.4)
# FDW Export table as foreign data wrapper tables
# MVIEW Export materialized view as snapshot refresh view
# QUERY Convert Oracle SQL queries from a file.
# KETTLE Generate XML ktr template files to be used by Kettle.

TYPE PROCEDURE
# By default all output is dump to STDOUT if not send directly to PostgreSQL
# database (see above). Give a filename to save export to it. If you want
# a Gzip’d compressed file just add the extension .gz to the filename (you
# need perl module Compress::Zlib from CPAN). Add extension .bz2 to use Bzip2
# compression.
OUTPUT output.sql
# Base directory where all dumped files must be written
#OUTPUT_DIR /var/tmp
OUTPUT_DIR /tmp/ora2pg

#————————————————————————————————–#

#————————————————————————————————–#

Limiting object to export

Test your connection:
ora2pg -t SHOW_VERSION -c ora2pg.conf
...Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

ora2pg -c ora2pg.conf -t PROCEDURE

Converts PL/SQL to PL/PGSQL

Go to the directory you set in your parameter file and review your output.  The conversion is not 100% and like everything, it will require testing and fixing.

cd /tmp/ora2pg

vi output.sql

Reference

http://ora2pg.darold.net/documentation.html

Advertisements