Today I want to write about one of the most used tools for migrating from Oracle to PostgreSQL.
Ora2Pg is Free Software written and maintained by Gilles Darold.
In this example Oracle database and the PostgreSQL database are running on the same server.
I will show both variants of migration:
Variant 1- Oracle and PostgreSQL running on the same server.
We are using:|
- Oracle 12.1 database
- PostgreSQL 11.5
- Oracle Enterprise Linux 6.7
- DBD-Oracle latest version
- DBD-Pg latest version
- DBI latest version
- ora2pg latest version
Installing pre-requisites:
Download and install Perl :
[root@ora2pg ~]# yum install perl-DBD-Pg perl perl-devel perl-DBI perl-CPAN -y
Installation
Install Database independent interface for Perl (DBI module)
Perl module DBD::Oracle will used for connectivity to an Oracle database from Perl DBI. That is why we must first install the DBI module.
Download the latest DBI module for Perl from CPAN (https://www.cpan.org/modules/by-module/DBI/).
root@ora2pg ~]# mkdir install [root@ora2pg ~]# cd install [root@ora2pg install]# wget https://www.cpan.org/modules/by-module/DBI/DBI-1.642.tar.gz [root@ora2pg install]# tar xvzf DBI-1.642.tar.gz [root@ora2pg install]# cd DBI-1.642 [root@ora2pg DBI-1.642]# perl Makefile.PL [root@ora2pg DBI-1.642]# make [root@ora2pg DBI-1.642]# make install
Install Oracle database driver for the DBI module(DBD-Oracle )
[root@ora2pg ~]# export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1 [root@ora2pg ~]# export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/db_1/lib [root@ora2pg ~]# perl -MCPAN -e shell . . . . . . Would you like me to configure as much as possible automatically? [yes] . . . . . . cpan[1]> get DBD::Oracle . . . . . . cpan[2]> quit [root@ora2pg ~]# cd ~/.cpan/build/DBD-Oracle-1.80-zGJNXY/ [root@ora2pg DBD-Oracle-1.80-zGJNXY]# perl Makefile.PL [root@ora2pg DBD-Oracle-1.80-zGJNXY]# make [root@ora2pg DBD-Oracle-1.80-zGJNXY]# make install
Install Postgres database driver for DBI module (DBD-Pg)
[root@ora2pg ~]# cd install [root@ora2pg install]# rm -rf * [root@ora2pg install]# wget https://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/DBD-Pg-3.10.0.tar.gz [root@ora2pg install]# tar xvzf DBD-Pg-3.10.0.tar.gz [root@ora2pg install]# cd DBD-Pg-3.10.0 [root@ora2pg DBD-Pg-3.10.0]# perl Makefile.PL [root@ora2pg DBD-Pg-3.10.0]# make [root@ora2pg DBD-Pg-3.10.0]# make install
And finally install Ora2Pg
[root@ora2pg ~]# cd install [root@ora2pg install]# rm -rf * [root@ora2pg install]# wget https://sourceforge.net/projects/ora2pg/files/20.0/ora2pg-20.0.tar.bz2 [root@ora2pg install]# bzip2 -d ora2pg-20.0.tar.bz2 [root@ora2pg install]# tar xvf ora2pg-20.0.tar [root@ora2pg install]# cd ora2pg-20.0 [root@ora2pg ora2pg-20.0]# perl Makefile.PL [root@ora2pg ora2pg-20.0]# make && make install
Configure Ora2Pg
By default Ora2Pg will look for /etc/ora2pg/ora2pg.conf configuration file, create the configuration file from the default template and grant the privileges to the oracle user:
[root@ora2pg ~]# cd /etc/ora2pg/ [root@ora2pg ora2pg]# cp ora2pg.conf.dist ora2pg.conf [root@ora2pg ora2pg]# chown oracle:oinstall /etc/ora2pg/ora2pg.conf [root@ora2pg ora2pg]# vi ora2pg.conf
Fisrt we must find the following four Oracle parameters and change them :
ORACLE_HOME /u01/app/oracle/product/12.1.0.2/db_1 #If you use SID ORACLE_DSN dbi:Oracle:host=localhost;sid=inzmapdb;port=1521 #If you use service name ORACLE_DSN dbi:Oracle://localhost:1521/inzmapdb ORACLE_USER system ORACLE_PWD oracle
For defining which objects we want to export, we must add object type names after TYPE keyword:
If we will use this example , then all tables, views, tablespaces and their data will be exported.
TYPE TABLE VIEW GRANT TABLESPACE COPY
There is much detailed information in the conf file you can read and configure export for your needs.
#——————————————————————————
# EXPORT SECTION (Export type and filters)
#——————————————————————————
# Type of export. Values can be the following keyword:
# TABLE Export tables, constraints, indexes, …
# PACKAGE Export packages
# INSERT Export data from table as INSERT statement
# COPY Export data from table as COPY statement
# VIEW Export views
# GRANT Export grants
# SEQUENCE Export sequences
# TRIGGER Export triggers
# FUNCTION Export functions
# PROCEDURE Export procedures
# TABLESPACE Export tablespace (PostgreSQL >= 8 only)
# TYPE Export user defined Oracle types
# PARTITION Export range or list partition (PostgreSQL >= v8.4)
# FDW Export table as foreign data wrapper tables
# MVIEW Export materialized view as snapshot refresh view
# QUERY Convert Oracle SQL queries from a file.
# KETTLE Generate XML ktr template files to be used by Kettle.
# DBLINK Generate oracle foreign data wrapper server to use as dblink.
# SYNONYM Export Oracle’s synonyms as views on other schema’s objects.
# DIRECTORY Export Oracle’s directories as external_file extension objects.
# LOAD Dispatch a list of queries over multiple PostgreSQl connections.
# TEST perform a diff between Oracle and PostgreSQL database.
# TEST_VIEW perform a count on both side of rows returned by viewsZ
We can also define an output file name where exported data will be saved:
OUTPUT output.sql
We can also set parameter SCHEMA to the source DB schema names:
# Oracle schema/owner to use SCHEMA ORCL_SCHEMA_NAME1 ORCL_SCHEMA_NAME2 ORCL_SCHEMA_NAME3
Parameter PG_SCHEMA is for setting the target schema name
PG_SCHEMA postgresql_schema_name # Export Oracle schema to PostgreSQL schema EXPORT_SCHEMA 1
Also set parmeters bellow
ORA_INITIAL_COMMAND commit DROP_FKEY 1 DISABLE_SEQUENCE 1 DISABLE_TRIGGERS USER TRUNCATE_TABLE 1
In this example, I am not going to the deeps of the configuration, you can read the manual and config file and configure your conf file for your needs. http://ora2pg.darold.net/documentation.html
To copy the data from Oracle to PostgreSQL we first will Export objects under specified schema from Oracle, then Import to PostgreSQL and finally copy the data. In this example i will import only tables and their data :
-
- Export from Oracle
[root@ora2pg ora2pg]# ora2pg -d [2019-09-29 20:51:01] Ora2Pg version: 20.0 [2019-09-29 20:51:01] Trying to connect to database: dbi:Oracle:host=localhost;sid=inzmapdb;port=1521 [2019-09-29 20:51:01] Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE [2019-09-29 20:51:01] Looking forward functions declaration in schema AAP. [2019-09-29 20:51:01] Looking at package pkg_administrative_area... [2019-09-29 20:51:02] Looking at package PKG_CONSTANT... [2019-09-29 20:51:02] Looking at package PKG_SEARCH... [2019-09-29 20:51:03] Looking at package PKG_SECURITY... [2019-09-29 20:51:03] Looking at package PKG_USER... [2019-09-29 20:51:03] Retrieving table information... [2019-09-29 20:56:17] [1] Scanning table ACTION_LOG (2912 rows)... [2019-09-29 20:56:17] [2] Scanning table ADDRESS (899 rows)... [2019-09-29 20:56:17] [3] Scanning table ADMINISTRATIVE_AREA (899 rows)... [2019-09-29 20:56:17] [4] Scanning table AREA (900 rows)... [2019-09-29 20:56:17] [5] Scanning table AREA_ADDRESS (900 rows)... [2019-09-29 20:56:17] [6] Scanning table AREA_CONTACT (84 rows)... [2019-09-29 20:56:17] [7] Scanning table AREA_NUMBER (900 rows)... :::::::::output trimmed:::::::: [2019-09-29 20:56:23] Dumping table ACTION_LOG... [2019-09-29 20:56:23] Dumping table ADDRESS... [2019-09-29 20:56:23] Dumping table ADMINISTRATIVE_AREA... [2019-09-29 20:56:23] Dumping table AREA... [2019-09-29 20:56:23] Dumping table AREA_ADDRESS... [2019-09-29 20:56:23] Dumping table AREA_CONTACT... :::::::::output trimmed:::::::: 2019-09-29 22:29:44] Trying to connect to database: dbi:Oracle:host=localhost;sid=inzmapdb;port=1521 [2019-09-29 22:29:44] Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE [2019-09-29 22:29:44] Retrieving partitions information... [2019-09-29 22:30:02] Looking how to retrieve data from ACTION_LOG... [2019-09-29 22:30:02] DEGUG: Query sent to Oracle: SELECT "ID","ACTION_NAME","ACTION_DATE","USERS_ID","IP_ADDRESS","HOST_NAME","ACTION_DATA","STATUS" FROM "AAP"."ACTION_LOG" a [2019-09-29 22:30:02] Fetching all data from ACTION_LOG tuples... [2019-09-29 22:30:02] DEBUG: number of rows 2912 extracted from table ACTION_LOG [2019-09-29 22:30:02] DEBUG: Formatting bulk of 10000 data (real: 2912 rows) for PostgreSQL. [2019-09-29 22:30:03] DEBUG: Creating output for 10000 tuples [2019-09-29 22:30:03] Dumping data from ACTION_LOG to file: output.sql [2019-09-29 22:30:03] Extracted records from table ACTION_LOG: total_records = 2912 (avg: 2912 recs/sec)[> ] 2912/113806 total rows (2.6%) - (1 sec., avg: 2912 recs/sec). [2019-09-29 22:30:03] Looking how to retrieve data from ADDRESS... [2019-09-29 22:30:03] DEGUG: Query sent to Oracle: SELECT "ID","NAME","STATUS" FROM "AAP"."ADDRESS" a [2019-09-29 22:30:03] Fetching all data from ADDRESS tuples... [2019-09-29 22:30:03] DEBUG: number of rows 984 extracted from table ADDRESS [2019-09-29 22:30:03] DEBUG: Formatting bulk of 10000 data (real: 984 rows) for PostgreSQL. [2019-09-29 22:30:03] DEBUG: Creating output for 10000 tuples [2019-09-29 22:30:03] Dumping data from ADDRESS to file: output.sql [2019-09-29 22:30:03] Extracted records from table ADDRESS: total_records = 984 (avg: 984 recs/sec)[2019-09-29 22:30:32] Total time to export data from 32 tables (0 partitions, 0 sub-partitions) and 113806 total rows: 48 wallclock secs (18.43 usr + 0.12 sys = 18.55 CPU) [2019-09-29 22:30:32] Speed average: 2370.96 rows/secImport to Postgres
import syntax like bellow
psql -U postgresql_username -d pg_database_name < export_file_name.sql
- Export from Oracle
[root@ora2pg ~]# su - postgres -bash-4.1$ psql postgres=# CREATE DATABASE testdb; postgres=# \c testdb testdb=# CREATE SCHEMA inzmapdb; [root@ora2pg ora2pg]# su - postgres -bash-4.1$ cd /etc/ora2pg/ -bash-4.1$ psql -U postgres -d testdb < TABLE_output.sql SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE :::::::::output trimmed:::::::: :::::::::output trimmed:::::::: ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE -bash-4.1$ psql -U postgres -d testdb < COPY_output.sql SET SET SET SET SET SET SET SET SET :::::::::output trimmed:::::::: SET SET SET SET SET SET SET SET SET SET SET SET SET SET WARNING: there is no transaction in progress COMMIT
Now, let’s check imported data:
postgres=# \c testdb testdb=# SELECT count(*) from inzmapdb.action_log ; count ------- 2912 (1 row)
Later I will add more details, to be continued…
