Introduction à l’Administration d’Oracle

Table des matières CHAPITRE 1 INTRODUCTION ........................................................................... 6 1.1 Oracle : Vue d’ensemble .................................................................................................................... 6 1.2 Oracle Database ................................................................................................................................. 7 1.3 Interaction avec Oracle Database ..................................................................................................... 8 CHAPITRE 2 ARCHITECTURE D’ORACLE DATABASE .................................. 9 2.1 La base de données ............................................................................................................................. 9 2.1.1 Les fichiers de contrôle ................................................................................................................... 9 2.1.2 Les fichiers de données ................................................................................................................. 10 2.1.3 Les fichiers de journalisation ........................................................................................................ 11 2.1.4 Les archives des fichiers de journalisation .................................................................................... 13 2.1.5 Le fichier de paramètres ................................................................................................................ 13 2.1.6 Le fichier de mot de passe ............................................................................................................. 14 2.1.7 Autres fichiers ............................................................................................................................... 14 2.2 L’instance ......................................................................................................................................... 15 2.2.1 Les structures mémoires ................................................................................................................ 15 2.2.2 Les processus ................................................................................................................................ 18 2.3 Le schéma ......................................................................................................................................... 22 2.4 Le dictionnaire de données .............................................................................................................. 23 2.4.1 Les vues statiques .......................................................................................................................... 23 2.4.2 Les vues dynamiques de performance ........................................................................................... 24 2.5 Fonctionnement d’Oracle ................................................................................................................ 25 2.5.1 Pour exécuter une requête SELECT .............................................................................................. 25 2.5.2 Pour exécuter une requête UPDATE .............................................................................................. 26 2.5.3 Lors d’un COMMIT ........................................................................................................................ 26 2.5.4 Lors d’un ROLLBACK ................................................................................................................... 26 CHAPITRE 3 DEMARRAGE/ARRET DE LA BASE DE DONNEES ET GESTION DE L’INSTANCE ..................................................................................... 28 3.1 Démarrage et Arrêt de la base de données .................................................................................... 28 3.1.1 Démarrage de la base de données .................................................................................................. 28 3.1.2 Arrêt de la base de données ........................................................................................................... 30 3.2 Gestion de l’instance ........................................................................................................................ 31 3.2.1 Paramètres dynamiques et paramètres statiques ............................................................................ 31 3.2.2 Instancier les paramètres via SQL ................................................................................................. 31 3.2.3 Exporter un fichier de paramètres ................................................................................................. 32 CHAPITRE 4 GESTION DES TABLESPACES ................................................. 33 4.1 Tablespaces : types et conseils d’utilisation ................................................................................... 33 4.2 Les tablespaces permanents ............................................................................................................ 34 4.2.1 Création des tablespaces permanents ............................................................................................ 34 4.2.2 Extension des tablespaces permanents .......................................................................................... 35 4.2.3 Basculer entre les modes OFFLINE/ONLINE .............................................................................. 36 4.2.4 Basculer entre les modes READ ONLY/READ WRITE .............................................................. 37 4.2.5 Basculer entre les modes LOGGING/NOLOGGING ................................................................... 37 4.2.6 Basculer entre les modes FORCE LOGGING/NO FORCE LOGGING ...................................... 37 4.2.7 Renommer un tablespace .............................................................................................................. 37 4.2.8 Renommer/déplacer un fichier de données ................................................................................... 37 4.2.9 Suppression d’un tablespace ......................................................................................................... 38 4.3 Gestion de l’espace à l’intérieur des tablespaces ........................................................................... 38 4.3.1 Les tablespaces gérés par le dictionnaire de données .................................................................... 39 4.3.2 Les tablespaces gérés localement .................................................................................................. 40 4.4 Informations sur les tablespaces ..................................................................................................... 41 CHAPITRE 5 GESTION DES UTILISATEURS ................................................. 43 5.1 L’objet USER .................................................................................................................................... 43 5.1.1 La connexion ................................................................................................................................. 43 5.1.2 Création d’un utilisateur ................................................................................................................ 43 5.1.3 Modification d’un utilisateur ......................................................................................................... 44 5.1.4 Suppression d’un utilisateur .......................................................................................................... 45 5.1.5 Informations sur les utilisateurs .................................................................................................... 45 5.2 L’objet PROFILE ............................................................................................................................. 45 5.3 Les privilèges .................................................................................................................................... 46 5.3.1 Les privilèges système .................................................................................................................. 47 5.3.2 Les privilèges objet ....................................................................................................................... 48 5.4 L’objet ROLE .................................................................................................................................... 49 5.4.1 Création et modification d’un rôle ................................................................................................ 49 5.4.2 Attribuer/retirer un privilège à un rôle .......................................................................................... 50 5.4.3 Attribuer/retirer un rôle à un utilisateur......................................................................................... 51 5.4.4 Activer/désactiver un rôle ............................................................................................................. 51 CHAPITRE 6 CREATION D’UNE NOUVELLE BASE DE DONNEES .............. 53 6.1 Créer les répertoires sur le disque : ................................................................................................ 53 6.1.1 La norme OFA .............................................................................................................................. 53 6.1.2 La création des répertoires ............................................................................................................ 55 6.2 Préparer un fichier de paramètres ................................................................................................. 56 6.3 Créer le service associé à l’instance ................................................................................................ 56 6.4 Lancer SQL*PLUS .......................................................................................................................... 57 6.5 Créer le fichier de paramètres serveur .......................................................................................... 57 6.6 Démarrer l’instance ......................................................................................................................... 57 6.7 Créer la base de données ................................................................................................................. 58 6.8 Finalisation de la création du dictionnaire de données ................................................................. 60 CHAPITRE 7 GESTION DES FICHIERS DE CONTROLE ET DE JOURNALISATION .................................................................................................. 61 7.1 Gestion des fichiers de contrôle ...................................................................................................... 61 7.1.1 Création initiale des fichiers de contrôle ....................................................................................... 61 7.1.2 Création de copies additionnelles, renommage, et déplacement des fichiers de contrôle ............. 62 7.1.3 S’informer sur les fichiers de contrôle .......................................................................................... 62 7.2 Gestion des fichiers de journalisation ............................................................................................ 63 7.2.1 Informations sur les fichiers journaux ........................................................................................... 63 7.2.2 Définir le nombre et les tailles des fichiers de journalisation ........................................................ 64 7.2.3 Gestion des fichiers journaux ........................................................................................................ 65 CHAPITRE 8 GESTION DES INFORMATIONS D’ANNULATION.................... 68 8.1 Segment d’annulation : vue d’ensemble......................................................................................... 68 8.1.1 Utilité des informations d’annulation ............................................................................................ 68 8.1.2 Gestion des informations d’annulation .......................................................................................... 68 8.1.3 Le segment d’annulation SYSTEM .............................................................................................. 69 8.1.4 Fonctionnement d’un segment d’annulation ................................................................................. 69 8.1.5 La durée de rétention des informations d’annulation .................................................................... 69 8.2 La gestion automatique des informations d’annulation ............................................................... 70 8.2.1 La mise en œuvre .......................................................................................................................... 70 8.2.2 Fonctionnement d’un tablespace d’annulation .............................................................................. 71 8.2.3 Création d’un tablespace d’annulation .......................................................................................... 71 8.2.4 Changement du tablespace d’annulation actif ............................................................................... 72 8.2.5 Modification et suppression d’un tablespace d’annulation ........................................................... 72 CHAPITRE 9 GESTION DES TABLES ET DES INDEX ................................... 73 9.1 La gestion des tables ........................................................................................................................ 73 9.1.1 Organisation du stockage dans les blocs ....................................................................................... 73 9.2 Le ROWID ........................................................................................................................................ 76 9.3 Chainage et migration ..................................................................................................................... 76 9.4 Spécifier le stockage d’une table ..................................................................................................... 77 CHAPITRE 10 SAUVEGARDE ET RESTAURATION ....................................... 81 10.1 Archivage des fichiers de journalisation ........................................................................................ 81 10.2 Les stratégies de sauvegarde ........................................................................................................... 83 10.3 Archivage des fichiers journaux ................................................................ Erreur ! Signet non défini. BIBLIOGRAPHIE ................................................................................................. 86 Chapitre 1 Introduction Dans ce cours, nous essayerons d’illustrer les notions de base de l’administration d’un serveur de base de données à travers la technologie Oracle. Le choix se porte sur Oracle parmi une variété d’éditeurs (IBM, Microsoft etc.) pour diverses raisons : 1- Il s’agit du leader du marché des SGBD. 2- La documentation est abondante sur le Web. 3- En Tunisie, il existe un représentant officiel de la firme (ORADIST), qui aide les académiques à travers la documentation et les formations des universitaires. Pour une maitrise parfaite de l’administration d’un serveur de bases de données, il faut passer par trois étapes, à savoir l’assimilation de l’architecture d’un serveur BD, l’administration du serveur, et finalement l’optimisation du serveur. Seul le troisième volet ne sera pas évoqué dans ce cours. 1.1 Oracle : Vue d’ensemble Oracle Corporation est sans conteste le leader du marché des Systèmes de Gestion de Base de Données Relationnelles, disposant de la plus grande part de marché (année 2013). Le produit fondamental commercialisé par Oracle est « Oracle Database », cela n’empêche que la firme américaine commercialise d’autres types de produits intégrés. La famille de produits Oracle est la suivante : Oracle Database Oracle Developer Suite Oracle Application Server Oracle Applications Oracle Collaboration Suite Oracle Services Brièvement, Oracle Database est le SGBD qui permet de stocker, gérer, administrer et manipuler des données d’un grand volume tout en assurant performance, sécurité et accès concurrentiel. Les fonctions décrites ci-dessus ne sont naturellement pas exhaustives, mais plutôt basiques et Oracle ne cesse d’innover en ce sens. La version Database la plus évoluée étant Oracle 12c (parue fin 2013). Oracle Developer Suite est un ensemble d’outils de conception ( Developer Designer ), développement ( Developer Forms, JDeveloper ), édition d’états ( Developer Reports, Discoverer ), implémentation de Data Warehouse ( Warehouse Builder ) et de déploiement d’applications basés Web. Oracle Application Server est utilisé pour le déploiement d’applications Web développés entre autres par Oracle Developer Suite. Ce produit assure la fiabilité et l’efficacité d’utilisation des applications pour des milliers d’utilisateurs distants. Oracle Introduction 7 Applications est un ensemble de modules standard prédéveloppés et paramétrables qui gèrent le personnel, la finance, la production, la vente, les achats, et autres fonctions d’entreprises de différents secteurs. À noter que ces modules utilisent naturellement Oracle Database, Developer Suite et Application Server. Oracle Collaboration Suite est un système exhaustif qui intègre des outils puissants de collaboration et de communication allant de la messagerie électronique jusqu’aux systèmes de conférences Web en passant par la télécopie, la messagerie instantanée, le calendrier partagé ainsi que la gestion de documents. Oracle Collaboration Suite utilise aussi Oracle Database, Oracle Developer Suite et Oracle Application Server. Oracle Services offre une plateforme complète de support technique aux utilisateurs pour qu’ils puissent choisir, installer, et configurer leurs outils Oracle de la meilleure manière possible relativement à leurs besoins spécifiques. Cette vue d’ensemble nous permet de mieux nous situer par rapport à l’ensemble des produits d’Oracle. En effet, nous essayerons à travers ce polycopié d’introduire les bases de l’administration d’un SGBD Oracle, ce qui nous place sur le produit « Database » parmi la panoplie des produits listés ci-dessus. Architecture basique et minimale d’Oracle Database, gestion de la mémoire, gestion du stockage physique et gestion de la sécurité seront donc les principaux axes de ce document. 1.2 Oracle Database C’est le produit principal d’Oracle, soit le Système de Gestion de Bases de Données Relationnelles qui est disponible sur plusieurs plateformes telles que Windows, Linux et Unix. Depuis 1977, année de création de la firme Oracle (jadis appelée SDL pour Software Development Laboratories ), le produit Database est passé de la version 1 à la version 11g en 30 ans. Dans ce polycopié, nous nous basons sur des documents (voir bibliographie) qui traitent surtout les versions 10g et 9i . Il est à noter qu’Oracle Database est disponible sous différentes éditions, les voici : Enterprise Edition : inclut toutes les fonctionnalités d’Oracle Database, en standard ou en option, et gère des données extrêmement volumineuses. Standard Edition : inclut les fonctionnalités de base, mais ne permet pas d’exploiter certaines options avancées. Cette édition est d’ailleurs destinée pour des serveurs avec une capacité maximale de quatre processeurs. Standard Edition One : pratiquement identique à l’édition standard mais limité à des serveurs biprocesseurs. Personal Edition : disponible uniquement sur Windows, destinée aux développeurs pour une utilisation mono-utilisateur. Express Edition : complètement gratuite, destinée pour des machines monoprocesseurs et spécialement pour les petites entreprises, voire les institutions à but académique. Lite Edition : inclut les fonctionnalités requises pour le développement et le déploiement Administration d’Oracle Database - Document 1.5 Feedbacks à www.bach-tobji.com Introduction d’applications et de base de données mobiles. 8 Cela étant dit, les notions de base étalées dans ce polycopié concernent toutes les éditions voire même les dernières versions. D’ailleurs nous ne manquerons pas de vous signaler une nouveauté particulière qui correspond à la version 10g . 1.3 Interaction avec Oracle Database Le moyen le plus basique d’interagir avec les objets d’une base de données Oracle, tels que les tables, les séquences, les index, les utilisateurs, les vues etc., est le langage SQL servant à définir (LDD), manipuler (LMD) et contrôler (LCD) les données. Un utilisateur peut formuler des requêtes SQL via : SQL Plus qui est un éditeur permettant la saisie et l’affichage des résultats de requêtes SQL. Naturellement, pour accéder aux objets de la base de données, il faut que l’utilisateur ait un login et un mot de passe et qu’il connaisse la chaîne de connexion (connection string) de la base de données si elle est distante, sachant que dans ce cas, l’éditeur SQL Plus est juste installé sur un poste client. iSQLPlus est aussi un éditeur de saisie de requêtes SQL. A la différence de SQL*Plus, c’est un éditeur Web et donc au poste client (celui de l’utilisateur), il suffit d’avoir un navigateur Web ainsi que l’URL du serveur hébergeant la base de données, avec bien sur un login et un mot de passe, pour que la connexion soit réussie. Il existe d’autres outils d’administration et de développement. Oracle Discoverer par exemple est un outil graphique qui permet à l’utilisateur de sélectionner les tables qu’il veut interroger en cliquant dessus. Oracle Forms et Oracle Reports permettent respectivement de développer des applications Web et d’éditer des états basés sur les données d’Oracle Database. OEM (Oracle Enterprise Manager) quant à lui est une interface graphique qui permet l’administration et la configuration d’Oracle Database. Lorsque l’utilisateur change la valeur d’un paramètre dans l’interface, une requête SQL est générée et est envoyée à la base de données. OEM est disponible en version Web et donc utilisable à partir de n’importe quel poste client connecté au serveur base de données (à condition que le processus OEM soit lancé sur le serveur, ou en version client ( EM Client ) installable sur la machine client et offre les même possibilités que la version Web. Le langage [1] PL/SQL permet aussi l’interaction avec Oracle Database. C’est un langage procédural extension du langage SQL qui permet non seulement d’utiliser des structures conditionnelles et itératives et de traiter les exceptions, mais aussi de créer des fonctions et procédures personnalisées et de développer des packages et des déclencheurs. 1 Il faut bien faire la différence entre langage (tel que SQL et PL/SQL) et outil tel qu’un éditeur ou autre nous permettant soit de saisir du code, soit de manipuler directement et graphiquement les objets base de données (OEM, SQL*Plus, iSQLPlus etc.). Administration d’Oracle Database - Document 1.5 Feedbacks à www.bach-tobji.com Chapitre 2 Architecture d’Oracle Database Un serveur de base de données Oracle inclut deux composantes importantes, l’instance et la base de données . La base de données est l’ensemble de fichiers qui contiennent entre autres les données, les informations de la base ainsi que le journal de modification des données. L’instance quant à elle est un ensemble de processus et d’emplacements mémoire qui facilitent l’accès et la manipulation de la base de données. 2.1 La base de données La base de données est constituée d’un ensemble de fichiers physiques situés sur les disques durs du serveur hébergeant la base. La Figure 1 montre les différents types de ces fichiers, il est à noter que les archives des fichiers de journalisation, le fichier de paramètres ainsi que le fichier des mots de passe ne font pas partie prenante de la base de données, mais y sont étroitement reliés. Fichier de paramètres Archives des fichiers de journalisation Fichier de mot de passe Figure 1 : Les fichiers physiques d'une base de données 2.1.1 Les fichiers de contrôle Ils contiennent des informations de contrôle sur la base de données tel que : Le nom de la base de données. Les noms, les chemins et les tailles des fichiers de données et de journalisation. Les informations de restauration de la base de données en cas de panne. Le fichier de contrôle est primordial pour que l’instance soit lancée correctement. En effet, cette dernière y lit les chemins de fichiers de données, ainsi que ceux des fichiers de journalisation (et d’autres informations nécessaires au lancement). Si ce dernier est endommagé, la base de données ne peut pas être chargée même si les autres fichiers physiques sont intacts. C’est pour Architecture d’Oracle Database 10 cela qu’il est possible (et recommandé) de multiplexer le fichier de contrôle sur des endroits différents du disque dur. Il est à noter que les informations des fichiers de contrôle peuvent être examinées à partir de la vue V$CONTROLFILE . 2.1.2 Les fichiers de données Ce sont les fichiers physiques qui stockent les données de la base sous un format spécial Oracle. Les fichiers de données sont logiquement regroupés en structures logiques appelées tablespaces . Une base de données comporte au moins deux fichiers de données relatifs à deux tablespaces différents réservés par Oracle ( SYSTEM et SYSAUX , ce dernier étant apparu en Oracle 10g). Ces deux tablespaces ne doivent logiquement contenir aucune donnée applicative. A titre indicatif, le tablespace SYSTEM inclut le dictionnaire de données ainsi que le code PL/SQL (fonctions, procédures etc.). En revanche, une organisation parmi tant d’autres consiste à réunir les tables qui portent sur le même contexte (comptabilité, gestion de stock, gestion de personnel etc.) dans un même tablespace ; le résultat est d’avoir des données regroupés par application/contexte. A noter que les vues DBA_TABLESPACES et DBA_DATA_FILES incluent toutes les informations respectivement relatives aux tablespaces et aux fichiers de données de la base. La requête suivante liste les fichiers de données utilisés dans la base triés par les tablespaces : REQ 1 SELECT tablespace_name, file_name FROM DBA_DATA_FILES ORDER BY tablespace_name; Un fichier de données est un ensemble de blocs d’une taille donnée (4 Ko, 8Ko, 16 Ko etc.). Le bloc de données est la petite unité d’E/S utilisée par Oracle et un fichier de données a forcément une taille qui est multiple de la taille du bloc. Logiquement, un tablespace (étalé sur un ou plusieurs fichiers de données), est un ensemble de segments . Un segment est l’espace occupé par un objet base de données dans un tablespace, il y en a quatre types : Segment de table : espace occupé par une table Segment d’index : espace occupé par un index Segment d’annulation : espace temporaire utilisé pour stocker les données nécessaires à l’annulation d’une transaction, ainsi qu’à la lecture cohérente des données. Segment temporaire : espace temporaire ou sont stockées des données temporaires utilisées lors d’un tri, d’une jointure, lors de la création d’un index etc. En effet, les principaux types d’objets appartenant à un utilisateur (constituant un schéma ) sont les tables, les index, les vues, les synonymes, les séquences et les programmes PL/SQL. Administration d’Oracle Database - Document 1.5 Feedbacks à www.bach-tobji.com Architecture d’Oracle Database 11 Parmi ces différents types d’objets, seuls les tables et les index [1] consomment de l’espace disque en dehors du dictionnaire de données. Plus précisément, ils occupent de l’espace mémoire sur les fichiers de données. Les autres types d’objets n’ont qu’une définition stockée dans le dictionnaire de données. Un segment est à son tour composé de structures logiques appelées extensions . Une extension est un ensemble de blocs contigus dans un même fichier de données, tandis qu’un segment peut être étalé sur plusieurs extensions chacun sur un fichier unique. Un bloc de données est la plus petite unité physique d’E/S des données. Sa taille est définie via le paramètre DB_BLOCK_SIZE . Col1 Segm ent A (Ext ent 1) Col6 Segm ent B (Ext ent 1) Segm ent C (Ext ent 1) Col1 Segm ent A (Ext ent 2 ) Segm ent B (Ext ent 2) Data01.dbf Data02.dbf Figure 2 : Structure d'un tablespace incluant deux fichiers La Figure 2 présente un tablespace composé de deux fichiers de données. Le tablespace inclut trois segments A, B et C. Les segments A et B incluent chacun deux extensions chacune sur un fichier différent, pendant que le segment C inclut une seule extension. La Figure 3 représente la structure logique et physique d’une base de données Oracle, remarquez que le sens des flèches entre les différentes structures indique une relation un à plusieurs. 2.1.3 Les fichiers de journalisation Tout d’abord, faisons un petit rappel sur la notion de transaction ; une transaction est un ensemble d’opérations (requêtes) de mises à jour (insert, update et delete) qui finit par un COMMIT (validation) ou un ROLLBACK (annulation). La validation/annulation concerne tout le bloc de mises à jour (l’ensemble des opérations) depuis un COMMIT/ROLLBACK ultérieur ou depuis le début de la connexion. Une transaction finit donc par un COMMIT/ROLLBACK ou par une déconnexion de l’utilisateur qui vaut un COMMIT si c’est une déconnexion normale et un ROLLBACK si elle ne l’est pas. 1 Il existe en effet d’autres objets occupant de l’espace disque, tel que les vues matérialisées, les tables organisées en index (IOT), les clusters etc. Administration d’Oracle Database - Document 1.5 Feedbacks à www.bach-tobji.com Architecture d’Oracle Database 12 Bloc de donnée Col2 Bloc de donnée Structure Logique Structure Physique Figure 3 : Structure logique et physique d'une base...