November 14, 2024: PostgreSQL 17.1, 16.5, 15.9, 14.14, 13.17, and 12.21 Released!

Ora2Pg v23.1 released

Posted on 2022-02-14 by MigOps
Related Open Source

Antananarivo, Madagascar - February 10, 2022

Ora2Pg

Version 23.1 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 release fix several issues reported since past four months and adds some new major features and improvements.

  • Add use of greatest() / least() functions from new version of Orafce when required to return NULL on NULL input like Oracle.
  • ALLOW and EXCLUDE configuration values can now be read from a file. This is useful if you have a lot of table to filter.
  • Add possibility to use of System Change Number (SCN) for data export or data validation by providing a specific SCN. It can be set at command line using the -S or --scn option. You can give a specific SCN or if you want to use the current SCN at first connection time set the value to 'current'. Example of use:
        ora2pg -c ora2pg.conf -t COPY --scn 16605281
    You can also use the --scn option to use the Oracle flashback capability by specifying a timestamp expression instead of a SCN. For example:
        ora2pg -c ora2pg.conf -t COPY --scn "TO_TIMESTAMP('2021-12-01 00:00:00', 'YYYY-MM-DD HH:MI:SS')"
    or for example to only retrieve yesterday's data:
        ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"
  • Add json output format to migration assessment.
  • Add new TO_CHAR_NOTIMEZONE configuration directive to remove any timezone information into the format part of the TO_CHAR() function. Disabled by default. Note that the new default setting breaks backward compatibility, old behavior was to always remove the timezone part.
  • Add new configuration directive FORCE_IDENTITY_BIGINT. Usually identity column must be bigint to correspond to an auto increment sequence so Ora2Pg always force it to be a bigint. If, for any reason, you want Ora2Pg to respect the DATA_TYPE that was set for identity column then disable this directive.
  • Allow to export only invalid objects when EXPORT_INVALID is set to 2
  • Disable per partition data export when a WHERE clause is define on the partitioned table or that a global WHERE clause is defined.

There is also two new options to command ora2pg.

  • New command line option --lo_import. By default Ora2Pg imports Oracle BLOB as bytea, the destination column is created using the bytea data type. If you want to use large object instead of bytea, just add the --blob_to_lo option to the ora2pg command. It will create the destination column with data type Oid and will save the BLOB as a large object using the lo_from_bytea() function. The Oid returned by the call to lo_from_bytea() is inserted in the destination column instead of a bytea. This option can only be used with actions SHOW_COLUMN, TABLE and INSERT, action COPY is not supported. If you want to use COPY or have huge size BLOB ( > 1GB ) than can not be imported using lo_from_bytea() you can add option --lo_import to the ora2pg command. This will allow to import data in two passes:

  • 1) Export data using COPY or INSERT will set the Oid destination column for BLOB to value 0 and save the BLOB value into a dedicated file. It will also create a Shell script to import the BLOB files into the database using psql command \lo_import and to update the table Oid column to the returned large object Oid. The script is named lo_import-TABLENAME.sh

  • 2) Execute all scripts lo_import-TABLENAME.sh after setting the environment variables PGDATABASE and optionally PGHOST, PGPORT, PGUSER, etc. if they do not correspond to the default values for libpq. You might also execute manually a VACUUM FULL on the table to remove the bloat created by the table update.

    Limitation: the table must have a primary key, it is used to set the WHERE clause to update the Oid column after the large object import. Importing BLOB using this second method (--lo_import) is very slow so it should be reserved to rows where the BLOB > 1GB for all other rows use the option --blob_to_lo. To filter the rows you can use the WHERE configuration directive in ora2pg.conf.

  • New command line option --cdc_ready to use current SCN per table when exporting data and register them into a file named TABLES_SCN.log This can be used for Change Data Capture (CDC) tools.

For a complete list of change see https://github.com/darold/ora2pg/blob/master/changelog

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

Links & Credits

I would like to thank all users who submitted patches and users who reported bugs and feature requests, they are all cited the ChangeLog file.

Ora2Pg is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to ora2pg@darold.net.

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 into PostgreSQL compatible code.

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

Docs, Download & Support at http://www.ora2pg.com/