Ora2pg

Documentation

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

Pre-Requisites

Install Full Oracle Client or Instant Client
Instant Client must include the below 4 zip files

wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-basic-linux.x64-23.7.0.25.01.zip
wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-sqlplus-linux.x64-23.7.0.25.01.zip
wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-sdk-linux.x64-23.7.0.25.01.zip
wget https://download.oracle.com/otn_software/linux/instantclient/2370000/instantclient-jdbc-linux.x64-23.7.0.25.01.zip

Install Perl 5.10+

export ORACLE_HOME=/home/ian/oracle/instantclient_23_7
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH
perl -MCPAN -e 'install DBD::Oracle'

Installation

https://github.com/darold/ora2pg/releases

tar xvf ora2pg-25.0.tar.gz
cd ora2pg-25.0/
perl Makefile.PL
sudo make && make install

Configuration

Example conf file (/etc/ora2pg/ora2pg.conf.dist)

Important Parameters

# Set the Oracle home directory
ORACLE_HOME     /home/ian/oracle/instantclient_23_7

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN      dbi:Oracle:host=localhost;service_name=XEPDB1;port=1523
ORACLE_USER     system
ORACLE_PWD      ipswich

# Export Oracle schema to PostgreSQL schema
EXPORT_SCHEMA   1

# Oracle schema/owner to use
SCHEMA          HR

# Enable/disable the CREATE SCHEMA SQL order at starting of the output file.
# It is enable by default and concern on TABLE export type.
CREATE_SCHEMA   1
COMPILE_SCHEMA  1

# 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.
#       DBLINK          Generate oracle foreign data wrapper server to use as dblink.
#       SYNONYM         Export Oracle's synonyms as views on other schema's objects.
#       DIRECTORY       Export Oracle's directories as external_file extension objects.
#       LOAD            Dispatch a list of queries over multiple PostgreSQl connections.
#       TEST            perform a diff between Oracle and PostgreSQL database.
#       TEST_COUNT      perform only a row count between Oracle and PostgreSQL tables.
#       TEST_VIEW       perform a count on both side of number of rows returned by views
#       TEST_DATA       perform data validation check on rows at both sides.
#       SEQUENCE_VALUES export DDL to set the last values of sequences

TYPE            TABLE

# Define the following directive to send export directly to a PostgreSQL
# database, this will disable file output. Note that these directives are only
# used for data export, other export need to be imported manually through the
# use of psql or any other PostgreSQL client.
PG_DSN          dbi:Pg:dbname=postgres;host=host.docker.internal;port=5433
PG_USER         postgres
PG_PWD          ipswich

# 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      /home/ian/tmp

# Enable PLSQL to PLPSQL conversion. This is a work in progress, feel
# free modify/add you own code and send me patches. The code is under
# function plsql_toplpgsql in Ora2PG/PLSQL.pm. Default enabled.
PLSQL_PGSQL     1

# If you want to use functions defined in the Orafce library and prevent
# Ora2Pg to translate call to these function, enable this directive.
# The Orafce library can be found here: https://github.com/orafce/orafce
# By default Ora2pg rewrite add_month(), add_year(), date_trunc() and
# to_char() functions, but you may prefer to use the orafce version of
# these function that do not need any code transformation.
USE_ORAFCE      0

# By default when using SHOW_REPORT the migration report is generated as
# simple text, enabling this directive will force ora2pg to create a report
# in HTML format.
DUMP_AS_HTML    0

# Set the PostgreSQL major version number of the target database. Ex: 9.6 or 10
# Default is current major version at time of a new release. This replace the
# old PG_SUPPORTS_* configuration directives.
PG_VERSION      17

Test Connectivity

ora2pg -t SHOW_VERSION -c /etc/ora2pg/ora2pg.conf

Other Example Commands

ora2pg -t SHOW_SCHEMA -c /etc/ora2pg/ora2pg.conf
ora2pg -t SHOW_ENCODING -c /etc/ora2pg/ora2pg.conf
ora2pg -n HR -t SHOW_TABLE -c /etc/ora2pg/ora2pg.conf
ora2pg -n HR -t SHOW_COLUMN -c /etc/ora2pg/ora2pg.conf
ora2pg -n HR -t SHOW_REPORT -c /etc/ora2pg/ora2pg.conf (--dump_as_html > migration.html)

Migration Template

mkdir -p /home/ian/oracle/ora2pg/migration
ora2pg --project_base /home/ian/oracle/ora2pg/migration --init_project test_project

Copy ora2pg.conf file to the config directory
Following types will be exported:
SEQUENCE SEQUENCE_VALUES TABLE PACKAGE VIEW GRANT TRIGGER FUNCTION
PROCEDURE TABLESPACE PARTITION TYPE MVIEW DBLINK SYNONYM DIRECTORY

./export_schema.sh

Run highlighted command at end to export the copy the data.
Create password file and samples database.

./import_all.sh -h localhost -p 5433 -d samples -U postgres -o hr