Heterogeneous DB Connection Between Oracle And Postgres Plus

Hi ,

Finally,i have configured the heterogeneous (HS) Database Connectivity between Oracle 11.2 and PPAS 9.1. I believe, the same steps will be suitable for the PostgreSQL as well. Please find the steps one by one .

Step 1:-
--------
We need to Configure the odbcinst.ini file which is having all the Drivers Information.
[enterprisedb]
Description=PostgresPlus Advanced Server ODBC driver
Driver=/opt/PostgresPlus/9.1AS/connectors/odbc/lib/edb-odbc.so
Setup=/opt/PostgresPlus/9.1AS/connectors/odbc/lib/libodbcedbS.so
UsageCount=1

Step 2:-
--------
We need to Configure the odbc.ini file which is having all the DNS entries.

[edb]
Driver=enterprisedb
Description=Connection to LDAP/POSTGRESQL
Servername=localhost   
Port=5444
Protocol=7.4
FetchBufferSize=99
Username=enterprisedb
Password=adminedb
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@CentOS62STM64bit ~]# isql -v edb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

If you are getting any error like "/opt/PostgresPlus/9.1AS/connectors/odbc/lib/edb-odbc.so", then do like "ldd *.so" file.
 
It will show some of the "*.so" files not found. Then fix the issue by downloading those binaries or soft linking the higer version to lower version files.
 
Step 4:-
--------
Create "initedb.ora" file in $ORACLE_HOME/hs/admin Location with the below contents.

# 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>

Make Sure, your init<sid>.ora file must like init<DNS Name in odbc.ini>.ora. Avoid the Uppercase while giving the DNS names.

Step 5:-
--------
Configure the $ORACLE_HOME/network/admin/listener.ora file with the below contents.

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = edb)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (PROGRAM = dg4odbc)
    )
   )
ADR_BASE_LISTENER = /home/oracle/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 as below.

# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
edb =
       (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521))
         (CONNECT_DATA =(SID = edb)
         )
    (HS=OK)
        )
Step 8:-
--------
Testing the "tnsping" to given new HS's 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:-
--------

Created a dblink as below.
-> create public database link toppas connect to "enterprisedb" identified by "adminedb" using 'edb';

Step 10:-
---------
Tested the HS connectivit as below.
SQL> select * from "pg_database"@topg;
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
 

--Dinesh