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:
![](http://www.toadworld.com/resized-image.ashx/__size/550x0/__key/communityserver-blogs-components-weblogfiles/00-00-00-00-25/sz_2D00_022315_2D00_2.png)
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.