Tuesday, February 11, 2014

ETL : Moving Data From Oracle To PostgreSQL

  • Installing and configuring oracle instant client x86_64
  • Installing perl-DBD-Oracle
  • Installing and configuring ora2pg 
 
  1. Install and Configure Oracle Instant Client for linux
  2. I'm going to cover this step in quite a bit of detail; even though there are pre-built .rpm packages available from oracle for both i386 and x86_64 platforms, getting everything in running can be quite a mission if you omit a few vital steps...
    1. Download the Oracle Instant Client .rpm's from Oracle here
      Make sure you get the following :
      • oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
      • oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm
      • oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm

    2. Once you've downloaded the .rpm's, you can install them either with rpm or yum (I generally prefer yum as it will automatically handle resolving and installing dependencies for you).

      $ sudo yum -y localinstall oracle-instantclient*.rpm
    3. Update the ld library cache

    4. $ cat > /etc/ld.so.conf.d/oracle.conf <<EOF
      /usr/lib/oracle/11.2/client64/lib
      EOF

      $ ldconfig

    5. Set the Oracle environment variable

      $ cat >> /etc/bashrc <<EOF
      export TNS_ADMIN=$HOME
      export EDITOR=vim
      export ORACLE_HOME=/usr/lib/oracle/11.2/client64
      EOF

      $ source /etc/bashrc

    6. Place your tnsnames.ora file in your home directory
    7. You can test your oracle client installation by running sqlplus

      $ sqlplus64 /nolog
  3. Install Perl-DBD-Oracle

    1. Before we start ensure that you have the following packages installed
      • perl
      • perl-CPAN
      • perl-DBD
      • perl-DBI
      • gcc
      • libaio
      • libaio-devel

    2. Download the Perl-DBD-Oracle Source with CPAN


    $ perl -MCPAN -e shell
    CPAN> get DBD::Oracle
    CPAN> exit

    $ cd ~/.cpan/build/DBD-Oracle*
    $ perl Makefile.PL
    $ make
    $ make install

  4. Install and Configure ora2pg

    1. Download ora2pg here
    2. Uncompress the tar archive

      $ tar xjvf ora2pg*.tar.bz2 && rm ora2pg*.tar.bz2
    3. Build ora2pg

    4. $ cd ora2pg*
      $ perl Makefile.PL
      $ make && make install

    5. Configure ora2pg

    6. $ gedit /etc/ora2pg/ora2pg.conf
      Look out for the following configuration properties
      • ORACLE_HOME - you can set this to $ORACLE_HOME
      • ORACLE_DSN - set this to the desired entry in you tnsnames.ora
      • ORACLE_USER - the oracle username
      • ORACLE_PWD - the oracle password
      • USER_GRANTS - set this to 1 if you're not connecting as a DBA role
      • TYPE - set this to DATA if you only want to export data and ignore structure
    7. run ora2pg
    8. Simply run ora2pg from the command line, by default your data will be dumped to a file called output.sql in the current directory.

No comments:

Post a Comment