Oracle Gateway To PostgreSQL

This document describes how to create and manage an Oracle database link to a PostgreSQL database, assuming both databases are located on Linux servers.

PostgreSQL server setup

  1. Create entry in pg_hba.conf file for the Oracle database server host.

Oracle server setup

  1. yum install unixODBC*
  2. yum install postgresql-odbc
  3. odbcinst -j
  4. odbc_config --odbcini --odbcinstini
  5. edit /etc/odbcinst.ini to configure the PostgreSQL driver
  6. edit /etc/odbc.ini to set up a data source for connecting to the PostgreSQL database. You will need:
    1. a data source name (try to make this match the database name)
    2. the database name
    3. the database server
    4. a database login
    5. the login password *** Make sure password does not contain a % ***
    6. the database port number
  7. use isql to test connection

    1. isql -v <data source name>

Oracle home configuration

  1. create $ORACLE_HOME/hs/admin/initDSN.ora (where "DSN" is the data source name), using $ORACLE_HOME/hs/admin/initdg4odbc.ora as a model
    1. include any additional environment variables you may need
  2. edit $ORACLE_HOME/network/admin/listener.ora to add an entry for the DSN
  3. edit $ORACLE_HOME/network/admin/tnsnames.ora to add an entry for the DSN
  4. restart the listener

  1. create a user in the Oracle database to own the database link and views to the PostgreSQL data
    1. try to make the username match the username in the PostgreSQL database
    2. grant connect, resource, create view, and create database link to the Oracle user
  2. create the database link.
    1. It must NOT be a public database link!
    2. Since the Banner database must have global_names set to true, the name of the database link must match an internal name in the PostgreSQL database. This is most likely NOT the same as the name you entered in /etc/odbc.ini. If you select the wrong name, the link will be created; however, when you query against a table in the link, you will get an error message that will tell you what the correct name should be.

Data security

  1. create Oracle views to the PostgreSQL data.
  2. grant select access on the views to Oracle users and/or roles as needed.

References

Accessing PostgreSQL databases using psqlODBC in Oracle: https://www.enterprisedb.com/postgres-tutorials/accessing-postgresql-databases-using-psqlodbc-oracle

---

BTSHowTo

DataAdminHowTo

DB_Administration