Antananarivo, Madagascar - February 10, 2022
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.
--scnoption. 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
--scnoption 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')"
ora2pg -c ora2pg.conf -t COPY --scn "SYSDATE - 1"
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
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.
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 firstname.lastname@example.org.
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/