TP projet DBA Oracle

TP projet DBA Oracle

Instructions :
 A rendre avant le 20 septembre 2022
 Le travail peut être fait en binôme ou seul
 Rendre un document MS Word contenant les captures expliquées des différentes étapes du TP
(numéroter toutes les parties et toutes les questions) :
 Les captures ne doivent pas être floues.
 Les captures provenant de sources différentes pour un même travail seront considérées
comme frauduleuses
 Des captures similaires de 2 travaux différentes aussi seront considérées comme
frauduleuses.
 Rendre un fichier .sql contenant les différentes commandes SQL du TP. Mettre les procédures
PL/SQL, les mettre dans des fichiers SQL isolés.
 Mettre les noms des membres du groupe sur tous les fichiers.

1
TP projet DBA Oracle

Première partie

1. En ligne de commande, lancer le processus d’écoute Oracle : lsnrctl start

2. Lancer l’assistant de gestion de bases de données (Database Configuration Assistant).
3. Créer votre propre base de données nommé projetORCL en suivant les étapes d’installation.
4. Afficher fichier de paramètres d’initialisation.
5. Afficher les fichiers de contrôle de la base.
6. Quels sont les noms et les emplacements des fichiers redo log ?
7. Quels sont les noms et les numéros des tablespaces ?
8. Quels sont les noms des fichiers de données (datafile) et la taille de leurs blocs ?
9. Créer un tablespace par défaut ayant un seul fichier de 20M avec les valeurs suivantes
 premier extent : 2M,
 extents suivants : 1M,
 nombre initial d’extent à allouer : 1, `
 nombre maximum d’extents : 10
10. Créer un espace de stockage temporaire ayant un seul fichier de 10M.

2
TP projet DBA Oracle

Partie II : utilisateurs, rôles, profils et privilèges

1. Après avoir lancé l’instance, créer un utilisateur tpuser1 dans la base de données
Oracle.
2. Si ça échoue, expliquez-en la cause et corrigez-la.
3. Créer un utilisateur tpuser1 dans la base de données pluggable créée lors de
l’installation.
4. Essayer de vous connecter avec le nouvel utilisateur. Si ça échoue, corrigez et
connectez-vous avec.
 Donner à cet utilisateur le droit de créer des tables.
 Connectez-vous avec et créez les tables emp et deptno en utilisant le script vu
sur le cours.
 Donnez à tous les utilisateurs le droit d’interroger la table emp.
5. Affecter à l’utilisateur le tablespace par défaut comme espace par défaut en limitant a
1M la taille de l’espace utilisable ;
6. Quels sont les droits de l’utilisateur pour la session en cours (session_privs) ?
7. Exécuter le scenario ci-dessous en affichant les privilèges actifs après chaque
instruction :
 Se déconnecter, se reconnecter : quels sont ses privilèges ?
 Créer un rôle IndextRoles ayant CREATE ANY INDEX, ALTER ANY INDEX,
DROP ANY INDEX comme privilèges ;
 Allouer ce rôle à l’utilisateur créé ;
 Créer un rôle RoleAdmin ayant GRANT ANY PRIVILEGE comme privilège ;
 Allouer ce rôle à l’utilisateur créé ;
8. Vérifier les limites des ressources de l’utilisateur.
9. Créer un tablespace USERS01 avec 2 datafiles de 10M ;
10. Affecter USERS01 comme tablespace par défaut à l’utilisateur en limitant son espace à
5M ;
11. Créer un profil nommé profiler01 autorisant deux connections simultanées par le même
utilisateur, chaque session ne devant pas dépasser 3 minutes ;
 s’assurer que le profil est créé ;
 affecter le profil à l’utilisateur créé dans la première partie et vérifier qu’il lui
est bien affecté.
 Vérifier que les limites du profil s’appliquent à l’utilisateur.
12. Supprimer le profil ; quelles sont les nouvelles limites de ressources de l’utilisateur ?

3
TP projet DBA Oracle

Partie III : Sécurité et reprise

Cette session permet de mettre en œuvre les différents mécanismes lies à la sécurité d’une base de
données :
(i) surveillance (auditing) du serveur de données,
(ii) archivage de journaux,
(iii) sauvegarde
(iv) reprise après incident
(v) duplication (ou multiplexage).
Voir la documentation oracle : https://docs.oracle.com/en/database/oracle/oracle-database/index.html

Surveillance (auditing)
1. Vérifier que l’audit trail et ses vues existent, sinon les créer ;
2. Quelles sont les options d’audit par défaut ? Positionner les options d’interrogation
et de modification de toute table par l’utilisateur ; vérifier que ces options sont bien
positionnées ;
3. Vérifier que ces options sont effectives. Si tel n’est pas le cas, corriger et vérifier ;

Archivage des journaux

4. Quel est le mode d’archivage des journaux en vigueur dans la base ? Si le mode
d’archivage automatique n’est pas actif, l’activer au sein de la base active. Indiquer
un répertoire comme destination de l’archivage et vérifier le résultat des deux
actions qui précédent ;
5. Se déconnecter et se connecter de nouveau.
 Quel est l’état de la base ?
 Quel est son mode d’archivage (ARCHIVE LOG LIST) ?
 Rendre permanent le mode d’archivage automatique ;
 Forcer l’archivage des journaux ;
 Quel est le redo log en cours d’utilisation (actif) ?
 Archiver le seul journal actif ; vérifier le résultat ;
6. Créer trois tables, y faire un grand nombre d’insertions (100 au moins) puis
déterminer le nombre de fichiers redo log archivés ;
7. Quels sont les journaux dont aurait besoin le système pour réaliser une reprise (voir
V$RECOVERY_LOG) ? Quelles sont les numéros des modifications contenues dans
chaque journal archivé (voir V$LOG_HISTORY) ?

4
TP projet DBA Oracle

Sauvegardes
8. Créer un répertoire de sauvegarde ; y sauvegarder manuellement un tablespace.
9. Sauvegarder le fichier de contrôle.
10. Exécuter le scenario ci-dessous en utilisant le gérant de reprise RMAN :
 Lancer RMAN en tant que SYSOPER ou en tant que SYSDBA ;
 Monter la base (sans l’ouvrir) ;
 Afficher les infos sur les tablespaces (nom, taille, emplacement, etc.) ;
 Créer une copie du premier fichier de données ;
 Sauvegarder l’espace system ;
 Lister les sauvegardes et leur contenu ;
 Vérifier la consistance d’une sauvegarde identifiée par sa clé

Reprise après incident

Cette section comporte trois cas :
(i) reprise automatique après incident,
(ii) reprise d’un datafile sans disposer de sa sauvegarde
Ces différents cas supposent la base en mode automatique d’archivage des journaux.

Reprise automatique
Cette session simule un incident logiciel afin de tester la reprise automatique.

11. Quel est le mode de gestion du journal des images avant modification (rollback
segments ou undo tablespaces) ?
12. Avec l’utilisateur de la première partie, créer une table, y faire quelques insertions
puis vérifier et mémoriser le contenu de la table ;
13. Créer et exécuter la procédure ci-dessous :

CREATE OR REPLACE PROCEDURE no_end AS

i INT;
BEGIN
i:= 1;
WHILE TRUE
LOOP INSERT
INTO T values(...); i:= i + 1;
END LOOP;
END;

14. La procédure ne terminant pas, en tant qu’administrateur arrêter l’instance Oracle ;

relancer l’instance et vérifier l’état de la table après la relance. Expliquer. On pourra
également consulter les fichiers de trace et d’alerte (localisés, par défaut, dans . . .
\admin\...\bdump).

5
TP projet DBA Oracle

Reprise d’un fichier de données sans sa sauvegarde ´

Cette suite d’opérations simule la perte ou la détérioration d’un datafile.

15. Créer un tablespace USERS02 comportant un fichier de données users0201.dbf ;

16. Créer un utilisateur (ou modifier un utilisateur existant) en lui affectant USERS02
comme espace de stockage par défaut ;
17. Se connecter en tant que cet utilisateur, créer une ou plusieurs tables et y faire des
insertions ;
18. Mettre la tablespace hors ligne et supprimer le fichier users0201.dbf au niveau du
système d’exploitation ;
19. Créer de nouveau le fichier users0201.dbf
 Mettre la tablespace en ligne et donner le contenu de la ou des tables créées ;
 Mettre la tablespace hors ligne et effectuer la reprise du fichier users0201.dbf ;
 Mettre la tablespace en ligne et donner le contenu de la ou des tables créées

Multiplexage
20. Quel est le redo log en cours d’utilisation (V$LOGFILE.STATUS) et son numéro de
groupe ?
21. Créer un nouveau groupe de fichiers de journalisation ne comportant qu’un seul
fichier de 64K ; vérifier le résultat. Ajouter un membre à chaque groupe existant et
vérifier le résultat ;
22. Créer une table et y faire un grand nombre d’insertions (afin d’instancier un
significatif d’enregistrements dans le journal) ;
 Quel est le redo log en cours d’utilisation (actif) et son numéro de groupe ?
 Supprimer les membres ajoutés ; en cas de non fonctionnement, expliquer ;
 Supprimer le groupe ajouté ; en cas de non fonctionnement, expliquer.

6
TP projet DBA Oracle

Partie IV : Optimisation de requêtes

Préalables
1. Vérifier l’existence de la table plan_table et la créer si elle n’existe pas ;
2. Créer les relations (tables) suivantes sans clés :

CREATE TABLE produit ( prod# INT NOT NULL, libelle VARCHAR2(20),

pu FLOAT NOT NULL);
CREATE TABLE depot (dep# INT NOT NULL, adr VARCHAR2(20), capacite
INT);
CREATE TABLE stock (prod# INT NOT NULL, dep# INT NOT NULL, qte
INT NOT NULL);

3. Procédures à créer :
(a) ProcInitProd.sql : procédure permettant d’instancier la relation produit.
Cette procédure aura comme argument NbProd, le nombre de produits à insérer.
Les tuples de la relation sont de la forme <i, ’Produit i’, i>, i ∈ [1..NbProd]
(Exemple: <1, Produit 1, 1>, <2, Produit 2, 2>, etc.).
(b) ProcInitDep.sql : procédure permettant d’instancier la relation dépôt.
De la même façon, cette procédure aura un argument NbDep désignant le nombre
de dépôts à insérer. Les tuples de la relation sont de la forme <j, ’Depot Numero j’,
x >, j ∈ [1..NbDep].
(c) ProcInitStock.sql : procédure permettant d’instancier la relation stock.
Cette procédure aura comme argument NbrStock désignant le nombre moyen de
dépôts dans lesquels est stocké un même produit. Les tuples de la relation sont de
la forme <prod#, dep#, 3000>.

4. Créer (compiler) les procédures.

5. Instancier les relations avec un nombre conséquent de produits (NbProd > 1500), de
dépôts (NbDep >= 20) en considérant qu’un produit est stocké en moyenne dans 10
dépôts (exec NomProcedure(Valeur du paramètre)).

7
TP projet DBA Oracle

Plans d’exécution
6. Question 1 :
Exécuter les requêtes ci-dessous et examiner leur plan d’exécution. Inclure des éléments
de coût de la requête dans l’examen du plan d’exécution. Comparer les plans des couples
de requêtes : (R1, R2) ; (R3, R4) ; (R5, R6) et (R7, R8)

R1: SELECT * FROM produit WHERE prod# = 700 or prod# = 800

R2: SELECT * FROM produit WHERE prod# in (700, 800)

R3: SELECT p.libelle FROM produit p, stock s

WHERE s.dep# = 4 AND p.prod# = s.prod#
R4: SELECT p.libelle FROM produit p
WHERE p.prod# IN (SELECT s.prod# FROM stock s
WHERE s.dep# = 4)

R5: SELECT d.adr FROM depot d, stock s

WHERE d.dep# = 4 AND d.dep# = s.dep#
AND s.prod# > 100 AND s.prod# < 500
R6: SELECT d.adr FROM depot d where d.dep# = 4
AND d.dep# IN (select s.dep# FROM stock s
WHERE s.prod# > 100 AND s.prod# < 500)

R7: SELECT prod#, SUM(qte) "Total en stock" FROM stock

WHERE dep# > 10 AND dep# < 100 GROUP BY prod#
R8: SELECT prod#, SUM(qte) "Total en stock" FROM stock
WHERE dep# > 10 AND dep# < 100 GROUP BY prod#
ORDER BY prod#

7. Question 2 :
(a) Changer l’ordre des relations dans la clause FROM des requêtes R3 et R5.
(b) Exécuter ces requêtes et comparer leurs plans d’exécution avec ceux obtenus
dans le (6).

8
TP projet DBA Oracle

8. Question 3 :
a. Quel est l’espace occupé par chacune des relations (dans user_tables, nombre
de blocs, nombre de blocs libres, nombre de tuples, etc.) ?
b. Collecter des statistiques sur les tables.
c. Quel est l’espace occupé par chacune des relations ? Expliquer.
d. Exécuter les requêtes et comparer les plans d’exécution avec ceux des deux
questions précédentes.

9. Question 4
a. Créer un index sur la relation produit ayant prod# comme clé
b. Créer un index sur la relation stock ayant (prod#, dep#) comme clé
c. Exécuter quelques requêtes de la liste ci-avant et comparer les plans d’exécution
avec ceux obtenus dans les questions précédentes.

10. Question 5 :
a. Collecter les statistiques sur les index.
b. Examiner l’encombrement des index crées (Nombre de blocs, nombre de valeurs
par blocs, nombre de niveaux, etc.).
c. Exécuter quelques requêtes de la liste ci-avant et comparer les plans d’exécution
avec ceux obtenus dans les questions 1, 2, 3 et 4.