Posts Tagged ‘Gateway’

Configuring Oracle Gateway 10.2 for ODBC for PostgreSQL

February 4, 2010

I’ll describe here how to configure an Oracle ODBC gateway to connect to a postgres database. As it turns out, it’s fairy straightforward – if it works ūüôā With 10.2 these are three simple steps and voila ūüėČ With 11.1 there are very similar steps, but so far I didn’t manage to make it work :).

The process involves three steps and it’s good to complete and test them one at a time, so that any¬†occurring¬†problems are easier to locate and fix.

#1. Check connectivity with the database

Ensure that we can connect to the database with a native client:

[root@ora ~]# psql -h 10.132.28.183 -p 5432 -d callcenter -U cc
Welcome to psql 8.3.1 (server 8.1.11), the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
WARNING: You are connected to a server with major version 8.1,
but your psql client is major version 8.3. Some backslash commands,
such as \d, might not work properly.

callcenter=>

So this works. Most common problems here could be firewalls not allowing the connection or missing configuration entry in the pg_hba.conf

#2. Configure and check ODBC

Next we have to configure ODBC connectivity. I’m using unixODBC package supplied with Centos 5. It’s system-wide configuration is in /etc/odbc.ini and I’ll be using that file, but if you don’t have access to it, you can use ~/.odbc.ini in oracle owner home directory. Actually you can use any filename as long as you configure Oracle to use it too, but only these two are seen by isql and operating system ODBC tools.

We add a  section describing our datasource:

/etc/odbc.ini

[cctest]
Driver = PostgreSQL
Database = callcenter
Servername = 10.132.28.183
Port = 5432
ReadOnly = No
Username = cc
Password = xxxxx

We could also use the ODBCConfig tool from unixODBC-kde to configure it “the clicky way”. Anyway, now we can test if ODBC works:


[root@ora ~]# isql cctest
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

And it works. At least for me – I really don’t have any troubleshooting experience with ODBC, because it worked for me out-of-the-box, but if something is wrong at this point, recheck the config file and if it’s ok – strace is your friend – this tool can sometimes provide some answers really quickly.

#3. Configure Oracle Gateway

Configuring Oracle is also a three step process:

  • create initSID.ora file for the agent
  • setup a¬†listener and a service
  • add a TNS entry pointing to our listener

First I’ll show it for the 10.2 engine and then point out the differences between 11.1.

Create initSID.ora

In $ORACLE_HOME/hs/admin we create initSID.ora file, where SID is the name for an instance of Heterogeneous Services Agent that will serve out ODBC datasource. It doesn’t have to be the same as datasource name, but here it is for simplicity sake.

$ORACLE_HOME/hs/admin/initcctest.ora

HS_FDS_CONNECT_INFO = cctest
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so
set ODBCINI=/etc/odbc.ini

The HS_FDS_CONNECT_INFO however points to our datasource and so it has to be the same as in /etc/odbc.ini and HS_FDS_SHAREABLE_NAME should point to the actual driver.

Configure the listener

Next we add a new listener and a service (we can also use an existing listener if we want)

$ORACLE_HOME/network/admin/listener.ora

LISTENERHS =
        (ADDRESS_LIST =
                (
                        ADDRESS= (PROTOCOL=tcp)
                        (HOST = ora)
                        (PORT = 1526)
                )
        )

SID_LIST_LISTENERHS =
        (SID_LIST =
                (SID_DESC =
                        (SID_NAME = cctest)
                        (ORACLE_HOME = /u00/oracle/product/10.2.0/db_1)
                        (PROGRAM = hsodbc)
                )
        )

The SID_NAME parameter must match the SID from initSID.ora file, not necessarily the name of a datasource

And now start the listener:

oracle@ora[DB25]:/u00/oracle/product/10.2.0/db_1/network/admin> lsnrctl start listenerhs 

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 04-LUT-2010 21:41:02

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Starting /u00/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.3.0 - Production
System parameter file is /u00/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u00/oracle/product/10.2.0/db_1/network/log/listenerhs.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora)(PORT=1526)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=ora)(PORT=1526))
STATUS of the LISTENER
------------------------
Alias                     listenerhs
Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date                04-LUT-2010 21:41:02
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u00/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u00/oracle/product/10.2.0/db_1/network/log/listenerhs.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora)(PORT=1526)))
Services Summary...
Service "cctest" has 1 instance(s).
  Instance "cctest", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@ora[DB25]:/u00/oracle/product/10.2.0/db_1/network/admin>

Add a TNS entry

And all that’s left is to add a line to tnsnames.ora pointing to our listener:

cctest = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora) (PORT = 1526)) (CONNECT_DATA = (SID = cctest)) (HS = OK))

For Heterogeneous Services to work, the HS = OK part must be present.

Now we can create a database link to a Postgres  database:

CREATE PUBLIC DATABASE LINK cctest CONNECT TO "cc" IDENTIFIED BY "xxxxx" USING 'cctest';

And now we can do SELECT * from “sometable”@cctest;

User and object names must be quoted. I’ll explain why at a later time.