1. installation and use of the Oracle to PostgreSQL migration script ora2pg
  2. ora2pg is a free tool (based on Perl) used to migrate an Oracle database to a PostgreSQL compatible schema. It is available here.

    In October 2021 I used it to migrate an Oracle database to PostgreSQL. In what follows I describe the installation of the tool, and then its usage.


    2. I downloaded the sources for version 22.1 from here.

      UPDATE a few days later in response to a bug report of mine, there was a bug fix which I had to get (but no new release). So I had to clone the repo locally and issue the perl Makefile.PL and make && make install commands I mention below from the repo rather than from the exploded tarball.

      The tools requires a version of Perl greater than 5.10 which I alread had:
      $ perl --version | head -2
      This is perl 5, version 30, subversion 0 (v5.30.0) built for x86_64-linux-gnu-thread-multi

      After exploding the tarball

      , I cd-ed into that directory and read the README file, then I tried to install with:
      perl Makefile.PL
      followed by
      make && make install
      The above failed because of lack of sudo privilleges, so
      sudo make && sudo make install

      I then tried to simply run ora2g but that failed with:

      Can't locate DBI.pm in @INC (you may need to install the DBI module ...
      So I installed a number of modules with:
      perl -MCPAN -e 'install DBD::Oracle'

      I then tried to install DBI with:

      perl -MCPAN -e 'install DBI::Perl'
      … but that failed with:
      Warning: Cannot install DBI::Perl, don't know what it is.
      … so I checked and verified that the DBI Perl module was already installed and thus, no need to install it from MCPAN:
      perl -MDBI -e 1
      perl -e 'use DBI'
      (both the above produced no output and exited with a status of 0).

      The next step was to install the DBD::Oracle Perl module but I had read that that requires installing the Oracle Instant Client. So I downloaded from the Oracle site the following zip files:

      I verified by experimenting that the SDK is indeed needed otherwise you get messages about missing oci.h file further down the road. To install the Oracle Instant Client I basically followed the instructions found on the Oracle site (here, local copy here). After exploding both zip files in the same directory I made a note to always prefix any command with LD_LIBRARY_PATH=/path/to/exploded/oracle/instant/client/zips as I didn't want to export that variable or put in some bashrc file.

      Following the successful installation of the Oracle Instant Client I was able to install the Oracle Perl DBD module with:

      perl -MCPAN -e 'install DBD::Oracle'

      Also, I do recall that at some point, either during the installation of the tool, or during the use of the tool to extract data I had to install libaio (Linux kernel asynchronous I/O):

      sudo apt install libaio-dev

    3. USAGE

    4. NB: In what follows it is assumed that the environmental variable LD_LIBRARY_PATH is properly set on the shell or its value is prefixed on each command. This variable must point to the directory where the Oracle Instant Client is located.

      I started by copying the ora2pg.conf file from its installation location (where it resides as ora2pg.conf.dist) locally:

      cp /etc/ora2pg/ora2pg.conf.dist ora2pg.conf

      I then edited the file to include (among other things) the coordinates to the Oracle database I wish to connect, and then tried some simple commands that don't do anything much:

              ora2pg -t SHOW_VERSION -c ora2pg.conf
              ora2pg -t SHOW_REPORT  -c ora2pg.conf

      (the usage of the tool is described, in detail, here)

      I then created a new project to extract the data from the Oracle database. I named the project xfiles:

      ora2pg -c ora2pg.conf --project_base . --init_project xfiles

      Once the project was created I cd into it and run the export_schema.sh script

      The above created all necessary DDL files, I then downloaded the data using:

      ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf
      … as of the time of this writing, the above command has been running for a couple of hours now. And there the matter rests (for now).