Configure heterogeneous connection from Oracle DB to PostgreSQL DB

An organisation have lot of choices to choose from the RDBMS world.Oracle Database, Microsoft SQL Server, IBM DB2,PostgreSQL, MySQL are few of them. Most of the organisations out there do have a mix of all these which are used to store their data and in course of time it is inevitable that they would want to transfer data from ‘this database’ to ‘that database’. Oracle, the uncrowned king of this RDBMS world call this “Heterogeneous Connectivity” and have incorporated this facility into their database and this is installed by default.

Oracle documentation says:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14232/tgvsgc.htm#sthref36

The Heterogeneous Services component in the Oracle database server talks to a Heterogeneous Services agent process which, in turn, talks to the non-Oracle system. We can conceptually divide the code into three parts:

1)The Heterogeneous Services component in the Oracle database server.
Most of the processing related to heterogeneous connectivity is done in this module.
2)Agent generic code.
This is code in the agent that is generic to all Heterogeneous Services products. This consists, for the most part, of code to communicate with the database and multithreading support.
3)The driver.
This is the module that communicates with the non-Oracle system. It is used to map calls from the Heterogeneous Services onto the native API of the non-Oracle system and it is non-Oracle system specific.

Connecting to PostgreSQL from Oracle using an ODBC data source

To connect to a PostgreSQL from Oracle, we will have to use the Oracle Generic Connectivity Agent(hsodbc).
A simple call would be like: Oracle DB -> Oracle Heterogeneous Service -> Oracle Generic Connectivity Agent -> ODBC Driver for PostgreSQL -> PostgreSQL DB
http://download.oracle.com/docs/cd/B19306_01/server.102/b14232/gencon.htm#i1005976

Oracle does not provide the ODBC driver for PostgreSQL, it should be acquired from third party. I downloaded the psqlOBDC driver from http://www.postgresql.org/download/products/2 .

1.PostgreSQL Server [PostgreSQL 8.3.5 on Windows]
1.1)Create the DB and user, which would be used by oracle to make connection.

2.Oracle Server [Oracle 10.2.0.1 on Windows]
2.1)Install Oracle(standard edition also will do)
2.2)Create a database
2.3)Go to $ORACLE_HOME/hs/admin/ and confirm the files
2.4)Download and install the psqlOBDC driver from postgresql.org
2.5)Create the ODBC data source
2.6)Configure initHSODBC.ora ($ORACLE_HOME/hs/admin/) and listener.ora and tnsnames.ora ($ORACLE_HOME/network/admin/)
2.7)Create the database link

This post will cover only the details to be done at the Oracle side.

2.1 – 2.3:
I installed oracle 10.2.0.1 standard edition and created a generic database.
The $ORACLE_HOME/hs/admin/ would contain the files inithsodbc.ora,inithsoledb.ora,listener.ora.sample and tnsnames.ora.sample.
The inithsodbc.ora is the one of our interest. We will configure it later.

2.4:
I downloaded the “psqlodbc_08_04_0100.zip”(the latest when i installed) from http://www.postgresql.org/ftp/odbc/versions/msi/ .
(I could not access this site using IE and hence used Firefox.)
The installation is nothing big the typical “click next button”

Click Next -> Accept the terms -> Next -> Install -> Finish

2.5:
Go to Control Panel -> Administrative Tools -> Data Sources -> System DSN -> Add

Select the “PostgreSQL ANSI” driver -> Finish.

Enter the ODBC Datasource name ( i gave PG), Server Name (put the IP address of PostgreSQL DB server), Database Name (PostgreSQL DB name) and User Name (PostgreSQL userid) and password.
Click “Test” to test the connection to PostgreSQL DB and it would show “Connection Succesful” and Save. Now we have an ODBC Data Source called “PG”.

2.6:
Now we have the datasource PG which would use the ODBC driver psqlODBC.
We need to configure 3 files to complete the connectivity
a)initHSODBC.ora
b)listener.ora
c)tnsnames.ora

a.1)
Go to $ORACLE_HOME/hs/admin/ and make a copy of the initHSODBC.ora and name it init<SID>.ora, where sid is the system identifier you want to use for the instance of the non-Oracle system to which the agent connects and i renamed the file to “initPG.ora“. Edit the file and provide the ODBC Data Source Name which we created in step 2.5.

# 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 = PG                        <<- ODBC Data Source Name
HS_FDS_TRACE_LEVEL = ON
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

b.1)

Listener.ora ($ORACLE_HOME/network/admin/)

 SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = PG)          <<- Should be same as the SID in $ORACLE_HOME/hs/admin/initSID.ora 
 (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
 (PROGRAM = hsodbc)       <<- Set the program as "hsodbc"
 )
 (SID_DESC =
 (SID_NAME = PLSExtProc)
 (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
 (PROGRAM = extproc)
 )
 )

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))        <<- Set key as PNPKEY
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.20)(PORT = 1521))
 )
)

Restart the listener.

The sid that you add to the listener.ora file must match the sid in an initsid.ora file, because the agent spawned by the listener searches for a matching initsid.ora file. When you copy and rename the initsid.ora file, ensure that it remains in the $ORACLE_HOME/hs/admin directory

c.1)

Add an entry for the PostgreSQL DB into the tnsnames.ora for the DB link to work.

 PG =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.20.20)(PORT=1521))
 (CONNECT_DATA=(SID=PG))
 (HS=OK)
 )

Confirm the “tnsping PG” works.

2.7:
Create the database link in oracle

 create public database link topg connect to "postgreuser" identified by "password" using 'PG';

Confirm the DBlink to PostgreSQL using this query.

 select * from all_tables@topg

Unlike Oracle, PostgreSQL is case sensitive. So for user tables(other than all_tables) use the double quotes like

 select * from "tbl_user_data"@topg;
 

References:

http://www.databasejournal.com/features/oracle/article.php/10893_3442661_2/Making-a-Connection-from-Oracle-to-SQL-Server.htm

http://birijan.com.np/?q=Oracle+Heterogeneous+Services+(Accessing+ODBC+datasource)

Advertisements

14 thoughts on “Configure heterogeneous connection from Oracle DB to PostgreSQL DB

  1. Pingback: Index « My confrontations with oracle

  2. Hi!

    Tested and working. Thanks.

    I use the Oracle 10g Express Edition and de PostgreSQL 8.4. And I have no problems.

    Thanks.
    Bruno.

  3. Hi!
    I’ve got one Linux RHEL4 server with the Oracle 10.2.0.4 in it, and another Linux RHEL4 server with PostgreSQL 8.2.4 in it.
    I’ve installed DG4ODBC (oracle gateway) and unixODBC in the Oracle server. When I try to installpsqlodbc-08.04.0100 running ‘./configure’ in the Oracle server, it needs the pg_config file to exist. How am I supposed to have this file on the Oracle server?
    What can I do?
    Thanks a lot!
    Daniela.

  4. Hi,

    I am having oracle database running on linux and postgres database running on windows. Please elaborate the step 2.4 and let me know the link for psqlOBDC driver in linux OS.

    Regards,

    Raj

  5. thanks a lot……….. you have saved my life 🙂 …………. i didn’t know about postgres….. with your help i understood and learnt a lot

  6. Awesome tutorial.

    I did everything on Oracle 11.2 and Postgres 9.1 .

    Earlier, I’s getting below error:

    SQL> select * from “student”@topg;
    ERROR at line 1:
    ORA-28545: error diagnosed by Net8 when connecting to an agent
    Unable to retrieve text of NETWORK/NCR message 65535
    ORA-02063: preceding 2 lines from TOPG

    Resolution:
    Make sure that listener.ora file has the only SID_LIST_LISTENER description.

    #The content of listener.ora at my machine is:

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\Oracle\parvinder.singh\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = “EXTPROC_DLLS=ONLY:C:\Oracle\parvinder.singh\product\11.2.0\dbhome_1\bin\oraclr11.dll”)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = C:\Oracle\parvinder.singh

  7. Please ignore comment # 9 (submitted by me, it contains bad content of listener.ora):

    The corrent contents of listener.ora are as follow:

    # listener.ora Network Configuration File: C:\Oracle\parvinder.singh\product\11.2.0\dbhome_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PG)
    (ORACLE_HOME = C:\Oracle\parvinder.singh\product\11.2.0\dbhome_1)
    (PROGRAM = dg4odbc)
    )
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\Oracle\parvinder.singh\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    )

  8. We have an Oracle 11.2.0.2 database running on sun solaris sparc version 10 and postgresql on windows. Where do we configure the odbc DSN?

  9. Hello again,

    We have oracle database running in version 11.2.0.2 on unix sparc version 10 and Postgresql on windows. Where and how do we complete steps 2.4 and 2.5 (where do we download the driver and create DSN because these are windows based steps you have shown and our environment is unix). Thanks a lot.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s