Wednesday, 30 September 2020

PostgreSQL Ora2Pg : Migrate Oracle to Postgres

Ora2Pg is Free Software written and maintained by Gilles Darold. Its an Ideal for large migration projects.

 

3 Reasons to move to PostgreSQL :

  • Reduce Infra & Business Costs
  • Reduction of license costs to ZERO
  • Reduction of support costs by 80% or more

How it works: https://ora2pg.darold.net/start.html


Installing pre-requisites:

  • Install DBI (database interface module)
  • Install DBD::Oracle and DBD::Pg  
  • Oracle Enterprise Linux 6.5
  • Oracle 11.2 database
  • ora2pg latest version


Download the latest version of ora2pg from http://sourceforge.net/projects/ora2pg/ and run the following commands

Perl module DBD::Oracle will used for connectivity to an Oracle database from Perl DBI.

 

Install Ora2pg

# tar -xvf ora2pg-18.0.tar
# cd ora2pg-18.0/
# perl Makefile.PL
# make
# make install

 

Validate the installation / Check the version of ora2pg

[root@oracleasm1 ora2pg_mig]# ora2pg -t SHOW_VERSION -c /var/lib/pgsql/pg10/data/ora2pg_new.conf

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

 

Configuration for Ora2Pg

 The default configuration file for Ora2Pg will be under /etc/ora2pg/ora2pg.conf

Let us go thru the four parameters and configure them.

Note : For defining which objects we want to export, we must add object type names after TYPE keyword

[root@oracleasm1 ora2pg_mig]# ora2pg -c /etc/ora2pg/ora2pg.conf

vi /etc/ora2pg/ora2pg.conf

ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1

ORACLE_DSN dbi::Oracle:host=oracleasm1.localdomain;sid=orcl;port=1521

ORACLE_USER system

ORACLE_PWD manager

TYPE TAPE PACKAGE INSERT COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION QUERY DBLINK SYNONYM LOAD

Where, ORCL is database name.


If you want to call another configuration file, just give the path as a command-line argument:

[root@oracleasm1 ora2pg_mig]# ora2pg -c /etc/ora2pg/ora2pg.conf

 

 

To Initialize a new project :

[root@oracleasm1 ~]# /usr/local/bin/ora2pg --init_project ora2pg_mig

[root@oracleasm1 ora2pg_mig]# ls -ltr ./config/ora2pg.conf
-rw-r--r-- 1 root root 58173 Sep 14 13:11 ./config/ora2pg.conf

 

Generate an Estimate report

[root@oracleasm1 ora2pg_mig]# ora2pg -t show_report  --estimate_cost -c /etc/ora2pg/ora2pg.conf --dump_as_html > /tmp/ora2pg.html
[========================>] 4/4 tables (100.0%) end of scanning.
[========================>] 6/6 objects types (100.0%) end of objects auditing.

 

Above will generate an html report which will give an estimate. The report looks good to me. Now, To copy the data from Oracle to PostgreSQL, first we will Export objects under specified schema from Oracle, then Import to PostgreSQL and finally copy the data.

There will be .sql file created in the current directory (of your project) which will have converted data into PostgreSql.

[root@oracleasm1 ora2pg_mig]# ./export_schema.sh
[========================>] 1/1 tables (100.0%) end of scanning.
[========================>] 5/5 objects types (100.0%) end of objects auditing.

Running: ora2pg -p -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
[========================>] 1/1 tables (100.0%) end of scanning.
[========================>] 1/1 tables (100.0%) end of table export.

Running: ora2pg -p -t PACKAGE -o package.sql -b ./schema/packages -c ./config/ora2pg.conf
[========================>] 0/0 packages (100.0%) end of output.

Running: ora2pg -p -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf
[========================>] 0/0 views (100.0%) end of output.
Running: ora2pg -p -t GRANT -o grant.sql -b ./schema/grants -c ./config/ora2pg.conf
WARNING: Exporting privilege as non DBA user is not allowed, see USER_GRANT

Running: ora2pg -p -t SEQUENCE -o sequence.sql -b ./schema/sequences -c ./config/ora2pg.conf
[========================>] 0/0 sequences (100.0%) end of output.

Running: ora2pg -p -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf
[========================>] 0/0 triggers (100.0%) end of output.

Running: ora2pg -p -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf
[========================>] 0/0 functions (100.0%) end of functions export.

Running: ora2pg -p -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf
[========================>] 0/0 procedures (100.0%) end of procedures export.

Running: ora2pg -p -t TABLESPACE -o tablespace.sql -b ./schema/tablespaces -c ./config/ora2pg.conf

WARNING: Exporting tablespace as non DBA user is not allowed, see USER_GRANT

Running: ora2pg -p -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf
[========================>] 0/0 partitions (100.0%) end of output.

Running: ora2pg -p -t TYPE -o type.sql -b ./schema/types -c ./config/ora2pg.conf
[========================>] 0/0 types (100.0%) end of output.

Running: ora2pg -p -t MVIEW -o mview.sql -b ./schema/mviews -c ./config/ora2pg.conf
[========================>] 0/0 materialized views (100.0%) end of output.

Running: ora2pg -p -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf
[========================>] 0/0 dblink (100.0%) end of output.

Running: ora2pg -p -t SYNONYM -o synonym.sql -b ./schema/synonyms -c ./config/ora2pg.conf
[========================>] 0/0 synonyms (100.0%) end of output.

Running: ora2pg -p -t DIRECTORY -o directorie.sql -b ./schema/directories -c ./config/ora2pg.conf
[========================>] 0/0 directory (100.0%) end of output

Running: ora2pg -t PACKAGE -o package.sql -b ./sources/packages -c ./config/ora2pg.conf
[========================>] 0/0 packages (100.0%) end of output.

Running: ora2pg -t VIEW -o view.sql -b ./sources/views -c ./config/ora2pg.conf
[========================>] 0/0 views (100.0%) end of output.

Running: ora2pg -t TRIGGER -o trigger.sql -b ./sources/triggers -c ./config/ora2pg.conf
[========================>] 0/0 triggers (100.0%) end of output.

Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions -c ./config/ora2pg.conf
[========================>] 0/0 functions (100.0%) end of functions export.

Running: ora2pg -t PROCEDURE -o procedure.sql -b ./sources/procedures -c ./config/ora2pg.conf
[========================>] 0/0 procedures (100.0%) end of procedures export.

Running: ora2pg -t PARTITION -o partition.sql -b ./sources/partitions -c ./config/ora2pg.conf
[========================>] 0/0 partitions (100.0%) end of output.

Running: ora2pg -t TYPE -o type.sql -b ./sources/types -c ./config/ora2pg.conf
[========================>] 0/0 types (100.0%) end of output.

Running: ora2pg -t MVIEW -o mview.sql -b ./sources/mviews -c ./config/ora2pg.conf
[========================>] 0/0 materialized views (100.0%) end of output.

 

To extract the data, use the following command 

[root@oracleasm1 ora2pg_mig]# ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

[========================>] 3999988/3999988 rows (100.0%) on total estimated data (5158 sec., avg: 558 recs/sec)

 

Load schemas to Postgres DB :

[root@oracleasm1 ora2pg_mig]# su - postgres

-bash-4.1$ psql
postgres=# CREATE DATABASE NEWDB ;
postgres=#\q

-bash-4.1$ cd ./ora2pg_mig
-bash-4.1$ psql -U postgresql_username -d NEWDB < data.sql

 

Find more about Postgres Administration here





No comments:

Post a Comment