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 184.108.40.206.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 220.127.116.11.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 18.104.22.168.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 22.214.171.124.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