database, unix

install ora2pg on mac osx

Posted by LeOn on

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:

  1. Oracle Basic
  2. Sqlplus
  3. sdk

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:

export LD_LIBRARY_PATH=/Users/liha/develop/instantclient_12_1 export ORACLE_HOME=/Users/liha/develop/instantclient_12_1

Edit 11/23/2020, According to Alessandro Rodi, the LD_LIBRARY_PATH cannot be set on MacOS any more, need to use the DYLD_LIBRARY_PATH

install DBI/DBD

Now we need to install DBI/DBD stuff that is required during the ora2pg Makefile phase, especially the DBD::Oracle. 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
…………
[/code]

Check module

Now we can check whether the dependencies are correctly installed by running a perl script.

 #!/usr/bin/perl
use ExtUtils::Installed;
my $inst = ExtUtils::Installed->new();
print join "\n",$inst->modules();

result should look like something:

➜  ora2pg perl test.pl                                                                                                                                                                              12:48:46
Bundle::NetSNMP
DBD::Oracle
DBI
Ora2Pg
Perl⏎

install ora2Pg

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:

perl Makefile.PL
make && make install

After installation, the default conf file will be under etc/ora2pg/ora2pg.conf.

ora2Pg config

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

ORACLE_PWD      password

You should set as below to connect and retrieve required schema data and objects

USER_GRANTS     1

I also turned debug on to view progress

DEBUG           1

Other settings used were

EXPORT_SCHEMA   1

SCHEMA          schema_owner_name

CREATE_SCHEMA   1

I also used the same schema name in PG

PG_SCHEMA       schema_owner_name

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

NLS_LANG       AMERICAN_AMERICA.UTF8

NLS_CHAR        LATIN1

Now you can run the dump:

ora2pg -c myOra2pg.conf -t TABLE -o tables.sql

另外一个中文文章

Leave a comment