Oracle Heterogeneous Services (sous Solaris) vers MS SQL Server

Introduction

Le composant Oracle Transparent Gateway for MSSQL, composant qui permet de faire dialoguer une instance Oracle avec un serveur Microsoft SQL Server, n’est pas disponible pour les plateformes Unix et n’est proposé que pour les plateformes Windows. Pour pallier à ce problème sur les plateformes Unix SunOS Sparc, les services hétérogènes d’Oracle (Oracle Heterogeneous Services) peuvent être mis en œuvre pour passer par un pont ODBC Unix vers un serveur MS SQL Server.

Les services hétérogènes d’Oracle (Oracle Heterogeneous Services) permettent d’intégrer des données ne provenant pas d’Oracle. Avec l’utilisation de l’agent générique de connectivité Oracle, les communications vers les serveurs MS SQL Server sous windows sont possibles même si l’instance Oracle est sur une plateforme Unix.

DataDirect Connect for ODBC fournit l’accès aux données le plus fiable et le plus performant pour les services hétérogènes d’Oracle.

Cette documentation technique montre comment utiliser et mettre en œuvre DataDirect Connect for ODBC avec les services hétérogènes Oracle 9i pour communiquer avec une base de données MS SQL Server 2000, l’instance Oracle étant sur une plateforme SunOS Solaris. Par la même occasion, une normalisation est mise en œuvre.

Voici un schéma global de mise en œuvre des services hétérogènes d’Oracle :

Connectivité générique

La connectivité générique est implémentée en utilisant l’Agent ODBC des services hétérogènes d’Oracle. Cet agent ODBC est inclus dans le système Oracle.

Pour accéder aux données non Oracle, l’agent passe par un driver ODBC. Le driver ODBC doit être sur la même plateforme que l’agent ODBC, en revanche la base de données non Oracle peut être sur la même machine que l’instance Oracle ou sur une machine différente.

DataDirect Connect for ODBC

Installation normalisée de DataDirect Connect for ODBC

L’installation de DataDirect Connect for ODBC est effectuée dans le répertoire /Software/odbc.

Le propriétaire pour cette installation est sybase:dba.

Dans cette installation, il faut s’assurer que les librairies dans le répertoire /Software/odbc/lib sont exécutables pour tous les users Unix (notamment Oracle) et que le fichier odbc.ini dans le répertoire /Software/odbc est en lecture pour tous les users Unix.

Fichiers odbc.sh et odbc.csh

Les fichiers odbc.sh et odbc.csh à la racine de l’installation /Software/odbc permettent de sourcer (respectivement en shell et cshell) l’environnement DataDirect Connect et plus particulièrement mettre à jour la variable d’environnement $LD_LIBRARY_PATH pour l’accès aux librairies dans le répertoire /Software/odbc/lib.

Ces fichiers doivent refléter l’installation dans /Software/odbc.

odbc.sh

if [ "$LD_LIBRARY_PATH" = "" ]; then
   LD_LIBRARY_PATH=/Software/odbc/lib
else
   LD_LIBRARY_PATH=/Software/odbc/lib:$LD_LIBRARY_PATH
fi
      export LD_LIBRARY_PATH

odbc.csh

if ($?LD_LIBRARY_PATH == 1) then
  setenv LD_LIBRARY_PATH /Software/odbc/lib:${LD_LIBRARY_PATH}
else
  setenv LD_LIBRARY_PATH /Software/odbc/lib
endif

Fichier odbcinst.ini

Le fichier odbcinst.ini à la racine de l’installation de DataDirect Connect for ODBC (/Software/odbc) recense les drivers disponibles dans /Software/odbc/lib.

Ce fichier doit refléter le chemin d’installation de DataDirect Connect (/Software/odbc) et plus particulièrement le driver pour MS SQL Server.

[DataDirect 5.1 SQL Server Wire Protocol]
ConnectFunctions=YYY
APILevel=1
Driver=/Software/odbc/lib/ivmsss21.so
DriverODBCVer=3.52
FileUsage=0
SQLLevel=1

Configuration de la source de données ODBC vers MS SQL Server (fichier odbc.ini)

La source de données odbc vers MS SQL Server peut alors être ajoutée dans le fichier odbc.ini situé dans le répertoire d’installation de DataDirect Connect. Dans l’exemple qui suit, la source de données est appelée SENTINEL.

[ODBC Data Sources]

SENTINEL=DataDirect 5.1 SQL Server Wire Protocol

[SENTINEL]
   Driver=/Software/odbc/lib/ivmsss21.so
   Description=DataDirect 5.1 SQL Server Wire Protocol
   Address=FRDMIN408,1433
   AlternateServers=
   AnsiNPW=Yes
   ConnectionRetryCount=0
   ConnectionRetryDelay=3
   Database=<dbname>
   LoadBalancing=0
   LogonID=<loginName>
   Password=<passWord>
   QuotedId=No

Pour SQL Server 2000, le driver est ivmsss21.so (Driver=/Software/odbc/lib/ivmsss21.so)

Le champ Address est obligatoire et doit comporter l’adresse IP ou logique de la machine hébergeant le serveur MS SQL ainsi que le port d’écoute du serveur MS SQL.

Le champ Database peut être optionnel car un login sous MS SQL Server a toujours une base de données par défaut.

Les champs LoginID et Password sont optionnels car ces paramètres sont donnés lors de la création du lien dans l’instance Oracle.

Configuration de l’instance Oracle pour les services hétérogènes

Installation des tables et vues du dictionnaire

Pour utiliser les services hétérogènes d’Oracle, il est nécessaire d’installer les tables et vues du dictionnaire liées aux services hétérogènes dans l’instance Oracle qui va solliciter l’agent Oracle HS.

En tant que sys, il faut exécuter le script caths.sql localisé dans le répertoire $ORACLE_HOME/rdbms/admin.

UNIX > sqlplus "/ as sysdba"

@?/rdbms/admin/caths.sql;

? dans la syntaxe ci-dessus est l’équivalent de $ORACLE_HOME.

Entrées dans les fichiers tnsnames.ora et listener.ora de l’agent Oracle HS

Une normalisation a été créée pour l’ajout d’agent HS Oracle dans les fichiers tnsnames.ora et listener.ora : HS<ODBC Data Source Name>.

Toutefois il faut rappeler ici que la source de données ODBC ne doit pas dépasser 14 caractères car on se limite à 16 caractères pour HS<ODBC Data Source Name>.

Dans le contexte de ce cas pratique, la source de données ODBC étant SENTINEL, les services Net Oracle pour cet agent Oracle HS seront dénommés HSSENTINEL dans les fichiers listener.ora et tnsnames.ora.

Après avoir pris de soin de réserver un port pour l’agent Oracle HS sur la machine de l’instance Oracle qui va utiliser les services de l’agent, le fichier $TNS_ADMIN/tnsnames.ora doit comporter alors l’entrée pour HSSENTINEL avec la clause hs=ok :

tnsnames.ora

# HETEROGENEOUS SERVICES ###############################################
HSSENTINEL =
(DESCRIPTION =
        (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = SRVUNXFR1)(PORT = 5559 ))
)
        (CONNECT_DATA =
        (SID = HSSENTINEL)
)
(hs=ok)
)

La clause hs=ok dans les fichier tnsnames.ora indique qu’il s’agit d’un agent Oracle pour les services hétérogènes.

Le listener pour les services hétérogènes doit comporter la clause PROGRAM=hsodbc, hsodbc étant l’exécutable des services hétérogènes ODBC d’Oracle.

listener.ora

# HETEROGENEOUS SERVICES -----------------------------------------------
LISTENER_HSSENTINEL =
        (DESCRIPTION_LIST =
                (DESCRIPTION =
                        (ADDRESS_LIST =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = SRVUNXFR1)(PORT = 5559))
                        )
                )
        )
SID_LIST_LISTENER_HSSENTINEL =
        (SID_LIST =
                (SID_DESC =
                        (SID_NAME = HSSENTINEL)
                        (ORACLE_HOME = /Software/oracle/app/product/9.2.0)
                        (PROGRAM = HSODBC)
                )
        )
LOG_DIRECTORY_LISTENER_HSSENTINEL=/Software/oracle/Instances/HSSENTINEL/bdump
LOG_FILE_LISTENER_HSSENTINEL=listener_HSSENTINEL.log

Démarrage du listener pour l’agent Oracle HS

Avant de démarrer le listener HSSENTINEL , il faut s’assurer que le répertoire des librairies DataDirect /Software/oracle/odbc/lib ($ODBC_HOME) soit bien référencé dans la variable d’environnement LD_LIBRARY_PATH, si ce n’est pas le cas :

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/Software/odbc/lib
export LD_LIBRARY_PATH

Le listener HSSENTINEL peut alors être démarré avec le binaire lsnrctl et en lançant la commande start LISTENER_HSSENTINEL.

oracle@RISKD > lsnrctl
LSNRCTL for Solaris: Version 9.2.0.7.0 - Production on 28-JAN-2007 17:16:18
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start LISTENER_HSSENTINEL
Starting /Software/oracle/app/product/9.2.0/bin/tnslsnr: please wait...
TNSLSNR for Solaris: Version 9.2.0.7.0 - Production
System parameter file is /Software/oracle/Network/listener.ora
Log messages written to /Software/oracle/Instances/HSSENTINEL/bdump/listener_hssentinel.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVUNXFR1)(PORT=5559)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SRVUNXFR1)(PORT=5559)))
STATUS of the LISTENER
------------------------
Alias LISTENER_HSSENTINEL
Version TNSLSNR for Solaris: Version 9.2.0.7.0 - Production
Start Date 25-JAN-2007 17:16:24
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /Software/oracle/Network/listener.ora
Listener Log File /Software/oracle/Instances/HSSENTINEL/bdump/listener_hssentinel.log
Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SRVUNXFR1)(PORT=5559)))
Services Summary...
Service "HSSENTINEL" has 1 instance(s).
Instance "HSSENTINEL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Fichier d’initialisation init pour l’agent Oracle HS

Vient ensuite l’étape de la création du fichier d’initialisation pour l’agent Oracle HS. Oracle fournit un fichier d’initialisation nommé inithsodbc.ora pour exemple et localisé dans le répertoire $ORACLE_HOME/hs/admin.

Pour créer le fichier d’initialisation de l’agent Oracle HS, copier le fichier exemple inithsodbc.ora et renommer ce dernier en init<SID de l’Agent>.ora.

Dans notre cas pratique, le fichier d’initialisation est donc nomenclaturé initHSSENTINEL.ora et ce dernier est installé dans le répertoire normalisé /Software/oracle/Instances/<SID Oracle Agent>/pfile ( /Software/oracle/Instances/HSSENTINEL/pfile ).

Dans ce fichier d’initialisation de l’agent Oracle HS sont indiqués les chemins complets d’accès au driver libodbc.so et au fichier odbc.ini contenant les sources de données ODBC. Le même fichier contient également la source de données ODBC à utiliser avec la variable HS_FDS_CONNECT_INFO.

Ci-dessous l’extrait du fichier HSSENTINEL.ora

HSSENTINEL.ora

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = SENTINEL
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = HSSENTINEL.trc
HS_FDS_SHAREABLE_NAME = /Software/odbc/lib/libodbc.so

#
# ODBC specific environment variables
#
set ODBCINI=/Software/odbc/odbc.ini

Les niveaux de trace HS_FDS_TRACE_LEVEL ( de 0 à 4 ) feront l’objet d’une documentation spécifique.

Création du lien vers MSSQL Server dans l’instance Oracle, tests

Un lien DB (database link) est alors créé vers la base de données MS SQL Server cible avec la commande Oracle CREATE DATABASE LINK.

create database link SRV_SENTINEL;
connect to "user" identified by "password" using 'hssentinel';

Attention l’utilisation des simples quotes et doubles quotes comme écrits dans l’exemple ci-dessus doit être respectée.

Pour ensuite tester, il suffit d’interroger une table de la base MS SQL Server cible :

select count(*) from INSTRUMENT@SRV_SENTINEL;

87778

select "ID" from INSTRUMENT@SRV_SENTINEL;

1222
...

Dans les commandes Select et DML ( INSERT, UPDATE, DELETE ), les fonctions propres à Oracle sont interdites, exemple :

insert into ORATEST@SRV_SENTINEL values (2,'Autre test',20,to_date(sysdate,'YYYY MON DD'));

insert into ORATEST@SRV_SENTINEL values (2,'Autre test',20,to_date(sysdate,'YYYY MON DD'))
*ERROR at line 1:
ORA-02070: database SRV_SENTINEL does not support TO_DATE in this context

Il est imperatif d’écrire la syntaxe appropriée :

insert into ORATEST@SRV_SENTINEL values (2,'Autre test',20,'2007 JAN 26');
1 row created.
commit;
Commit complete.

Ou encore de mettre en œuvre des variables bindées avec la donnée formatée :

insert into ORATEST@SRV_SENTINEL values (2,'Autre test',20,:1);

Quelques mots au sujet des mises à jour :