Connecting Oracle to PostgreSQL using Oracle Gateways

转自： http://www.toadworld.com/platforms/oracle/b/weblog/archive/2015/02/23/connecting-oracle-to-postgresql-using-oracle-gateways.aspx

Introduction:

In an enterprise business environment multiple RDBMS technologies may exists  and some point in time these Database systems may need to connect to each other  due to the business requirements.  In such situation you as DBA is responsible for establishing such Integration.
This document will provide  all steps required for Integrating Oracle Database with Open source PostgreSQL

Architecture:

Oracle Database supports heterogeneous  connectivity for non-oracle database sources. To establish such heterogeneous connectivity  Oracle database need Oracle Gateways and ODBC drivers.

There two types of ODBC drivers currently available unixODBC and Data Direct ODBC, In this article DataDirect ODBC drivers are used to establish connectivity between Oracle and PostgreSQL.

Environment Details:

2. Installation of Oracle Database gateways:

• Download 11gR2 Oracle Gateways software
• Upload the software on the server
• Set the proper display variable
• Execute the Installer

• Specify any Name for the database GW Home and make sure specified directory has read/write privileges

• Select only oracle Database Gateways for ODBC

• Execute root.sh script

I’ve skipped the network configuration wizard and configured listener manually after successful completion of Installation.

3. Installation of Progress Data Direct Software:

• Download and upload the Progress data direct software on the server where Oracle gateways for HS in Installed.  This software should be Installed by "root" user.

[root@TESTDB[/]#cd /oracle/11204_GW_SW/datadirect_sw
[root@TESTDB[/oracle/11204_GW_SW/datadirect_sw]#ls -ltr
total 344632
-rw-r--r--    1 oracle:oinstall        824 Jun 19 2013  install.mi
-rw-r--r--    1 oracle:oinstall       4093 Jun 19 2013  autorun.dat
-rwxr-xr-x    1 oracle:oinstall     170960 Jun 28 2013  unixmi.ksh
-rw-r--r--    1 oracle:oinstall  176271360 Sep 16 14:51 PROGRESS_DATADIRECT_CONNECT64_ODBC_7.1.4_AIX_64.tar
drwxr-xr-x    3 oracle:oinstall        256 Sep 16 15:21 aix
drwxr-xr-x    4 oracle:oinstall        256 Sep 16 15:21 etc
[root@TESTDB[/oracle/11204_GW_SW/datadirect_sw]#kshunixmi.ksh
Progress DataDirect Connect for ODBC Setup is preparing....

English has been set as the installation language.

Log file : /tmp/logfile.7471192.1
----------------------------------------------------------------------
Progress DataDirect Connect (R) and Connect XE for ODBC 7.1 SP3
for UNIX operating systems
----------------------------------------------------------------------

The following operating system has been detected:

AIX version 6.1
Is this the current operating system on your machine (Y/N) ? [ Y ] yes
Is this the current operating system on your machine (Y/N) ? [ Y ] y
Enter YES to accept the above agreement : YES
Please enter the following information for proper registration.

In the Key field, enter either EVAL or the Key provided.

Name          :  zaheer
Company       :  oralabs
Serial Number :  EVAL
Key           :  EVAL

You can install drivers for all supported databases or
install a single driver.

Drivers for All Supported Databases includes
one driver for each supported database.  In the case
of Oracle, DataDirect Connect64 for ODBC 7.1 SP3 has two
available drivers that support the database--a
wire protocol driver that does not require any database client
software to connect to the database, and a client-based driver
that does require client software.  By default, the Oracle Wire
Protocol driver is installed.

Single Driver allows you to choose one from
any of the DataDirect Connect64 for ODBC 7.1 SP3 drivers, including the
client-based Oracle driver.

If you want to evaluate more than one single
driver, you will have the opportunity to do
so after completion of the initial installation.

1.   Drivers for All Supported Databases
2.   Single Driver
Please enter an option [1]:2


1.   Apache Hive Wire Protocol (XE)
2.   DB2 Wire Protocol
3.   Greenplum Wire Protocol (XE)
4.   Impala Wire Protocol (XE)
5.   Informix Wire Protocol
6.   MySQL Wire Protocol
7.   Oracle Wire Protocol
8.   PostgreSQL Wire Protocol
9.   Progress OpenEdge Wire Protocol
10.  Salesforce (XE)
11.  SQL Server Wire Protocol
12.  Sybase Wire Protocol
13.  Sybase IQ Wire Protocol (XE)
14.  Teradata (XE)
15.  Oracle (client)
16.  SQL Server Legacy Wire Protocol

Please enter driver option:8

You have chosen to eval the PostgreSQL Wire Protocol driver.

To change this information, enter C. Otherwise, press Enter to continue. :
DataDirect Connect for ODBC Setup is preparing the installation.
Choose a temporary directory.

Enter the full path to the temporary install directory.[/tmp]:
Checking for available space...

There is enough space.
Extracting files...

Choose a destination directory.
Enter the full path to the install directory.[/opt/Progress/DataDirect/Connect64_for_ODBC_71]: /oracle/data_direct
Checking for available space...

There is enough space.
Extracting files...

Creating license file......


DataDirect Connect for ODBC Setup successfully removed all of the temporary files.


Thank you for using Progress DataDirect products.

Installation ended successfully.

Would you like to install another product (Y/N) ? [ Y ] N
Thank you for using Progress DataDirect products.

Installation ended successfully.

[root@TESTDB[/oracle/11204_GW_SW/datadirect_sw]#

4. Configure oracle network configuration in Oracle database Gateways Home:

In Oracle Gateways home configure the listener.ora file, this file will be responsible to provide connection  to non-oracle data sources (PostgreSQL)

• Configured listener.ora file

# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER2 =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1551))
 )

SID_LIST_LISTENER2=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=/oracle/11204_GW)
         (PROGRAM=dg4odbc)
         (ENVS=LD_LIBRARY_PATH=/oracle/data_direct/lib:/oracle/11204_GW/lib)
      )
  )

• Configured tnsnames.ora file

[oracle@TESTDB[/oracle/11204_GW/hs/admin]#cat tnsnames.ora
dg4odbc=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=testdb.oralabs.com)(PORT=1551))
            (CONNECT_DATA=(SID=dg4odbc))
            (HS=OK)
        )

[oracle@TESTDB[/oracle/11204_GW/hs/admin]#pwd
/oracle/11204_GW/hs/admin
[oracle@TESTDB[/oracle/11204_GW/hs/admin]#

• Configure Oracle DB gateways parameter file
  
  This file contains information about the source database name which you want to connect and the location of driver which is responsible for  communicating with Oracle heterogeneous service and establishing connection to a non-oracle data source

[oracle@TESTDB[/oracle/11204_GW/hs/admin]#cat initdg4odbc.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO =postgres_test
HS_FDS_TRACE_LEVEL = OFF
#HS_FDS_SHAREABLE_NAME = /oracle/data_direct/lib/odbc.so
HS_FDS_SHAREABLE_NAME = /usr/local/unixODBC/lib/libodbc.a
#
# ODBC specific environment variables
#
set ODBCINI=/oracle/data_direct/odbc.ini<--- For unixODBC use its Install loc.
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
[oracle@TESTDB[/oracle/11204_GW/hs/admin]#

• Set GW_HOME environment variables  and start up the above configured Listener.

• Environment file for GW home

[oracle@TESTDB[/oracle]#cat 11g_GW.env
export ORACLE_HOME=/oracle/11204_GW
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/oracle/11204_GW/network/admin
[oracle@TESTDB[/oracle]#

• Start GW listener:

[oracle@TESTDB[/oracle]#lsnrctl start LISTENER2
 LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 05-FEB-2015 10:18:31
 Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 Starting /oracle/11204_GW/bin/tnslsnr: please wait...
 TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
System parameter file is /oracle/11204_GW/network/admin/listener.ora
Log messages written to /oracle/11204_GW/log/diag/tnslsnr/TESTDB/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1551)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1551))
STATUS of the LISTENER
------------------------
Alias                     LISTENER2
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
Start Date                05-FEB-2015 10:18:34
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/11204_GW/network/admin/listener.ora
Listener Log File         /oracle/11204_GW/log/diag/tnslsnr/TESTDB/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1551)))
Services Summary...
Service "dg4odbc" has 1 instance(s).
  Instance "dg4odbc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@TESTDB[/oracle]#

5. Configure Driver file  for connecting PotgreSQL database:

• Change the highlighted parameters in progress data direct odbc.ini file

[ODBC Data Sources]
PostgreSQL Wire Protocol=DataDirect 7.1 PostgreSQL Wire Protocol
[ODBC]
IANAAppCodePage=4
InstallDir=/oracle/data_direct
Trace=0
TraceFile=odbctrace.out
TraceDll=/oracle/data_direct/lib/ddtrc27.so
[postgres_test]
Driver=/oracle/data_direct/lib/ddpsql27.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
AlternateServers=
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=postgres_test
DefaultLongDataBuffLen=2048
EnableDescribeParam=1
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=192.168.1.22
HostNameInCertificate=
InitializationString=
KeyPassword=
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=5433
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
TrustStore=
TrustStorePassword=
ValidateServerCertificate=1
XMLDescribeType=-10
[oracle@TESTDB[/oracle/data_direct]#

6. Create database link:

Create database link on testdb  - oracle database for connecting it to non-oracle PostgreSQL

create database link pgdbuatconnect to "erp" identified by "erp" using 'DG4ODBC';

7. Grant Required privileges to ERP user on postgres database objects:

• connect to postgres database using erp user:

[postgres@postgresdb01][ ~ ]# psql -d postgres_test -U erp
psql (9.3.4)
Type "help" for help.
postgres_test=>

select records from table 'test':

postgres_test=> select * from test;
  name
---------
 zaheer
 jareer
 beenish
(3 rows)

• Now connect to Oracle database and check database link:

[oracle@TESTDB[/oracle/R12/db/11.2.0]#sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 24 16:39:00 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from "test"@pgsql;

name
--------------------
zaheer
jareer
beenish

SQL>

Oracle Database and non-oracle source postgres has been successfully connected using database link.

8. Errors/Issues and solutions:

ORA-28500: connection from ORACLE to a non-Oracle

SQL> SELECT * FROM test@PGSQL;
SELECT * FROM test@PGSQL
                   *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-28541: Error in HS init file on line 21.
ORA-02063: preceding 2 lines from PGSQL

Solution:

• Make sure that there are valid entries of source database in file odbc.ini,listener.ora and tnsnames.ora
• Please refer above files for sample configuration

ORA-28500: [ODBC lib] Data source name not found and no default driver

SQL> select * from TEST@pgsql;
select * from TEST@pgsql
                   *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[DataDirect][ODBC PostgreSQL Wire Protocol driver]Socket closed.
{08S01}[DataDirect][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]FATAL: no
pg_hba.conf entry for host "192.168.1.22", user "erp", database
"postgres_test", SSL off(File auth.c;Line 486;Routine ClientAuthentication;)
{28000,NativeErr = 3732480}
ORA-02063: preceding 2 lines from PGSQL

Solution:

• Add entry for the oracle host  in pg_hba.conf file in oracle database server

##below entry added for oracle Integration
host    all             all             0.0.0.0/0                md5

Individual  IP can be provided to allow specific hosts in the above file. As this is a test system above entry will allow all IPS to connect to postgres database. Also make sure that there is no firewall configured in between these hosts.

ORA-00942: table or view does not exist

SQL>  select * from test@pgsql;
 select * from test@pgsql
               *
ERROR at line 1:
ORA-00942: table or view does not exist
[DataDirect][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: relation
"TEST" does not exist(Position 15;File parse_relation.c;Line 984;Routine
parserOpenTable;) {42S02,NativeErr = 6844177}[DataDirect][ODBC PostgreSQL Wire
Protocol driver][PostgreSQL]Failed transaction. The current transaction rolled
back. {42S02,NativeErr = 3624914}
ORA-02063: preceding 2 lines from PGSQL

Solution:

Use double quotes (" ")  for accessing postgres database using dblink from oracle database.

[oradev@TESTDB[/oradev/R12/db/11.2.0]#sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 24 16:39:00 2014
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from "test"@pgsql;

name
--------------------
zaheer
jareer
beenish

If you are creating synonym in oracle database for connecting  to postgres using database link, then also use double quotes in your code.

CREATE  SYNONYM APPS.test FOR "test"@pgdbsql;

Conclusion:

Oracle Gateways supports heterogeneous connectivity to non-oracle sources. For connecting to postgres database using dblink  its mandatory to use Oracle Database Gateways  with ODBC drivers. There are two drivers available which can be used with Oracle GW - HS. One is unixODBC and another is Progress data direct, unixODBC is opensource and free whereas data direct  is a licensed software.

In this article progress data direct is used. If you are using unixodbc drivers then all steps will be same just unixODBC library location should be changed in all required configuration files.