An Introduction to migrating from Oracle to PostgreSQL using ora2pg

Introduction:

Here’s my first attempt at blogging about my favourite database PostgreSQL: The World’s Most Advanced Open Source Relational Database. With it’s robust features, open-source nature, and growing ecosystem, migrating to PostgreSQL opens up exciting new possibilities for your database environment. Migrating a database from one system to another can be complex, especially when dealing with large and complex databases. However, with the right tools and techniques, this process can be streamlined significantly. In this blog post, we will explore the migration of an Oracle database to a PostgreSQL database using the ora2pg tool, with a specific focus on running Oracle within a Docker container.

Assumptions:

a) Oracle Database: Running in a Docker container-Oracle 21c.
b) PostgreSQL: Running on a Docker container with version 16

Preparedness of the system:

• Make sure your docker containers for Oracle and Postgres are up and running
• As ora2pg is written in Perl language, we need to install Perl-core on the Oracle container using the below command.

bash-4.2# yum install perl-core

Download ora2pg-24.3V

Here are the links to download ora2pg.

>> Click On this link https://ora2pg.darold.net/documentation.html

>> Click on Download =>Source code(tar.gz)

Copy the ora2pg to the Oracle database and unzip the tar file.
NB: Following are executed on a power shell prompt

C:\Users\lokes>cd .\Downloads\
PS C:\Users\lokes\Downloads>docker cp ora2pg-24.3.tar.gz oracle-db:/home/downloads
Successfully copied 579kB to oracle-db:/home/downloads

Let’s connect docker bash(bash-4.2) using the below command.

C:\Users\lokes>docker exec -u 0 -it oracle-db bash
bash-4.2# cd downloads/
bash-4.2# ls
ora2pg-24.3.tar.gz
bash-4.2#  unzip ora2pg-24.3.tar.gz
bash-4.2# ls -lrt
total 568
drwxrwxr-x 6 root root   4096 Mar 28  2024 ora2pg-24.3
-rwxr-xr-x 1 root root 576965 Nov  7 09:59 ora2pg-24.3.tar.gz

Installation of ora2pg

Check the Makefile.PL file in the ora2pg directory and run the following

bash-4.2# cd ora2pg-24.3
bash-4.2# ls -lrt
total 668
drwxrwxr-x 2 root root   4096 Mar 28  2024 scripts
drwxrwxr-x 5 root root   4096 Mar 28  2024 packaging
drwxrwxr-x 3 root root   4096 Mar 28  2024 lib
drwxrwxr-x 2 root root   4096 Mar 28  2024 doc
-rw-rw-r-- 1 root root 374342 Mar 28  2024 changelog
-rw-rw-r-- 1 root root 171271 Mar 28  2024 README
-rw-rw-r-- 1 root root  75191 Mar 28  2024 Makefile.PL
-rw-rw-r-- 1 root root    180 Mar 28  2024 MANIFEST
-rw-rw-r-- 1 root root  32472 Mar 28  2024 LICENSE
-rw-rw-r-- 1 root root     21 Mar 28  2024 INSTALL

bash-4.2# perl Makefile.PL
bash-4.2# ls -lrt
total 784
drwxrwxr-x 5 root root   4096 Mar 28  2024 packaging
drwxrwxr-x 3 root root   4096 Mar 28  2024 lib
drwxrwxr-x 2 root root   4096 Mar 28  2024 doc
-rw-rw-r-- 1 root root 374342 Mar 28  2024 changelog
-rw-rw-r-- 1 root root 171271 Mar 28  2024 README
-rw-rw-r-- 1 root root  75191 Mar 28  2024 Makefile.PL
-rw-rw-r-- 1 root root    180 Mar 28  2024 MANIFEST
-rw-rw-r-- 1 root root  32472 Mar 28  2024 LICENSE
-rw-rw-r-- 1 root root     21 Mar 28  2024 INSTALL
-rw-r--r-- 1 root root  70666 Nov  7 10:10 ora2pg.conf.dist
drwxrwxr-x 2 root root   4096 Nov  7 10:10 scripts
-rw-r--r-- 1 root root  28787 Nov  7 10:10 Makefile
-rw-r--r-- 1 root root    545 Nov  7 10:10 MYMETA.yml
-rw-r--r-- 1 root root    956 Nov  7 10:10 MYMETA.json
drwxr-xr-x 8 root root   4096 Nov  7 10:10 blib
-rw-r--r-- 1 root root      0 Nov  7 10:10 pm_to_blib

Create the ora2pg.conf file using ora2pg.conf.dist as reference.

bash-4.2# cp ora2pg.conf.dist ora2pg.conf
bash-4.2# ls -lrt
total 856
drwxrwxr-x 5 root root   4096 Mar 28  2024 packaging
drwxrwxr-x 3 root root   4096 Mar 28  2024 lib
drwxrwxr-x 2 root root   4096 Mar 28  2024 doc
-rw-rw-r-- 1 root root 374342 Mar 28  2024 changelog
-rw-rw-r-- 1 root root 171271 Mar 28  2024 README
-rw-rw-r-- 1 root root  75191 Mar 28  2024 Makefile.PL
-rw-rw-r-- 1 root root    180 Mar 28  2024 MANIFEST
-rw-rw-r-- 1 root root  32472 Mar 28  2024 LICENSE
-rw-rw-r-- 1 root root     21 Mar 28  2024 INSTALL
-rw-r--r-- 1 root root  70666 Nov  7 10:10 ora2pg.conf.dist
drwxrwxr-x 2 root root   4096 Nov  7 10:10 scripts
-rw-r--r-- 1 root root  28787 Nov  7 10:10 Makefile
-rw-r--r-- 1 root root    545 Nov  7 10:10 MYMETA.yml
-rw-r--r-- 1 root root    956 Nov  7 10:10 MYMETA.json
drwxr-xr-x 8 root root   4096 Nov  7 10:10 blib
-rw-r--r-- 1 root root      0 Nov  7 10:10 pm_to_blib

Configuration setting in ora2pg.conf file

ORACLE_HOME     /opt/oracle/product/21c/dbhome_1
ORACLE_DSN      dbi:Oracle:host=172.17.0.2;sid=MYCDB;port=1521
ORACLE_USER     sys
ORACLE_PWD      oracle123
PG_VERSION     	16
USER_GRANTS     1
EXPORT_SCHEMA   1
TRANSACTION   	READONLY
SCHEMA   	ABC
TYPE         	TABLE 

Set the environment variables if they are not as per below.

bash-4.2# echo $LD_LIBRARY_PATH
/opt/oracle/product/21c/dbhome_1/lib:/usr/lib
bash-4.2# echo $ORACLE_HOME
/opt/oracle/product/21c/dbhome_1

Install the DBD::Oracle library for the Oracle database

bash-4.2# yum install gcc
bash-4.2# yum install libaio-devel
bash-4.2# perl -MCPAN -e 'install DBD::Oracle'

Check the version of ora2pg

bash-4.2# ora2pg -t SHOW_VERSION -c ora2pg.conf
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0

Now, Ora2Pg connection is successful to the Oracle server.

Export the schemas from the Oracle server using the below command

ora2pg  -c /home/downloads/ora2pg-24.3/ora2pg.conf -d -o TABLE_persons.sql

Use the following switches for debugging the command:

 -c | --conf file: Set an alternate configuration file other than the
                        default /etc/ora2pg/ora2pg.conf.
 -d | --debug: Enable verbose output.
 -o | --out file: Set the path to the output file where SQL will
                        be written. Default: output.sql in running directory.

Importing to PostgreSQL server.

Open the Windows command line and copy the file to the local
NB: Following are executed on a power shell prompt

C:\Users\lokes\Downloads> docker cp oracle-db:/home/downloads/ora2pg-24.3/output/TABLE_persons.sql .
Successfully copied 2.56kB to C:\Users\lokes\Downloads\.

C:\Users\lokes\Downloads> dir TABLE*.sql
    Directory: C:\Users\lokes\Downloads
Mode          LastWriteTime         Length Name
----          -------------         ------ ----
-a----        13-11-2024 11:58      494    TABLE_persons.sql

PS C:\Users\lokes\Downloads> docker cp .\TABLE_persons.sql postgres:/home/data
Successfully copied 2.56kB to postgres:/home/data

Login to the Postgres server using the below command and create 

PS C:\Users\lokes> docker exec -it postgres psql -U postgres -d postgres -h 127.0.0.1 -p 5432
postgres=# Create role abc login createdb nosuperuser password 'postgres';

• LOGIN: Grants the ability to log into the database server.
• CREATE DB: Grants the ability to create new databases.
• PASSWORD: Sets the password for the role to ‘postgres’.

Open docker in the Postgres terminal and import the schema using 

# cd data
# ls -lrt
total 4
-rwxr-xr-x 1 root root 506 Nov 11 06:35 TABLE_persons.sql
# psql --version
psql (PostgreSQL) 16.4 (Debian 16.4-1.pgdg120+2)

# psql -f TABLE_persons.sql -U postgres -d postgres
SET
CREATE SCHEMA
ALTER SCHEMA
SET
CREATE TABLE
Verify the table
postgres=# select * from abc.persons;
 personid | lastname | firstname | address | city
----------+----------+-----------+---------+------
(0 rows)

Suggestion: Since Ora2Pg is written in Perl, using it for data migration is not recommended. Instead, use Ora2Pg for schema migration, and migrating data, consider leveraging ETL tools like Pentaho to design a more efficient and scalable data migration strategy.

Conclusion:

Migrating an Oracle database to PostgreSQL using the ora2pg tool can significantly streamline the transition process. This approach allows for a structured and automated method of transferring schemas from Oracle to PostgreSQL, facilitating compatibility between these two powerful database platforms.
In this specific case, after setting up Oracle 21c and PostgreSQL 16 in Docker containers, the migration steps involved:

• Installation of ora2pg on the Oracle Docker container.
• Configuration of ora2pg, ensuring that the correct Oracle connection settings were in place.
• Exporting the Oracle schema using ora2pg, and then importing the schema into PostgreSQL.

While ora2pg is effective for schema migration, it’s important to note that this tool, being written in Perl, may not be the best choice for data migration due to performance limitations and complexity. You could use ora2pg for schema migration, and rely on ETL tools like Pentaho for migrating user/application data. This would be a much better and recommended way for a more scalable, efficient, and robust data transfer.