In this article, I will show how to integrate an Oracle database with a PostgreSQL database. First, we need to gather all the necessary information, such as:
- IP address of the Oracle server
- Passwords for the root and oracle OS users
- Passwords for the sys and system Oracle users
- IP address of the PostgreSQL server
- User that will connect from Oracle to PostgreSQL
- Password for the user that will connect from Oracle to PostgreSQL
- Port used by the PostgreSQL database, if it is not the default 5432
Let’s validate if the firewall and SELinux are disabled on the PostgreSQL server.
For CentOS and Red Hat 7 and higher, stop service this way:
[root@postgresqlsrv ~]$ systemctl status firewalld
[root@postgresqlsrv ~]$ sestatus
[root@postgresqlsrv ~]$ setenforce disable
For lower:
[root@postgresqlsrv ~]$ service iptables status
[root@postgresqlsrv ~]$ sestatus
[root@postgresqlsrv ~]$ setenforce disable
To definitely change SELinux status, edit /etc/selinux/config file and change SELINUX to disable:
[root@postgresqlsrv ~]$ vim /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are pro
tected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
After disabling SELinux, if it was not already disabled, we must restart the server.
With the information gathered above, we need to configure the following files on the Oracle server:
[root@redhatora ~]$ cat /etc/odbc.ini
# This string is important, because is used like connection alias
[DB_PGSQL]
Description = DB_PGSQL
Driver = PostgreSQL
Trace = Yes
TraceFil = /tmp/odbc.log
Database = postgres
# Don't forget to set /etc/hosts with this host
Servername = postgresqlsrv
Username = postgres
Password = postgres
Port = 5432
ReadOnly = No
[root@redhatora ~]# cat /etc/odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
[root@redhatora ~]#
On the Oracle server, we need to configure an initialization file within the Heterogeneous Services directory, as shown below:
[oracle@redhatora ~]$ cat /u01/app/oracle/product/11.2.0.4/db/hs/admin/initDB_PGSQL.ora
# This init has the Postgres alias name of odbc.ini file
HS_FDS_CONNECT_INFO = DB_PGSQL
HS_FDS_TRACE_LEVEL = Debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI= /etc/odbc.ini
[oracle@redhatora ~]$
In some cases, it may be necessary to add parameters to the initDB_PGSQL.ora file:
HS_LANGUAGE=american_america.we8mswin1252
HS_FDS_SQLLEN_INTERPRETATION=32
We must have these two packages installed on the Oracle server:
[oracle@redhatora ~]$ yum install unixODBC
[oracle@redhatora ~]$ rpm -ivh unixODBC
[oracle@redhatora ~]$ yum install postgresql-odbc
[oracle@redhatora ~]$ rpm -ivh postgresql-odbc
Once these configurations are done, we need to configure the tnsnames.ora and listener.ora connection files:
[oracle@redhatora ~]$ cat /u01/app/oracle/product/11.2.0.4/db/network/admin/tnsnames.ora
DB_PGSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.223)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DB_PGSQL)
)
(HS = OK)
)
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.223)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = db11g)
)
)
The HOST parameter should be the Oracle server. This point is important because it took time for us to figure this out.
[oracle@redhatora ~]$ cat /u01/app/oracle/product/11.2.0.4/db/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = DB_PGSQL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db)
(PROGRAM = dg4odbc) # This for Oracle for 11g
#(PROGRAM = hsodbc) # This for Oracle for 10g
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.223)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
After this, we can perform some tests such as:
[oracle@redhatora ~]$ tnsping DB_PGSQL
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 03-MAY-2016 09:41:57
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 = 192.168.25.223)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = DB_PGSQL)) (HS = OK))
OK (0 msec)
[oracle@redhatora ~]$
Note: In the case of a cluster, in the listener, use the ORACLE_HOME of the database and not of the cluster. Also, make sure to provide the full path to the dg4odbc executable, like this:
[grid@brtora02 ~]$ cat /grid/product/11.2/grid/network/admin/listener.ora
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = DB_PGSQL)
(ORACLE_HOME = /oracle/product/11.2/db)
(PROGRAM = /oracle/product/11.2/db/bin/dg4odbc)
)
)
[oracle@redhatora ~]$ /usr/bin/isql -v DB_PGSQL postgres postgres select * from films;
+------+-----------------------------------------+------------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| code | title | did | date_prod | kind | len |
+------+-----------------------------------------+------------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | RAMBO 4 | 1 | 2010-01-01| TESTE | 00:02:00 |
+------+-----------------------------------------+------------+-----------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
Once these tests are done, we should create a DBLink:
[oracle@redhatora ~]$ sqlplus "/as sysdba"
SQL> create public database link DBPG_LNK connect to "postgres" identified by "postgres" using 'DB_PGSQL';
Database link created.
Postgres is case-sensitive, so we need to be careful. After creating the DBLink, we can test the connection:
[oracle@redhatora ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 3 07:15:36 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> set lines 400
SQL> select * from "films"@DBPG_LNK;
code title did date_prod kind len
----- ---------------------------------------- ---------- --------- ---------- ---------------------------------------------------------
1 RAMBO 4 1 01-JAN-10 TESTE 00:02:00
We may encounter issues related to the file /var/lib/pgsql/data/postgresql.conf, located on the Oracle server.
[root@postgresqlsrv ~]$ netstat -nlt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
tcp6 0 0 :::22 :::* LISTEN
tcp6 0 0 ::1:631 :::* LISTEN
tcp6 0 0 ::1:5432 :::* LISTEN
tcp6 0 0 ::1:25 :::* LISTEN
After configuring this parameter, validate the connection via telnet on port 5432.
[root@redhatora Packages]$ telnet 192.168.25.8 5432 # PostgreSQL server and port
Trying 192.168.25.8...
Connected to 192.168.25.8.
Escape character is '^]'.
quit
Connection closed by foreign host.
And in the file /var/lib/pgsql/data/pg_hba.conf, also located on the Oracle server:
Possible errors:
[root@redhatora ~]$ /usr/bin/isql -v DB_PGSQL postgres teste postgres
[28000][unixODBC]FATAL: Ident authentication failed for user "postgres"
[ISQL]ERROR: Could not SQLConnect
Example of how the iptable file should be configured:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust local all postgres trust
# IPv4 local connections:
host all all 127.0.0.1/32 ident
#host all all 192.168.25.0/24 md5
host all all 192.168.25.0 255.255.255.0 trust
# IPv6 local connections:
host all all ::1/128 ident
My Piece of Data 