Bases de données réparties

par Gilles Thomassin

Public:
BTS Informatique de Gestion 2ième année

Pré-requis:
Notions sur l'architecture Oracle
Langage de Définition De Données.
Langage de Définition de droits
Triggers et un peu de PLSQL

Matériel nécessaire pour mettre en oeuvre ce TP:
deux serveurs Oracles.
On en avait déjà un, il a suffit d'installer provisoirement Oracle sur un serveur linux pour faire le deuxième durant les 60 jours réglementaires.


Ce document essaye de présenter les notions nécessaires pour que les étudiants puisssent répondre à l'exonet 36 élaboré par Christian Fisher
L'extrait de l'exonet se trouve à la fin du document et l'exonet complet avec sa correction est sur le site du certa.


Bases de données distantes



On les appelle aussi bases de données distribuées. Il est possible d’interroger des bases de données se trouvant sur des sites distants.

Toute base est accessible pourvu qu’elle possède une entrée dans le fichier tnsnames .ora et que vous ayez un compte sur cette base.

Visualisez ce fichier: démarrer rechercher fichier ou dossiers *.ora

Ouvrez le avec notepad.

Comptez les bases accessibles via tcp_ip.

Le TNSNAMES.ora est mis à jour automatiquement par sql net easy configuration.

Pour se connecter à une base distante, il suffit de taper sous sqlplus:

Connect utilisateurdistant/motdepasse@alias_sitedistant.

Vous travaillez alors sur la base distante.

Passons à l’'action:

Nous disposons de deux serveurs Oracle.
L'’un a pour adresse 172.16.48.150 et gère l'’instance de base ‘BTSIG’ ce serveur est situé physiquement à TURIN.
L'’autre a pour adresse 172.16.48.154 et gère l'’instance de base ‘DBTRAV’ il est installé à Paris.

Créez les alias alias_turin et alias_paris à l’'aide du logiciel ‘SQL NET Easy Configuration’.

Connectez-vous sur le site @alias_Turin avec votre nom d'’utilisateur et votre mot de passe habituel et faîtes afficher le nom de toutes vos tables.

Connectez-vous sur le site de Paris @alias_paris. Que ce passe-t-il?

Vous n'’êtes pas reconnu en tant qu'’utilisateur de l’instance DBTRAV sur le serveur de Paris.

Vous allez alors prendre votre casquette d’'administrateur de la base ORACLE de Paris.

L'’administrateur de cette base s'’appelle system et a pour mot de passe" ‘mdp"’.

Connect system/mdp@alias_Paris.

Si tout se passe bien, vous êtes alors connecté à la base Oracle parisienne en tant qu'’administrateur.

Créez alors un nouvel utilisateur (vous remplacerez Gdupond par votre login et motdepasse par votre mot de passe, ATTENTION, laissez le suffixe paris sinon vous pourriez vous perdre):

CREATE USER Gdupondparis IDENTIFIED BY ‘motdepasse’;

Accordez lui le droit de se connecter à la base, et d’y créer des tables:

GRANT CONNECT TO Gdupondparis;

L'’utilisateur Gdupondparis est donc maintenant un utilisateur parisien.

essayez de vous connecter:

Connect GdupontParis/motdepasse@alias_Paris;

Lister vos tables sur Paris.

Commande:

Il ne doit pas y en avoir vu que vous n'’en avez pas encore créé.

Créez une table ‘Employé’ qui contiendra les informations de vos employés parisiens.

Y insérer vos deux premiers employés.

Retournez sur la base italienne et créez la même table qui contiendra vos employés turinois. Insérez dans la table trois employés italiens.



Comment obtenir les employés parisiens:

Connect nomutilparis/mdp@alias_paris;

Select * from employe;



Comment obtenir les employés italiens:

Connect nomutil/mdp@alias_Turin;

Select * from employe;

En une seule requête, on ne peut pas pour l'’instant obtenir les employés du site de Turin et les employés du site de Paris.

Et pourtant il est possible dans une même requête de faire appel à des tables situées sur des sites différents, jointure entre deux tables situées sur des sites différents.



Comment faire?

Vous travaillez sur Paris et désirez obtenir la liste de tous les employés (parisiens et turinois).

Vous devez créer un lien dans la base de Paris vers la base Italienne (un DataBase Link).





Les DATABASE LINK

Connect system/mdp@alias_Paris //vous devez être administrateur pour créer un lien

Create Database Link nomutilparis.LienVersTurin //ce lien appartient à nomutilparis et a pour nom: LienVersParis.

CONNECT to nomutil IDENTIFIED by motdepasse //nomutil est le user de la base turinoise

USING alias_turin;



Une fois le lien créé, on va le tester:

Connect nomutilparis/motdepasse@alias_paris

Select * from employe@LienVersTurin;

Vous obtenez ainsi la liste des employés italiens.

Pour obtenir les employés des deux tables, il suffit de faire une union:

(Select * from employe)UNION (select * from employe@LienVersTurin)



et pour rendre la chose plus transparente:

Create synonyme empgap for employe;

CREATE SYNONYME empturin for employe@LienVersTurin;

Select * from empgap UNION SELECT * from empTurin;



Et pour que ce soit «hyper transparent»:

CREATE VIEW ALL_EMP AS

(select * from empgap) UNION (SELECT * from empTurin);



Il suffit alors pour visualiser tous les employés de faire:

Select * from all_EMP;

La répartition des données

Dans l'’exemple abordé ci-dessus, les informations sur les employés sont réparties sur différents sites.

Il est possible de recopier les données d'’un site sur d'’autres sites on parle alors de réplication de données.



Un cas de réplication:

Imaginons une société de vente d'’électroménager ayant plusieurs points de vente répartis dans toutes les grandes villes et un siège établit à GAP. Chaque site dispose d'’un serveur Linux connecté à internet via une ligne numéris faisant tourner une base ORACLE 8.1.5. Le siège idem. Tous les sites vendent les mêmes appareils, la table appareil étant centralisée au siège. Seuls Rpignon,Hjunot et Xcuerten employés du siège et responsables du service marketing ont le droit d'’insertion, de mise à jour et de suppression sur la table APPAREILS. C'’est Rpignon qui est le user propriétaire de la table. Les deux autres y accèdent par Rpignon.APPAREIL

exemple:

connect Xcuerten/glouglou@alias_Gap

select * from Rpignon.appareil.



Les employés des sites délocalisés ont uniquement un droit de consultation sur cette table.



Voici la structure de la table:

APPAREILS=(no_appareil, designation, Pri_unit_HT, Caracteristique_technique).



Les consultations de ces tables sont si fréquentes que les lignes numéris étaient saturées. Il a donc été décidé de recopier la table centrale sur tous les autres sites.



Mais attention, les copies de la table doivent contenir les mêmes informations que l'’original.



Si RPignon modifie le prix unitaire du lave linge THOMSON SQ658 dans l’original, il est important de mettre les copies à jour.

La mise à jour des copies peut se faire:

Immédiatement ou de temps en temps (exemple: tous les soirs).

Mise en Oeuvre de la réplication synchrone:


Elle nécessite l'’écriture d’un trigger sur la base centrale (à GAP) pour la table APPAREILS.



CONNECT Rpignon/sonmotdepasse@alias_GAP

CREATE OR REPLACE TRIGGER maj_copies_APPAREILS

BEFORE INSERT OR UPDATE OR DELETE

ON APPAREILS

BEGIN

IF INSERTING THEN

INSERT INTO APPAREILS@LIEN_Toulouse values

( :new.no_appareil,

:new.designation,
:new.Pri_uni_HT,

:new.Caracteristique_technique

);

INSERT INTO APPAREILS@LIEN_Paris values

( :new.no_appareil,

:new.designation,
:new.Pri_uni_HT,

:new.Caracteristique_technique

);

INSERT INTO APPAREILS@LIEN_Bordeaux values

( :new.no_appareil,

:new.designation,
:new.Pri_uni_HT,

:new.Caracteristique_technique

);

END IF;

IF UPDATING THEN

UPDATE APPAREILS@Lien_Toulouse SET

Designation=:new.designation,
Pri_uni_HT= :new.Pri_uni_HT,

Caracteristique_technique= :new.Caracteristique_technique

WHERE no_appareil=:new.no_appareil;

UPDATE APPAREILS@Lien_Bordeaux SET

Designation=:new.designation,
Pri_uni_HT= :new.Pri_uni_HT,

Caracteristique_technique= :new.Caracteristique_technique

WHERE no_appareil=:new.no_appareil;



UPDATE APPAREILS@Lien_Paris SET

Designation=:new.designation,
Pri_uni_HT= :new.Pri_uni_HT,

Caracteristique_technique= :new.Caracteristique_technique

WHERE no_appareil=:new.no_appareil;

END IF;

IF DELETING THEN

DELETE FROM APPAREILS@Lien_Paris where no_appareil=:new.no_appareil;

DELETE FROM APPAREILS@Lien_Bordeaux where no_appareil=:new.no_appareil;

DELETE FROM APPAREILS@Lien_Toulouse where no_appareil=:new.no_appareil;

END IF;

END;



Exercice:

Dans quelle(s) base(s) doivent être créés les DATABASE LINK.

Les propriétaires des tables APPAREILS copiées sont respectivement à Paris, Bordeaux et Toulouse.

Owner_Paris_prod identifié par ‘mlk#9P’

Owner_Bordeaux_prod identifié par ‘jub6E’

Owner_Toulouse_prod identifié par ‘smurf.[9d’



Le DBA de la base gapençaise s’appelle: PETITGOUROU et son mot de passe est ‘JLAiPasOublie’

Ecrire les commandes SQL permettant de créer les DATABASE LINK nécessaires.



Mise en oeuvre de la réplication asynchrone:



A Bordeaux

Connect Owner_Bordeaux_prod/ jub#6E @ alias_Bordeaux



CREATE SNAPSHOT APPAREILS

REFRESH FAST

START WITH SYSDATE

NEXT SYSDATE+1

AS SELECT * FROM APPAREILS @ Lien_Gap;



A Toulouse

Connect Owner_Toulouse_prod/ smurf.[9d’@ alias_Toulouse



CREATE SNAPSHOT APPAREILS

REFRESH COMPLETE

START WITH SYSDATE

NEXT SYSDATE+1

AS SELECT * FROM APPAREILS @ Lien_Gap;

Exercice:

Créer le cliché de Paris

Quels sont les liens à créer et où. Ecrire les requêtes SQL permettant de les créer.



Quelques infos sur les clichés (snapshot):

Le rafraîchissement peut être rapide ou complet ou laissé à l'’appréciation d'’Oracle:

COMPLETE: Complet: la copie est remplacée par l’original.

FAST: Rapide: seules les lignes modifiées de l’original sont remplacées dans la copie. Cela nécessite la création d’un journal dans la base qui détient l'’original

Connect Rpignon/sonmotdepasse@alias_GAP

create snapshot log on APPAREILS.

Attention, cela ne fonctionne que lorsque le snapshot porte sur une requête simple liée à une seule table comme create snapshot …. As select * from APPAREILS.

FORCE: laissé au choix d’'oracle.



Le paramètre Start spécifie la date de création de la première copie.

Next précise la date de rafraîchissement suivante.

L’intervalle de rafraîchissement est ici d’un jour . Cela signifie que le cliché sera mis à jour tous les jours à la même heure.

Exercice:Une société basée à Turin dispose de plusieurs sites chacun disposant d’une base de donnée ORACLE.Chaque site a une base dans laquelle on trouve une table «EMPLOYE».Afin d’équilibrer les salaires entre les différents sites, la table «BAREME» est stockée sur la base de la maison mère à Turin.Voici la structure de la table BAREME: (Grade, année embauche, salaire brut)Pour effectuer la paie du personnel, il va falloir établir un lien entre la base locale et la base de Turin. Effectuez les travaux nécessaires.



Exercice de synthèse EXONET 36:

Énoncé

Contexte de travail Vous êtes chargé(e) de mettre en œuvre un système d'information réparti dans le contexte suivant :

Une société française vend des pièces détachées d'automobiles. Elle dispose de quatre distributeurs indépendants disposant eux-mêmes d'un entrepôt dans les régions de France suivantes : Lorraine, Alsace, Franche-Comté et Bretagne.

Les niveaux de stock minimum ne sont pas pris en compte tant que les entrepôts sont capables de répondre à la totalité de la commande d'un client. Lorsque l'entrepôt ne peut plus honorer les commandes, le responsable du contrôle de l'entrepôt présent sur le site passe une commande au fournisseur. Cette procédure implique que chaque entrepôt surveille ses stocks et passe ses commandes manuellement, car il n'existe aucune surveillance centralisée des stocks.

Chaque site possède une base de données Oracle 7.3.2 installée sur un serveur Windows NT4. Les différents sites sont reliés via le réseau téléphonique et une ligne Numéris.

On souhaite, depuis chaque site, pouvoir passer commande. La table COMMANDE doit être une table répartie sur les différents sites.

Travail à Réaliser

  1. Choisir un mécanisme de mise en cohérence des données de la table COMMANDE parmi les solutions suivantes :

  • Réplication synchrone de la table COMMANDE de chaque région vers la table COMMANDE du siège

  • Réplication synchrone de la table COMMANDE du siège vers chaque région

  • Réplication asynchrone de la table COMMANDE de chaque région vers un répliquât central unique

  • Réplication asynchrone de la table COMMANDE de chaque région vers quatre répliquâts centraux

  1. Vous disposez sur chaque site d'un compte STOCK identifié par le mot de passe STOCK et d'une chaîne de connexion de la région vers le siège nommée : PRODSI
    Créer les liens bases de données vous permettant de consulter à partir de la région les tables du siège.

  1. La table des commandes des distributeurs est nommée : COMDIST
    La table des commandes au siège est appelée : COMSIEGE

  • A partir du siège fournir l'instruction SQL permettant de consulter tous les commandes du siège :

  • Fournir l'instruction SQL permettant de créer un synonyme nommé COMMANDE pour l'objet distant COMSIEGE

  1. Vous désirez disposer d'un répliquât SNCOMSIEGE de la table COMSIEGE sur le site de chaque distributeur. Le rafraîchissement doit être rapide et sa mise à jour doit être effectuée toutes les 30 minutes.

  • Fournir l'instruction SQL permettant de créer le répliquât

  • Fournir l'instruction SQL nécessaire pour le rafraîchissement rapide