Ora2pg

4 min readFeb 3, 2024

Open Source tool for migration activity

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 SQL scripts that you can load into your PostgreSQL database. Ora2pg is a open source tool . A person with zero knowledge can convert and migrate the oracle database to PostgreSQL .

Things you can do using Ora2pg:

Export full database schema (tables, views, sequences, indexes), with unique, primary, foreign key and check constraints.
Export grants/privileges for users and groups.
Export range/list partitions and sub partitions.
Export a table selection (by specifying the table names).
Export Oracle schema to a PostgreSQL 8.4+ schema.
Export predefined functions, triggers, procedures, packages and package bodies.
Export full data or following a WHERE clause.
Full support of Oracle BLOB object as PG BYTEA.
Export Oracle views as PG tables.
Export Oracle user defined types.
Provide some basic automatic conversion of PLSQL code to PLPGSQL.
Works on any platform.
Export Oracle tables as foreign data wrapper tables.
Export materialized view.
Show a detailed report of an Oracle database content.
Migration cost assessment of an Oracle database.
Migration difficulty level assessment of an Oracle database.
Migration cost assessment of PL/SQL code from a file.
Migration cost assessment of Oracle SQL queries stored in a file.
Generate XML ktr files to be used with Penthalo Data Integrator (Kettle)
Export Oracle locator and spatial geometries into PostGis.
Export DBLINK as Oracle FDW.
Export SYNONYMS as views.
Export DIRECTORY as external table or directory for external_file extension.
Full MySQL export just like Oracle database.

Installation and setup using GCP compute Instance

(Note: Can replicate it in any other cloud platforms aslo or can use it locally)

Step 1: Create a linux vm in GCP

gcloud compute instances create ora2pg_test_vm\
— zone=asia-south1-a \
— boot-disk-device-name=ora2pg_test_vm \
— boot-disk-size=10GB \
— boot-disk-type=pd-balanced \
— image-family=debian-10 \
— image-project=debian-cloud

Step 2 : SSH into the linux vm created.

Step 3 : Install Docker in the vm shell

sudo apt-get update -y
sudo apt-get install -y \
apt-transport-https \
ca-certificates \
curl \
gnupg-agent \
software-properties-common
curl -fsSL https://download.docker.com/linux/debian/gpg | sudo apt-key add -
sudo add-apt-repository -y \
“deb [arch=amd64] https://download.docker.com/linux/debian \
$(lsb_release -cs) \
stable”
sudo apt-get update -y
sudo apt-get install -y docker-ce docker-ce-cli
sudo usermod -aG docker ${USER}

Step 4 : Logout of the vm and ssh again for docker reload

Step 5 : Install git in the vm and clone the repo

sudo apt-get install git -y
git clone https://github.com/GoogleCloudPlatform/community.git

Step 6: Download Oracle Instant Client packages:

Go to the Oracle Instant Client download page.

2. Download the RPM packages for your source database version.

For example, download the following files for Oracle 12c:

oracle-instantclient12.2-basiclite-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-odbc-12.2.0.1.0-2.x86_64.rpm

Step 7: Copy the RPM files to

community/tutorials/migrate-oracle-postgres-using-datastream/ora2pg/oracle/ on the linux VM.

Step 8 :Build the Ora2Pg Docker image

docker build . \
-f Ora2PGDockerfile \
-t ora2pg \
— build-arg ORA2PG_VERSION=${ORA2PG_VERSION} \
— build-arg ORACLE_ODBC_VERSION=${ORACLE_ODBC_VERSION}

Perform schema conversion

Step 1:On the bastion VM, edit the community/tutorials/migrate-oracle-postgres-using-datastream/ora2pg/ora2pg/config/ora2pg.conf configuration file to set up database connection details, target PostgreSQL version, and Oracle schema to export.Refer to the Ora2Pg documentation for detailed setting descriptions.

Here is a sample configuration file:

#################### Ora2Pg configuration file #####################

# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
# INPUT SECTION (Oracle connection or input file)
# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

# Set the Oracle home directory
ORACLE_HOME /usr/local/oracle/10g

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN dbi:Oracle:host=<ORACLE_HOST>;sid=<ORACLE_DATABASE>;port=<ORACLE_PORT>
ORACLE_USER <ORACLE_USER>
ORACLE_PWD <ORACLE_PASSWORD>

PG_VERSION <CLOUD_SQL_PG_VERSION>

# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
# SCHEMA SECTION (Oracle schema to export and use of schema in PostgreSQL)
# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
SCHEMA demoapp

# Export Oracle schema to PostgreSQL schema
EXPORT_SCHEMA 1
ROLES 1

# This overrides the owner of the target schema to postgres.
# Removing this parameter instructs Ora2Pg to alter the owner of the schema to a user of the same name.
# You need to manually create the owner user in PostgreSQL before import.
FORCE_OWNER postgres

USE_RESERVED_WORDS 1
FKEY_DEFERRABLE 1

# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
# EXPORT SECTION (Export type and filters)
# — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

TYPE TABLE

OUTPUT_DIR /data
OUTPUT output.sql
FILE_PER_INDEX 0
FILE_PER_CONSTRAINT 0
FILE_PER_FKEYS 1
FILE_PER_TABLE 0

2.Run the ora2pg.sh wrapper script to perform schema export and conversion:

cd ~/community/tutorials/migrate-oracle-postgres-using-datastream/ora2pg

./ora2pg.sh

3. (Optional) Examine the schema conversion result stored in ora2pg/data/output.sql:

community/tutorials/migrate-oracle-postgres-using-datastream/ora2pg/ora2pg/data

you will find TABLES.SQL,VIEWS.SQL,output.sql

Basically the DDL files generated .

Checkout :https://github.com/GoogleCloudPlatform/community/blob/master/archived/migrate-oracle-postgres-using-datastream/index.md