Name: Hevo
Brand: Hevo
Rating: 4.9 (200 reviews)

Prerequisites for connecting Oracle to PostgreSQL

Oracle version 11 or higher, ensuring compatibility with modern migration tools and features

Database user must have SELECT access and SYSDBA privileges, required for enabling and using LogMiner

Redo log replication must be enabled to support continuous, near real-time data capture during migration

Archive log mode should be turned on to allow full recovery options and uninterrupted replication processes

How to use Hevo to Load Data from Oracle to PostegreSQL?

Step 1: Prepare Your Oracle Database

Oracle Version: Ensure Oracle 11 or above is used.

Permissions: The database user needs SELECT permissions, as well as SYSDBA privileges for LogMiner (used in Redo Log mode).

Redo Log Replication: Enable Redo Log replication for near-real-time data capture.

Step 2: Set Up Oracle Database User and Privileges

Create a User: Connect to Oracle using SQL tools and create a new user:

Grant Required Privileges: Allow access to necessary metadata and enable LogMiner:

Enable Archive Logs and Supplemental Logging: Archive logs ensure full data recovery, and supplemental logging tracks column changes. Ensure these are enabled:

ARCHIVELOG: Indicates that the archive log is enabled.

NOARCHIVELOG: Indicates that the archive redo is disabled.

Step 3: Configure Oracle Database for Hevo Data

Verify Archive Log Mode: Confirm that archive logs are enabled using:

SELECT LOG_MODE FROM V$DATABASE;

If NOARCHIVELOG, enable it:

ALTER DATABASE ARCHIVELOG;

Set Up Supplemental Logging: Enable logging for all columns in Oracle:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Step 4: Connect Hevo Data to Your Oracle Database

Create a New Pipeline in Hevo: Navigate to Pipelines > Create Pipeline in Hevo.

Select Oracle as your source.

Enter Connection Details:

Database Host: Enter your Oracle database IP or DNS (e.g., oracle-rds-1.xxxx.rds.amazonaws.com)
Database User/Password: Enter the credentials for the user created
Service Name: Retrieve it from Oracle using:

Get Service Name:

select name from v$database;

Select Ingestion Mode: Redo Log is recommended for real-time, change-data capture. If using Table Mode or Custom SQL, you'll need to specify additional settings.

Step 4: Connect Hevo Data to Your Oracle Database

Step 5: Configure PostgreSQL as the Destination

Create PostgreSQL Destination in Hevo: Navigate to Destinations > Create Destination.

Select PostgreSQL as the destination.

Enter connection details:

Host: Provide the PostgreSQL server’s IP or DNS.
User/Password: Enter PostgreSQL credentials.
Database Name: Specify the PostgreSQL database.

Step 6: Monitor and Test the Connection

Test the Connection: Use Hevo’s Test Connection feature to ensure everything is configured correctly.

Run the Pipeline: Start the pipeline to initiate data replication.

If you want your source to be Amazon RDS Oracle connected to PostgreSQL destination then read our blog.

Why Hevo?

From cost savings to zero downtime, discover what makes Hevo the go-to platform for data-driven companies.

Always-on support, exactly when you need it.

Zero downtime and unmatched platform reliability.

Built for speed, simplicity, and performance.

Manual Section Explanation

Method 1
Method 2

Method 1: How to Set Up Oracle to PostgreSQL Migration using ora2pg

Ora2Pg is a free tool for migrating Oracle or MySQL databases to PostgreSQL. It automatically scans and extracts the structure or data from your Oracle database, generating SQL scripts for loading into PostgreSQL. It's versatile, suitable for reverse engineering, large-scale migrations, or data replication. Its user-friendly interface requires minimal Oracle database knowledge, only necessitating connection parameters.

Let's examine the steps to migrate your data from Oracle to PostgreSQL.

Before the Migration begins, you need to have the following things installed in your system:

Oracle Database
Strawberry Perl
PostgreSQL
Ora2pg

Step 1: Installation

Installing Strawberry Perl

Strawberry Perl is an MS Windows environment that contains everything you need to run and develop Perl applications. It is designed to be as close as possible to the Perl environment on UNIX systems.

You can install it from the link provided below:

Link to install Strawberry Perl. I have downloaded the Latest Release 5.38.2.2 MSI(171.7 MB)
After it is downloaded, Accept the license agreement and click on Next.
Select the Destination Folder Path.

The progress bar shows the progress of your installation.

Note: To check if your Strawberry Pearl is correctly installed, open your text editor and create a new text file. While saving it, give the extension as .pl (My file name is xyz.pl). Then open your command prompt and type in:

perl filename.pl

It shows the content of the text file which you just created. Now you know that Strawberry Perl is running successfully.

How to Install Ora2pg

You can install Ora2pg from the link to the Github Repository.

Ora2Pg is a free tool for migrating an Oracle database to a PostgreSQL-compatible schema. It connects to your Oracle database, scans it automatically, extracts its structure or data, and generates SQL scripts that you can load into your PostgreSQL database.

cd ora2pg-master

Open the command prompt and type the command cd ora2pg-master

Congratulations. We are done with the key installations. Now, we will move on to how to configure them to migrate our data from Oracle to PostgreSQL using Ora2pg successfully.

Step 2: Configurations

Install DBD::Oracle Perl Module

Ora2Pg needs the Perl module DBD::Oracle to connect to an Oracle database from Perl DBI. To get DBD::Oracle, go to CPAN, a Perl module repository.

Install Oracle Instant Client Basic and SQL PLUS Package

Then, you need to set up the Environment Variables as follows:

Go to the admin folder of Instant Client.
Go to Advanced System → Environment Variables → New → New System Variable.
Give the Variable Name 'ORACLE_HOME' and the Variable Value the admin folder path, which in my case is 'C:\add\db_home.'
Similarly, add another System Variable, LD_LIBRARY_PATH, with the name and value shown below.

Verify Environment Variables

Open Command Prompt and type the command:

echo %ORACLE_HOME%

The file directory will change to Variable Admin path, which in my case is C:\add\db_home.

Install DBD::Oracle via CPAN

Now, you can use the CPAN command to install DBD::Oracle. and give the command install "DBD::Oracle."

install "DBD::Oracle"

Create Configuration File

Go to any Text Editor, such as Visual Studio Code, and create an ora2pg.conf text file in the same directory where ora2pg is downloaded.

Note: We will make changes to this file to connect to Oracle and PostgreSQL.

We need to configure the DSN, and then we can connect to Oracle and PostgreSQL, respectively.

I used Oracle Database Express Edition (XE) version 19 for the migration. The Oracle home directory was set to C:\add\db_home, and the LD_LIBRARY_PATH environment variable was set to C:\add\db_home\bin

Step 3: Pre-migration

3.1. Discover:

This involves scanning the present network to identify all Oracle instances' versions and features.

— Version of the Oracle database

SELECT banner
FROM v$version;

— Instance information

SELECT instance_name, host_name, version, status
FROM v$instance;

— Data file information

SELECT file_name, tablespace_name
FROM dba_data_files;

— Tablespaces information

SELECT tablespace_name, status, contents
FROM dba_tablespaces;

3.2. Assess:

Use the command: to get a detailed text report and estimate the migration cost in human days using the estimate_cost option

ora2pg -t SHOW_REPORT --estimate_cost

3.3. Convert:

In this step, we will convert the Oracle code and DDL scripts to PostgreSQL. The ora2pg tool helps export Oracle objects in a PostgreSQL-compatible format. However, some objects require manual changes.

I will create a migration template using the ora2pg command:

ora2pg --project_base /app/migration/ --init_project test_project

The following message will be displayed:

migration template using the ora2pg command:

Step 4: Migration

4.1. Export Oracle Objects:

We will now export Oracle objects as PostgreSQL objects using the script:

cd /app/migration/test_project

./export_schema.sh

4.2. Compile Files:

We will run SQL commands to load the DDL files and import the data into PostgreSQL. The command is:

psql -f /app/migration/test_project/schema/sequences/sequence.sql -h server1-server.postgres.database.azure.com -p 5432 -U username@server1-server -d database -L /app/migration/test_project/schema/sequences/create_sequences.log

psql -f /app/migration/test_project/data/table1.sql -h server1-server.postgres.database.azure.com -p 5432 -U username@server1-server -d database -l /app/migration/test_project/data/table1.log

4.3. Sync Data Selectively:

We will use specific queries to migrate data based on parameters such as date.

select * from table1 where filter_data < '01/06/2024'

Step 5: Post Migration

Run Tests to Verify Application Functionality

We will run tests to verify that applications work correctly with the new setup. The command for doing the same is:

ora2pg -t TEST -c config/ora2pg.conf > migration_diff.txt

Method 2: How to set up Oracle to PostgreSQL Migration using JDBC?

Now, I will show you how to migrate your data from Oracle to PostgreSQL using JDBC manually. I followed the following steps to set up Oracle to PostgreSQL Migration manually:

Step 1: Install PostgreSQL ODBC Driver

The first step was to install the PostgreSQL ODBC driver in the system in which Oracle is installed. As I'm using Linux, I did this by executing the following command:

yum install postgresql93-odbc

The command will differ according to the version of your Linux.

I created a file named odbc.ini and placed it in my user folder. This file contained the details of the target PostgreSQL database.

[ODBC Data Sources]

TARGET_PG_LINK = PostgreSQL

[TARGET_PG_LINK]

Debug = 1

CommLog = 1

ReadOnly = no

Driver = /usr/pgsql-9.3/lib/psqlodbc.so

Servername = <PostgreSQL_SERVER_IP>

FetchBufferSize = 99

Username = pg_user

Password = pg_pass

Port = 5432

Database = pgdb

[Default]

Driver = /usr/lib64/liboplodbcS.so.1

Step 2: Configure Heterogeneous Oracle Services

The next step was to configure the heterogeneous services in Oracle. For this, I created three files with the extension .ora. I began by creating the first while, this initialized the heterogeneous services. Navigated to /hs/admin in my Oracle installation directory and created a file named initdblink.ora with the following content:

HS_FDS_CONNECT_INFO = TARGET_PG_LINK

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME=/usr/pgsql-9.3/lib/psqlodbc.so

set ODBCINI=/PATH_TO_ODBC_FILE/.odbc.ini

The important section is where we need to mention the ODBC information for the destination PostgreSQL installation. I ensured that I specified the same name in HS_FDS_CONNECT_INFO.

Step 3: Modify the File Located in the Directory

The next step was to modify a file located in the Oracle installation's/network/admin directory. I found the file tnsnames.ora and added details about the destination PostgreSQL instance.

TARGET_PG_LINK =

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<ORACLE_HOSTNAME>)(PORT=<ORACLE_PORT>))
(CONNECT_DATA=(SID=TARGET_PG_LINK))
(HS=OK)
)

Step 4: Find the File for feeding more information

I located the listener.ora file in the same directory and added the following details:

SID_LIST_<your_LISTENER_NAME>=

(SID_LIST=

(SID_DESC=

(SID_NAME=TARGET_PG_LINK)

(ORACLE_HOME=<ORACLE_HOME_DIRECTORY>)

(PROGRAM=dg4odbc)

)

)

Oracle listeners are responsible for listening for connections to other databases and handling them appropriately. The name of the configured listener was found out from the listener.ora file. After changing the file, I executed the below command to restart the listener.

lsnrctl reload <your_LISTENER_NAME>

Step 5: Establish a Connection for Oracle to PostgreSQL Migration

The final step was to start PostgreSQL and type the following statement to establish the connection:

Create database link TARGET_PG_LINK connect to "pg_user" identified by "pg_pass" using 'TARGET_PG_LINK';

When everything went well, I executed the following query in PostgreSQL.

select * from "target_table_name"@TARGET_PG_LINK;

If the manual method seems too overwhelming and you are looking for an automated method for transferring Oracle data to PostgreSQL, try Hevo Data.