Corrigé janvier 2006

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.