September 26, 2024: PostgreSQL 17 Released!

Ora2Pg 16 released

Posted on 2015-10-16 by Dalibo

Paris, France - October 16th, 2015

Ora2Pg 16 released

Version 16.0 of Ora2Pg, a free and reliable tool used to migrate an Oracle database to PostgreSQL, has been officially released and is publicly available for download.

This major release improve PL/SQL code replacement, fixes several bugs and adds some major new features:

  • Full migration of MySQL database, it just work like with Oracle database.
  • Full migration assessment report for MySQL database.
  • New script, ora2pg_scanner, to perform a migration assessment of all Oracle and MySQL instances on a network.
  • Add technical difficulty level in migration assessment.
  • Allow migration assessment on client queries extracted from AUDIT_TRAIL (oracle) or general_log table (mysql).
  • Ora2Pg has a "made in one night" brand new Web site (still need some work) http://ora2pg.darold.net/

Example of technical difficulty level assessment output using the sakila database with some more difficulties:

Total 83.90 cost migration units means approximatively 1 man-day(s). Migration level: B-5

Here are the explanation of the migration level code:

  • Migration levels:
  • A - Migration that might be run automatically
  • B - Migration with code rewrite and a human-days cost up to 5 days
  • C - Migration with code rewrite and a human-days cost above 5 days
  • Technical levels:
  • 1 = trivial: no stored functions and no triggers
  • 2 = easy: no stored functions but with triggers, no manual rewriting
  • 3 = simple: stored functions and/or triggers, no manual rewriting
  • 4 = manual: no stored functions but with triggers or views with code rewriting
  • 5 = difficult: stored functions and/or triggers with code rewriting

This is to help you to find the database that can be migrated first with small efforts (A and B) and those who need to conduct a full migration project (C).

This release has also some new useful features:

  • Export type SHOW_TABLE now shows additional information about table type (FOREIGN, EXTERNAL or PARTITIONED with the number of partition).
  • Connection's user and password can be passed through environment variables ORA2PG_USER and ORA2PG_PASSWD to avoid setting them at ora2pg command line.
  • Improve PL/SQL replacement on ADD_MONTH(), ADD_YEAR(), TRUNC(), INSTR() and remove the replacement limitation on DECODE().
  • Add detection of migration difficulties in views, was previously reserved to functions, procedures, packages and triggers.
  • Replace values in auto generated configuration file from command line options -s, -n, -u and -p when --init_project is used.
  • Adjust lot of scores following new functionalities in Ora2Pg, ex: autonomous transactions, dblink or synomyms are now more easy to migrate.

There is some new command line options to ora2pg script:

  • -m | --mysql : to be used with --init_project and -i option to inform ora2pg that we work with a MySQL format
  • -T | --temp_dir : option to be able to set a distinct temporary directory to run ora2pg in parallel.
  • --audit_user : option to set the user used in audit filter and enable migration assessment report on queries from AUDIT_TRAIL (oracle) or general_log table (mysql).
  • --dump_as_sheet and --print_header options to be able to compute a CSV file with all migration assessment from a list of oracle database.
  • --dump_as_csv option to report assessments into a csv file. It will not include comments or details, just objects names, numbers and cost.

Some change can break backward compatibility:

  • Change NULL_EQUAL_EMPTY to be disabled by default to force change in the application instead of transforming the PL/SQL.

This release adds some new configuration directives:

  • MYSQL_PIPES_AS_CONCAT: Enable it if double pipe and double ampersand (|| and &&) should not be taken as equivalent to OR and AND.
  • MYSQL_INTERNAL_EXTRACT_FORMAT: Enable it if you want EXTRACT() replacement to use the internal format returned as an integer.
  • AUDIT_USER: Set the comma separated list of user name that must be used to filter from the DBA_AUDIT_TRAIL or general_log tables.
  • REPLACE_ZERO_DATE: the "zero" date: 0000-00-00 00:00:00 is replaced by a NULL by default, use it to use the date of your choice. Useful when you have a NOT NULL constraint.
  • INDEXES_RENAMING: force renaming of all indexes using a name with tablename_columnsnames. Useful for database that have multiple time the same index name or that use the same name than a table.
  • HUMAN_DAYS_LIMIT: default to 5 days, used to set the number of human-days limit for migration of type C.

As usual there is also some bug fixes and Oracle to PostgreSQl conversion adjustments, for a complete list see the changelog.

Links & Credits

Thanks to all contributors, they are all cited in the changelog file.

Useful Links:

About Ora2Pg

Ora2Pg is an easy and reliable tool to migrate from Oracle to PostgreSQL. It is developed since 2001 and can export most of the Oracle objects (table, view, materialized view, tablespace, sequence, indexes, trigger, grant, function, procedure, package, partition, data, blob, external table, etc.).

Ora2Pg works on any platform and is available under the GPL v3 licence.

Docs, Download & Support at ora2pg.darold.net.