Ora2Pg
Challenge:
- Install Perl 5.0 on Windows
- Install Oracle DBD libraries to Perl on Windows
- Tables structure should exist prior running the tool
- Oracle client "Administrator" pack to be installed on windows.
Pre-requisites:
- Download and install Perl on your Windows from "http://strawberryperl.com/"
- Install Oracle client on your windows from "www.oracle.com/downloads"
- Download ora2pg software and untar to a known location on windows from
“https://github.com/darold/ora2pg”
- Considered that PostgreSQL is pre-installed on your Windows machine. Else download it from "http://www.enterprisedb.com/products-services-training/pgdownload#windows"
Installation:
- In cmd on your windows. Make sure you have internet connection:
cd <un tarred location>\ora2pg-15.2
perl Makefile.PL
perl Makefile.PL
dmake && dmake install
- Set the environment variables:
Set ORACLE_HOME=<ORACLE_CLIENT_SOFTWARE_LOCATION>
Set LD_LIBRARY_PATH=<ORACLE_CLIENT_HOME>/lib
- Install DBD::Oracle libraries - Internet is must
perl -MCPAN -e "install DBD::Oracle"
- Configuration file is created by default in C:\ora2pg
- Copy the template config file to original file name and edit
Copy ora2pg_dist.conf to ora2pg.conf
Example:
Source Oracle Server Target PostgreSQL Server
Schema: HR Schema: HR
Source Schema Structure:
Objects
|
Count (object name)
|
Table
|
7
|
SEQUENCE
|
3
|
PROCEDURE
|
2
|
TRIGGER
|
2
|
INDEX
|
19
|
VIEW
|
1
|
1. TABLE COUNTRIES (owner: HR, 25 rows)
2. TABLE DEPARTMENTS (owner: HR, 27 rows)
3. TABLE EMPLOYEES (owner: HR, 107 rows)
4. TABLE JOBS (owner: HR, 19 rows)
5. TABLE JOB_HISTORY (owner: HR, 10 rows)
6.TABLE LOCATIONS (owner: HR, 23 rows)
7. TABLE REGIONS (owner: HR, 4 rows)
SQL> desc COUNTRIES;
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNTRY_ID NOT NULL CHAR(2)
COUNTRY_NAME VARCHAR2(40)
REGION_ID NUMBER
SQL> desc DEPARTMENTS;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
SQL> desc EMPLOYEES;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> desc JOBS;
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
SQL> desc JOB_HISTORY;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)
SQL> desc LOCATIONS;
Name Null? Type
----------------------------------------- -------- ----------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)
SQL> desc REGIONS;
Name Null? Type
----------------------------------------- -------- ----------------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)
Sample Configuration in ora2pg.conf
ORACLE_HOME c:\oracle\product64\11.2.0\client_1
ORACLE_DSN dbi:Oracle:host=<source server hostname>;sid=<mysid>;port=1521
ORACLE_USER <username>
ORACLE_PWD <password>
USER_GRANTS 0
#DEBUG 1
#EXPORT_SCHEMA 0
SCHEMA hr
CREATE_SCHEMA 0
COMPILE_SCHEMA 0
EXPORT_INVALID 0
PG_DSN dbi:Pg:dbname=<dbname>;host= <target server hostname>;port=5432
PG_USER postgres
PG_PWD *******
TYPE TABLE,VIEW,SEQUENCE,TRIGGER,FUNCTION,PROCEDURE,PACKAGE,SYNONYM,DBLINK,QUERY
STOP_ON_ERROR 0
#OUTPUT output.sql
DATA_LIMIT 15000
PG_SCHEMA hr
Configuration in tnsnames.ora
test11=
(DESCRIPTION=
ADDRESS=(PROTOCOL=tcp)(HOST=<Source Hostname>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=<mysid>)
Migrations Steps:
· First we need to create database in target database where we are going to migrate the schema.
· Then, we need to create a script for creating objects in target database.
Note: Here first we are going to create objects in target database then migrating data.
So, for creating script we need to change below parameter in ora2pg.conf file.
#PG_DSN dbi:Pg:dbname=<dbname>;host=<Target server>;port=5432
#PG_USER postgres
#PG_PWD *********
TYPE TABLE,VIEW,SEQUENCE,TRIGGER,FUNCTION,PROCEDURE,PACKAGE,SYNONYM,DBLINK,QUERY
OUTPUT output.sql
**Comment all PG_DSN, PG_USR and PG_PWD parameter and add OUTPUT output.sql for getting structure script.
· Run the ora2pg now:
c:\ora2pg>ora2pg -c ora2pg.conf
The script is generated in the output.sql file.
· Now create the structure in target database using output.sql
Note: exclude foreign keys constraints and add after migrating data.
· Then after creating structure in the target database now time to migrate data from oracle to PostgreSQL
For migrating data, we to again change in the config file.
PG_DSN dbi:Pg:dbname=<dbname>;host=<Target hostname>;port=5432
PG_USER postgres
PG_PWD *********
TYPE TABLE,VIEW,SEQUENCE,INSERT
#OUTPUT output.sql
**Uncomment all PG_DSN, PG_USR and PG_PWD parameter, add INSERT in the TYPE.
· Re-Run the ora2pg now:
c:\ora2pg>ora2pg -c ora2pg.conf
· Apply foreign keys to the target database from the generated script.
· To generated migration report run the below commands.
ora2pg -c ora2pg.conf -t SHOW_REPORT --estimate_cost --dump_as_html > report.html
Now can check on PostgreSQL Server.
Oracle
|
PostgreSQL
|
Supported
|
Table
|
Table
| YES |
Indexes
|
Indexes
| YES |
View
|
View
| YES |
Table Constraints
|
Table Constraints
| YES |
Grant or Revoke
|
Grant or Revoke
| YES |
Sequence
|
Sequence
| YES |
TABLESPACE
|
TABLESPACE
| YES |
TRIGGER
|
TRIGGER
| YES |
FUNCTION
|
FUNCTION
| YES |
PROCEDURE
|
FUNCTION
| YES |
INSERT
|
INSERT
| YES |
COPY
|
COPY
| YES |
PARTITION
|
PARTITION
| YES |
DLINK
|
DLINK
| YES |
FDW
|
FDW
| YES |
TYPE (Oracle user defined type)
|
TYPE(PostgreSQL)user defined type
| YES |
SYNONYM
|
SYNONYM
| YES |
DIRECTORY
|
DIRECTORY
| YES |
QUERY
|
QUERY
| YES |
PACKAGE’S
|
PACKAGE’S
| YES |