Tales From A Lazy Fat DBA

Hey Everyone,

Nowadays lot’s of organizations are started looking to migrate their databases from Oracle to open source databases and specially when they are looking for such replacements they in general looks for cost efficiency, High performance, good data integrity and easy integration with cloud providers i.e. Amazon. PostgreSQL Database is the answer for most of them, i mean not only the cost but with PostgreSQL you are not compromising any of the good features like replication, clustering, NoSQL support and other features as well.

PostgreSQL has always been a popular database for about a decade now and currently the second most loved DB.
It’s gradually taking over many databases as it’s a true open source, flexible, standard-compliant, and highly extensible RDBMS solution. Recently it has gotten significantly better with features like full text search, logical replication, json support and lot of other cool features.

* Of course i love Oracle and will always remain my first love, it is just that i am a fan of PostgreSQL too! 🙂 🙂

Okay so coming back to the purpose of writing this post – How to do the from your existing Oracle Database to PostgreSQL using one of the popular open source software Ora2pg ?

During the post i will be discussing about one migration that i did using the tool.
Here during the post i won’t be discussing in depth checks and factors that you will be considering while adopting the right approach, tool, methodology or strategy. I am planning to cover these items during future posts.

Before the start i would like to give a short introduction about the tool and the approach. ora2pg is the most open-source tool used for migrating the Oracle database to PostgreSQL.
Most of the Schema migration can be done automatically using ora2pg. The Oracle database objects not supported by PostgreSQL must be identified and must be migrated manually. Ora2pg partially migrated PL/SQL objects. For example: PostgreSQL does not support objects like Packages and SCHEMA can be used as an alternative for Package definitions and Package Body must be converted to FUNCTION(S) as Package Body alternative.

Few of the features that are offered by its latest version (19.1)
– 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.
– Provide some basic automatic conversion of PLSQL code to PLPGSQL.
– 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.
– 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.

There are few other unsupported objects like Materialized Views, Public Synonyms IOT Tables and has other alternatives in PostgreSQL.

Okay now i will be jumping to the real execution.

Step 1: Installation
You need to install Oracle & postgres database drivers and perl db modules which is required by the ora2pg tool to run.

I will be using ora2pg version 19.1, PG Driver (DBD-Pg-3.7.4), Oracle Driver (DBD-Oracle-1.75_2) and Perl Module (DBI-1.641.tar.gz).
All pakages you can download from https://metacpan.org/ and for tool itself go to https://sourceforge.net/projects/ora2pg/
First i’ve installed Perl Modules and the usual steps to install all of the required three packages is given below. Unzip packages and call files mentioned below in same sequence.

perl Makefile.PL
make
make test
make install

Step 2: Next you have to install the ora2pg tool.
Like any other Perl Module Ora2Pg can be installed with the following commands.

tar xjf ora2pg-x.x.tar.bz2
cd ora2pg-x.x/
perl Makefile.PL
make && make install

Step 3: Configuring the tool as per need.
Ora2Pg consist of a Perl script (ora2pg) and a Perl module (Ora2Pg.pm), the only thing you have to modify is the configuration file ora2pg.conf by setting the DSN to the Oracle database and optionally the name of a schema. Once that’s done you just have to set the type of export you want: TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT or COPY, FDW, QUERY, SYNONYM.

Installation creates the configuration file under /etc directory i.e.
[root@gunna ora2pg]# pwd
/etc/ora2pg

Next you have to set few of the required parameters within the configuration file, for example.

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN      dbi:Oracle:host=gunna.localdomain;sid=gunnadb;port=1539
ORACLE_USER     system
ORACLE_PWD      oracle90


# Oracle schema/owner to use
SCHEMA          soe


# Type of export. Values can be the following keyword:
-- Here i will be exporting the TABLES, PROCEDURES, FUNCTION and will be exporting from tables as INSERT statements (Or you can choose COPY as format).
TYPE            TABLE INSERT PROCEDURE FUNCTION

# Output file name
OUTPUT          oracletopgmigrationsoeschema.sql

Step 4: Now after the configuration set, we are all good to call the tool and take the export dump for Oracle’s SOE schema in our database.

The SOE schema in Oracle contains only 2 tables – ADDRESSES (1506152 Rows) and CARD_DETAILS (1505972 rows).
Let’s quickly verify it …

SQL> select count(*) from addresses;
 count
-------
 150615

Next you will be required to set the Oracle Library Path.
[root@gunna ora2pg]#  export LD_LIBRARY_PATH=/home/oracle/app/oracle/product/12.2.0/dbhome_1/lib

Now, call the tool

[root@gunna ora2pg]# ora2pg
[========================>] 2/2 tables (100.0%) end of scanning.
[========================>]  0/2 tables (0.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
[========================>] 1506152/1506152 rows (100.0%) Table ADDRESSES (9538 recs/sec)
[========================>]  3012124/3012124 total rows (100.0%) - (159 sec., avg: 9538 recs/sec).
[========================>] 1505972/1505972 rows (100.0%) Table CARD_DETAILS (16666 recs/sec)
[========================>] 1/1 Indexes(100.0%) end of output.
[========================>] 2/2 Tables(100.0%) end of output.
[root@gunna ora2pg]#
[root@gunna ora2pg]#

This will create the dump in the same directory from where you’ve called.

[root@gunna ora2pg]# ls -ltrh
-rw-r--r--. 1 root root  47K Sep 24 07:18 ora2pg.conf.dist_main
-rw-r--r--. 1 root root  47K Oct  8 05:04 ora2pg.conf
-rw-r--r--. 1 root root 668M Oct 10 03:49 oracletopgmigrationsoeschema.sql

Step 5: Let’s see what’s inside the dump.
Here you will see all data type conversions and Insert statements will be created by the tool itself.
example: integer to bigint, date to timestamp, varchar2 to varchar and etc.

Below are the contents copied from the dump.

CREATE TABLE addresses (
        address_id bigint NOT NULL,
        customer_id bigint NOT NULL,
        date_created timestamp NOT NULL,
        house_no_or_name varchar(60),
        street_name varchar(60),
        town varchar(60),
        county varchar(60),
        country varchar(60),
        post_code varchar(12),
        zip_code varchar(12)
) ;
ALTER TABLE addresses ADD PRIMARY KEY (address_id);


CREATE TABLE card_details (
        card_id bigint NOT NULL,
        customer_id bigint NOT NULL,
        card_type varchar(30) NOT NULL,
        card_number bigint NOT NULL,
        expiry_date timestamp NOT NULL,
        is_valid varchar(1) NOT NULL,
        security_code integer
) 
CREATE INDEX carddetails_cust_ix ON card_details (customer_id);
ALTER TABLE card_details ADD PRIMARY KEY (card_id);


BEGIN;
ALTER TABLE addresses DROP CONSTRAINT IF EXISTS add_cust_fk;

INSERT INTO addresses (address_id,customer_id,date_created,house_no_or_name,street_name,town,county,country,post_code,zip_code) VALUES (5876,984495,'2008-12-13 08:00:00',E'8',E'incompetent gardens',E'Armadale',E'West Lothian',E'Norway',E'4N2W7M',E'406013');
INSERT INTO addresses (address_id,customer_id,date_created,house_no_or_name,street_name,town,county,country,post_code,zip_code) VALUES (5877,166622,'2005-05-21 23:00:00',E'35',E'nasty road',E'Millport',E'Glasgow',E'Austria',NULL,NULL);
INSERT INTO addresses (address_id,customer_id,date_created,house_no_or_name,street_name,town,county,country,post_code,zip_code) VALUES (5878,221212,'2009-03-21 14:00:00',E'80',E'mushy road',E'Innerleithen',E'Flintshire',E'Germany',E'RIUMCV',E'813939');
INSERT INTO addresses (address_id,customer_id,date_created,house_no_or_name,street_name,town,county,country,post_code,zip_code) VALUES (5879,961529,'2004-01-02 08:00:00',E'73',E'obedient road',E'Milton',E'South Gloucestershire',E'Massachusetts',NULL,NULL);
INSERT INTO addresses (address_id,customer_id,date_created,house_no_or_name,street_name,town,county,country,post_code,zip_code) VALUES (5880,361999,'2000-04-16 22:00:00',E'56',E'chilly road',E'Cupar',E'Dorset',E'Philippines',NULL,NULL);

and so on ....

Step 5: Next, time to import the Oracle data to Postgres Database
Before import lets quickly create the sample database and schema.

postgres=# CREATE DATABASE migra;
CREATE DATABASE
postgres=#

dixit=# create schema soe;
CREATE SCHEMA


postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 migra     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Now i will be starting the import process.

dixit=# \i oracletopgmigrationsoeschema.sql
SET
CREATE TABLE
CREATE TABLE
CREATE INDEX
INSERT 1,0
.......
.........



dixit=# \dt+
                            List of relations
 Schema |         Name         | Type  |  Owner   |  Size   | Description
--------+----------------------+-------+----------+---------+-------------
 public | addresses            | table | postgres | 40 MB   |
 public | card_details         | table | postgres | 10 MB   |


postgres=# select count(*) from addresses;
 count
-------
 150615
(1 row)

There are whole lot of areas that i could cover, but just to keep the post simple and easy to digest for readers i will be covering issues that i faced or manual efforts that are needed during the migration and other areas.

Hope It Helps
Prashnt Dixit