ORACLE TO POSTGRESQL
MIGRATION USING Ora2Pg
In this blog, I will explain how to migrate
Oracle DB to PostgreSQL DB using ora2Pg tool.
Source Machine: ora12c.ora.com 192.168.0.105
Oracle
12C
Target Machine postgres222.ora.com 192.168.0.222 PostgreSQL 10.4
Pre-requisites
To install ora2pg we need following:
ora2pg-18.1.tar.gz : ora2pg archive
DBD-Oracle-1.75_2.tar.gz : Oracle database
driver for the DBI module
Download from https://metacpan.org/pod/DBD::Oracle
DBD-Pg-3.6.0.tar.gz : PostgreSQL database
driver for the DBI module
DBI-1.636.tar.gz : Database independent
interface for Perl
Either way we can install directly using yum
installation with below command.
yum install perl-DBD-Pg perl perl-devel perl-DBI
perl-CPAN
Note: A
version of perl > 5.6 require for Ora2pg as it is written in perl
Configure ora2pg and DB Module
I have downloaded all the software and moved to the oracle
machine and untar
[root@ora12c ORA2PG]# ls -ltr
/ORA2PG
total 6340
drwxrwxr-x 6 1000
1000 4096 Apr 24 2014 DBD-Oracle-1.74
drwxr-x--- 5 502
games 4096 Apr 24 2016 DBI-1.636
drwxr-xr-x 5 1000
1000 4096 Apr 17 2017 DBD-Pg-3.6.0
drwxrwxr-x 6 root root
4096 Sep 1 2017 ora2pg-18.2
Install DBI
module
[root@ora12c ORA2PG]# cd DBI-1.636
[root@ora12c DBI-1.636]# pwd
/ORA2PG/DBI-1.636
[root@ora12c DBI-1.636]# perl Makefile.PL
[root@ora12c DBI-1.636]# make
[root@ora12c DBI-1.636]# make install
Install
DBD-Oracle
[root@ora12c ORA2PG]# cd DBD-Oracle-1.74
[root@ora12c DBD-Oracle-1.74]# pwd
/ORA2PG/DBD-Oracle-1.74
[root@ora12c DBD-Oracle-1.74]# export
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[root@ora12c DBD-Oracle-1.74]# export
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
[root@ora12c DBD-Oracle-1.74]# export
PATH=$ORACLE_HOME/bin:$PATH
[root@ora12c DBD-Oracle-1.74]# perl Makefile.PL
[root@ora12c DBD-Oracle-1.74]# make
[root@ora12c DBD-Oracle-1.74]# make install
Install
DBD-Pg
[root@ora12c ORA2PG]# cd DBD-Pg-3.6.0
[root@ora12c DBD-Pg-3.6.0]# pwd
/ORA2PG/DBD-Pg-3.6.0
[root@ora12c DBD-Pg-3.6.0]# perl Makefile.PL
[root@ora12c DBD-Pg-3.6.0]# make
[root@ora12c DBD-Pg-3.6.0]# make install
Install
ora2pg
[root@ora12c ORA2PG]# cd ora2pg-18.2
[root@ora12c ora2pg-18.2]# pwd
/ORA2PG/ora2pg-18.2
[root@ora12c ora2pg-18.2]# perl Makefile.PL
[root@ora12c ora2pg-18.2]# make
[root@ora12c ora2pg-18.2]# make install
[root@ora12c ora2pg-18.2]# ora2pg –version
Ora2Pg v18.2
For Migration we will use ora2pg.conf
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 ora2pg.conf. In our
case the configuration file is located in /etc/ora2pg directory.
[root@ora12c ora2pg]# ls -ltr /etc/ora2pg/
total 120
-rwxrwxrwx 1 oracle oinstall 58356 Jul 2 05:13 ora2pg.conf ç Change the ownership to oracle
-rw-r--r-- 1 root root
58162 Jul 2 08:22
ora2pg.conf.dist
Note:
Before change in the ora2pg.conf file. Take a backup of the original file.
Update the parameters in ora2pg.conf
Now I will be modify the /etc/ora2pgo/ora2pg.conf file like ORACLE_HOME, Schema name,
password and connectivity details of the DB which we have to convert to
PostgreSLQ.
Find the following four Oracle related
parameters and change them accordingly:
ORACLE_HOME /u01/app/oracle/product/12.1/dbhome_1
ORACLE_DSN
dbi:Oracle:host=192.168.0.105;sid=orcl;port=1521
ORACLE_USER SYSTEM
ORACLE_PWD manager
Find the following parameters and set them as
descibed:
TYPE
TABLE PACKAGE COPY VIEW GRANT
SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION
DISABLE_SEQUENCE 1
DISABLE_TRIGGERS USER
TRUNCATE_TABLE 1
DROP_FKEY
Set parameter OUTPUT to the desired value.
Set parameter SCHEMA to the source DB
schema(s), I am here testing only on HR schema
Set parameter PG_SCHEMA to the target schema
name
Add the following line to the configuration
file:
ORA_INITIAL_COMMAND commit
####Put the below file in the source (oracle)
server in the /etc/ora2pg/ora2pg.conf file
PG_DSN
dbi:Pg:dbname=orcl;host=192.168.0.222;port=5598
PG_USER hr
PG_PWD
temp1234
pg_hba.conf make a entry of the source server for
connectivity
Make an entry in the pg_hba.conf in target side postgres222.ora.com, so that
Oracle can be made connection to postgresql. I opened for all as per demo purpose
but we cannot use this for prod.
# "local" is for Unix domain socket
connections only
local
all all trust
# IPv4 local connections:
host
all all 127.0.0.1/32 trust
host
all all 192.168.0.221/32 trust
host
all all 192.168.0.222/32 trust
host all all 0.0.0.0/0 trust
Run the report and check if any manual action
required.
Before run the actual migration, let us check
the report, if we need to take any action manually.
[oracle@ora12c
~]$ ora2pg -t SHOW_REPORT
--estimate_cost --cost_unit_value 10 --conf /etc/ora2pg/ora2pg.conf --dump_as_html >
report.html
[========================>] 7/7 tables
(100.0%) end of scanning.
[========================>] 9/9 objects
types (100.0%) end of objects auditing.
Once we will check the report and found
everything is fine then we can start the actual migration. If there is required
any manually intervention in the objects then we need to check those objects
before migrate the data. We will do migration in two parts
1
Migrate the schema structure
2
Migrate the Actual
Export DDL from Oracle
[oracle@ora12c ~]$ ora2pg -d
Ora2Pg version: 18.2
Trying to connect to database: dbi:Oracle:host=192.168.0.105;sid=orcl;port=1521
Isolation level: SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE
DEBUG: executing initial command to Oracle:
commit
Looking forward functions declaration in
schema HR.
Retrieving table information...
[1] Scanning table COUNTRIES (25 rows)...
[2] Scanning table DEPARTMENTS (27 rows)...
[3] Scanning table EMPLOYEES (107 rows)...
[4] Scanning table JOBS (19 rows)...
[5] Scanning table JOB_HISTORY (10 rows)...
[6] Scanning table LOCATIONS (23 rows)...
[7] Scanning table REGIONS (4 rows)...
Dumping table COUNTRIES...
Dumping table LOCATIONS...
Dumping table DEPARTMENTS...
Dumping table EMPLOYEES...
Dumping table JOB_HISTORY...
Dumping table REGIONS...
Dumping table JOBS...
Dumping RI COUNTRIES...
Dumping RI LOCATIONS...
Dumping RI DEPARTMENTS...
Dumping RI EMPLOYEES...
Dumping RI JOB_HISTORY...
Below file will be created. This file will be
copied to target server and import the DDL
[oracle@ora12c ~]$ ls -ltr HR_output.sql
-rw-r--r-- 1 oracle oinstall 3599 Jul 2 17:38 HR_output.sql
Note: We provided the output file name and
path in the /etc/ora2pg/ora2pg.conf file
OUTPUT /home/oracle/HR_output.sql
.
Before import the data, I created the same
database, user & schema in the Postgresql server
postgres=# create user hr WITH ENCRYPTED
PASSWORD 'temp1234';
postgres=# alter user hr with SUPERUSER;
postgres=# create database orcl;
postgres=# grant all privileges on database
orcl to hr;
postgres=# alter database orcl owner to hr;
postgres=# \l orcl
List of databases
Name |
Owner | Encoding | Collate |
Ctype | Access privileges
------+-------+----------+-------------+-------------+-------------------
orcl |
hr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/hr +
| | | | | hr=CTc/hr
postgres=# \c orcl hr
orcl=# create schema hr;
Import the DDL in PostgreSQL
-bash-4.1$ /opt/PostgreSQL/10/bin/psql -d
orcl -U hr -W -p 5598 < /tmp/HR_output.sql
Note: The output file HR_output.sql, which
were generated by ora2pg –d we copied from oracle to postgresql server
Check the objects in PostgreQL
postgres=# \c orcl hr
orcl=# \dt
List of relations
Schema
| Name | Type
| Owner
--------+-------------+-------+-------
hr |
countries | table | hr
hr |
departments | table | hr
hr |
employees | table | hr
hr |
job_history | table | hr
hr | jobs
| table | hr
hr |
locations | table | hr
hr |
regions | table | hr
(7 rows)
orcl=# \di
List of relations
Schema
| Name | Type
| Owner | Table
--------+------------------+-------+-------+-------------
hr |
countries_pkey | index | hr | countries
hr |
departments_pkey | index | hr |
departments
hr |
employees_pkey | index | hr | employees
hr |
job_history_pkey | index | hr |
job_history
hr |
jobs_pkey | index | hr | jobs
hr |
locations_pkey | index | hr | locations
hr |
regions_pkey | index | hr | regions
(7 rows)
Now the structure has been transferred in Postgresql.
This is empty structure.
Let copy the data now. For that we will
directly run the command from oracle server and by network it will send the
data to PostgreSQL server.
[oracle@ora12c ~]$ ora2pg -d -t COPY
Ora2Pg version: 18.2
Trying to connect to database:
dbi:Oracle:host=192.168.0.105;sid=orcl;port=1521
Isolation level: SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE
DEBUG: executing initial command to Oracle:
commit
Retrieving table information...
Once it will be completed then verify the
data at postgresql side.
orcl=# select count(*) from hr.countries;
LOG:
duration: 0.434 ms statement:
select count(*) from hr.countries;
count
-------
25
(1 row)
orcl=# select count(*) from hr.departments;
LOG:
duration: 0.275 ms statement:
select count(*) from hr.departments;
count
-------
27
(1 row)
We could see data copied without any issue.
Hi when i am going to Install DBD::Oracle facing below mentioned issue by using .
ReplyDelete[root@ora12c DBD-Oracle-1.74]# make
[root@ora12c DBD-Oracle-1.74]# make install
/usr/bin/ld: cannot find -lnsl
collect2: error: ld returned 1 exit status
make: *** [Makefile:524: blib/arch/auto/DBD/Oracle/Oracle.so] Error 1
Waiting for response. wahid542@yahoo.com