Creating a dblink from Oracle to PostgreSQL

Advanced How-tos Beginner

Amit Sharma Sr. Manager, Technical Support

This article shows how to create a dblink connection from Oracle to EDB Postgres Advanced Server or PostgreSQL,

  1. Installation
  2. Connection
  3. Testing connectivity 

 

There are several reasons where we might need to use both Oracle and PostgreSQL databases in a business environment. One common scenario is to use existing Oracle infrastructure for the front end and then PostgreSQL for reporting purposes. This setup also helps us evaluate PostgreSQL without taking risks with the production environment and make a path forward to future migration and reducing costs.

For this tutorial, we will be using EDB Postgres Advanced Server, but the steps for achieving a dblink from Oracle to PostgreSQL are the same. 

 

Installation

The prerequisites include installing the ODBC driver from the EnterpriseDB website. It can easily be achieved by using the following command: 

 yum install edb-odbc

 

Or, if you are comfortable with the GUI installer, you can install using the stack builder as well:

Creating a dblink from Oracle to PostgreSQL

 

Configuration

The following steps will help you set up a  test case to create a dblink from an Oracle database to EDB Postgres Advanced Server:

Step 1:

Configure the odbcinst.ini file, which has all the information related to drivers. 

[oracle@Oracle11g admin]$ cat /etc/odbcinst.ini



[enterprisedb]

Description=PostgresPlus Advanced Server ODBC driver

Driver=/opt/PostgresPlus/connectors/odbc/lib/edb-odbc.so

Setup=/opt/PostgresPlus/connectors/odbc/lib/libodbcedbS.so

UsageCount=1

 

Step 2:

Configure the odbc.ini file for the DNS entries.

[oracle@Oracle11g admin]$ cat /etc/odbc.ini

[edb]

Driver=enterprisedb

Description=Connection to LDAP/POSTGRESQL

Servername=localhost

Port=5444

Protocol=7.4

FetchBufferSize=99

Username=enterprisedb

Password=edb

Database=edb

ReadOnly=no

Debug=1

Trace = yes

CommLog=1

UseDeclareFetch=0

TraceFile=/tmp/sql.log

UseServerSidePrepare=1

dbms_name=PostgreSQL

 

Step 3:

Check the DNS connectivity.

[root@Oracle11g enterprisedb]# isql -v edb

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

 

Step 4:

Create the file "initedb.ora" in location $ORACLE_HOME/hs/admin with the contents below. 

# This is a sample agent init file that contains the HS parameters that are

# needed for an ODBC Agent.

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = edb

HS_FDS_TRACE_LEVEL = 4

HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log

HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

 

NOTE: Make sure your init<sid>.ora file looks like init<DNS Name in odbc.ini>.ora. Avoid the uppercase while assigning the DNS names.

 

Step 5:

Configure the $ORACLE_HOME/network/admin/listener.ora file with the following contents.

[oracle@Oracle11g admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.



LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.35.193)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )



SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=orcl)

      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME=orcl))



(SID_DESC =

      (SID_NAME = edb)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (PROGRAM = dg4odbc)

 )

)



ADR_BASE_LISTENER = /u01/app/oracle

 

Step 6:

Restart the Listener Service. 

->lsnrctl stop all

->lsnrctl start

->lsnrctl status



Service "edb" has 1 instance(s).

  Instance "edb", status UNKNOWN, has 1 handler(s) for this service…

 

Step 7:

Configure the $ORACLE_HOME/network/admin/tnsnames.ora file as shown below.

[oracle@Oracle11g admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.



orcl =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

      (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

edb = (DESCRIPTION=

                      (ADDRESS=(PROTOCOL=tcp)

                               (HOST=172.24.35.86)

                               (PORT=5444)

                      )

                      (CONNECT_DATA = (SID = edb)

                      )

                      (HS = OK)

              )

 

Step 8:

Test the "tnsping" to the new HS's given SID.

-> tnsping edb



Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SID = edb)) (HS=OK))

OK (0 msec)

 

Step 9:

Create a dblink as shown below.

-> create public database link toppas connect to "enterprisedb" identified by "edb" using 'edb';

 

Testing Connectivity

Test HS connectivity as shown below.

SQL> select * from "pg_database"@toppas;

datname                                                              datdba

---------------------------------------------------------------- ----------

  encoding datcollate

---------- ----------------------------------------------------------------

datctype                                                         datis datal

---------------------------------------------------------------- ----- -----

datconnlimit datlastsysoid datfrozenxid dattablespace

------------ ------------- ------------ -------------

datacl

--------------------------------------------------------------------------------

template1                                                                10

         6 en_US.UTF-8

en_US.UTF-8                                                      1     1

 

Amit SharmaSr. Manager, Technical Support