Bases de Données Distribuées (BDD) –TP 1– Distribution sous Oracle Dr. GHEMMAZ W Faculté NTIC [email protected] Etudiants concernés Faculté/Institut Département Niveau Spécialité Nouvelles technologies TLSI Master 1 SITW Université Constantine 2 2020/2021. Semestre 1 Résumé Prérequis Bases de données (L2) Exploitation & Administration des BD (L3) Objectifs du TP Comprendre le principe de distribution sous Oracle Comprendre le principe de réplication sous Oracle Maitriser les outils de distribution: Database Link & synonym Maitriser les outils de réplication: Copy & Snapshot Université Constantine 2 © Dr GHEMMAZ. W 2 Plan Connexion à Oracle Préparation des utilisateurs Outils de distribution sous Oracle Réplication sous Oracle Université Constantine 2 © Dr GHEMMAZ. W 3 Plan Connexion à Oracle Préparation des utilisateurs Outils de distribution sous Oracle Réplication sous Oracle Université Constantine 2 © Dr GHEMMAZ. W 4 Connexion à oracle Pour se connecter au SGBD Oracle, il faut fournir trois paramètres : Le nom d'utilisateur Le mot de passe L'alias : il renseigne sur plusieurs données à la fois : Le protocole réseau utilisé pour accéder à la machine cible, Le nom ou l'adresse de la machine cible sur laquelle se situe le serveur, Le SID cible ou le nom global de la base, Le port d'écoute du serveur. Définition: Le processus d'écoute Oracle (LISTNER) Le processus d'écoute Oracle est un service permettant à des clients d'utiliser le protocole TCP pour accéder une base de données distante …………… Université Constantine 2 © Dr GHEMMAZ. W 5 Connexion à oracle Le fichier de configuration du LISTNER se trouve dans C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN/ et se nomme « listener.ora » Dans ce fichier, on peut configurer (voir la figure ci-dessous): le nom de la machine (HOST), le port (par défaut est 1521), le nom du service d'écoute (DEFAULT_SERVICE_LISTENER). -> Utiliser Oracle Net Manager pour configurer le LISTENER Université Constantine 2 © Dr GHEMMAZ. W 6 Plan Connexion à Oracle Préparation des utilisateurs Outils de distribution sous Oracle Réplication sous Oracle Université Constantine 2 © Dr GHEMMAZ. W 7 Préparation des utilisateurs Sur chaque site, vous devez créer un utilisateur avec les droits suffisants .Vous pouvez utiliser "ALL PRIVILEGES" pour donner tous les privilèges à l'utilisateur. CREATE USER Nom_UT IDENTIFIED BY Password; GRANT ALL PRIVILEGES TO Nom_UT; Ou bien, vous pouvez préciser les privilèges à donner à l’utilisateur. GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE DATABASE LINK, CREATE SNAPSHOT TO Nom_UT; Université Constantine 2 © Dr GHEMMAZ. W 8 Plan Connexion à Oracle Préparation des utilisateurs Outils de distribution sous Oracle Réplication sous Oracle Université Constantine 2 © Dr GHEMMAZ. W 9 Outils de distribution 1. Les liens des bases de données Pour interroger une BD distante, il faut créer un lien de base de données (Database Link) Définition: Database Link Un lien de base de données est un chemin de communication unidirectionnel d'une BD à une autre. Il est contenu dans les méta-données dans la BD. Université Constantine 2 © Dr GHEMMAZ. W 10 Outils de distribution 1. Les liens des bases de données Le chemin inverse n'est pas possible Un client connecté à une BD 1, peut utiliser un lien stocké dans la BD1 pour accéder à la base distante BD2, mais les utilisateurs connectés à BD2 ne peuvent pas utiliser le même lien pour accéder aux données sur BD1. BD2 connaît BD1 mais l'inverse n'est pas vrai Université Constantine 2 © Dr GHEMMAZ. W 11 Outils de distribution 1. Les liens des bases de données Si les deux BD doivent se connaître mutuellement, il faut créer deux Database links comme le montre la figure ci-dessous. Un client connecté à la BD1 peut utiliser un lien stocké dans la BD1 pour accéder à la base distante BD2. Un client connecté à la BD2 peut utiliser un lien stocké dans la BD2 pour accéder à la base distante BD1 Université Constantine 2 © Dr GHEMMAZ. W 12 Outils de distribution 1. Les liens des bases de données Lorsqu'un lien est référencé par une instruction SQL, Oracle ouvre une session dans la base distante et y exécute l'instruction. La syntaxe de création des liens des bases de données est la suivante : CREATE [PUBLIC|PRIVATE] DATABASE LINK dbLink [CONNECT TO User IDENTIFIED BY Password] [USING connect_string]; PUBLIC|PRIVATE : Un lien est soit privé ou public. Seul l'utilisateur qui a crée un lien privé peut l'utiliser, alors qu'un lien public est utilisé par tous les utilisateurs de la base de données. CONNECT TO active une session vers la base distante. USING connect_string : spécifie le nom de service d'une base distante (ou le nom de la base distante). Université Constantine 2 © Dr GHEMMAZ. W 13 Outils de distribution 1. Les liens des bases de données Remarques Si vous spécifiez uniquement le nom de la base de données, Oracle ajoutera de façon implicite le domaine de la base de données dans laquelle le lien est créé à la chaîne de connexion pour former un nom de service complet. Dans un système de base de données distribuées, le domaine d'une BD (DB_DOMAIN) spécifie l'emplacement logique de la base de données dans la structure du réseau. Les noms de service d'instances sont stockés dans le fichier de configuration tnsnames.ora. Ce fichier spécifie l'hôte, le port, et l'instance associés à chaque nom de service. le fichier tnsnames.ora se trouve dans le répertoire C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN/ Vous pouvez mettre seulement le port et hot. Université Constantine 2 © Dr GHEMMAZ. W 14 Outils de distribution 1. Les liens des bases de données L'instruction suivante montre comment créer le lien de base de données vers un utilisateur dans une base de données distante avec une chaîne de connexion complète : CREATE DATABASE LINK dblink CONNECT TO user IDENTIFIED BY password USING '(DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>)) (CONNECT_DATA = (SERVICE_NAME = <service_name>)) )'; Vous pouvez également ajouter le code ci-dessous au fichier de configuration tnsnames.ora, puis utiliser CREATE DATABASE LINK. <addressname> = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>)) (CONNECT_DATA = (SERVICE_NAME = <service_name>)) ) Université Constantine 2 © Dr GHEMMAZ. W 15 Outils de distribution 1. Les liens des bases de données CREATE DATABASE LINK dblink CONNECT TO user IDENTIFIED BY password USING addressname; Exemple Créer un lien de base de données vers un serveur de base de données Oracle distant situé dans le serveur 10.50.100.143 avec le port 1521 et le nom de service SALES-PRD. <SALES> = (DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(Host = 10.50.100.143)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = <SALE_PRD>)) ) CREATE DATABASE LINK Link_Sale CONNECT TO Mohamed IDENTIFIED BY mod1234 USING SALES; Université Constantine 2 © Dr GHEMMAZ. W 16 Outils de distribution 1. Les liens des bases de données Utilisation du Database Link Une fois un lien de BD est créé, vous pouvez accéder aux tables ou aux vues à partir de la BD distante en utilisant le schéma suivant: table_name@database_link Par exemple, vous pouvez interroger les données d'une table de la base de données distante en utilisant l'instruction suivante: SELECT * FROM table_name@database_link; Exemple SELECT * FROM customers@Link_sales; Université Constantine 2 © Dr GHEMMAZ. W 17 Outils de distribution 1. Les liens des bases de données Affichage des liens créés SELECT * FROM dba_db_links; Vers une transparence à la localisation Dans les BDD, les applications ne doivent pas connaître l’emplacement physique des données. Donc, les noms des objets doivent être indépendants de leurs localisation --> Le concept de l'indépendance à la localisation peut être traité par l'utilisation des synonymes. Université Constantine 2 © Dr GHEMMAZ. W 18 Outils de distribution 2. Synonymes Après avoir crée les liens de bases de données, les synonymes peuvent servir à cacher la distribution des données aux utilisateurs. Définition: Synonyme Les synonymes sont des noms simples qui permettent d'identifier de façon unique dans un système distribué les objets qu'ils nomment. Les synonymes peuvent être crées pour différents objets : Tables, Types, Views, Snapshots, Procedures, Functions, Packages. La syntaxe de création des synonyme est la suivante : CREATE OR REPLACE [PUBLIC] SYNONYM nom-synonyme FOR [schéma.]nom-objet[@nom-lien-BD] Université Constantine 2 © Dr GHEMMAZ. W 19 Outils de distribution 2. Synonymes L'interrogation des synonymes se fait par l'instruction suivante : SELECT * FROM nom_synonyme; Exemple CREATE OR REPLACE SYNONYM Cust FOR Customers@Link_sales; SELECT * FROM Cust; Université Constantine 2 © Dr GHEMMAZ. W 20 Plan Connexion à Oracle Préparation des utilisateurs Outils de distribution sous Oracle Réplication sous Oracle Université Constantine 2 © Dr GHEMMAZ. W 21 Réplication sous Oracle 1. Copy La première option consiste à répliquer régulièrement les données sur le serveur local au moyen de la commande COPY de SQL*Plus. Cette commande permet de copier des données entre deux SGBDs . La meilleure utilisation est d'exécuter cette commande sur la machine où réside la base de données. La syntaxe est la suivante : COPY [FROM database | TO database | FROM database TO database] [APPEND|CREATE|INSERT|REPLACE destination_table [(column, column, column, ...)] USING query; [FROM database | TO database | FROM database TO database] : FROM database : La base de données qui contient les données à copier. TO database : La base de données contenant la table de destination. Université Constantine 2 © Dr GHEMMAZ. W 22 Réplication sous Oracle 1. Copy "database" a la syntaxe suivante : username[/password]@connect_identifier APPEND : Si la table de destination existe , COPY insère les lignes de la requête (Query) dans cette table (INSERT) Si la table de destination n'existe pas, COPY la crée et ensuite insère les lignes (CREATE + INSERT). CREATE : Si la table n'existe pas, COPY insère les lignes de la requête (Query) après la création de la table de la destination (CREATE + INSERT) Si la table de destination existe déjà, COPY renvoie une erreur. Université Constantine 2 © Dr GHEMMAZ. W 23 Réplication sous Oracle 1. Copy INSERT : Si la table existe, COPY Insère les lignes de la requête cette table (INSERT). Si la table de destination n'existe pas, COPY renvoie une erreur. (Lorsque vous utilisez INSERT, la requête USING doit sélectionner une colonne pour chaque colonne de la table_destination). REPLACE : Remplace destination_table et son contenu par les lignes de la requête. Si la table de destination n'existe pas, COPY la crée et ensuite insère les lignes ( CREATE + INSERT). Si la table existe, COPY supprime la table existante et la remplace par une table contenant les données copiées (DROP + CREATE + INSERT). Université Constantine 2 © Dr GHEMMAZ. W 24 Réplication sous Oracle 1. Copy Exemple COPY FROM BDRE1/[email protected]/XE TO BDRE2/[email protected]/XE REPLACE Employé USING SELECT * FROM Employé Remarque • • La commande COPY n'exporte pas les contraintes ( sauf NOT NULL). L'inconvénient est que les données ne peuvent pas être mises à jour. La commande REPLACE est utilisée pour remplacer le contenu des tables. Université Constantine 2 © Dr GHEMMAZ. W 25 Réplication sous Oracle 2. Snapshot (Materialized View) Définition: Snapshot Un Snapshot est une copie conforme d'une table (ou plusieurs) située sur une base de donnée du système distribué . Afin d'assurer la cohérence de données, une mise à jour régulière et automatique est effectuée à partir du site maître. Les snapshots ( Instantanés ou clichés) est une technique d'oracle pour la réplication asynchrone. Les snapshots sont utilisés pour répliquer les données depuis une source maître vers plusieurs esclaves. Les snapshots peuvent être : En lecture seule (read-only) Mis à jour (updateable) Université Constantine 2 © Dr GHEMMAZ. W 26 Réplication sous Oracle 2. Snapshot Snapshot en lecture seule (read-only) Réplication de base : des réplicats de tables sont gérés pour l'accès en lecture seule. Pour les mises à jour, on ne peut pas accéder aux données que sur un seul site maître (non modifiable à partir du site esclave) Snapshot de mis à jour (updateable) Réplication avancée : permet aux applications de mette à jour des réplicats de tables à travers une BDD répliquée. Les données peuvent être lues et mise à jour sur n'importe quel site. Remarque Avant de créer un snapshot, il faut d'abord créer un lien vers la base de données source. Université Constantine 2 © Dr GHEMMAZ. W 27 Réplication sous Oracle 2. Snapshot Snapshot read-only CREATE SNAPSHOT nom_snapshot /CREATE MATERIALIZED VIEW nom_VueM [REFRESH FAST | COMPLETE | FORCE] START WITH date_de_debut_de_synchronisation NEXT date_de_la_prochaine_synchronisation AS requete_select; Snapshot updateable CREATE SNAPSHOT nom_snapshot [REFRESH FAST | COMPLETE | FORCE] START WITH date_de_debut_de_synchronisation NEXT date_de_la_prochaine_synchronisation ENABLE QUERY REWRITE AS requete_select; Université Constantine 2 © Dr GHEMMAZ. W 28 Réplication sous Oracle 2. Snapshot REFRESH FAST: Le mode rapide permet de faire un rafraîchissement en tenant compte seulement des mises à jour effectuées sur le site maître. En effet, un REFRESH FAST utilise un snapshot log, pour actualiser le snapshot. Ce fichier se trouve sur le même site que la table maître. Dans le snapshot log, sont stockées les modifications intervenues sur la table maître. Ainsi, pour chaque mise à jour, seules les modifications qui sont envoyées, et non l'ensemble des données. La syntaxe de snapshot log est la suivante: CREATE SNAPSHOT LOG ON nom_Table; REFRESH COMPLETE : est obligatoire pour les snapshots complexes. REFRESH FORCE (FAST + COMPLETE) : Un rafraîchissement rapide est d'abords tenté; s'il ne marche pas le rafraîchissement complet est effectué Université Constantine 2 © Dr GHEMMAZ. W 29 Réplication sous Oracle 2. Snapshot Les snapshots peuvent être : simples ou complexes. Snapshot simple: Un snapshot simple ne contient pas de clause distinct, group by, connect by, de jointure multitables (JOIN)ou d'opérations SET. Snapshot complexe: Un snapshot est dit complexe dans le cas contraire. Université Constantine 2 © Dr GHEMMAZ. W 30 Réplication sous Oracle 2. Snapshot Exemple Le snapshot suivant est défini de façon à extraire les données maîtres et renouveler l'opération 7 jours plus tard. CREATE SNAPSHOT employé_snap_fast REFRESH FAST START WITH Sysdate NEXT Systdate + 7 AS SELECT * FROM employé@DBlink1 Remarque Les snapshots en mise à jour peuvent engendrer des conflits. Un déclencheur (ang. trigger) sauve les mises à jour opérées sur le snapshot et les transmet au site maître au moment du rafraîchissement du snapshot. --> Les triggers seront discutés dans "TP Trigger". Université Constantine 2 © Dr GHEMMAZ. W 31 Références Rim Moussa. « Systèmes de Gestion de Bases de Données Réparties & Mécanismes de Répartition avec Oracle » Abdelouahed Abdou. « Bases de données réparties par la pratique » Bachtarzi Chahinez. « Concepts des bases de données distribuées » « SGBD distribués Concepts avancés ». Éditions Reynald Goulet inc. « Réplication et bases de données mobiles ». Éditions Reynald Goulet inc. https://www.oracletutorial.com/ https://www.oracle.com/database/ Université Constantine 2 © Dr GHEMMAZ. W 32