# Migration # From Oracle to PostgreSQL By Using ora2pg open source tool

@ 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;

@ 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@YesBolisetty ~]# tar xvzf DBD-Oracle-1.75_2.tar.gz

[root@YesBolisetty ~]# cd DBD-Oracle-1.75_2/

[root@YesBolisetty DBD-Oracle-1.75_2]# export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

[root@YesBolisetty DBD-Oracle-1.75_2]# export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/db_1/lib

[root@YesBolisetty DBD-Oracle-1.75_2]# perl Makefile.PL

[root@YesBolisetty DBD-Oracle-1.75_2]# make

[root@YesBolisetty DBD-Oracle-1.75_2]# make install

2.Installing postgres database driver:

[root@YesBolisetty]# tar xvzf DBD-Pg-3.6.0.tar.gz

[root@YesBolisetty]# cd DBD-Pg-3.6.0

[root@YesBolisetty DBD-Pg-3.6.0]# perl Makefile.PL

Configuring DBD::Pg 3.6.0

[root@YesBolisetty DBD-Pg-3.6.0]# make

[root@YesBolisetty DBD-Pg-3.6.0]# make install

[root@YesBolisetty DBD-Pg-3.6.0]# perl Makefile.PL

3.Installing Perl modules:

[root@YesBolisetty]#tar xvzf DBI-1.636.tar.gz

[root@YesBolisetty]#cd DBI-1.636

[root@YesBolisetty]#perl Makefile.Pl

[root@YesBolisetty]#make

[root@YesBolisetty]#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@YesBolisetty]# cd ora2pg-18.1/

[root@YesBolisetty]# perl Makefile.PL

[root@YesBolisetty]# make

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

[root@YesBolisetty 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@YesBolisetty 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)