juin
2011
Un article de zinzineti
Il est possible depuis SQL SERVER d’exécuter des requêtes sur une instance ORACLE se trouvant sur une machine distante. L’objectif de ce billet est de présenter les différentes étapes pour la mise en oeuvre de requêtes distribuées entre une instance SQL SERVER et une instance ORACLE.
=> Contexte ORACLE
IP du Serveur ORACLE : 172.20.4.160
Nom de l’instance de la BD (Oracle _SID) : AIC
Nom de la Base de données ORACLE : REPOSITORY
=> Contexte SQL SERVER
C’est à partir de SQL SERVER que nous allons requêter la base ORACLE. Les deux SGBD, SQL SERVER et Oracle sont installés sur deux machines physiques distinctes. Mais pour l’exécution des requêtes SQL il est indispensable de vérifier quelques points clés permettant la communication entre les deux serveurs.
=> Check-up du serveur ORACLE
–> Configuration du listner
Sur le serveur ORACLE il faut vérifier/configurer le processus d’écoute : LISTENER
La configuration du LISTENER peut se faire par écriture directe dans le fichier listener.ora ou bien via l’IHM Oracle Net Manager
1.Ecriture directe dans le fichier listener.ora
Emplacement du fichier : \oracle\product\10.2.0\db_1\network\admin\listener.ora
———————-
# listener.ora Network Configuration File: \oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(PROGRAM = extproc)
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
)
)LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.4.160)(PORT = 1521))
)
)
2.configuration du listener via l’IHM Oracle Net Manager
L’IHM ne fait rien d’autre que d’écrire dans le fichier listener.ora
En général il ya 1 seul processus d’écoute par serveur, même si le serveur abrite plusieurs bases de données.
–> Test du listener
Sur le serveur ORACLE, lancer la console DOS
Démarrer > Exécuter > cmd
Exécuter la commande LSNRCTL
Puis tapez status
Les principales commandes LSNRCTL :
STATUS : Affiche des informations sur la configuration du processus d’écoute, les emplacements d’écoute et les services enrégistés
START : Démarre le processus d’écoute
STOP : Arrête le processus d’écoute
SERVICES : Affiche les informations détaillées sur les services enrégistrés auprès du processus d’écoute
RELOAD : Recharge la configuration du processus d’écoute (listener.ora). Permet d’ajouter ou de modifier les services enregistrés auprès du processus d’écoute, sans arrêter ce dernier
HELP : Affiche la liste des commandes
=> Check-up SQL SERVER
Il faut installer le client Oracle sur SQL SERVER puis configurer les fichiers sqlnet.ora et tnsnames.ora
–> Installation du client Oracle sur la machine SQL SERVER
■ Lancer Oracle Universal Installer (OUI) et cliquer sur Suivant
■ Choisir le type d’installation Administrateur pour installer les outils d’administration et de développement
Cliquer sur le bouton Suivant
Oracle Universal Installer (OUI) enchaine avec l’assistant de configuration pour configurer
les méthodes de résolution de noms utilisables par le client (stockées dans le fichier sqlnet.ora)
Cliquer sur le bouton Suivant
Les méthodes de résolution de noms utilisables par le client (stockées dans le fichier sqlnet.ora) sont ainsi configurées. OUI reprend la main et annonce la fin de l’installation du client oracle
Cliquer sur le bouton quitter
Une autre fenêtre apparaît
Cliquer sur le bouton Oui de cette fenêtre pour quitter
–> TnsPing depuis SQL SERVER : test si le fichier sqlnet.ora est OK
Le OK du TnsPing indique que le fichier de paramétrage sqlnet.ora est bien configuré
–> Vérification/configuration du fichier tnsnames.ora
On peut directement écrire dans le fichier tsnames.ora ou utiliser l’IHM Net Manager pour effectuer la même opération
1. tnsnames.ora : Ecriture directe dans le fichier tsnames.ora
Emplacement du fichier : \oracle\product\10.2.0\client_1\NETWORK\ADMIN\tsnames.ora
———————-
# tnsnames.ora Network Configuration File: \oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
MY_SERVICE_NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.4.160)(PORT = 1521))
)
(CONNECT_DATA =
(SID = AIC)
)
)
2. tnsnames.ora : IHM Net Manager
Lancer le Net Manager
Dans la fenêtre qui s’affiche, à gauche, cliquer sur le bouton vert + pour créer un service name
Entrer le nom du service name (dans cet exemple le nom est : my_service_name)
cliquer sur le bouton Next
cliquer sur le bouton Next
cliquer sur le bouton Next
cliquer sur le bouton Next
Cliquer sur le bouton Test pour tester la connexion à la base ORACLE
Le test avec le compte scott/tiger est expiré.
Cliquer sur le bouton Change Login pour tester la connexion à la base REPOSITORY
Avec notre compte repository la connexion à la base REPOSITORY est OK
Et le service name créé s’affiche dans l’IHM
–> Test de connexion à la base ORACLE depuis SQL SERVER
Après l’installation du client ORACLE et paramétrage des fichiers sqlnet.ora et tnsnames.ora on peut tester la connexion à la base ORACLE depuis SQL SERVER
Pour celà lancer Oracle SQLPlus et entrer login/password et le service name. Pour notre exemple le login de la base oracle est repository et le service name a pour nom my_service_name
Une fois la connexion à la base oracle est Ok depuis la machine SQL SERVER.
Il ne reste qu’à créer un lien depuis SQL SERVER vers la base ORACLE.
=> Création du Lien SQL SERVER – ORACLE
1. Activer l’option avancée et configurer l’exécution des requêtes distribuées sous le serveur SQL SERVER
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', '1';
GO
RECONFIGURE
GO
2. Créer le lien SQL SERVER – ORACLE
Exécuter l’instruction ci-dessous dans SQL SERVER Management Studio (SSMS) pour créer le lien « LINK_ORACLE »
EXEC master.dbo.sp_addlinkedserver @server = N’LINK_ORACLE‘ ,@srvproduct=N’ORACLE’, @provider=N’MSDAORA’
,@datasrc=N’MY_SERVICE_NAME‘EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’LINK_ORACLE‘,@useself=N’False’,@rmtuser=N’repository‘
,@rmtpassword=’MotDePasse‘
=> Test d’une requête distribuée depuis SQL SERVER vers ORACLE
Voilà une procédure pour requêter ORACLE depuis SQL SERVER.
Pour réaliser une requête distribuées entre deux instances SQL SERVER il suffit juste de créer le lien entre les deux instances SQL SERVER avec la procédure sp_addlinkedserver puis d’utiliser OPENQUERY
——————————-
— Etienne ZINZINDOHOUE
——————————-