Ora2pg Mirgation Tool




 Introduction


Ora2pg tool is open source tool used for migration of oracle databases to PostgreSQL database. This tools works at schema level, meaning that it does not convert/ migrate whole oracle database to PostgreSQL, instead it converts/ migrates one schema at a time. Hence if you have a database having four different schema/ users, you have to run this tool for each schema separately. 

Ora2pg can be used in variety of scenarios like severe engineering of oracle databases to migration of large scale databases, or migrating data of oracle tables to PostgreSQL. It is user friendly and does not require any expertise to migrate your oracle databases to PostgreSQL. It only requires some configurations in its two configuration files and then run couple of commands to migrate the oracle schema to PostgreSQL.

Features

  • Migrate schema which objects (tables, views etc).
  • Export partitions and their sub-partitions.
  • Export data of tables
  • Support for exporting Oracle BLOB objects as PG BYTEA data.
  • Has option of exporting the Oracle view as a table in PostgreSQL.
  • Provide some basic PLSQL code to PLPGSQL code conversion.
  • Support various platforms like Windows, Linux etc
  • Export materialized views.
  • Can generate detailed report about Migration
  • Can generate database migration cost assessment report.
  • Export Database links to Foreign Data Wrappers.
  • Export synonyms as views.
  • Can provide cost estimates for migrating PL/SQL Code from files.
ora2pg is very good tool for migrating oracle database schema to PostgreSQL database. Most of the migration it does on its own, but some manual intervention from DBA is also required at times. The Oracle specific PL/SQL code generated for functions, procedures, packages and triggers has to be reviewed to match the PostgreSQL syntax. 

INSTALLATION ON LINUX

All Perl modules can always be found at CPAN (http://search.cpan.org/). Just type the full name of the module (ex: DBD::Oracle) into the search input box, 
it will brings you the page for download.

Ora2Pg latest release can be downloaded from this page:

Requirements

The Oracle Instant Client or a full Oracle installation must be installed on the system. You can download the rpm package for Linux from Oracle. Once downloaded and available to your server, below commands can be used to install them

    rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
    rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
    rpm -ivh oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64.rpm
    rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

ora2pg Installation Method


Download the latest version (rpms) of ora2pg for linux from above mentioned website and ship it to postgresql server in /tmp. 
Login to server as postgres user and go to /tmp directory and run below commands to install ora2pg

$ cd /tmp
$ tar xjf ora2pg-23.2.tar.bz2
$ cd ora2pg-23.2/
$ perl Makefile.PL
$ su root
$ make
$ make install

Configurations for Oracle 19c

By default Ora2Pg will look to ora2pg.conf configuration file into /etc/ora2pg/ directory.
For connecting to oracle database, following configuration chabges are to be done in ora2pg.conf file

ORACLE_HOME     /u01/app/oracle/product/19.0.0
ORACLE_DSN      dbi:Oracle:host=[db_srvr_ip];sid=[ORACLE_SID]
ORACLE_USER     [SYSTEM]
ORACLE_PWD      [password_for_system]
USER_GRANTS     1

Once these above mentioned configurations are done,  you are ready to use ora2pg. Testing can be using below listed conmand:

# ./ora2pg t --SHOW_VERSION -c /etc/ora2pg/ora2pg.conf
Oracle Database 19c Enterprise Edition Release 19.10.0.0.0

<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-6554968143327802"
     crossorigin="anonymous"></script>

Comments

Post a Comment

Popular posts from this blog

How to Install PostgreSQL on Linux

   Installation of PostgreSQL on Linux In this article, you will learn how to install PostgreSQL on Linux using rpm packages. Most Linux platforms such as Debian, Oracle Linux, Red Hat / CentOS, SUSE, and Ubuntu have PostgreSQL integrated with their package management. It is recommended that you install PostgreSQL this way since it ensures a proper integration with the operating system including automatic patching and other update management functionality. In this tutorial, we’ll show you how to install PostgreSQL on Oracle Linux version 8.2 Installation using RPMS: Below URL can be used to download rpms of PostgreSQL version 15.1 for Linux platforms. https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8.2-x86_64/ For smooth installation, below listed rpms are required: postgresql15-devel-15.1-1PGDG.rhel8.x86_64.rpm  postgresql15-contrib-15.1-1PGDG.rhel8.x86_64.rpm  postgresql15-libs-15.1-1PGDG.rhel8.x86_64.rpm postgresql15-server-15.1-1PGDG.rhel8.x86_64.rpm...
Read more

Oracle vs PostgreSQL Comparison

   The War has began Oracle Database is a commercial, proprietary multi-model database management system produced by Oracle Corporation, and the largest relational database management system (RDBMS) in the world. While Oracle remains the #1 database on the market, its popularity has steadily declined by over 18% since 2013. What’s causing this massive shift? This all is happening due to rise of open source databases. One database in particular is causing a huge dent in Oracle’s market share – open source PostgreSQL. When comparing the two solutions, things to consider are: How easier to use it is, How easily you can setup and administer database How much this database fulfills your requirements How much performance efficient it is How much you have to pay for it How much support is available over the internet How much availability it gives to you Is your data secure inside this database And many more….. Below table compares both databases feature wise and gives a good hint to ...
Read more