Migrating From Oracle to PostgreSQL using ora2pg open source tools

In this blog we are going to 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 12C database and a postgresql edb 9.6, The server is runing on OEL 7.2,The oracle database and the postgresql server are running on the same server.

we are going to talk about migrating from oracle to postgresql using ora2pg open source tool
before migrating you need to know little bit knowledge of functions,procedure,trigger and  Difference between oracle vs postgresql data types

1.Oracle Installation 12C
2.Oracle instalaltion 11gr2
3.postgresql installation 9.3
4.postgresql installation 10.3
-------------------------At Oracle---------------------------------------------------
PREREQUEST:
1.In a database all schema size:

set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

2.issuing below script you will extract the size & path of datafiles
along with tablespace name,allocated space in GB, used space in GB & free space in GB.

set linesize 200
set pagesize 2000
COLUMN tablespace_name format a10
COLUMN file_name format a45
COLUMN free% format a7
SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes/1024/1024/1024 allocated_GB,
round(((df.bytes/1024 /1024/1024) – NVL(SUM(dfs.bytes)/1024/1024 /1024, 0)),1) used_GB,
round(NVL(SUM (dfs.bytes)/1024/1024/1024, 0),2) free_space_GB
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME
ORDER BY df.tablespace_name;

3.Check the tablecount & row count for data validation

select count(*) from countries; #for table's row count

For table count:

select owner, count(*) from dba_tables;

select table_name,to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)) ,'/ROWSET/ROW/C')) count from user_tables;

POSTREQUEST:
------------------------- AT PostgreSQL-----------------------------------------------
1.CHECK PostgreSQL all schema Size But size will be varried compare to destination, event though check the schema's table count also:
Edit: just noticed the workaround with summing up all tables to get the database size is not necessary:

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name

2.All tablespace size for refernece only not mandatory:

SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;

3.Check the tablecount & row count of some sample table if possible at postgresql for data validation

select count(*) from countries; #for table's row count

For table count:

select count(*) from information_schema.tables where table_schema = 'scott';

===========================
NOW WE HAVE TO GO CONFIGURATION PART:
I.FOR INSTALL ORA2PG WE NEED FOLLOWING DEPENDENCIES:
1.DBD-Oracle-1.75_2.tar.gz : Oracle database driver for the DBI module
  Oracle Database Drivers Link: http://search.cpan.org/~mjevans/DBD-Oracle-1.75_2/lib/DBD/Oracle/Troubleshooting.pod

2.DBD-Pg-3.6.0.tar.gz : PostgreSQL database driver for the DBI module
  Postgres Database Drivers Link: http://download.openpkg.org/components/cache/perl-dbi/

3.DBI-1.636.tar.gz : Database independent interface for Perl(DBI requires one or more 'driver' modules to talk to databases,used for 'perldoc DBI' command)
  Perl Database Module Link: https://www.cpan.org/modules/by-module/DBI

4.ora2pg-18.1.tar.gz :This is the Migration free tool
  ORA2PG TOOLS LINK : https://sourceforge.net/projects/ora2pg/

II.After Download the above mentioned file then install this ora2pg drivers following method

1.installing Oracle database driver:

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

2.Installing postgres database driver:

[root@production]# tar xvzf DBD-Pg-3.6.0.tar.gz
[root@production]# cd DBD-Pg-3.6.0
[root@production DBD-Pg-3.6.0]# perl Makefile.PL
Configuring DBD::Pg 3.6.0
[root@production DBD-Pg-3.6.0]# make
[root@production DBD-Pg-3.6.0]# make install
[root@production DBD-Pg-3.6.0]# perl Makefile.PL

3.Installing Perl modules:

[root@production]#tar xvzf DBI-1.636.tar.gz
[root@production]#cd DBI-1.636
[root@production]#perl Makefile.Pl
[root@production]#make
[root@production]#make install

I got some error at the time of perl installing this is other server I noted this i sharing this error just for knowledge

[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

4.Installing ora2pg tools:

 tar xvzf ora2pg-18.1.tar.gz
[root@production]# cd ora2pg-18.1/
[root@production]# perl Makefile.PL
[root@production]# make
[root@production]# make install

III.After Installed These Above Three Modules check the following status of oracle:
1.Check Oracle Tns and Listener status using "lsnrctl status" if not listener up just start it,if not in lister tns file just create using "netca" utiluity
2.And check the Oracle Database up and running  using ps -ef|grep pmon
3.check the oracle user  account status locked or not using
select username,account_status from dba_users where username='system';
4.check the port and hostname using "tnsping sid" & "hostname"
5.check the size of schema and table row count i already told in prerequest and postrequest

IV.You have note the following configuration file and utility path:
/usr/local/bin/ora2pg                    => This is the ora2pg utility Path
/usr/local/bin/ora2pg_scanner      =>This is the scanner path =>
/etc/ora2pg/ora2pg.conf               =>This is the configuration file path
[root@production]#ora2pg -help   => for help command

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@production ora2pg]# cp ora2pg.conf.dist ora2pg.conf
[root@production ora2pg]# vi ora2pg.conf

V.Now we are going to migrate the schema:
open the Configuration file and put a below parameter's entries
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.

ORACLE_DSN      dbi:Oracle:host=production.localdomain;sid=test;port=1521
ORACLE_USER     system
ORACLE_PWD      manager
SCHEMA          scott
TYPE           TABLE  PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION
OUTPUT          oracleMigrationscript.sql
USER_GRANTS 1   #0 FOR DISABLE WE WANTS GRANTS ALSO IF YOU GET ERRORS SET IT 0 FOR  WE WILL GIVE GRANTS PERSMISSION MANULLY(IF NOT USERS ERRORS WILL BE OCCURS BETTER DISABLE IT)

then save and exit using " :wq! "

VI.Then run  ORA2PG the utility,it automatically taken config file and scanning then going to migrate.
this scanning will take more time its depend on database size

[root@production ora2pg]# ora2pg 
[========================>] 9/9 tables (100.0%) end of scanning.         
[>                        ] 0/9 tables (0.0%) end of scanning.           
[========================>] 9/9 tables (100.0%) end of table export.
[>                        ] 0/1 rows (0.0%) Table AIRTEL_OTPS (0 recs/sec)
[>                        ] 0/9 total rows (0.0%) - (1 sec., avg: 0 recs/sec).
[>                        ] 0/1 rows (0.0%) Table BONUS (0 recs/sec)                          
[>                        ] 0/9 total rows (0.0%) - (2 sec., avg: 0 recs/sec).
[========================>] 4/1 rows (400.0%) Table DEPT (4 recs/sec)                         
[==========>              ] 4/9 total rows (44.4%) - (2 sec., avg: 2 recs/sec).
[========================>] 14/1 rows (1400.0%) Table EMP (14 recs/sec)                       
[========================>] 18/9 total rows (200.0%) - (3 sec., avg: 6 recs/sec).
[>                        ] 0/1 rows (0.0%) Table IMAGES (0 recs/sec)                         
[========================>] 18/9 total rows (200.0%) - (4 sec., avg: 4 recs/sec).
[========================>] 1/1 rows (100.0%) Table P1 (1 recs/sec)                           
[========================>] 19/9 total rows (211.1%) - (4 sec., avg: 4 recs/sec).
[>                        ] 0/1 rows (0.0%) Table PBLOB (0 recs/sec)                          
[========================>] 19/9 total rows (211.1%) - (5 sec., avg: 3 recs/sec).
[>                        ] 0/1 rows (0.0%) Table P4 (0 recs/sec)                             
[========================>] 19/9 total rows (211.1%) - (7 sec., avg: 2 recs/sec).
[>                        ] 0/1 rows (0.0%) Table P1 (0 recs/sec)                
[========================>] 19/9 total rows (211.1%) - (7 sec., avg: 2 recs/sec).
[>                        ] 0/1 rows (0.0%) Table P3 (0 recs/sec)                
[========================>] 19/9 total rows (211.1%) - (7 sec., avg: 2 recs/sec).
[>                        ] 0/1 rows (0.0%) Table P2 (0 recs/sec)                
[========================>] 19/9 total rows (211.1%) - (7 sec., avg: 2 recs/sec).
[>                        ] 0/1 rows (0.0%) Table P5 (0 recs/sec)                
[========================>] 19/9 total rows (211.1%) - (7 sec., avg: 2 recs/sec).
[========================>] 5/1 rows (500.0%) Table SALGRADE (5 recs/sec)                     
[========================>] 24/9 total rows (266.7%) - (9 sec., avg: 2 recs/sec).
[========================>] 9/9 rows (100.0%) on total estimated data (9 sec., avg: 1 recs/sec)
[========================>] 1/1 procedures (100.0%) end of procedures export.                  
[===================>     ] 4/5 partitions (80.0%) end of output.            
[========================>] 0/0 triggers (100.0%) end of output. 
[========================>] 0/0 views (100.0%) end of output. 

Once finished, a file oracleMigrationscript.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 scott.

VII.Now we are going to import the oracle schema :

[root@production ora2pg]# ls
ora2pg.conf  ora2pg.conf.dist  oracleMigrationscript.sql  PARTITION_INDEXES_oracleMigrationscript.sql

After migrating the two file ill created run the file in Postgresql Server.

[root@production ~]# psql
Password: 
psql.bin (9.6.2.7)
Type "help" for help.

edb=# 
edb=# create user scott identified by 'tiger';
CREATE ROLE
edb=# 
edb=# 
edb=# alter user scott superuser ;
ALTER ROLE
edb=# \q
[root@production ~]# export PGUSER=scott
[root@production ~]# psql
Password: 
psql.bin (9.6.2.7)
Type "help" for help.

edb=# 
edb=# 
edb=# \c
You are now connected to database "edb" as user "scott".
edb=# 

edb=# \i /etc/ora2pg/oracleMigrationscript.sql
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT 

edb=# \i /etc/ora2pg/PARTITION_INDEXES_oracleMigrationscript.sql

edb=# \dt+
                           List of relations
 Schema |    Name     | Type  |    Owner     |    Size    | Description 
--------+-------------+-------+--------------+------------+-------------
 public | jobhist     | table | enterprisedb | 8192 bytes | 
 scott  | airtel_otps | table | scott        | 0 bytes    | 
 scott  | bonus       | table | scott        | 0 bytes    | 
 scott  | dept        | table | scott        | 8192 bytes | 
 scott  | emp         | table | scott        | 8192 bytes | 
 scott  | images      | table | scott        | 8192 bytes | 
 scott  | p1          | table | scott        | 16 kB      | 
 scott  | pblob       | table | scott        | 8192 bytes | 
 scott  | sales       | table | scott        | 0 bytes    | 
 scott  | salgrade    | table | scott        | 8192 bytes | 
(10 rows)

VII.Next we will migrate the HR schema this schema having some foreign key it will threw errors and i given solution also

[root@production 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      manager
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@production 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@production 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.
Here i created orclpg database becouse my requirement is want to store the HR schemas data want to store the orclpg database that is why i created orclpg database

[root@production ~]$ 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=#

Solution:
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; # for data validation count source(oracle) database as well as destination(postgres) database
 count
-------
    25
(1 row)
orclpg=#