This article shows how to create a dblink connection from Oracle to EDB Postgres Advanced Server or PostgreSQL,
- Installation
- Connection
- Testing connectivity
There are several reasons where we might need to use both Oracle and PostgreSQL databases in a business environment. One common scenario is to use existing Oracle infrastructure for the front end and then PostgreSQL for reporting purposes. This setup also helps us evaluate PostgreSQL without taking risks with the production environment and make a path forward to future migration and reducing costs.
For this tutorial, we will be using EDB Postgres Advanced Server, but the steps for achieving a dblink from Oracle to PostgreSQL are the same.
Installation
The prerequisites include installing the ODBC driver from the EnterpriseDB website. It can easily be achieved by using the following command:
yum install edb-odbc
Or, if you are comfortable with the GUI installer, you can install using the stack builder as well:
Configuration
The following steps will help you set up a test case to create a dblink from an Oracle database to EDB Postgres Advanced Server:
Step 1:
Configure the odbcinst.ini file, which has all the information related to drivers.
[oracle@Oracle11g admin]$ cat /etc/odbcinst.ini
[enterprisedb]
Description=PostgresPlus Advanced Server ODBC driver
Driver=/opt/PostgresPlus/connectors/odbc/lib/edb-odbc.so
Setup=/opt/PostgresPlus/connectors/odbc/lib/libodbcedbS.so
UsageCount=1
Step 2:
Configure the odbc.ini file for the DNS entries.
[oracle@Oracle11g admin]$ cat /etc/odbc.ini
[edb]
Driver=enterprisedb
Description=Connection to LDAP/POSTGRESQL
Servername=localhost
Port=5444
Protocol=7.4
FetchBufferSize=99
Username=enterprisedb
Password=edb
Database=edb
ReadOnly=no
Debug=1
Trace = yes
CommLog=1
UseDeclareFetch=0
TraceFile=/tmp/sql.log
UseServerSidePrepare=1
dbms_name=PostgreSQL
Step 3:
Check the DNS connectivity.
[root@Oracle11g enterprisedb]# isql -v edb
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Step 4:
Create the file "initedb.ora" in location $ORACLE_HOME/hs/admin with the contents below.
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = edb
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
NOTE: Make sure your init<sid>.ora file looks like init<DNS Name in odbc.ini>.ora. Avoid the uppercase while assigning the DNS names.
Step 5:
Configure the $ORACLE_HOME/network/admin/listener.ora file with the following contents.
[oracle@Oracle11g admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.35.193)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=orcl))
(SID_DESC =
(SID_NAME = edb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4odbc)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Step 6:
Restart the Listener Service.
->lsnrctl stop all
->lsnrctl start
->lsnrctl status
Service "edb" has 1 instance(s).
Instance "edb", status UNKNOWN, has 1 handler(s) for this service…
Step 7:
Configure the $ORACLE_HOME/network/admin/tnsnames.ora file as shown below.
[oracle@Oracle11g admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
edb = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)
(HOST=172.24.35.86)
(PORT=5444)
)
(CONNECT_DATA = (SID = edb)
)
(HS = OK)
)
Step 8:
Test the "tnsping" to the new HS's given SID.
-> tnsping edb
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521)) (CONNECT_DATA =(SID = edb)) (HS=OK))
OK (0 msec)
Step 9:
Create a dblink as shown below.
-> create public database link toppas connect to "enterprisedb" identified by "edb" using 'edb';
Testing Connectivity
Test HS connectivity as shown below.
SQL> select * from "pg_database"@toppas;
datname datdba
---------------------------------------------------------------- ----------
encoding datcollate
---------- ----------------------------------------------------------------
datctype datis datal
---------------------------------------------------------------- ----- -----
datconnlimit datlastsysoid datfrozenxid dattablespace
------------ ------------- ------------ -------------
datacl
--------------------------------------------------------------------------------
template1 10
6 en_US.UTF-8
en_US.UTF-8 1 1