using edbmtk to migrate oracle to postgres plus advanced server

this is merely a documenation post for myself as I always forgot the steps to get this working. as postgres plus advanced server 9.4 was released some days ago we wanted to do another poc for an oracle migration. using edbmtk was clearly the preferred way to do this as it automates most of the tasks. but how did we need to set this up the last time?

as a first step one needs to download the oracle jdbc driver for the java version available on the postgres server.

example:
ojdbc6.jar – for use with java 6
ojdbc7.jar – for use with java 7

put one of these under:

ls -la /etc/alternatives/jre/lib/ext/
total 5264
drwxr-xr-x.  2 root root    4096 Feb 12 12:54 .
drwxr-xr-x. 11 root root    4096 Jan 26 18:26 ..
-rw-r--r--.  1 root root   10075 Jan  9 02:39 dnsns.jar
-rw-r--r--.  1 root root  452904 Jan  9 02:48 gnome-java-bridge.jar
-rw-r--r--.  1 root root  558461 Jan  9 02:40 localedata.jar
-rw-r--r--.  1 root root     427 Jan  9 02:45 meta-index
-rw-r--r--.  1 root root 3698857 Feb 12 12:54 ojdbc7.jar
-rw-r--r--.  1 root root   69699 Jan  9 02:46 pulse-java.jar
-rw-r--r--.  1 root root  225679 Jan  9 02:41 sunjce_provider.jar
-rw-r--r--.  1 root root  259918 Jan  9 02:39 sunpkcs11.jar
-rw-r--r--.  1 root root   78194 Jan  9 02:42 zipfs.jar

the next step is to configure the toolkit.properties file:

cat /opt/PostgresPlus/edbmtk/etc/toolkit.properties
SRC_DB_URL=jdbc:oracle:thin:@[ORACLE_SERVER]:[LISTENER_PORT]:[DATABASE]
SRC_DB_USER=system
SRC_DB_PASSWORD=manager

TARGET_DB_URL=jdbc:edb://localhost:5432/[POSTGRES_DATABASE]
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=postgres

… and then kickoff the migration:

cd /opt/PostgresPlus/edbmtk/bin
./runMTK.sh -fastCopy -logBadSQL -fetchSize 10000 -loaderCount 6 -dropSchema true -useOraCase ORACLE_SCHEMA1,ORACLE_SCHEMA2,...

pretty easy. wait for edbmtk to finish and start fixing the objects that are invalid :)

btw: for migrations to pure community postgres take a look at ora2pg