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.
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
ora2pg-22.1.tar.gz, I cd-ed into that directory and read the README file, then I tried to install with:
perl Makefile.PLfollowed by
make && make installThe above failed because of lack of sudo privilleges, so
sudo make && sudo make installsucceeded.
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
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).