Aug 23 2016

Create Dblink From Oracle to PostgreSQL Database

Category: Administration,AdministrationFatih Acar @ 14:36

Since fetching data from PostgreSQL database to Oracle database, you can create database link between two database systems. For that you can apply below operations.

Test Systems

PostgreSQL DB
IP : 192.168.10.10
OS : Centos 7 x64
Service : postgresql.service

Oracle DB
IP : 192.168.10.20
OS : OEL 6 x64

dblinkoracletopostgresql

PostgreSQL Side

psql# create user dblinkuser encrypted password ‘dblinkuserpass’;

psql# grant usage on schema “testschema” to dblinkuser;

psql# grant select on “testschema”.tbl_test to dblinkuser;

Add new line to pg_hba.conf

vi /pgdir/data/pg_hba.conf

host testdb dblinkuser 192.168.10.20/32 md5

systemctl reload postgresql.service

Oracle Side

yum install postgresql-odbc

vi /etc/odbc.ini

[PG]
Description = PG
Driver = /usr/lib64/psqlodbc.so
ServerName = 192.168.10.10
Username = dblinkuser
Password = dblinkuserpass
Port = 5432
Database = testdb
[Default]
Driver = /usr/lib64/liboplodbcS.so

vi $ORACLE_HOME/hs/admin/initPG.ora

# 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 = PG
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

#
# ODBC specific environment variables
#

#
# Environment variables required for the non-Oracle system
#
#set =

vi $ORACLE_HOME/network/admin/tnsnames.ora

PG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.20)(PORT = 1521))
(CONNECT_DATA =
(SID = PG)
)
(HS = OK)
)

vi $GRID_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=PG)
(ORACLE_HOME=/oracle/db/11.2.0/db_home)
(PROGRAM=dg4odbc)
)
)

lsnrctl reload

Listener reload can be adacuate to apply changes;but, sometimes not.

lsnrctl stop
lsnrctl start
lsnrctl status

Create Database Link on Oracle

CREATE DATABASE LINK PG
CONNECT TO “dblinkuser”
IDENTIFIED BY ‘dblinkuserpass’
USING ‘PG’;

Test

select * from “testschema”.”tbl_test”@PG

28,872 total views, 10 views today

Tags: Database Administration, Oracle Administration, PostgreSQL Administration

facebook comments:

4 Responses to “Create Dblink From Oracle to PostgreSQL Database”

  1. PK says:

    I’m trying to connect from oracle database to aws rds postgresql.
    AWS rds postgresql instances will not have OS level access, when trying to connect getting following errors.
    Any idea what these errors are?

    ==
    SQL> select * from names@pg;

    select * from names@pg
    *
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    FATAL: password authentication failed for user “abcd”
    {08001,NativeErr = 113}
    ORA-02063: preceding 3 lines from PG
    ==

  2. Ballat Marc says:

    Hi,

    Google Apps Script does not support connection to Postgresql (unless in Google Cloud). It does support connection to Oracle. Does it make sense to develop the tables, views and triggers in Postgres and access them through Oracle Express/dblink ? I don’t know the implications in terms of performance, security and type compatibility. This would be temporary, the time for me to rewrite my GAS spreadsheets using node.js and jQuery.

    I would appreciate if someone can shed some light on this.

    Thx, Marc

  3. Stephanie says:

    Im trying to configure db link to a postgress cluster the same thing you have done only with port=9999 and oracle us throwing me an error, what can i do?
    Thanks stepg

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.