Recently we need to migrate our on prem oracle database into postgresql on aws RDS. Looks like ora2pg is a nice little tool to mitigate the problem though not perfect.
I am running OS X 10.10.5 Yosemite. It already comes with perl v5.18.2 as well as cpan for installing packages.
download/install Oracle client
The official document specifies quite vaguely about what is the oracle client libraries. Turns out we need to download 3 packages from oracle website, for my use case we have oracle 12c so my current version is 12.1.02. The packages you need are:
- Oracle Basic
Then we unzip them to the SAME directory like: /Users/liha/develop/instantclient_12_1 .
Now we need to set 2 env variables for installation:
Now we need to install DBI/DBD stuff that is required during the ora2pg Makefile phase, especially the
DBD:Pg is optional, only if you need to dump data/schema live rather than offline to file.
#perl –MCPAN –e shell
cpan> install DBI
cpan> install DBD::Oracle
cpan> install DBD::Pg
Now we can check whether the dependencies are correctly installed by running a perl script.
my $inst = ExtUtils::Installed->new();
print join "\n",$inst->modules();
result should look like something:
➜ ora2pg perl test.pl 12:48:46
now can start to install the real thing by download and extract the ora2Pg tar.bz2 and run its makefile.pl which will eventually generate our
makefile which we can make and install:
make && make install
After installation, the default conf file will be under
Now we can start config the ora2Pg by changing the default conf file or create a new conf file based on default conf. If you have multiple DB schemas to migrate or have multiple environments, it would be better to create different conf for each use case.
The important fields are:
ORACLE_DSN dbi:Oracle:host=hostname;sid=Oracle SID
ORACLE_USER schema_owner name
You should set as below to connect and retrieve required schema data and objects
I also turned debug on to view progress
Other settings used were
I also used the same schema name in PG
I did have some problems with characters not being copied across as required and had to use settings below to override defaults, on some occasions
Now you can run the dump:
ora2pg -c myOra2pg.conf -t TABLE -o tables.sql