Corrigé janvier 2006 1. a) Un transaction est un ensemble cohérent de modifications faites sur les données. Une transaction est soit entièrement annulée (ordre SQL ROLLBACK) , soit entièrement validée (ordre SQL COMMIT). Tant qu'il n'y a pas eu COMMIT, seul l'utilisateur courant voit ses mises à jour. Une transaction débute lorsque l'on se connecte à la base (en début de session donc) ou lorsque la transaction précédente se termine. Il est possible de définir des sous-transactions et de faire des annulations partielles de transaction jusqu’à une étiquette préalablement définie, grace a la commande SQL savepoint. b) connect … update… delete … commit … update … delete … rollback c) La structure permettant de faire des retours en arrière (ROLLBACK) s'appelle un ROLLBACK SEGMENT et est gérée par le DBA. Les COMMIT et ROLLBACK peuvent être implicites. COMMIT : Lorsqu'on envoie un ordre SQL de création ou modification de table par exemple, ou plus généralement tout ordre du langage de définition de données. ROLLBACK : en cas d'interruption anormale du traitement ou d'erreur. d) Commit situé dans la mémoire, alors que rollback situé dans un segment (fichier). 2. L'instance est en fait la composition de 2 sous ensembles : Une zone mémoire : La SGA Elle va servir à stocker les données issues des fichiers de données sur le disque dur. Afin de pouvoir les partager entre les différents processus. Des processus d'arrière plan : Ils vont servir à gérer les transferts de données entre la mémoire et le disque dur, plus d'autres actions nécessaires au bon fonctionnement de la base de données. L'instance est indispensable au bon fonctionnement d'une base de données Oracle. Sans instance il ne vous sera pas possible d'accèder à la base de données. Il faut savoir qu'une instance ne pourra être assignée qu'à une seule base de données (sauf en environnement RAC). 3) Le compte SYS : Créé lors de l’installation d’Oracle avec tous les privilèges systèmes. Mot de passe par défaut : change_on_install, à changer après l’installation. Le schéma de SYS stocke toutes les tables et les vues du dictionnaire de base de données. Aucun utilisateur ne peut modifier ou créer de table dans ce schéma. Le compte SYSTEM : Créé lors de l’installation d’Oracle avec tous les privilèges systèmes. Mot de passe par défaut : manager à changer après l’installation. Le schéma de SYSTEM stocke les tables et les vues administratives ainsi que des informations administratives propres à certains produits Oracle supplémentaires. Faille : existance de l’utilisateur Scott (mdp :tiger) ; accès qu'au schéma de Scott et pas au autres donc faille mineure. 4. a) Pour permettre de gérer l’étendue des modifications dans alter system, Oracle a enrichi cette commande de quelques notions supplémentaires : Alter system set instruction SCOPE=SPFILE Le paramètre ainsi modifié sera répertorié dans spfile, mais ne prendra effet que lors du démarrage suivant de l’instance. Alter system set instruction SCOPE=MEMORY Le paramètre ainsi modifié ne sera pas répertorié dans spfile et entrera immédiatement en vigueur pour l’instance en cours. Alter system set instruction SCOPE=BOTH Le paramètre ainsi modifié sera répertorié dans spfile et entrera immédiatement en vigueur pour l’instance en cours. Le contenu du spfile est visible dans la vu dynamique V$SPPARAMETER et non pas V$PARAMETER qui est utilisé par le pfile. Manque une partie de la réponse 5. Table partitionnée : Les partitions trouvent une utilité particulière pour les tables volumineuses qui peuvent être interrogées ou manipulées par plusieurs processus simultanés. Tables organisées en index (Voir Module 4, 1.4) Vous pouvez modifier la valeur des paramètres d'utilisation de blocs pour : - améliorer l'utilisation de l'espace, - réduire les possibilités de migration. 6. (slide 225) (Voir LaboOracle Module 4, 2.1.Types d'index) a) Un index B-tree est une arborescence ordonnée composée de nœuds d'index. Il contient une entrée par ligne. Un index bitmap est un index physique contenant une entrée par groupe de lignes. b) (Voir LaboOracle Module 4, 2.1.5.Index bitmap ou index B-tree) c) i) Oui chois judicieux : Un index B-tree est une arborescence ordonnée composée de noeuds d'index. Il contient une entrée par ligne. Un index bitmap est un index physique contenant une entrée par groupe de lignes. On a normalement un id unique, alors que les départements de naissance peuvent être les même, donc il ne sert a rien de grouper les id en revanche on peut grouper les départements. ii) B-Tree : CREATE INDEX etudiant_id ON ETUDIANT(id); Bitmap : CREATE BITMAP INDEX etudiant_dep ON etudiant (departement_naissance) ; iii) iv) Si des modifications sont apportées à la colonne clé d'une table, les bitmaps doivent être modifiées. Pour cela, les segments bitmap appropriés sont verrouillés. Etant donné que les verrous sont placés sur l'intégralité du segment bitmap, le bitmap ne peut être mis à jour par d’autres transactions tant que les verrous ne sont pas libérés. 7) a)C’est possible car aucune contrainte n’a été définie lors de la création de la table b)Alter table etudiant ( ADD (CONSTRAINT etu_pk FOREIGN KEY(id) NOT DEFERRABLE INITIALLY IMEDIATE) ; ALTER TABLE etudiant ENABLE VALIDATE CONSTRAINT etu_pk ; (Voir Module 4 fin de la page 40 – 41) 8) a) Une table standard contient des données stockées sans ordre particulier. Dans une table organisée en index, les données sont stockées dans une structure de type Btree, triées sur la clé primaire. Une table organisée en index procure un accès rapide aux données car toutes les informations sont contenues dans la structure de type index (en effet les données n'appartenant pas à l'index ne sont pas stockées dans un espace différent). Pour la même raison, l'espace de stockage nécessaire est moindre puisque les données contenues dans les colonnes indexées ne sont pas dupliquées (table et index). Il n'est donc plus nécessaire de stocker dans l'index le ROWID pointant sur le reste des données non indexées comme dans une table standard. Ce type d'organisation convient parfaitement pour les requêtes dont les termes sont basés exclusivement sur les éléments de la clé primaire. Un argument PCTTHRESHOLD pour définir le pourcentage d'espace réservé dans le bloc d'index. Chaque partie de la ligne qui excèdera ce pourcentage sera stockée dans la zone de débordement. En d'autres termes, le contenu d'une ligne peut être divisé en deux partie. une partie "préfixe" stockée dans la feuille d'index et une partie "suffixe" stockée dans la zone de débordement. L'entrée de l'index (partie préfixe) contient donc les éléments de la clé primaire plus les autres données à concurrence du pourcentage indiqué dans le paramètre PCTTHRESHOLD ainsi qu'un pointeur vers le reste de la ligne b) exemple de création d'une table organisée en index CREATE TABLE perso ( nom VARCHAR2(30), prenom VARCHAR2(20), age NUMBER(3), infos VARCHAR2(512), CONSTRAINT pk_perso PRIMARY KEY (nom, prenom) ) ORGANIZATION INDEX TABLESPACE tbs1 PCTTHRESHOLD 20 OVERFLOW TABLESPACE tbs2 ; Une table est créée avec 4 colonnes. les deux premières sont les éléments de la clé primaire. La table (partie préfixe) est stockée dans le tablespace tbs1 Le pourcentage d'espace maximum occupé par les colonnes n'appartenant pas à la clé primaire et devant donc être déplacées dans la zone de débordement est de 20% de la taille d'un bloc La zone de débordement est stockée dans le tablespace tbs2 c) Erreur car nom n’est pas une clé primaire (l'index qu'on a créé a supprimer les rowid puisqu'il ne les utilise plus) d) 9) Voir ploy Administration de Bases de Données (donné par Ping6) • préparation du système : création des répertoires – bdump : répertoire de stockage des fichiers trace et alert – cdump : core dump files – udump : trace des utilisateurs – pfile # Nom de l'instance db_name=DBHAT # Fichiers de contrôle (contiennent les metadonnées, nom de la base...) control_files=/opt/oradata/DBHAT/control01.ctl, /opt/oradata/DBHAT/control02.ctl # Nombre de processus processes=50 # Taille d'un bloc db_block_size=8192 # Taille du cache db_cache_size=2516582 # Mémoire partagée shared_pool_size=128265318 java_pool_size=0 # Répertoires de dump background_dump_dest=/opt/oradata/DBHAT/bdump core_dump_dest=/opt/oradata/DBHAT/cdump user_dump_dest=/opt/oradata/DBHAT/udump # À décommenter si utilisation d'un fichier de mot de passe remote_login_passwordfile=NONE • création de l'instance -- connexion à la base connect / as sysdba startup nomount pfile=/users/.../admin/DBHAT/pfile/initDBHAT.ora ORACLE instance started. Total System Global Area 138412032 bytes Fixed Size 1218148 bytes Variable Size 130025884 bytes Database Buffers 4194304 bytes Redo Buffers 2973696 bytes • création de la base -- création de la base CREATE DATABASE DBHAT MAXLOGFILES 32 MAXLOGMEMBERS 4 MAXDATAFILES 50 MAXLOGHISTORY 50 LOGFILE GROUP 1 ('/opt/oradata/DBHAT/log1a.rdo') SIZE 6M, GROUP 2 ('/opt/oradata/DBHAT/log2a.rdo') SIZE 6M DATAFILE '/opt/oradata/DBHAT/system01.dbf' SIZE 30M autoextend on SYSAUX DATAFILE '/opt/oradata/DBHAT/sysaux01.dbf' SIZE 10M REUSE CHARACTER SET WE8ISO8859P1; 10) (Voir slide 366+) a)Une sauvegarde à froid signifie qu'un arrêt de la base de données est effectué. Lorsque la base est arrêtée, l'activité est interrompue et les fichiers peuvent alors être copiés sans corruption de données. Comme nous venons de le voir, effectuer une restauration ou récupération d'une base de données implique que la base soit arrêtée, cependant dans des environnements à haute disponibilité cela est parfois impossible. Une solution dans ce cas existe: "Hot backup" ou sauvegarde à chaud. Le problème est le suivant, dans le cas d'une haute disponibilité, forcément l'état des fichiers change constamment : des modifications sont apportés dans les fichiers de données, des informations de contrôle sont écrites dans les fichiers de contrôle, et des informations de reprise sont consignées dans les fichiers REDO, lesquels peuvent également être archivés. Pour effectuer une sauvegarde dans ce cas, la solution est de placer chaque tablespace dans le mode de sauvegarde et de sauvegarder les fichiers de données, puis de rétablir le tablespace dans le mode normal. On sauvegarde donc des fichiers incohérents puisque les SCN, contenus dans les entêtes de fichiers de données sont différents. Il faudra effectuer une récupération de support pour rendre ces différents SCN cohérents et pouvoir ouvrir la base. b) Différentiel ou cumulatif RMAN offre la possibilité de réaliser des sauvegardes incrémentales. La sauvegarde incrémentale a l’avantage de sauvegarder uniquement les blocs modifiés depuis la dernière sauvegarde. Cela vous permettra de diminuer la taille de vos fichiers de sauvegardes. Il existe pour les sauvegardes incrémentales 2 niveaux de sauvegarde : le niveau 0 : correspond à une sauvegarde complète de la base. Cette sauvegarde est un pré-requis à une sauvegarde incrémentale de niveau 1. le niveau 1 : il existe deux options possibles pour les sauvegardes de niveau 1. Une sauvegarde différentielle (schéma 1) ou cumulative (schéma 2) Schéma 1 Une sauvegarde différentielle de niveau 1 sauvegarde uniquement les blocs modifiés depuis la dernière sauvegarde de niveau 0 ou 1. On peut imaginer réaliser une sauvegarde de niveau 0 une fois par semaine puis exécuter chaque jour une sauvegarde différentielle de niveau 1. Schéma 2 Une sauvegarde cumulative de niveau 1 sauvegarde les blocs modifiés depuis la dernière sauvegarde de niveau 0. La taille des sauvegardes et donc plus importante dans cette configuration. Les commandes pour réaliser les sauvegardes incrémentales sont les suivantes: Sauvegarde de niveau 0 : BACKUP INCREMENTAL LEVEL 0 DATABASE; Sauvegarde différentielle de niveau 1 : BACKUP INCREMENTAL LEVEL 1 DATABASE; Sauvegarde cumulative de niveau 1 : BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; c) Le stockage des informations relatives aux sauvegardes effectuées via RMAN peut se faire soit dans un catalogue de récupération (recovery catalog), soit exclusivement dans les fichiers de contrôle des bases de données cibles. Le recovery catalog Le catalogue est un référentiel (ensemble de tables) pour RMAN contenant des informations sur : Les ensembles de sauvegardes des fichiers de données et archives Les copies de fichiers de données La structure physique de la base de données cible. Les archives logs. Les scripts de travail (si utilisés). Ce catalogue est en fait une base de données qu'il faut créer sur un serveur distinct de la base cible de préférence. L'inconvénient est que ce catalogue est une base de données qu'il faut surveiller et gérer. Dans votre stratégie de sauvegarde doit figurer cette base. Cependant un simple import du schéma suffit pour restaurer ce catalogue. Oracle recommande que le catalogue soit : Stocké dans une base différente des bases qu'il gère. Qu'il soit aussi sur un disque différent. Qu'une base à part soit utilisée si RMAN doit en gérer beaucoup. Qu'il est nécessaire de sauvegarde AUSSI la base contenant le catalog RMAN. Le Recovery catalog va permettre de stocker les informations relatives à la base de donnée et à ses sauvegardes. Ainsi RMAN sera capable de déterminer quels fichiers ont été sauvegardés et quels fichiers ont besoin de l'être (sauvegarde incrémentielle). L'usage du Recovery Catalog bien qu'optionnel, transforme RMAN d'un simple outil de sauvegarde pour oracle en un réel logiciel de sauvegarde à part entière. Il ne faut bien entendu pas oublier d'effectuer une sauvegarde de la base de celui-ci, pour cela il est possible d'utiliser la méthode de l'export. Il est parfaitement possible de ne pas utiliser le catalogue. Dans ce cas, RMAN va sauvegarder les métadonnées dans les fichiers de contrôle de la base de données cible. L'avantage est de ne pas avoir à gérer le catalogue. Cependant un certain nombre de fonctionnalités de RMAN sont alors indisponibles (par exemple, le stockage des scripts de sauvegarde/restauration ou les fonctions de reporting). De plus, la perte des fichiers de contrôle dans ce cas peut devenir problématique.