Migration Tools

There are many commercial tool available based on the type of database migration. If you are thinking of migrating from self maintained Oracle database to Cloud, then each cloud vendor provides tools to ease your migration task. When it comes, Oracle to PostgreSQL database migration, there is no tool as such that can migrate 100% of your Oracle database workloads to PostgreSQL. In that case, you need both tools that migrate some of your Oracle database functionalities and rest your database migration experts have to do manually.

There are some open-source tools which are also very helpful for an Oracle to PostgreSQL database migration.

Orafce

Oracle compatible functions, data type and packages can be use as it is in PostgreSQL. This is an open source tool with BSD licence so anyone can use this tool.

Most of the major functions are covered in Orafce.

Applications usually use those functions with multiple occurrences. You can reduce the modification cost of SQL by using this tool. All the functions and packages are implemented correctly and it is well tested.

Some of the functions:

• Dbms_output
• dbms_random
• utl_file – filesystem related functions
• Dbms_pipe and dbms_alert
• PLVdate,PLVstr, PLVchr
• Oracle compatible DATE data type and functions like ADD_MONTHS, LAST_DAY,NEXT_DAY and so on.
• NVL function
• SUBSTR and SUBSTRB function
• VARCHAR2 and NVARCHAR2 support
• TO_DATE()

Ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema.

It connects to the Oracle database, scans it automatically, extracts its structure or data and then generates SQL scripts that you can load into your PostgreSQL database.

Ora2Pg inspects all database objects, all functions and stored procedures to detect if there’s still some objects and PL/SQL code that cannot be automatically converted by Ora2Pg.

This tool is very helpful for the following conversions:

• Schema conversion
• PLSQL to PLPGSQL conversion