This post outlines the procedure involved in
setting up a database link between Oracle 11gR2 and MS SQL Server using
Oracle's Database Gateways for ODBC (DG4ODBC) using the freeware odbc drivers
available from Microsoft.
The software components involved are:
·
Microsoft SQL Server ODBC Driver 1.0
for Linux
·
Oracle 11gR2 DG4ODBC (shipped with
Oracle Server)
·
MS SQL Server 2005 (or later versions
2008, 2012)
1. Install
the unixODBC driver manager and ODBC driver as per the instructions below as
'root' user.
2. Setup
/etc/odbc.ini as below (as root user)
[mssql]
Description=Microsoft SQL Server ODBC Driver V1.0
for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=1
AutoTranslate=No
#Had to use the above AutoTranslate setting to avoid a single quote in MS SQL records
being #allocated an extra byte at Oracle end.
Database=MSSQL_Database_Name
UserName=MSSQL_DB_User
Password=xxxxxx
Port=1433
Server=MSSQLServer
Trace=Yes
TraceFile=/u00/odbctrace.log
3. Setup
Oracle listener.ora ($ORACLE_HOME/network/admin/listener.ora) as below
*************************
# listener.ora Network Configuration File: xxxx/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=Oracle_Home_Path)
(ENV="LD_LIBRARY_PATH=/usr/lib64:Oracle_Home_Path/lib")
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux_Host)(PORT = 1521))
)
)
# listener.ora Network Configuration File: xxxx/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=Oracle_Home_Path)
(ENV="LD_LIBRARY_PATH=/usr/lib64:Oracle_Home_Path/lib")
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux_Host)(PORT = 1521))
)
)
ADR_BASE_LISTENER
= /u00/app/oracle
*************************
*************************
4. Add
the below entry to your tnsnames.ora
(Oracle_Home_Path/network/admin/tnsnames.ora) as below
# tnsnames.ora
Network Configuration File: Oracle_Home_Path/network/admin/tnsnames.ora
# Generated by
Oracle configuration tools.
DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=Linux_Host)(PORT=1521))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)
5. Setup
HS Gateways as below (Oracle_Home_Path/hs/admin/initDG4ODBC.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 = mssql
HS_FDS_TRACE_LEVEL = on
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>
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mssql
HS_FDS_TRACE_LEVEL = on
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>
6. Setup
a database link as below
CREATE PUBLIC DATABASE LINK linkname
CONNECT TO "MSSQL_DB_User" IDENTIFIED BY "xxx" USING 'DG4ODBC';
CONNECT TO "MSSQL_DB_User" IDENTIFIED BY "xxx" USING 'DG4ODBC';
7. Add
the below env variables to the .profile or .bash_profile of Oracle user
export
LD_LIBRARY_PATH=/opt/microsoft/sqlncli/lib64:Oracle_Home_Path/lib
export ODBCINI=/etc/odbc.ini
export NLS_LANG=Language_Territory.Characterset
export ODBCINI=/etc/odbc.ini
export NLS_LANG=Language_Territory.Characterset
