CREATING DATABASE LINK FROM ORACLE TO POSTGRES

Sometimes there are needs to fetch data from other different RDBMS.

Working with postgres, its easy to establish a database connection to oracle but the other round can involved a lot of steps to create a database from the oracle side to connect to postgres. And that is the purpose of this blog, to give you a step by step process to archive this.

Oracle supports heterogeneous services to allow data in non-Oracle database to be queried using SQL. This support has been in the form of transparent gateways, which are vendor specific, or generic connectivity which uses ODBC or OLEDB to make the connections. The functionality supported by generic connectivity is typically more limited than that possible when using vendor specific gateways, but it is quick and simple to configure. To read more, click here

DEMO ENVIRONMENT

Postgres server: 192.168.91.128

Oracle Database: 192.168.91.151

I PERFORMED THE BELOW ON MY POSTGRES SERVER

-bash-4.2$ psql
psql.bin (10.15.24)
Type "help" for help.

edb=# 
edb=# create database benstore owner benson;
CREATE DATABASE
edb=# 
edb=# \c benstore 
You are now connected to database "benstore" as user "enterprisedb".
benstore=# 
benstore=# create table account as select * from pg_description;
SELECT 4899
benstore=# 
benstore=# create table fin as select * from pg_description;
SELECT 4899
benstore=# 
benstore=# \dt
            List of relations
 Schema |  Name   | Type  |    Owner     
--------+---------+-------+--------------
 public | account | table | enterprisedb
 public | fin     | table | enterprisedb
(2 rows)

benstore=# 

BELOW STEPS IN MY ORACLE SERVER

–I INSTALL ODBC

[root@primary ~]# yum install postgresql-odbc
vi /etc/odbc.ini

[benstore]
Description = benstore
Driver = /usr/lib64/psqlodbc.so
ServerName = 192.168.91.128
Username = benson
Password = postgres
Port = 5432
Database = benstore
[Default]
Driver = /usr/lib64/liboplodbcS.so.2
[oracle@primary ~]$ 
[oracle@primary ~]$ exit
logout
[root@primary ~]# su - oracle
Last login: Wed Dec  2 20:43:31 EST 2020 on pts/0
-bash-4.2$ cat .bash_profile 
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=benson
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
-bash-4.2$ 
-bash-4.2$ 
-bash-4.2$ cd $ORACLE_HOME/hs/admin
-bash-4.2$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/hs/admin
-bash-4.2$ ll
total 16
-rw-r--r--. 1 oracle oinstall 1109 Jul  7  2014 extproc.ora
-rw-r--r--. 1 oracle oinstall  489 Jul  7  2014 initdg4odbc.ora
-rw-r--r--. 1 oracle oinstall  411 Jul 15 13:41 listener.ora.sample
-rw-r--r--. 1 oracle oinstall  244 Jul 15 13:41 tnsnames.ora.sample
-bash-4.2$ 
-bash-4.2$ cp initdg4odbc.ora initbenstore.ora 
-bash-4.2$ 
-bash-4.2$ vi initbenstore.ora
-bash-4.2$ 
-bash-4.2$ cat initbenstore.ora |grep -v ^#

HS_FDS_CONNECT_INFO = benstore
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so

set ODBCINI=/etc/odbc.ini
-bash-4.2$ 
-bash-4.2$ cd $ORACLE_HOME/
-bash-4.2$ cd network/admin/
-bash-4.2$ ll
total 28
-rw-r--r--  1 oracle oinstall 337 Dec  2 19:31 listener2012027PM3103.bak
-rw-r--r--  1 oracle oinstall 335 Dec  2 19:31 listener.ora
drwxr-sr-x. 2 oracle oinstall  64 Jul 15 13:27 samples
-rw-r--r--. 1 oracle oinstall 373 Oct 31  2013 shrept.lst
-rw-r--r--  1 oracle oinstall 195 Dec  2 19:31 sqlnet2012027PM3103.bak
-rw-r--r--. 1 oracle oinstall 195 Jul 15 13:50 sqlnet.ora
-rw-r--r--  1 oracle oinstall 332 Dec  2 19:31 tnsnames2012027PM3103.bak
-rw-r-----. 1 oracle oinstall 332 Jul 15 14:16 tnsnames.ora

-bash-4.2$ vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = benson)
 )
  (SID_DESC =
    (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
    (ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib:/usr/lib")
    (SID_NAME = benstore)
    (PROGRAM = dg4odbc)
  )
 )


lsnrctl stop
lsnrctl start
lsnrctl status

Services Summary...
Service "benson" has 2 instance(s).
  Instance "benson", status UNKNOWN, has 1 handler(s) for this service...
  Instance "benson", status READY, has 1 handler(s) for this service...
Service "bensonXDB" has 1 instance(s).
  Instance "benson", status READY, has 1 handler(s) for this service...
Service "benstore" has 1 instance(s).
  Instance "benstore", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$ vi tnsnames.ora 

BENSON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.151)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = benson)
    )
  )

benstore =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.151)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = benstore)
    )
    (HS=OK
  )
-bash-4.2$ tnsping benstore

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 02-DEC-2020 21:16:40

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter file
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.91.151)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = benstore)) (HS=OK))
OK (0 msec)
-bash-4.2$ 

-bash-4.2$ 
-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 2 21:17:38 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create public database link ben_link connect to "benson" identified by "postgres" using 'benstore';

Database link created.

SQL> 

—-Next go to postgres and allow conection in the pg_hba.conf file

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             192.168.91.128/32       md5    
host    all             all             192.168.91.151/32       md5
host    all             all             127.0.0.1/32            md5


-bash-4.2$ 
-bash-4.2$ psql -U benson -d benstore
psql.bin (10.15.24)
Type "help" for help.

benstore=# 
benstore=# \dt
            List of relations
 Schema |  Name   | Type  |    Owner     
--------+---------+-------+--------------
 public | account | table | enterprisedb
 public | dept    | table | benson
 public | emp     | table | benson
 public | fin     | table | enterprisedb
 public | jobhist | table | benson
(5 rows)

benstore=# select * from emp;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)

benstore=# 
benstore=# select count(*) from account ;
 count 
-------
  4899
(1 row)

benstore=# select count(*) from emp ;
 count 
-------
    14
(1 row)

benstore=# select count(*) from dept ;
 count 
-------
     4
(1 row)

benstore=# 

—-BACK ON ORACLE SERVER, WE CAN HOW TEST OUR CONNECTION

-bash-4.2$ 
-bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 2 21:43:07 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 
SQL> select count(*) from "public"."emp"@ben_link;

  COUNT(*)
----------
	14

SQL> select count(*) from "public"."account"@ben_link;

  COUNT(*)
----------
      4899

SQL> select count(*) from "public"."dept"@ben_link;

  COUNT(*)
----------
	 4

SQL> 

SQL> select * from "public"."emp"@ben_link;

     empno ename
---------- ------------------------------------------------------------
job							      mgr hiredate
------------------------------------------------------ ---------- ---------
       sal	 comm	  deptno
---------- ---------- ----------
      7369 SMITH
CLERK							     7902 17-DEC-80
       800		      20

      7499 ALLEN
SALESMAN						     7698 20-FEB-81
      1600	  300	      30

     empno ename
---------- ------------------------------------------------------------
job							      mgr hiredate
------------------------------------------------------ ---------- ---------
       sal	 comm	  deptno
---------- ---------- ----------

      7521 WARD
SALESMAN						     7698 22-FEB-81
      1250	  500	      30

      7566 JONES
MANAGER 						     7839 02-APR-81

     empno ename
---------- ------------------------------------------------------------
job							      mgr hiredate
------------------------------------------------------ ---------- ---------
       sal	 comm	  deptno
---------- ---------- ----------
      2975		      20

      7654 MARTIN
SALESMAN						     7698 28-SEP-81
      1250	 1400	      30

      7698 BLAKE

     empno ename
---------- ------------------------------------------------------------
job							      mgr hiredate
------------------------------------------------------ ---------- ---------
       sal	 comm	  deptno

SO AS SEEN, WE CAN FETCH THE DATA AND ALSO EVEN DO DML OR UPDATES AS WELL

About the author

Avatar photo

bensonyerima

Hi, I'm Benson Yerima, a database administrator with an obsession for all things tech. This blog is dedicated for helping people learn about database technology.

View all posts