sexta-feira, 27 de fevereiro de 2015

How to create database link between Oracle XE 11 vs PostgreSQL 9.3

1. Introduction


1.1. Summary

This post shows how to create a database link between Oracle XE vs PostgreSQL


1.2. Scenario

  • Oracle Linux x86_64 server: 
- DNS Name: oracle.mydomain.com
- IP: 192.168.1.20
- Oracle database instance SID=prod
  • PostgreSQL Linux x86_64 server
- DNS Name: postgresql.mydomain.com
- IP: 192.168.1.20
- PostgreSQL database = prod


1.3. Cookbook

  • Connect to PostgreSQL and create user for database link and grant appropriated permission
  • Connect to Oracle, install and configure Unix ODBC packages and drivers
  • Configure Oracle Heterogeneous Service
  •  Create and test Oracle Database Link to PostgreSQL throught Oracle Heterogeneous Service



2. Step-by-Step


2.1. Connect to PostgreSQL and create user for database link and grant appropriated permission


a) Create PostgreSQL user used by Oracle dblink connection


postgres=# CREATE USER dblink_oracle     WITH PASSWORD 'dblink_oracle';
CREATE ROLE


b) Grant connection to dblink user to appropriated PostgreSQL database

postgres=# GRANT CONNECT ON DATABASE prod TO dblink_oracle;
GRANT


c) Grant to dblink user to appropriated schema and database objects permissions

prod=# GRANT USAGE   ON                  SCHEMA schema1 TO dblink_oracle;
GRANT
prod=# GRANT SELECT  ON ALL TABLES    IN SCHEMA schema1 TO dblink_oracle;
GRANT
prod=#


d) Disconnect and Connect with dblink user to test permissions given

prod=# \q
-bash-4.2$ psql  -h localhost -U dblink_oracle -W prod
Password for user dblink_oracle:
psql (9.3.5)
Type "help" for help.

prod=> select table_catalog, table_schema, table_name from information_schema.tables;
 table_catalog |    table_schema    |              table_name
---------------+--------------------+---------------------------------------
       :                 :                              :


2.1. Connect to Oracle Server, install and configure Unix ODBC packages and drivers


a) Install packages required

# yum install unixODBC
# yum install postgresql-odbc


b) Configure connection on ODBC.INI

# vim /etc/odbc.ini
[DB_PGSQL]
Description = DB_PGSQL
Driver = PostgreSQL
Trace = False
TraceFil = /tmp/odbc.log
Database = prod
Servername = postgresql.mydomain.com
Username = dblink_oracle
Password = dblink_oracle
Port = 5432
ReadOnly = Yes


c) Configure driver on ODBCINST.INI

# cat /etc/odbcinst.ini
# Example driver definitions

 # Driver from the postgresql-odbc package
# Setup from the unixODBC package
[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
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[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


d) Configure Oracle Heterogeneous Services init file on $ORACLE_HOME/hs/admin/initDB_PGSQL.ora

  • Change user to oracle installation user on Unix

# su - oracle
$ id
uid=500(oracle) gid=500(dba) groups=500(dba)
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/xe
$ ls -la $ORACLE_HOME/hs/admin
total 24
drwxr-xr-x 2 oracle oinstall 4096 Dec 20  2013 .
drwxr-xr-x 5 oracle oinstall 4096 Dec 20  2013 ..
-rw-r--r-- 1 oracle oinstall 1109 Sep 17  2011 extproc.ora
-rw-r--r-- 1 oracle oinstall  489 Sep 17  2011 initdg4odbc.ora
-rw-r--r-- 1 oracle oinstall  411 Dec 20  2013 listener.ora.sample
-rw-r--r-- 1 oracle oinstall  244 Dec 20  2013 tnsnames.ora.sample


  • Let's use Oracle Heterogeneous Service default database gateway file 'initdg4odbc.ora' as base of 'initDB_PGSQL.ora'
  • Configure key "HS_FDS_SHAREABLE_NAME" to point to appropriated binary installation of your system x86 32bit or 64bit. In my case, it was a 64bits  installation '/usr/lib64'
  • Configure environment variables if necessary. In my case, I needed to force CHARSET 


$ cd  $ORACLE_HOME/hs/admin
$ cp initdg4odbc.ora initDB_PGSQL.ora
$ vim initDB_PGSQL.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 = DB_PGSQL
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so

 #
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
set CHARSET = UTF8


e) before continuing with Oracle HS configuration, let's check if ODBC connection is working properly. We will use isql to connect throught ODBC connection and try some SQL statement on remote PostgreSQL database

$ -bash-4.1$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /u01/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

 $ isql DB_PGSQL dblink_oracle dblink_oracle
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select table_catalog, table_schema, count(*) from information_schema.tables group by table_catalog, table_schema;
+---------------+--------------+---------------------+
| table_catalog | table_schema | count               |
+---------------+--------------+---------------------+
        :               :           : 



f) Configure Oracle HS as database gateway on LISTENER.ORA


  • Oracle has changed ( since version 10) binary file for HS(Heterogeneous Service) from 'hsodbc' to 'dg4odbc'. Before configure your LISTENER.ORA look for correct binary file for Oracle Database Gateway for Heterogeneous Service ODBC in $ORACLE_HOME/bin/*. In my case, binary file is 'dg4odbc'


$ ls -la $ORACLE_HOME/bin/hsodbc*
ls: cannot access /u01/oracle/app//product/11.2.0/dbhome_1/bin/hsodbc: No such file or directory
$ ls -la $ORACLE_HOME/bin/dg4odbc*
-rwxr-x--x 1 oracle oinstall 536297 Dec 20  2013 /u01/oracle/app//product/11.2.0/dbhome_1/bin/dg4odbc
$ cd  $ORACLE_HOME/network/admin/
$ vim listener.ora
# listener.ora Network Configuration File:

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = DB_PGSQL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = dg4odbc)
    )
  )

 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.mydomain.com)(PORT = 1521))
    )
  )

 DEFAULT_SERVICE_LISTENER = (XE)


g) Configure TNSNAMES.ORA for HS

$ cd  $ORACLE_HOME/network/admin/
$ vim tnsnames.ora
DB_PGSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.mydomain.com)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SID = DB_PGSQL)
      )
      (HS = OK)
  )


h) Restart LISTENER

$ lsnrctl reload

 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-FEB-2015 12:11:58

 Copyright (c) 1991, 2011, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully


i) Create and test Oracle Database Link to PostgreSQL throught Oracle Heterogeneous Service

$ sqlplus

 SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 27 12:16:17 2015

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

 Enter user-name: / as sysdba

 Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> CREATE PUBLIC DATABASE LINK "DBPG_LNK" CONNECT TO "dblink_oracle" IDENTIFIED BY "dblink_oracle" USING 'DB_PGSQL';

 Database link created.

 SQL> SELECT DISTINCT "table_catalog", "table_schema" FROM "information_schema"."tables"@DBPG_LNK;

 table_catalog       table_schema
------------------- -------------------
       :                 :


3. References




Postado por às

4 comentários:

  1. Unknown11 de janeiro de 2017 às 14:58

    Este comentário foi removido pelo autor.

    ResponderExcluir
  2. Unknown17 de janeiro de 2017 às 10:14

    Very useful tutorial. Thank you very much.

    1.) In a external connection escenario in order to avoid an "isql" error is required open Postgres database to external connections.
    http://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

    2.) In our case we got the ora-28500 error when select through dblink. We resolved adding the following line on file initDB_PGSQL.ora

    HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

    ResponderExcluir
  3. Unknown9 de outubro de 2018 às 15:43

    Hello josemar, I followed your instructions and managed to access the PostgreSQL data, but I have an inconvenience with the accents and the letter ñ, I get some strange characters. Please your help on how I can solve this. Thank you

    ResponderExcluir
  4. Unknown10 de outubro de 2018 às 14:38

    Hi Hussein,

    Try to go Back to step “Configure environment variables if necessary. In my case, I needed to force CHARSET ”.

    Maybe you have Ro findo correct charset compatibility

    ResponderExcluir

Assinar: Postar comentários (Atom)