How to configure heterogeneous connections from Oracle to PostgreSQL

January 19, 2023

SUMMARY: This article discusses heterogeneous connections and how to set  up a connection from Oracle to PostgreSQL using one.

 

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!

 

 

Share this

Relevant Blogs

How to run hierarchical queries in Oracle and PostgreSQL

.summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article introduces hierarchical queries and shows the differences in their usage between Oracle and PostgreSQL. 1. Hierarchical query 2. Hierarchical query in Oracle 3. Displaying...
January 24, 2023

More Blogs

Switchover/Failover and Session Migration

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to...
January 23, 2023