Database Migration from Oracle to Postgres
In this blog we are going to talk about migrating from oracle to postgresql using ora2pg.
Ora2Pg is a free tool used to migrate an Oracle or MySQL database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your PostgreSQL database.
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
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=#
Login with Google