In this post, we are going to see what a heterogeneous connection is and how to set up a connection from Oracle to PostgreSQL via ODBC (open database connectivity).
Nowadays, lots of companies are using a mix of relational database management systems (RDBMS) on their data center — for example, some instances are running on Oracle and other instances are running on PostgreSQL, and if they want to access data (or even migrate) from X database (e.g., PostgreSQL) to Y database (e.g., Oracle), they’ll need to do it using a heterogeneous connection. Heterogeneous connections allow us to query data from non-Oracle databases using SQL.
System configuration
Centos 7.X (where X is the latest minor release)
1. Install PostgreSQL v11
Download the Repository package from yum.postgresql.org for Centos7 64bit machine.
Connect to root user and perform these steps:
- rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
- yum clean all
- yum makecache
- yum install epel-release
- yum install postgresql11*
- yum install unixODBC*
1.1. Initialize the cluster and start the service
You need to connect to a non-root user to perform the following steps:
-bash-4.2$ ./initdb -D /tmp/data
-bash-4.2$ ./pg_ctl -D /tmp/data/ start
1.2 Create a database user / table
-bash-4.2$ ./psql -p 5432 postgres
psql (11.6)
Type "help" for help.
postgres=# create user a with password 'a' superuser;
CREATE ROLE
postgres=# create table t(n int);
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# select * from t;
n
---
1
(1 row)
2. Install Oracle 11g
2.1. Download Oracle 11g Express edition (rpm) from Oracle website
- unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
- rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
- /etc/init.d/oracle-xe configure
2.2. Connect to Oracle database and create a Super user
[root@tushar-ldap-docker bin]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/
[root@tushar-ldap-docker bin]# ./sqlplus sys/edb@xe as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 4 14:43:47 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create user dblink identified by dblink ;
User created.
SQL> grant dba to dblink;
Grant succeeded.
3. Edit the “odbcinst.ini” file
The file resides in the /etc directory. Add the following entry:
[pg]
Description = PG
Driver = /usr/pgsql-11/lib/psqlodbc.so
Setup = /usr/lib64/libodbcpsqlS.so
Driver64 = /usr/pgsql-11/lib/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
4. Run “isql -v pg”
This will test DSN’s connectivity for PostgreSQL database.
[root@tushar-ldap-docker bin]# isql -v pg
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
5. Edit the ‘odbc.ini’ file.
This file resides in the /etc directory. Add the following entry:
[pg]
Description = pg
Driver = /usr/pgsql-11/lib/psqlodbc.so
ServerName = localhost
Username = a
Password = a
Port = 5432
Database = postgres
Trace = yes
TraceFile = /tmp/odbctrace.txt
Note: If this file is not present then you can create this file your own.
6. Run “isql”
This will test DSN’s connectivity for PostgreSQL database.
[root@tushar-ldap-docker bin]# isql -v pg
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from t;
+------------+
| n |
+------------+
| 1 |
+------------+
SQLRowCount returns 1
1 rows fetched
7. Create a “initpg.ora” file
This should reside under $ORACLE_HOME/hs/admin.
[root@tushar-ldap-docker bin]# cd /u01/app/oracle/product/11.2.0/xe/hs/admin/
[root@tushar-ldap-docker admin]# touch initpg.ora
Add the values below in the “initpg.ora” file:
HS_FDS_CONNECT_INFO = pg
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/pgsql-11/lib/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
set ODBCINI=/etc/odbc.ini
Save this file.
8. Edit the “listener.ora” file
This should reside under $ORACLE_HOME/network/admin.
Add these following contents for the SID_LIST_LISTENER parameter:
(SID_DESC =
(SID_NAME = pg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = dg4odbc)
)
Now, the “listener.ora” file should look like this:
“listener.ora” Network Configuration File:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = pg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
9. Edit the “tnsnames.ora” file
This should reside under $ORACLE_HOME/network/admin.
Add the following contents:
pg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = pg)
)
(HS=OK)
)
Now, the “tnsnames.ora” file should look like this:
“tnsnames.ora” Network Configuration File:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
pg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = pg)
)
(HS=OK)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
10. Reload the listener
Connect to “lsnrctl” and fire “reload” command:
[root@tushar-ldap-docker bin]# pwd
/u01/app/oracle/product/11.2.0/xe/bin
[root@tushar-ldap-docker bin]# ./lsnrctl
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-FEB-2020 16:58:35
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
The command completed successfully
LSNRCTL> exit
11. Verify using “tnsping”
“tnsping” is an Oracle utility to test if the listener is available or not.
[root@tushar-ldap-docker bin]# ./tnsping pg
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 04-FEB-2020 17:01:03
Copyright (c) 1997, 2011, 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 = pg)) (HS=OK))
OK (10 msec)
12. Connect to the Oracle database and create a database link to the PostgreSQL database
SQL> create database link test connect to "a" identified by "a" using 'pg';
Database link created.
SQL> select * from "t"@test;
n
----------
1
Hope it helps!