Migrating from oracle to postgresql with ora2pg

PostgreSQL is one of the most used Rdbms.

Here we gonna talk about migrating from oracle to postgresql using ora2pg. 
After installing ora2pg tool, we will see how to configure and to run it to migrate our data. We are using an oracle 12.1 database and a postgresql 9.6.2. The server is runing on OEL 7.2
The oracle database and the postgresql server are running on the same server. But different servers can be used.

To install ora2pg we need following:
-DBD-Oracle-1.75_2.tar.gz : Oracle database driver for the DBI module
-DBD-Pg-3.6.0.tar.gz : PostgreSQL database driver for the DBI module
-DBI-1.636.tar.gz : Database independent interface for Perl
-ora2pg-18.1.tar.gz : ora2pg archive

The DBI modules can be found here
And the ora2pg can be downloaded from here

Install DBI module
[root@serveroracle postgres]#tar xvzf DBI-1.636.tar.gz [root@serveroracle postgres]#cd DBI-1.636 [root@serveroracle postgres]#perl Makefile.Pl [root@serveroracle postgres]#make [root@serveroracle postgres]#make install

Install DBD-Oracle
[root@serveroracle postgres]# tar xvzf DBD-Oracle-1.75_2.tar.gz [root@serveroracle postgres]# cd DBD-Oracle-1.75_2/ [root@serveroracle DBD-Oracle-1.75_2]# export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 [root@serveroracle DBD-Oracle-1.75_2]# export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib [root@serveroracle DBD-Oracle-1.75_2]# perl Makefile.PL [root@serveroracle DBD-Oracle-1.75_2]# make [root@serveroracle DBD-Oracle-1.75_2]# make install

Install DBD-Pg
[root@serveroracle postgres]# tar xvzf DBD-Pg-3.6.0.tar.gz [root@serveroracle postgres]# cd DBD-Pg-3.6.0 [root@serveroracle DBD-Pg-3.6.0]# perl Makefile.PL Configuring DBD::Pg 3.6.0 Path to pg_config? /u01/app/postgres/product/96/db_2/bin/pg_config [root@serveroracle DBD-Pg-3.6.0]# make [root@serveroracle DBD-Pg-3.6.0]# make install
When we first run the command perl MakeFile.PL, we got following errors
[root@serveroracle DBD-Pg-3.6.0]# perl Makefile.PL Configuring DBD::Pg 3.6.0 PostgreSQL version: 90602 (default port: 5432) POSTGRES_HOME: /u01/app/postgres/product/96/db_2 POSTGRES_INCLUDE: /u01/app/postgres/product/96/db_2/include POSTGRES_LIB: /u01/app/postgres/product/96/db_2/lib OS: linux Warning: prerequisite version 0 not found. Could not eval ' package ExtUtils::MakeMaker::_version; no strict; BEGIN { eval { # Ensure any version() routine which might have leaked # into this package has been deleted. Interferes with # version->import() undef *version; require version; "version"->import; } } local $VERSION; $VERSION=undef; do { use version; our $VERSION = qv('3.6.0'); }; $VERSION; ' in Pg.pm: Can't locate version.pm in @INC (@INC contains: t/lib /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 11) line 16, line 19. BEGIN failed--compilation aborted at (eval 11) line 16, line 19. WARNING: Setting VERSION via file 'Pg.pm' failed at /usr/share/perl5/vendor_perl/ExtUtils/MakeMaker.pm line 619. Using DBI 1.636 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/local/lib64/perl5/auto/DBI/ Writing Makefile for DBD::Pg
We correct errors by running the command below.
[root@serveroracle DBD-Pg-3.6.0]# yum -y install 'perl(version)' Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package perl-version.x86_64 3:0.99.07-2.el7 will be installed --> Finished Dependency Resolution

Install ora2pg
[postgres@serveroracle ~]$ tar xvzf ora2pg-18.1.tar.gz [root@serveroracle postgres]# cd ora2pg-18.1/ [root@serveroracle postgres]# perl Makefile.PL [root@serveroracle postgres]# make [root@serveroracle postgres]# make install

[root@serveroracle postgres]# ora2pg -version Ora2Pg v18.1 [root@serveroracle postgres]#

Now that ora2pg is installed, we can proceed with the migration. The first step is to configure the ora2pg.conf file. We can do a copy of the default template and then modify the file. In our case the configuration file is located in /etc/ora2pg directory.
[root@serveroracle ora2pg]# cp ora2pg.conf.dist ora2pg.conf [root@serveroracle ora2pg]# vi ora2pg.conf
In our configuration file, following changes where done. We are exporting only the HR schema
ORACLE_DSN dbi:Oracle:host=serveroracle.localdomain;sid=ORCL ORACLE_USER system ORACLE_PWD root SCHEMA HR TYPE TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION OUTPUT HR_output.sql
ora2pg works by exporting and importing schemas. We can only export one schema at a time if we use the option SCHEMA. If we want to export all schemas we can just comment the option SCHEMA. In this case all non-oracle users will be extracted.
In the documentation we also have the option SYSUSERS
# Allow to add a comma separated list of system user to exclude from # from Oracle extraction. Oracle have many of them following the modules # installed. By default it will suppress all object owned by the following # system users: # CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,OLAPSYS,ORDDATA,OWBSYS, # ORDPLUGINS,ORDSYS,OUTLN,SI_INFORMTN_SCHEMA,SYS,SYSMAN,SYSTEM,WK_TEST, # WKSYS,WKPROXY,WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000, # FLOWS_040100,FLOWS_FILES,MDDATA,ORACLE_OCM,SPATIAL_CSW_ADMIN_USR, # SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,SQLTXPLAIN,DMSYS,TSMSYS,WKSYS, # APEX_040200,DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS # Other list of users set to this directive will be added to this list. #SYSUSERS OE,HR
Once configuration done, we can run the orap2g command. Note that you can see all options by running ora2pg -help
[root@serveroracle ora2pg]# ora2pg [========================>] 7/7 tables (100.0%) end of scanning. [> ] 0/7 tables (0.0%) end of scanning. [========================>] 7/7 tables (100.0%) end of table export. [========================>] 0/0 packages (100.0%) end of output. [========================>] 25/25 rows (100.0%) Table COUNTRIES (25 recs/sec) [==> ] 25/215 total rows (11.6%) - (0 sec., avg: 25 recs/sec). [========================>] 27/27 rows (100.0%) Table DEPARTMENTS (27 recs/sec) [=====> ] 52/215 total rows (24.2%) - (1 sec., avg: 52 recs/sec). [========================>] 107/107 rows (100.0%) Table EMPLOYEES (107 recs/sec) [=================> ] 159/215 total rows (74.0%) - (1 sec., avg: 159 recs/sec). [========================>] 19/19 rows (100.0%) Table JOBS (19 recs/sec) [===================> ] 178/215 total rows (82.8%) - (1 sec., avg: 178 recs/sec). [========================>] 10/10 rows (100.0%) Table JOB_HISTORY (10 recs/sec) [====================> ] 188/215 total rows (87.4%) - (2 sec., avg: 94 recs/sec). [========================>] 23/23 rows (100.0%) Table LOCATIONS (23 recs/sec) [=======================> ] 211/215 total rows (98.1%) - (2 sec., avg: 105 recs/sec). [========================>] 4/4 rows (100.0%) Table REGIONS (4 recs/sec) [========================>] 215/215 total rows (100.0%) - (3 sec., avg: 71 recs/sec). [========================>] 215/215 rows (100.0%) on total estimated data (3 sec., avg: 71 recs/sec) [========================>] 1/1 views (100.0%) end of output. [========================>] 3/3 sequences (100.0%) end of output. [========================>] 1/1 triggers (100.0%) end of output. [========================>] 0/0 functions (100.0%) end of output. [========================>] 2/2 procedures (100.0%) end of output. [========================>] 0/0 types (100.0%) end of output. [========================>] 0/0 partitions (100.0%) end of output. [root@serveroracle ora2pg]#

Once finished, a file HR_output.sql is generated. This file can be used to load data in the postgresql database. We can also load data directly without using a file. We just have to specify the connection info for the postgresql.
Let’s know prepare the postgresql server to receive our data. First we create the user HR.
[postgres@serveroracle ~]$ psql psql (9.6.2 dbi services build) Type "help" for help. postgres=# \c orclpg You are now connected to database "orclpg" as user "postgres". orclpg=# create user HR WITH PASSWORD 'root';
And then we can execute the file. The first time we ran the file, we had some constraints errors
orclpg=# \i HR_output.sql ERROR: insert or update on table "countries" violates foreign key constraint "countr_reg_fk" DETAIL: Key (region_id)=(2) is not present in table "regions". STATEMENT: COPY countries (country_id,country_name,region_id) FROM STDIN; psql:HR_output.sql:224: ERROR: insert or update on table "countries" violates foreign key constraint "countr_reg_fk" DETAIL: Key (region_id)=(2) is not present in table "regions". orclpg=#
To correct this, we put the option in the configuration file DROP_FKEY to 1
DROP_FKEY 1
With this option all foreign keys will be dropped before all data import and recreate them at the end of the import. After the load was successful.
orclpg=# \i HR_output.sql SET CREATE SCHEMA ALTER SCHEMA SET CREATE TABLE COMMENT SET SET SET SET SET BEGIN ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE SET COPY 107 SET COPY 19 SET COPY 10 SET COPY 23 SET COPY 4 ALTER TABLE ALTER TABLE ALTER TABLE … … COMMIT
We can verify that tables were created and that data were inserted.

orclpg=# \d List of relations Schema | Name | Type | Owner --------+-------------+-------+---------- hr | countries | table | postgres hr | departments | table | postgres hr | employees | table | postgres hr | job_history | table | postgres hr | jobs | table | postgres hr | locations | table | postgres hr | regions | table | postgres (7 rows)

orclpg=# select count(*) from countries; count ------- 25 (1 row) orclpg=#
Conclusion
As we can see ora2pg is a free easy tool to migrate data from oracle to postgresql. In coming blog we will talk about other tools that can be used to move data from oracle to postgresql

You can find more information in the official web site