Oracle Database Gateway for PostgreSQL

An organization 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 organizations 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 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 .

The steps are:

• Install Oracle Transparent Gateway for PostgreSQL
• Create the ODBC data source
• Configure the gateway initialization parameter file
• Configure the listener
• Configure the Oracle database tnsnames.ora
• Creating DB link from Oracle database to PostGresSQL Server

Step 1 :

Create System DSN for PostgreSQL :

Go to Control Panel > Administrative Tools > ODBC Data Sources

Select PostgresSQL odbc driver and Click on Add Button

Click On Finish

Enter the ODBC Datasource name ( i gave PostgreSQL), Server Name (put the IP address of PostgreSQL DB server), Database Name (PostgreSQL DB name) and User Name (PostgreSQL userid) and password.
Now we have an ODBC Data Source called “PostgreSQL”.

Step 2 :
Now go to cd $ORACLE_HOME\hs\admin\
Update following lines to initdg4odbc.ora file :

# 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 = PostgreSQL #dsn name
HS_FDS_TRACE_LEVEL = off
HS_FDS_FETCH_ROWS = 1
set ODBCINI=C:\Windows\ODBC.INI
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

Step 3 :
Add following lines to listener.ora file :

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = dg4odbc)
(ORACLE_HOME=D:\oracle\product\11.2.0\dbhome_1)
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = admin-PC)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = D:\oracle

Step 4 : Restart listener

C:\Users\Administrator>LSNRCTL RELOAD

LSNRCTL for 32-bit Windows: Version 11.2.0.4.0 - Production on 06-NOV-2017 17:40:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Admin-PC)(PORT=1521)))
The command completed successfully

Step 5 :Check listener status

C:\Users\Administrator>LSNRCTL STATUS

LSNRCTL for 32-bit Windows: Version 11.2.0.4.0 - Production on 06-NOV-2017 17:40:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Admin-PC)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.4.0 - Production
Start Date 06-NOV-2017 10:02:01 Uptime 0 days 7 hr. 38 min. 13 sec
Trace Level off
Security ON: Local OS Authentication SNMP OFF
Listener Parameter File D:\oracle\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File D:\oracle\Administrator\diag\tnslsnr\Admin-PC\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Admin-PC)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 2 handler(s) for this service...
Service "dg4odbc" has 1 instance(s).
Instance "dg4odbc", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).

Append following lines to TNSNAMES.ora file :

dg4odbc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST =localhost)(PORT = 1521) )
(CONNECT_DATA =(SID = dg4odbc) )
(HS = OK)
)

Check tnsping :

[oracle@cbsdb01 admin]$ tnsping dg4odbc

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 04-NOV-2017 16:52:17
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = dg4odbc)) (HS = OK))
OK (10 msec)
[oracle@cbsdb01 admin]$

Create a Database link using above mentioned tnsname entry and PostgreSQL Credential :

SQL> CREATE DATABASE LINK pg CONNECT TO "postgres" IDENTIFIED BY "pg1234" USING 'dg4odbc';
Database link created.

Now fetch data using Database Link :

SQL> select count(*) from city@pg;

COUNT(*)
----------
48

SQL>

In next article we can configure Oracle Database Gateway for MySQL.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp