How to create database link between PostgreSQL 9.3 to Oracle XE 11 - (SQL/MED Management External Data - Foreign Data Wrapper for Oracle)

1. Introduction

1.1. Summary

This post shows how to create a database link between PostgreSQL vs Oracle using 'oracle_fdw' a PostgreSQL Extension for Foreign Data Wrapper for Oracle.

1.2. Scenario

• Oracle Linux x86_64 server: 

  - DNS Name: oracle.mydomain.com
  - IP: 10.1.1.2
  - Oracle database instance SID=prod

• PostgreSQL Linux x86_64 server

  - DNS Name: postgresql.mydomain.com
  - IP: 10.1.0.21
  - PostgreSQL database = prod

1.3. Cookbook

• Connected to Oracle,  create user for database link and grant appropriated permission
• Connected to PostgreSQL server, install Oracle Instant Client packages and SDK Header files
• Connected to PostgreSQL server, install PostgreSQL headers and PGXS from development packages
• Connected to PostgreSQL server, install 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle
• Connected to PostgreSQL server, cofigure 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle
• Connected to PostgreSQL server, test dblink connection using 'oracle_fdw'

2. Step-by-Step

2.1. Connect to Oracle and create user for database link and grant appropriated permission

a) Create Oracle user used by PostgreSQL dblink connection

$ sqlplus '/ as sysdba'
SQL> CREATE USER dblink_postgres IDENTIFIED BY "dblink_postgres";

User created.


b) Grant connection to dblink-user to appropriated Oracle database instance

SQL> GRANT CONNECT TO dblink_postgres;

Grant succeeded.


c) Grant to dblink-user appropriated database objects permissions

SQL> DECLARE
BEGIN
  FOR rLoop IN
    (
 SELECT CASE 
               WHEN object_type IN ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
                 THEN
                   'EXECUTE' 
               ELSE
                   'SELECT' 
             END privilege, 
        owner, 
object_name 
 FROM   all_objects
 WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
 AND    owner IN ( 'OWNER_01', 'OWNER_02', 'OWNER_03' )
 ORDER  BY owner, object_name
) LOOP
 dbms_output.put_line( '- ' || rLoop.privilege || ' - ' || rLoop.owner || ' - ' || rLoop.object_name );
 BEGIN
        EXECUTE IMMEDIATE 'GRANT ' || rLoop.privilege || ' ON ' || rLoop.owner || '.' || rLoop.object_name || ' TO dblink_postgres';    
 EXCEPTION
   WHEN OTHERS THEN
 dbms_output.put_line( '- * FAILED!');
 END;
    END LOOP;
END;
/

PL/SQL procedure successfully completed.

SQL> 


d) Disconnect and Connect with dblink-user to test permissions given

SQL> CONNECT dblink_postgres/dblink_postgres
Connected.


e) Describe an existing table and select some data only for test

SQL> desc sys.dual

Name  Type        Nullable Default Comments 
----- ----------- -------- ------- -------- 
DUMMY VARCHAR2(1) Y                         

2.2. Connect to PostgreSQL server, install and configure "Oracle Instant Client" and "Instant Client Package - SDK Additional header files" and "Instant Client Package SQL*Plus"

a) Download appropriated Instant Client package for Linux. See http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html for details. In my case, I will download and install Oracle Version "11.2.0.4.0" for: i) "Instant Client for Linux x86-64"; ii) "Instant Client Package - SDK: Additional header files"; iii) "Instant Client Package - SQL*Plus".

# mkdir /opt
# cd    /opt
#
# echo "Download Oracle products here ..."
#


b) Unzip Oracle Instant Client and Oracle ODBC driver and execute install/update script

# cd    /opt
# unzip instantclient-basic-linux.x64-11.2.0.4.0.zip
# unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
# unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip


c) Configure environment variables for Oracle

#
# vim ~/.bash_profile
ORACLE_HOME=/opt/instantclient_11_2
LD_LIBRARY_PATH=/opt/instantclient_11_2
PATH=$PATH:$HOME/bin:$ORACLE_HOME
export PATH
export ORACLE_HOME
export LD_LIBRARY_PATH
#
# echo "Logout/Re-Login ..."
# logout


d) Test Oracle Client Connection

# sqlplus dblink_postgres/dblink_postgres@oracle-homolog:1521/XE

2.3. Install PostgreSQL headers and PGXS from development packages

a) Check if packages 'postgresql*-devel*' is installed

# yum list installed 'postgres*'
Installed Packages
postgresql93.x86_64                      9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-contrib.x86_64              9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-devel.x86_64                9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-libs.x86_64                 9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-server.x86_64               9.3.5-2PGDG.rhel7               @pgdg93


b) Check if PostgreSQL Extension Development Environment Variables 'pg_config' is configured on PATH if not configure it

# echo "Check ..."
# pg_config
-bash: pg_config: command not found
#
# echo 'Not configured! So where is "pg_config" binary ?'
# find / -name pg_config
/usr/pgsql-9.3/bin/pg_config
#
# echo 'Configure PATH on .bash_profile ...'
# vim ~/.bash_profile
ORACLE_HOME=/opt/instantclient_11_2
LD_LIBRARY_PATH=/opt/instantclient_11_2
PG_CONFIG=/usr/pgsql-9.3/bin
PATH=$PATH:$HOME/bin:$ORACLE_HOME:$PG_CONFIG
export PATH
export ORACLE_HOME
export LD_LIBRARY_PATH

2.4. Install 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle.

The 'oracle_fdw' is a PostgreSQL extension and uses the Extension Building Infrastructure "PGXS". You can download 'oracle_fdw' from http://pgxn.org/dist/oracle_fdw/

a) Check pre-requisites: i) PostgreSQL Development Package; ii) PATH environment variable point to 'pg_config'; iii) Oracle Client or Instant Client software installed; iv) Oracle ORACLE_HOME environment variable is set; v) Oracle LD_LIBRARY_PATH is set; vi) GNU Compiler Collection C/C++

# yum list installed 'postgres*' | grep 'postgresql' | grep 'devel'
postgresql93-devel.x86_64                9.3.5-2PGDG.rhel7               @pgdg93
# pg_config | head -n 1
BINDIR = /usr/pgsql-9.3/bin
# pg_config --pgxs
# echo $ORACLE_HOME
/opt/instantclient_11_2
# echo $LD_LIBRARY_PATH
/opt/instantclient_11_2
# yum list installed 'gcc*'
Error: No matching Packages to list
# which gcc
/usr/bin/which: no gcc in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/opt/instantclient_11_2:/usr/pgsql-9.3/bin)
# echo 'Install gcc'
# yum install gcc
Installed:
  gcc.x86_64 0:4.8.2-16.2.el7_0

Dependency Installed:
  cpp.x86_64 0:4.8.2-16.2.el7_0  glibc-devel.x86_64 0:2.17-55.el7_0.5  glibc-hea

Complete!


b) Download 'oracle_fdw'

# mkdir /opt
# cd    /opt
#
# echo "Download oracle_fdw here ..."


c) Unzip 'oracle_fdw'

# cd    /opt
# unzip oracle_fdw-1.2.0.zip


d) Create symbolic link for Oracle library referenced by 'oracle_fdw' and add Oracle to Linux Shared Lib

$ cd /opt/instantclient_11_2/
$ ln -s libclntsh.so.11.1 libclntsh.so
$ ln -s libocci.so.11.1   libocci.so
$ ln -s libsqora.so.11.1  libsqora.so
$
$ echo /opt/instantclient_11_2 > /etc/ld.so.conf.d/oracle.conf
$ ldconfig 


e) Change directory to 'oracle_fdw' and Make and Make install

# cd /opt/oracle_fdw-1.2.0/
# pg_config --pgxs # Last check if pg_config is correctly configured
# make
# make install


f) Check Shared Library Dependency

# pg_config | grep PKGLIBDIR
PKGLIBDIR = /usr/pgsql-9.3/lib
# ls -l /usr/pgsql-9.3/lib/oracle_fdw*
-rwxr-xr-x 1 root root 433239 Mar  4 10:08 /usr/pgsql-9.3/lib/oracle_fdw.so
# ldd /usr/pgsql-9.3/lib/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007fff3f3fe000)
        libclntsh.so.11.1 => /opt/instantclient_11_2/libclntsh.so.11.1 (0x00007fb3a5e9a000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fb3a5ad1000)
        libnnz11.so => /opt/instantclient_11_2/libnnz11.so (0x00007fb3a5704000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fb3a5500000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fb3a51fd000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb3a4fe1000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fb3a4dc8000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fb3a4bc5000)

        /lib64/ld-linux-x86-64.so.2 (0x00007fb3a8a27000)

2.5. Cofigure 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle

a) Restart PostgreSQL service

# systemctl stop postgresql-9.3.service ; systemctl start postgresql-9.3.service


b) Create PostgreSQL Extension, Server and Mapping

$ psql db_pgsql
db_pgsql=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
db_pgsql=# CREATE SERVER oraprod FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle-homolog.inmetrics.com.br/prod.inmetrics.com.br');
CREATE SERVER
db_pgsql=# GRANT USAGE ON FOREIGN SERVER oraprod TO postgres;
GRANT
db_pgsql=# CREATE USER MAPPING FOR postgres SERVER oraprod OPTIONS (user 'dblink_postgres', password 'dblink_postgres');
CREATE USER MAPPING

2.6. Test dblink connection using 'oracle_fdw'

db_pgsql=# select oracle_diag();

                                      oracle_diag

--------------------------------------------------------------------------------
--------
 oracle_fdw 1.2.0, PostgreSQL 9.3.5, Oracle client 11.2.0.4.0, Oracle server 11.
2.0.3.0
(1 row)

db_pgsql=# CREATE FOREIGN TABLE ft_dual (
           dummy character varying(1) NOT NULL
           ) SERVER oraprod OPTIONS (schema 'SYS', table 'DUAL');
db_pgsql=# 
db_pgsql=# SELECT * FROM ft_dual;
 dummy
-------
 X
(1 row)
db_pgsql=# \q

3. References

• http://pgxn.org/dist/oracle_fdw/
• https://www.youtube.com/watch?v=Sz69kdItkPw
• http://stackoverflow.com/questions/2708317/libclntsh-so-11-1-cannot-open-shared-object-file