There are in general three possibilities for semi-automatic DB migration from Oracle to PostgreSQL.
First and second can be used together.
1. orafce lib http://pgfoundry.org/projects/orafce – provide some functionality like data types, views, tables to the standard PostgreSQL through recompile DB server from source codes
2. ora2pg perl script http://ora2pg.darold.net/ - provide base types conversions for Oracle and data migration.
3. Using EnterpriseDB PostgreSQL Plus Advanced Server and EnterpriseDB Migration Tools for migration. Need to buy Subscription Plan (http://www.enterprisedb.com/store/products/postgres-plus-advanced-subscriptions ), Basic subscription is enough.
1. Orafce lib. Extension to the standard PostgreSQL server 8.2 or later, written on C. Due to the nature of the extension, can be installed only on PostgreSQL, which installed from source codes.
Provide some widely used functions, tables (views) and packages identical to the Oracle functions and packages to help in migration process.
Functions – add_months, last_date, next_day, months_between, trunc, round, concat, nvl, nvl2, lnnvl, decode, bitand, nanvl, sinh, cosh, tanh.
Functions – similar but not fully compatible – oracle.substr.
Table – dual.
Packages – dbms_output, utl_file, dbms_pipe, dbms_alert, PLVdate, PLVstr, PLVchr, PLVsubst, dms_utility, dbms_assert, plunit, dbms_random.
Packages similar but not fully compatible – PLVlex.
Out of the scope – types, oracle plsql itself, other not ported functions, views, packages, etc.
2. Ora2pg perl script. Provide export schemes, functions, etc to the file with base conversions to the PostgreSQL format and follow manual work on.
Features:
Export full database schema (tables, views, sequences, indexes), with unique, primary, foreign key and check constraints, can convert schema to PostgreSQL schema (need manual work).
Export grants/privileges for users and groups (need manual work).
Export predefined functions, triggers, procedures, packages and package bodies (need manual work).
Export range and list Oracle partition (not supported by PostgreSQL directly).
Export full datas or following a WHERE clause (full support of Oracle BLOB object as PG BYTEA).
Export Oracle views as PG tables (need manual work to rewrite as Postgre views)
Export Oracle user defined types (need manual work).
Provide basic help for converting PLSQL code to PLPGSQL (still needs manual work)
Orafce & Ora2pg can and should work together, but anyway conversion is possible if Oracle DB not actively use oracle packages which not ported by orafce and not widely use user defined types and oracle specific data types. Anyway, migration from Oracle to PostgreSQL with using this two tools require a significant amount of work and application rewrite but it is possible.
3. Using EnterpriseDB PostgreSQL Plus Advanced Server and EnterpriseDB Migration Tools.
PostgreSQL Plus Advanced Server is a commercial version of PostgreSQL, developed by EnterpriseDB. One of the features of PostgreSQL Plus Advanced Server is Oracle compatible and Migration Tool to online or offline migration.
Other useful features of PostgreSQL Plus AS is
- xDB Replication Server (provide possibility of online integration between PostgreSQL and Oracle or MSSQL DB's)
- SQL/Protect - protects PostgreSQL data against multiple SQL virus injection vectors by automatically learning safe data access patterns and collecting attack data
- SQL Profiler – on-demand or scheduled traces
- Advanced Security – security audit (oracle like), granular objects permissions (row & columns), external authentication (kerberos, PAM, LDAP, SSPI, etc), code obfuscation (for stored procedures, functions, etc), Virtual Private Database (segregate users and other data)
- Enterprise Manager, Monitors, etc
Oracle compatibility of PostgreSQL Plus AS based on implementation on PostgreSQL side Oracle types, functions, packages, PLSQL.
PLSQL realized through special PostgreSQL language – edbspl
PostgreSQL Plus AS has over 100 packages, ported from Oracle, all major types and functions.
Oracle compatibility is is well documented - EnterpriseDB published document Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide_v91.pdf (where 9.1 is PostgreSQL version), where on 726 pages fully highlighted the issue of compatibility.
EnterpriseDB Migration Tool is a Java application which connect simultaneously to Oracle SID and PostgreSQL Plus AS and can provide online conversion of structure from one DB to another and then data migration.
I provide some tests for live migrations and describe it below. Environment:
Oracle – 11.2.0.3 x64 with installed test DB
PostgreSQL Plus AS – 9.1 x64
Migration Tool – EDB Migrationstudio 9.0.72-1
3.1. Simple variant is migration of HR - Human Resources schema, included in the Oracle Sample Schemas.
Tables – 7
Procedures – 2
Sequences – 3
Views – 1
All successfully converted to Postgresql, data migration successfully done too.
3.2. Very complex variant is migration of APEX_030200 schema - Part of the Oracle Application Express Suite, standard freeware software development environment.
Successfully ported:
Object Types: 2 out of 4
Constraints: 1233 out of 1245
Indexes: 570 out of 577
Triggers: 341 out of 366
Functions – 11 out of 12
Packages – 138 out of 189
Procedures – 7 out of 19
Sequences – 4 out of 4
Tables – 355 out of 359
Views – 15 out of 125
Synonyms – 428 out of 462
Typical troubles:
CREATE OR REPLACE type wwv_flow_tree_num_arr as table of number not null – not null not supported
CREATE TABLE WWV_FLOW_QB_SAVED_TABS (… LEFT VARCHAR2(255) …) - LEFT not supported
CREATE TABLE WWV_FLOW_SW_STMTS ( … OFFSET NUMBER …) - OFFSET not supported
CREATE procedure apex wrapped a000000 .. - wrapped not supported
CREATE VIEW WWV_FLOW_MONTHS_MONTH_TEMP (MONTH_DISPLAY,MONTH_VALUE) AS select to_char(to_date(to_char(rownum,'00'),'MM'),'Month') month, - syntax not supported at month
CREATE VIEW WWV_FLOW_USER_MAIL_ATTACHMENTS ( … where security_group_id = (select wwv_flow.get_sgid from dual where rownum = 1)) - missing FROM-clause entry for table "wwv_flow"
CREATE VIEW WWV_FLOW_VERSION ( … major_version||'.'||minor_version||'.'||patch_version version …) - syntax not supported at or near "version"
Most of troubles is simple and can be quickly fixed. Some troubles, especially in packages, triggers and so on is serious and need time to rewrite this objects in PostgreSQL stile.
But anyway using PostgreSQL Plus AS can save huge amount of time in case of migration complex Oracle schemes or provide quick and simple live migration in case of simple Oracle schemes.