Setup ora2pg for Oracle to Postgres Migration

An important part of performing a migration from Oracle to PostgreSQL is selecting the right tool for helping with the conversion between systems. When people ask me how they can get the migration process started, I like to recommend a powerful open source utility called "ora2pg".

As the name suggests, ora2pg is a tool that migrates Oracle (or MySQL) databases to PostgreSQL by generating compatible SQL files As the documentation states, ora2pg “connects your Oracle database, scans it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database.”

ora2pg also has a bunch of neat features that you can see below. To see all of the features and to learn more about ora2pg please visit https://ora2pg.darold.net/start.html.

• 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 subpartitions.
• 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.

Summary

The process for installing ora2pg is fairly straightforward, but the number of steps involved tend to seem a bit daunting. Below is a summary of the steps we will go through:

1. Install necessary prerequisites
2. Build and install DBD::Oracle
3. Build and install ora2pg
4. Configure ora2pg
5. Test and use ora2pg

Demo Setup

For this demo on how to install ora2pg, we are using a Centos 7.7 Environment with Oracle 12.1 installed. We will also be using ora2pg 20.0. ora2pg can either be run on the PostgreSQL server or you can run it on the Oracle server. ora2pg was run on my Oracle server. In this demo I used a virtual box to create my environment, I will assume that you have your Oracle environment already setup.

If you don't want to install ora2pg on the Oracle server and instead on the PostgreSQL server there are a couple of extra steps that need to be done. First you will need to have PostgreSQL installed. If you need help installing PostgreSQL please look at the previously published blog post.

In your PostgreSQL server you will need to install the following prerequisites and will need to install the Oracle Instant Client SDK.

Prerequisites: After installing PostgreSQL and making sure everything is running properly you will need to run

sudo yum install -y gcc postgresql11-devel \ perl-ExtUtils-MakeMaker perl-DBI perl-DBD-Pg perl-tests

Oracle Instant Client SDK: These are the following steps that need to be taken to install the Instant Client in your PostgreSQL server.

• You will need to download the Oracle 12.2 Basic & SDK Instant Client RPMs, sqlplus suggested from the following site. https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

To install the packages simply run

sudo yum localinstall -y /path/to/oracle-instantclient12*.rpm

You will need to include the below in your ~/.bash_profile:

# InstantClient
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PAT
export PATH=$ORACLE_HOME/bin:$PATH

Then source the new env vars by running

source ~/.bash_profile

You will then test the connection to your Oracle server (if you've installed the sqlplus instantclient package simply run the below):

sqlplus hr/hr1234@//192.168.99.21:1521/ORCLPDB1

Keep in mind that you only have to do the above if you are going to install ora2pg in your PostgreSQL server. After you do the above you can continue with the Build/Install DBD:oracle section.

Prerequisites for Building

Before getting started there were some prerequisites my environment needed in order to build DBD::Oracle and ora2pg. I needed to install a couple of perl modules in order to get everything up and running. I did so by running:

sudo yum install perl-devel

sudo yum install perl-DBI.x86_64

Build/Install DBD::Oracle

Before we build/install ora2pg you need to build/install the Perl module DBD::Oracle. DBD::Oracle is a Perl module that works with the DBI module to provide access to Oracle databases. If you would like to learn more about it please click the following link https://metacpan.org/pod/DBD::Oracle.

As you can see below there are two ways you can build/install DBD::Oracle. In my environment, I did it the first way. You can see in the first step to run the wget command to get the tar file from the site. If you don’t have wget and are using a Centos or RHEL OS, you can install it by running

sudo yum install wget
wget https://cpan.metacpan.org/authors/id/Z/ZA/ZARQUON/DBD-Oracle-1.76.tar.gz
tar -xvzf DBD-Oracle-1.76.tar.gz
cd DBD-Oracle-1.76
perl Makefile.PL
make && make test
sudo make install

As mentioned previously, if you don’t want to do the above steps, you can simply do the below two steps to build/install DBD::Oracle.

sudo yum install perl-CPAN
perl -MCPAN -e 'install DBD::Oracle' # configure for sudo

Build/Install ora2pg

After you have installed DBD::Oracle, you will then need to build/install ora2pg. The steps below are similar to the steps in the documentation of ora2pg which you can find in this link https://ora2pg.darold.net/. The only difference is the first step, where you need to wget the tar file from ora2pg Github archive.

wget https://github.com/darold/ora2pg/archive/v20.0.tar.gz
tar -xvzf v20.0.tar.gz
cd ora2pg-20.0
perl Makefile.PL
make
sudo make install

Configure ora2pg

After you have ora2pg built and installed you can now configure the ora2pg.conf to work with your Oracle server. The settings that you see below are the settings in my environment. The ora2pg.conf file has many configuration settings that you can change. Below are the configuration changes that you will need to apply to your ora2pg.conf in order to be able to use ora2pg. Please keep in mind that you will need to change the settings to match your environment. If you would like to learn more about the other configurations please look at the documentation: https://ora2pg.darold.net/documentation.html#CONFIGURATION.

In ora2pg-20.0/ora2pg.conf:
PG_VERSION      11
ORACLE_DSN      dbi:Oracle:host=192.168.99.21;port=1521;service_name=ORCLPDB1
ORACLE_USER     hr
ORACLE_PWD      hr1234
USER_GRANTS	    1

EXPORT_SCHEMA   1
SCHEMA          hr
TRANSACTION     READONLY

# This is needed for Oracle XE 11 with ora2pg <= v20, but safe
# to add for any version
SYSUSERS        APEX_040000

Testing ora2pg

Now that ora2pg has been configured you can finally test connectivity. By testing the connectivity you will see that you have configured ora2pg correctly, if you didn’t you will receive errors. You can test by running the below command:

ora2pg -t SHOW_VERSION -c ora2pg.conf

Estimation report

Ora2pg has an amazing feature in that it can give you an estimate of the migration cost from Oracle to PostgreSQL. It is important that you use this feature before actually migrating. Per the documentation, “To obtain a good assessment of this migration cost, Ora2Pg will inspect all database objects, all functions and stored procedures to detect if there's still some objects and PL/SQL code that can not be automatically converted by Ora2Pg.”

As it says above generating an estimation report shows if there are any database objects, PL/SQL code, etc that won’t be automatically converted by ora2pg. This means that you will need to do some manual work. This may be something you didn’t want to hear, but only in a perfect world can a tool do all the work for you. To learn more about the estimation report please look at the documentation.

There are many ways to generate an estimation report as you can see in the link above. Below is the command that will give you the report as well as an estimate of the migration cost.

ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf

The following link shows a great example of the report feature that ora2pg has: https://ora2pg.darold.net/report.html.

As you can see in the link this feature gives a pretty detailed report and even provides workarounds when an object isn’t available in PostgreSQL. For example, if you look for the following objects SEQUENCE and SYNONYM in the report the comments say the below.

SEQUENCE: Sequences are fully supported, but all call to sequence_name.NEXTVAL or sequence_name.CURRVAL will be transformed into NEXTVAL('sequence_name') or CURRVAL('sequence_name').

SYNONYM: SYNONYMs will be exported as views. SYNONYMs do not exist with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access objects outside the current schema.

The report feature is valuable for planning your migration to ora2pg.

Work that you will have to do

Below I would like to provide bullet points for you to take into consideration when migrating from Oracle to PostgreSQL.

• Keep in mind that before you do the migration, your team will need PostgreSQL training. More PostgreSQL knowledge before starting to migrate will lead to a more efficient migration and you will learn more about both Oracle and PostgreSQL during the migration.
• You should do an in depth analysis of your Oracle database architecture. As mentioned previously, ora2pg doesn’t migrate every single object or function. It is best to analyze your Oracle database and determine if you can change anything in there to make your migration easier.
• Think about your applications that connect to the database. You need to make sure that you don't have a third party application that does work with PostgreSQL.
• Finally make sure that you do your research. It can be tedious but it's better to do the research than to have everything break. By reading this article, you’re already researching.
• Transactions in PostgreSQL are implemented differently than in Oracle. Oracle supports nested transactions, PostgreSQL does not. Often you can use PostgreSQL SAVEPOINTs to achieve similar behavior, but if your Oracle deployment uses PL/SQL extensively and uses nested transactions, you will likely have some manual porting to do.
• Schemas are different in PostgreSQL vs Oracle. In PostgreSQL, schema objects are used to add namespaces or scope to DB’s. In PostgreSQL, a schema is independent of a user, group or role object.
• In PostgreSQL, roles, users and groups are global objects - a role/user/group is not part of a database, they are independent of all databases in a PostgreSQL instance. A role/user/group can be granted privileges to any DB in an instance. A user instance can be directly connected to one database in a cluster at a time.
• Oracle provides hundreds of packages. If you use Oracle packages extensively, you will likely have some manual porting ahead.
• Both Oracle and PostgreSQL provide stored procedures and user-defined functions. PostgreSQL supports multiple embedded languages in the server. The most commonly used procedural server-side languages - PL/SQL in Oracle and PL/pgSQL in PostgreSQL - are similar enough that ora2pg will translate some functions or UDF’s directly. It will identify those that it can’t translate directly and identify problems.
• There are common alternatives in PostgreSQL to Oracle’s Native Dynamic SQL, EXECUTE IMMEDIATE - see using the dblink PostgreSQL extension.
• PostgreSQL provides a way to extend the core - PostgreSQL extensions. There is a large and growing number of extensions. It’s worth reviewing extensions to see if there are some that can help you migrate.