ADMINISTRATION DES BASES DE DONNEES Pr BOUZIDI
Année universitaire 2007-2008
Objectifs
Comprendre le rôle et l'importance de chacune des tâches de l'administration des bases de données Maîtriser l’administration de la base de données Oracle
D.BOUZIDI
2
Plan du cours
Introduction Architecture conceptuelle de la BD Oracle Installation et configuration du logiciel Oracle Data Databa base se 10g 10g Contrôle la base de données Gestion du fichier de contrôle Gestion des fichiers de journalisation journalisation Gestion des structures de stockages Administration Administration des utilisateurs et des schémas Gestion des rôles Sauvegarde / récupération
Introduction
Rappel (1)
Base de données (BD) : ensemble de données organisé en vue de son utilisation par des programmes correspondant à des applications applications distinctes et de manière manière à faciliter l'évolution l'évolution indépendante des données et des programmes. SGBD : ensemble de programmes qui permettent l'accè l'accès s à une BD Une table est l’élément de base d’une BD
Constituée de lignes de données Chaque ligne comporte une ou plusieurs colonnes Une colonne unique d'une ligne unique est appelée champ Ligne
Table
Colonne Champ 5
D.BOUZIDI
Rappel (2) base de données relationnelle : Entité/Relation Contraintes d'intégrité : utilisées utilisées pour garantir la validité validité des données données stockées dans dans les tables contrainte inte suivan suivants ts : Les types de contra
NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY
SQL : langage de programmation interactif standard permettant d'extraire et de mettre à jour les infos d'une BD BD (interrogation, màj, contrôle contrôle d’accès, cohérence, cohérence, intégrité, …)
D.BOUZIDI
6
Tâches d'un administrateur de BD (1)
Rôle SI (Système d’Information) d’Information) : Administration Administration et gestion efficace des données (lors de la mise en oeuvre de nouveaux systèmes basés sur les Bases de Données)
éviter la redondance des données éviter un mauvais mauvais transfert transfert d'un système système à l'autre assurer la bonne compréhension des données
implémenter la conception de la BD
7
D.BOUZIDI
Tâches d'un administrateur de BD (2)
Rôle technologique (Niveau physique) : Administration Administrat ion Administratio n et gestion efficiente des des BD (lors de la mise en oeuvre de nouveaux serveurs de BD ou de nouvelles BD)
Evaluer le maté matériel pour le serveur de base de donné données Installer le logiciel Oracle Planifier la BD Cré Créer et ouvrir la BD Sauvegarder la BD Récupé cupérer la BD suite à une panne Gérer de la sé sécurité curité Surveiller les performances de la BD
D.BOUZIDI
8
Architecture conceptuelle de la BD Oracle
Objectifs
Maî Ma îtriser les Maîtriser les concepts concepts et et le le fonctionnement ’ architecture architecture oracle fonctionnement de de ll’architecture oracle ::
D.BOUZIDI
Instance Instance Fichiers ées, Fichiers de de donn données, Fichiers étrage Fichiers de de param paramétrage
10
Présentation Processus utilisateur
PGA
F A i L c E h i R e T r
F T i r c h a i c e e r
P a r a m è t r e s
F i P c Wh i D e r
F i c h i e r
Instance
Processus serveur
Mémoire SGA
Fichiers de données
Fichiers de contrôle
Zone de mémoire partagée
Fichiers de journalisation
Base de données
11
D.BOUZIDI
Présentation
L'instance L'instance indispensable indispensable au au bon bon fonctionnement fonctionnement d'une d'une base base de de données données Oracle Oracle Les Les fichiers fichiers de de données données Les Les fichiers fichiers de de données données facultatifs facultatifs (fichier (fichier d'initialisation, d'initialisation, fichier fichier de de mots mots de de passe, passe, etc...) etc...)
D.BOUZIDI
12
L’instance L’instance Instance Zone de mémoire partagée
Mémoire SGA Cache des tampons des DATA SMON
PMON
Cache library
tampons Journalis ation DBW
Cache Cache dictio dictio D LGWR ARCH
CKPT
L'instance L'instance est est la la composition composition de de 22 sous sous ensembles ensembles :: •• Une zo nee m mém oire ee ((SGA )) pour Une zone zone zon mémoir mémo émoir ire pour stocker stocker les les données données issues issues des des SGA fichiers de données sur le disque dur. Afin de pouvoir fichiers de données sur le disque dur. Afin de pouvoir les les partager partager entre entre les les différents différents processus. processus. •Des processu d'arrièr e plan •Des processus processuss d'arrière processus d'arrière d'arrière plan :servent :servent àà gérer gérer les les transferts transferts de données entre la mémoire et le disque dur, plus de données entre la mémoire et le disque dur, plus d'autres d'autres actions actions nécessaires nécessaires au au bon bon fonctionnement fonctionnement de de la la base base de de données. données. 13
D.BOUZIDI
System Syst em gl glob obal all Area Area globa glo bal Instance Zone de mémoire partagée
Mémoire SGA Cache des tampons des DATA SMON
PMON
Cache library
tampons Journalis ation DBW
Cache Cache dictio dictio D LGWR ARCH
CKPT
La Syste La SGA SGAou ou System System System m global globall Area globa Area 1. Shar ared ed Po ol 1. Sh Shar Sh ared ed Pool Pool Po ol:: zone zone de de mémoire mémoire partagée partagée 2. Data taba base se Buff ffer er Cach che ee :: cache cach ee de tampo donné ess 2. Da Data Da taba base se Bu Buff Bu ffer er Ca Cach Ca che cache cach detampons tampons tampons nsde dedonnées donnée données 3. Redo do Log gg Bu Buff ffe eer r :: tampon 3. Re Red Re do Lo Log Lo Buff Bu ffer tamponde dejournalisation journalisation La Lataille tailletotale totalede dela lamémoire mémoireSGA SGAest est définie définiepar parleleparamètre paramètreSGA_MAX_SIZE SGA_MAX_SIZE Variable_size : sont deux variables exprime la taille de l’espace réservé rése rvé lalaSGA Variable_size : sont deux variables exprime exprime la taille de l’espace réservé réservé SGA fixedSize
fixedSize D.BOUZIDI
14
Sys System tem global bal Area a Syst Sy stem em glo glob gl obal al Are Area Area La SGA ou System System global Area Shared Pool : utilisé utilisée pour partager les l es informations informations sur sur les objets de la base de donn données ainsi que sur les droits et privilè privilèges accordé accordés aux utilisateurs. utilisateurs.
se dé découpe en 2 blocs :
La Libr Library ary Cac Cache he : stocke les informations sur les ordres SQL ex écutés récemment dans une zone SQL Cache qui contiendra le texte de l'ordre SQL, la version compil ée de l'ordre SQL et son plan d'ex écution.
Le Dictionn Dictionnary ary Cach Cache e : contenant les définitions des objets de la base de donn ées qui ont été utilisé récemment. Permettra au serveur Oracle de ne pas avoir à aller chercher ces informations sur le disque à chaque exécution d'une requête SQL .
15
D.BOUZIDI
Sy Syst stem em gl glob obal al Area Ar ea System Sys tem global glo bal Area Are a La Sy globa ll Area La SGA SGA ou ou System System System stemglobal global globa Area Da Data taba base se Buffe ffer rr Ca Cach che ee :: Data Da taba base se Bu Buffe Buf fer Cach Ca che •• Sert stoc ker bloc ss de donn ées utili sés réce mment ntt Sertàà stocker stocker stoc kerles lesblocs blocs bloc dedonnées données donn éesutilisés utilisés utili sésrécemme récemmen réce mment •• Fonction Fon ctionne nee selon se princ ipe dit ddit it du bbloc loc ancie aancie ncien. n. Fonctionn Fon ctionne selon selon lon le leprincipe principe prin cipe du bloc ancien. n. Sa Sataille tailleest estdéfinie définiepar par deux deuxparamètres paramètres
DB_BLOCK_SIZE DB_BLOCK_SIZE :: défini défini lors lors de de la la création création de d ela labase basede de données, représente la taille d'un bloc de données Oracle. données, représente la taille d'un bloc de données Oracle. Celui-ci Celui-ci est est défini défini de de manière manière définitive définitive et et ne ne pourra pourra plus plus être modifié. être modifié. DB_BLOCK_BUFFERS DB_BLOCK_BUFFERS :: défini défini le le nombre nombre de de blocs b locsOracle Oraclequi qui pourront pourron t être contenus cont enus dans dan s le Database Databa se Buffer Cache. Cache . pourront être être contenus contenus dans le le Database Database Buffer Cache Cache.. Ce Ce paramètre paramètre est est devenu devenuobsolète obsolète ààpartir obsolète partir de de la la version version 9i 9iet et aa été ét é rempl re mplac acé é par pa r DB_CACHE-Size. DB_CACHE-Size . Exprimé Exp rimé en octets oct ets ce été remp remplac lacé é par DB_CA DB_CACHE CHE-Si -Size. ze. Expr Exprimé imé en octet octets s ce paramètre paramètremodifiable modifiabledynamiquement dynamiquement alter alt er sy stem m set db_ cache che_si _size= ze=100 100M; M; alter alt er syste system sys tem set db_ca db_ca db_ cache che_si _size= ze=100 100M; M;
D.BOUZIDI
16
Sy Syst stem em gl glob obal al Area Ar ea System Sys tem global glo bal Area Are a La Sy globa ll Area La SGA SGA ou ou System System System stemglobal global globa Area Redo do Log gg Buf BBuf fer rr :: sert Re exc lusivement àà enregistrer Redo Re do Lo Log Lo Buffe uffe fer sert exclusivement exclusivement exclusivement enregistrertoutes toutes les modifications apportées sur les données de la base. les modifications apportées sur les données de la base.
Mémoire Mémoire de de type type circulaire, circulaire, et et dont dont on on pourra pourra changer changer la la taille avec le paramètre LOG_BUFFER taille avec le paramètre LOG_BUFFER LOG_BUFFER
Oracle Oracle ne ne pourra pourra écraser écraser les les données données contenues contenues dans dans ce ce buffer buf fer que si elles el les ont été écrites écr ites dans da ns les fichier fic hiers s buffer buff er que si elles elles ont été écri écrites tes dans dans les fichiers fichiers REDOLOG FILE REDOLOG FILE
Le para ee définissan défin issant ttla buff err est Le paramètr paramètre paramètr mètre défini définissan ssant lataille taillede deRedo Redolog Redo log buffer buffe buffer est log_buffer log_buffer
17
D.BOUZIDI
Program Progra m glo global bal Are Area a Processus utilisateur
Processus serveur PGA
Instance Mémoire SGA
Zone de mémoire partagée
Mémoire Mémoire non non partagée. partagée. seulement seulement utilisée utilisée par par des des processus processus serveur serveur ou ou d'arrière d'arrière plan plan
allouée ddémarrage émarrage pro cessus dés allouée lors lors du du démarrage dé marrage du du processus proc processus essus et et désallouée désallouée désallouée allouéelors lors de de l'arrêt du processus. l'arrêt du processus. Elle Ellecontient contient::
La La zone zone de de tri tri :: Appelée Appelée SORT SORT AREA. AREA. Les Les informations informationsde de sessions, sessions, les les privilèges privilèges de de l'utilisateur l'utilisateur Le Stac k Space Spac ee ::Cette co ntiendra dra toutes tout es les autr Le Stack Stack Space Spac Cettezone zonecontien contiendra contie ndra toutes toutes lesautres autres autres es variables d'environnem d'environnement ent et de session de l'utilisateur variables d'environnement d'environnement et de session de l'utilisateur
D.BOUZIDI
18
Les processus d'arrière plan Instance Mémoire SGA Cache des tampons des DATA SMON
PMON
tampons Journalis ation DBW
Zone de mémoire partagée
Cache library Cache Cache dictio dictio D LGWR ARCH
CKPT
Le pro cessus SMON (Syst se rt Le processus processus processus SMON (System (System (System em Monitor) Monitor)sert sert se rtàà :: Corriger Corrigerles lesplantages plantagesde del'instance l'instance Vérifier la synchronisation Vérifier la synchronisation des des données données .. Si l'instance plante : Si l'instance plante : Rejouer Rejouer des des transactions transactions de de REDO REDO LOG LOG FILE FILE validées validées mais non non enregistrées enregistrée s sur le disque d isque dur mais non non enregistrées sur le disque disque dur Ouvrir Ouvrir la la BD BD pour pour les les utilisateurs utilisateurs les les informations informations non non valides ne sont pas accessibles valides ne sont pas accessibles Annuler tte s les transac tions non Annuler ttes ttes les transactions transactions transactions non validés validés SMON segm ents TMP défragm SMON sert sert àànettoyer nettoyer les les segments segments segments TMP et et défragmente défragmente défragmente ente les les fichiers de données fichiers de données
D.BOUZIDI
19
Les processus d'arrière plan
Le (Pr ocess ess Mon itor) r) dédi Le processus processus PMON PMON (Proc (Proc (Pr ocess essMonito Monito Mon itor) r)dédié dédiéé aux dédié aux utilisate utili sateurs, urs, il sert s ert à : utilisateu utili sateurs, rs, il sert sert à : annuler annulerles lestransactions transactionsd'une d'unesession session(lors (lorsd'un d'un plantage plantage de la session par exemple) de la session par exemple) relâcher relâcher tous tous les les verrous verrous posés posés par par la la session, session, relâcher relâcher toutes toutes les les ressources ressources détenues détenues par par la la session. session.
Le (Dat aBaseW seWrit riter) er)) déd ddédié édié ié Le processus processus DBWR DBWR (DataBa (DataB (Da taBase aseWri Writer ter) déd ié àà :: l'écriture Da tabase le s fichiers fich dde e l'écritur l'éc riture e du du Database Databa Database se Buffer Buffer Buff er Cache Cache Cach e dans dans les les fichiers fichiers iers de données données vérifier vérifier en en permanence permanence le le nombre nombre de de blocs blocs libres libres dans dans le le Database Buffer Cache afin de laisser assez de place de Database Datab ase Buff Buffer er Cac Cache he afin de laisser laisser assez assez de place de disponible pour l'écriture des données dans disponible pour l'écriture des données danslelebuffer buffer Le Le processus processus DBWR DBWR se se déclanche déclanche :: Lorsque no mbre de dir ty atteint Lorsque le le nombre nombre debloc bloc dirty dirty atteint une une certaine certaine limite certaine limite Lorsqu'un Lorsqu'un processus processus recherchant recherchant de de blocs blocs libres libres dans dans le le Database Datab ase Buffer Buff er Cache, Cach e, et qu'il qu'i l n’ n ’ a trouvé. trou vé. Databa Dat abase se Bu Buffe fferr Ca Cache che,, et qu'il qu'il n’ a trouvé. trouvé. Lors de timeout (3 secondes par défaut) Lors de timeout (3 secondes par défaut) Lors Lors d'un d'un checkpoint checkpoint
D.BOUZIDI
20
Les processus d'arrière plan Le Le processus processus LGWR LGWR :: va va écrire écrire les les informations informationscontenues contenues dans contenues dans le REDO LOG Buffer Bu ffer dans les fichiers fic hiers REDOLOG REDO LOG FILE. le REDO LOG Buffer Buffer dans les fichiers fichiers REDOLOG REDOLOG FILE. IlIl se déclanche : se déclanche : Qd transa ction terminé Qd une une transaction transac transaction tion est est terminée terminéee avec terminée avec un unCOMMIT COMMIT COMMIT Qd le REDO LOG LO G Buffer Buff er est au 1/3 1/ 3 plein (paramé (pa trable) le) Qd le REDO LOG LOG Buffer Buffer est au 1/3 1/3 plein (paramé (paramétrab ramétrabl trable) e) Qd il y a plus de 1Mo d'infos de log co contenues ntenues dans le buffer buf ferr Qd il y a plus de 1Mo d'infos de log contenues contenues dans le buffer buffe Le mett re jou r les en-t êtes Le processus processus CKPT CKPT ::sert sert àà mettre mettre mett re ààjour jour lesen-têtes en-têtes en-têt esdes des des fichiers de données, et les fichiers de contrôle CONTROL fichiers de données, et les fichiers de contrôle CONTROLFILE FILE afin de spécifier que l'action de CHECKPOINT s'est bien afin de spécifier que l'action de CHECKPOINT s'est bien déroulée déroulée Le RED O LOG Le processus processus ARCH ARCH :: copie copie un unfichier fichier REDO REDO REDO LOG FILE FILEàà un un autre autreemplacement. emplacement.
La La copie copie se se déclenche déclenche automatiquement automatiquement en en mode mode ARCHIVELOG lors du changement de groupe de ARCHIVELOG lors du changement de groupe de REDO REDO LOG LOG FILE (en mode NOARCHIVELOG le processus n'existe FILE (en mode NOARCHIVELOG le processus n'existe pas). pas).
D.BOUZIDI
21
Exécution des requêtes Une Une requête requête SELECT SELECT :: Le Le client client se se connecte connecte au au serveur, serveur, ce ce qui qui génère la création d'un processus serveur. génère la création d'un processus serveur. L’exécution L’exécution d’une d’une requête requête SELECT SELECT s’effectue s’effectue en en trois trois phases phases :: ppar sage ge Le Le par parsa arsa sage ge :: Vérifier Vérifierl’existence l’existenced’une d’unerequête requêteidentique identique(Algorithme (Algorithmede de Hachage) dans la LIBRARY CACHE Hachage) dans la LIBRARY CACHE Vérifier Vérifier la la syntaxe syntaxe de de la la requête, requête, les lesnoms noms des desobjets objets et et les les privilèges de l’utilisateur (DICTIONNARY CACHE) privilèges de l’utilisateur (DICTIONNARY CACHE) Verrouiller Verrouille r les Verrouiller les objets objets en en question question durant durant la la phase phase de de parsage mo dificationss de stru cture. parsageafin afin d'éviter d'éviter toutes toutes modifications toutes modification modifications de structure. structu structure. re. Générer Générer le le meilleur meilleur plan plan d'exécution d'exécution de de la la requête requête qui qui sera sera enregistré dans LIBRARY CACHE afin d'optimiser les enregistré dans LIBRARY CACHE afin d'optimiser les prochaines prochaines exécutions exécutions de de la la requête. requête. : L'exécution Le serveur ex cute é larequête requêteet etrréécup cupèère reles lesdonn donnéées es L'exécution : Le serveur exécutela fetch Le Le fetch Récupération Récupération des des lignes lignes et et renvoie renvoie au au processus processus utilisateur utilisateur sous leur forme brute. (le formatage se fait au niveau sous leur forme brute. (le formatage se fait au niveau du du client clientselon selonles lesparamètres paramètresdéfinis définispar par l’utilisateur l’utilisateur D.BOUZIDI
22
Exécution des requêtes Une Une requête requête DML DML :: ppar sage e :: Le Le par parsag arsa sage ge Le Le même même scénario scénario que que pour pour une une requête requête SELECT. SELECT. : L'exécution L'exécution : Pour Pour assurer assurer une une bonne bonne cohérence cohérence des des données, données, création par le serveur création par le serveur Une Uneimage imageavant avant:: utile utile pour pourles lesautres autresutilisateurs utilisateursou ou en cas d’annulation, d’annu lation, enregistée dans le segment se gment en cas d’annulation, d’annulation, enregistée enregistée dans le segment segment ROLLBack ROLLBack Une Une image image après après ::utile utilepour pourla la transaction transaction en en cours). cours).
D.BOUZIDI
23
Installation et configuration du logici log iciel el Oracle Oracle Datab Database ase 10g
Objectifs
Connaître la configuration configuration requise requise pour le le système Utiliser l'architecture Optimal Flexible Architecture (OFA) Installer et configurer le logiciel avec Oracle Universal Universal Installer Installer
D.BOUZIDI
25
Con iguration syst me requise
512 Mo de mémoire mémoire physiqu physique e (RAM) 1 Go d'esp d'espace ace de de swap swap 400 Mo d'espa d'espace ce disque disque utilisé utilisé comme comme espace espace temporaire (/tmp ou \Temp) 1,5 Go d'espace d'espace disque disque pour le logiciel logiciel Oracle Oracle 1,5 Go d'espace disque pour pour la base de données préconfigurée
D.BOUZIDI
26
Architecture OFA (Optimal Flexible Architecture)
L’objectif de l'architecture OFA est de :
définir une organisation des répertoires (application, base de données, …) recomme recommender nder une conventio convention n de nomage des fichiers fichiers
OFA permet permet de faciliter faciliter les tâches tâches d'administration d'administration répétitives :
basculement entre plusieurs bases de données Oracle gestion et administration adéquate de la croissance de la base de données Contribution à élimination de la fragmentation de l'espace libre
27
D.BOUZIDI
Architecture OFA (Optimal Flexible Architecture)
Définir Définir les les point points s de montage montage dansl e cas cas de system UNIX:
/u01
/disk01
Nommer Nommer les répertoire répertoires s:
/u01/app/oracle
/u01/app/applmgr
Nommer Nommer les les fichie fichiers rs :
D.BOUZIDI
Fichiers Fichiers de de contrôle contrôle : controln.ctl Fichiers Fichiers de journalis journalisation ation : redon.log Fichiers Fichiers de de données données : tn.dbf
28
Les variables d'environnement
ORACLE_SID : nom de l'instance (par défaut ORCL)
ORACLE_HOME : répertoire de base contenant le
logiciel Oracle
ORACLE_BASE : base de la structure de répertoires
Oracle pour l'architecture OFA NLS_LANG : paramètres de langue, de territoire et de jeu de caractères client
D.BOUZIDI
29
Démarrage Démar rage de Oracle Oracle Universal Universal Inst Installer aller
D.BOUZIDI
30
Emplacement et type d’installation
D.BOUZIDI
31
Liste des produits de l’installation
D.BOUZIDI
32
Lancement de l’installation
Deux Messages Box affichant des erreurs A 43% de l’installation, cliquer sur OK Un autre à 63%, cliquer cliquer sur reéssayer reéssayer D.BOUZIDI
33
Assistant de Configuration des servic ser vice e NET, NET, iSQ iSQL L et BD
D.BOUZIDI
34
Configuration de la Base de données
D.BOUZIDI
35
Fin de l’installation
D.BOUZIDI
36
Contrôle la base de données Data Da taba base se 10 10g g
Objectifs
démarrer et arrêter des outils : iSQL*Plus et Enterpris Enterprise e Manager Manager Database Database Control Control démarrer et arrêter le processus d'écoute Oracle démarrer démarrer et arrêter arrêter Oracle Database Database 10g
D.BOUZIDI
38
D marrer et arr ter iSQL*Plus $ isqlplu isqlplusct sctl l start start iSQL*Plus 10.1.0.2.0 Copyr Copyrig ight ht (c) 2004 2004 O Orac racle. le. Starting Starting iSQL*Plu iSQL*Plus s ... iSQL*Plus started.
All rights rights reserv reserved. ed.
$ isqlp isqlplus lusctl ctl stop stop iSQL*Plus 10.1.0.2.0 Copyr Copyrig ight ht (c) 2004 2004 O Orac racle. le. Stopping Stopping iSQL*Plu iSQL*Plus s ... iSQL*Plus stopped.
All rights rights reserv reserved. ed.
39
D.BOUZIDI
Structure de gestion
Les trois composants de la structure de gestion gestion d'Oracle d'Oracle Databa Database se 10g sont les suiv suivan ants ts :
Instance de base de données Processus d'écoute Interface de gestion gestion (Database Control)
Database Control
D.BOUZIDI
Listener 40
Démarrer et arrêter Database Control $ emct emctl l star start t dbco dbcons nsol ole e TZ set to US/Pacific Oracle Oracle Enterprise Enterprise Manager Manager 10g Database Database Control Control Release Release 10.1.0.2 10.1.0.2.0 .0 Copyri Copyright ght (c) 1996, 1996, 2004 2004 Oracle Oracle Corpor Corporati ation. on. All rights rights reserv reserved. ed. http://edrsr9p1.us.oracle.com:5500/em/console/aboutApplication Starti Starting ng Oracle Oracle Enter Enterpri prise se Manage Manager r 10g Databa Database se Contro Control l ...................... started. -----------------------------------------------------------------Logs Logs are genera generated ted in direct directory ory /u01/app/oracle/product/10.1.0/db_1/edrsr9p1.us.oracle.com_orcl/sy sman/log
$ emctl emctl stop stop dbconso dbconsole le TZ set to US/Pacific Oracle Oracle Enterprise Enterprise Manager Manager 10g Database Database Control Control Release Release 10.1.0.2 10.1.0.2.0 .0 Copyri Copyright ght (c) 1996, 1996, 2004 2004 Oracle Oracle Corpor Corporati ation. on. All rights rights reserv reserved. ed. http://edrsr9p1.us.oracle.com:5500/em/console/aboutApplication Stoppi Stopping ng Oracle Oracle Enter Enterpri prise se Manage Manager r 10g Databas Database e Contro Control l ... ... Stopped.
D.BOUZIDI
41
Accé Ac céde derr à Da Data taba base se Co Cont ntro roll
D.BOUZIDI
42
Page d'accueil Database
D.BOUZIDI
43
Modifier le statut du processus d'écoute
D.BOUZIDI
44
Etapes des procédures de démarrage et d'arrêt DEMARRAGE
OUVERTURE Ouverture de tous les fichiers conformément au fichier de contrôle de cette instance.
MOUNT
Ouverture du fichier de contrôle NOMOUNT de cette instance. Démarrage de l'instance. ARRET
ARRET
45
D.BOUZIDI
Options d'arrêt Mode d'arrêt
A
I
T
N
Permettre de nouvelles connexions
x
x
x
x
Attendre la fin des sessions en cours
x
x
x
o
Attendre la fin des transactions en cours
x
x
o
o
Imposer un point de reprise et fermer les fichiers
x
o
o
o
Mode d'arrêt :
D.BOUZIDI
A Abort
I Immediate
x
NON
T Transactional
N Normal
o
OUI
46
Différents types d'arrêt d'arrêt Transfert de fonds
1
Vérification de soldes
2
Ajout de nouveaux fonds
3
Retrait de fonds de l'ancien compte
4 5
Arrêt en mode Normal
Arrêt en mode Immediate
Arrêt de la base de donné données
Validation Déconnexion
Arrêt en mode Transactional
Arrêt de la base de donné données Arrêt de la base de donné données
47
D.BOUZIDI
Options SHUTDOWN
Vers Vers le bas bas :
Pendant les opérations
Le cache de SHUTDOWN NORMAL tampons de la ou base de données SHUTDOWN est écrit dans les TRANSACTIONAL fichiers de données ou Les modifications SHUTDOWN IMMEDIATE non validées sont annulées Les ressources sont libérées Base de données cohérente (base de données propre) D.BOUZIDI
Vers le haut :
Pas de récupération d'instance
48
Options SHUTDOWN
Vers Vers le bas bas :
Pendant les opérations
Vers Vers le le haut haut :
Les fichiers de Les mémoires SHUTDOWN ABORT journalisation journalisation en ou tampon ligne sont échec d'une modifiées ne utilisés pour instance sont pas écrites ou réappliquer dans les STARTUP FORCE les modifications modifications fichiers de Les segments données d'annulation Les sont utilisés modifications pour annuler les non validées ne modifications sont pas Base de données non validées annulées incohérente Les ressources (base de données "dirty") sont libérées 49
D.BOUZIDI
Options SHUTDOWN STARTUP STARTUP [Option] [Option]
NOMOUNT arriè re NOMOUNT :: Créé Créé la la SGA SGA et et démarre démarre les les processus processus en processus en arrière en arrière plan mais ne permet pas pa s l'accès l'acc ès à la base (reste (re ste un OPEN à faire) . plan mais ne permet permet pas l'acc l'accès ès à la base (reste (reste un OPEN OPEN à faire). faire). faire ). MOUNT certai nes activités MOUNT :: Monte Monte la la base base pour pour certaines certaines certaines activités DBA DBA mais mais ne permet au cun accè base permet aucun auc aucun un accès accèss àà la accès la base. base.. base. OPEN d'accé der àà la OPEN :: Permet Permet aux aux utilisateurs utilisateurs d'accéder d'accéder la base. base. EXCLUSIVE Autori see l'instance EXCLUSIVE :: Autorise Autoris Autorise l'instance courant courant seulement courant seulement àà accéder accéder àà la base. la base. PFILE d'initial isation PFILE :: Spécifie Spécifie le le fichier le fichier d'initialisation d'initialisat d'initialisation ion àà prendre prendre en en compte. compte. FORCE FORCE :: Annule Annule l'instance l'instance courante courante avant avant d'effectuer d'effectuer un un démarrage normal. démarrage normal. RESTRICT RESTRICT :: Autorise Autorise seulement seulement l'accès l'accès aux aux utilisateurs utilisateurs avec avec le le privilège privilège RESTRICTED RESTRICTED SESSION. SESSION. RECOVER RECOVER Démarre Démarre la la restauration restauration media media quand quand la la base base démarre. démarre.
D.BOUZIDI
50
Exemple Exemple de de commande commande démarrage démarrage et et d’arrêt d’arrêt de de la la base base de de données données Démarre Démarre l'instance l'instance et et ouvre ouvre la la base base de de données de données ::
STARTUP PFILE=init015. ora STARTUP PFILE=init015.ora PFILE=init015.ora PFILE=init015. ora
Fait Fait passer passer la la base base de de données données de de l'état l'état NOMOUNT NOMOUNT àà MOUNT MOUNT :: ALTER OR CL MOUNT; ALTER DATABASE DATABASE ORCL ORCL O RCL MOUNT;
•• Ouvert Ouvert la la base base de de données données en en mode mode lecture lecture seule seule (READ (READ ONLY) ONLY) ::
ALTER OO RCL ALTER DATABASE DATABASE ORCL ORCL RCL OPEN OPEN READ READ ONLY; ONLY; ONLY;
Arrête dde Arrête de de ll ’instance ’instance et et la la base base de dee données données :: SHUTDOWN SHUTDOWN Immediate; Immediate;
D.BOUZIDI
51
Ouvrir une base de données en mode lecture seule (READ ONLY) Toute ou verte Toute base base de de données données peut peut être être ouverte ouver ouverte te en en mode mode lecture lecture seule. seule. mo de, vous pouv ezz :: Dans ce ce mode, mode, mode, vous pouvez vous pouvez pouve Dans
lancer lancer des des interrogations, interrogations, interrogation s, effectuer effectuer des des opérations opérations de de tri tri sur sur disque disque avec des tablesp ta blespaces aces gérés en local, lo cal, avec des tablesp tablespaces aces gérés en local, local, utiliser utiliser des des fichiers fichiers de de données données hors hors ligne ligne et en ligne, et non des tablespaces, et en ligne, et non des tablespaces, récupérer récupérer des des fichiers fichiers de de données données hors hors ligne et des tablespaces. ligne et des tablespaces.
D.BOUZIDI
52
Fichier de paramètres d'initialisation Instance Mémoire SGA
P a r a m è t r e s
F i c h i e r
Cache des tampons des DATA SMON
PMON
tampons Journalis ation DBW
Zone de mémoire partagée
Cache library Cache Cache dictio dictio D LGWR ARCH
CKPT
init001.ora
SQL> CO TT sys/PwdSy sys/P ss AS SYSDB A SQL> CONNEC CONNECT CONNEC NNECT sys/PwdSys sys/PwdSy wdSys AS SYSDBA SYSDBA SYSD BA SQL> SQL> STARTUP STARTUP PFILE=C:\oracle\product\10.1.0\admin\orcl\pfile\init001.ora PFILE=C:\oracle\product\10.1.0\admin\orcl\pfile\init001.ora
53
D.BOUZIDI
Utiliser les paramètres
Dimensionnez Dimensionnez la la mémoire mémoire SGA. SGA. Définissez Définissez les valeurs valeurs par défaut défaut de de la la BD BD et et de de l'instance. l'instance. Définissez Définissez les les limites limites relatives relatives aux aux utilisateurs utilisateurs ou ou aux a ux processus. processus. Définissez Définissez les les limites limites relatives relatives aux aux ressources ressources de de la la BD. BD. Définissez Définissez les les divers divers attributs attributs physiques physiques de la BD, physiques BD, tels tels que que la taille des blocs. la taille des blocs. Indiquez Indiquez l'emplacement l'emplacement des fichiers fichiers de de contrôle, contrôle, des des fichiers journaux archivés, du fichier ALERT et des fichiers journaux archivés, du fichier ALERT et des fichiers fichiers trace. trace.
D.BOUZIDI
54
Exemple de fichier de paramètres ## Initia Ini tializ lizati ation on Par amete eter rr File: Fil e: initU15 init U15.or .ora a Initi Initiali aliza zatio tion n Param Param Par amete eter File: Fi le: initU15.o initU1 5.ora ra ddb == U b_ _nnaamme e U1 155 cco == (/ ((/ on nttrrool l_ _f fiille es s (/ /DDIISSK K1 1/ /ccoon nt tr rooll001 1. .c coonn,, ddb b_ _bbllooc ck k_ _ssiiz ze e ddb _ b l o c k _ b u f f b_block_buffe errss ssh ha arreedd_ _p po oooll_ _s si izzee llo og g__bbuuf ff fe err ppr ro occeesss se es s ddb b_ _ffiille es s llo og g__ffiil le es s max ma x_dum _d ump_ fil l e_ size ze max ax_d _du ump_fi p_fi p_ fil le_si e_si e_ siz ze
/DISK2/control02.con) /DISK2/control02.con) == 8 1 819922 == 20 220 20 04488 == 5 52 24422888 80 00 0 == 6 64 4KK == 5 50 0 == 1 10 02244 == 1 10 0 == 10 1102 240 24 0 10 0240 40
background_dump_ background_dump_dest dest (/home/disk3/user15/BDUMP) er15/BDUMP) background_dump_dest background_dump_ dest == (/home/disk3/us (/home/disk3/user15/BDUMP) (/home/disk3/use r15/BDUMP) uus == ( se err__ddu um mp p__dde es st t (/ /hhoomme e/ /d diissk k3 3/ /uusseer r1 15 5//UUDDU UM MP P)) cco == ( or ree__ddu um mp p__dde es st t (/ /hhoomme e/ /d diissk k3 3/ /uusseer r1 15 5//CCDDU UM MP P)) rro == ol lllbbaac ck k_ _sseeg gm me ennttss (r01,r02,r03,r04,r05,r06,r07,r08) (r01,r02,r03,r04,r05,r06,r07,r08) ... ...
D.BOUZIDI
55
Quelques paramètres
BACK_GROUND_DUMP_DEST :Emplacement où les fichiers fichiers traces des processus en arrière plan sont enregistrés.
USER_DUMP_DEST Emplacement où les fichiers fichiers traces sont créés.
CONTROL_FILES : Noms des fichiers de contrôle.
DB_CACHE_SIZED : taille du tampon de données, (remplace le paramètre DB_BLOCK_BUFFERS définissant le nombre de blocs mis en cache dans la SGA, paramètre obsolète obsolète à partir de la version 9i)
DB_NAME : Identifiant de la base de données de 5 caractères ou moins. (seul paramètre nécessaire à la création d'une d'une base).
SHARED_POOL_SIZE : Taille en octets de la zone de partage.
IFILE : Permet de référencer référencer un autre fichier de de paramètre à imbriquer dans la définition.
d'octets alloués alloués au buffer buffer redolog redolog dans la SGA. LOG_BUFFER : Nombre d'octets
MAX_DUMP_FILE_SIZE : Taille maximum des fichiers trace, spécifiée en nombre de blocs de l'OS.
PROCESSES : Nombre de processus de l'OS pouvant se connecter simultaném simultanément ent à l’instanc l’instance. e.
SQL_TRACE : Active l’outil de suivi SQL pour chaque session utilisateur.
TIMED_STATISTICS : Active ou non le minutage dans les fichiers trace et sur les écrans.
D.BOUZIDI
56
Vues dynamiques des performances
Sont Sont gérées gérées par par le le serveur serveur Oracle serveur Oracle et et mises mises àà jour mises jour en en permanence. permanence. Contiennent Contiennent des des données données sur sur les les structures structures de de disque disque et et de de mémoire. mémoire. Contiennent Contiennent des des données données utiles utiles pour pour le le réglage réglage des des performances. performances.
Sont Sont associées associées àà des des synonymes synonymes publics publics portant portant le le préfixe préfixe V$. V$.
v$fixed_table v$fixed_table contient contient le le nom nom de de toutes toutes les les vues les vues disponibles disponibles
V$PARAMETER : des infos sur les param ètres d'initialisation
V$SYSTEM_PARAMETER : des infos sur les param ètres d’initialisation et leurs modification éventuelles
Mémoire SGA
V$SGA : des infos sur la SGA
V$OPTION : la liste des options install ées sur le serveur Oracle
V$PROCESS : des infos sur les processus actifs courant
V$SESSION : des infos sur la session courante
V$VERSION : Liste le num éro de version et les composants
V$INSTANCE : Affiche l' état de l'instance courante 57
D.BOUZIDI
Afficher les vues dynamiques des performances OUVERTURE
MOUNT
NOMOUNT
ARRET
D.BOUZIDI
Dictionnaire de donné données
Lecture des donné donn ées sur disque
Lecture des donné données en mé mémoire
58
Affichage des paramètres paramètres Afficher Afficher les les valeurs valeurs des des paramètres paramètres en en cours cours
Exécutez Exécutez la la commande commande ::
SHOW SHOW PARAMETER PARAMETER control control
Interrogez Interrogez la la vue vue dynamique dynamique des des performances performances V$PARAMETER : V$PARAMETER :
SELECT SEL ECT nam ee FROM FRO MM v$pa vv$ $param ramete eter rr WHERE WHE RE nam ee LIKE LIK EE '%co ''% %contr ntrol% ol%'; ';; SELECT SEL ECT name name nam FROM FRO v$pa param ramete eter WHERE WHE RE name nam n ame LIKE LIK '%con contro trol%' l%';
Certains Certains paramètres paramètres d'initialisation d'initialisation peuvent peuvent être être modifiés modifiés pendant pendant l'exécution l'exécution d'une d'une instance instance (grâce (grâce aux aux commandes commandes Alter Alt er ses sion Al ter rr syste sys tem m Alter Alt er session session sess ion et Alte Al ter syste sys tem m).). et Alte
ALTER SE TT SQL_TRACE=true; SQL_TRACE=t rue;; //session ALTER SESSION SESSION SET SET SE SQL_TRACE=true SQL_TRACE=true; //session courante courante ALTER TIMED_STATIS TICS=true; ALTER SYSTEM SYSTEM SET SET TIMED_STATISTICS=true; TIMED_STATISTICS=true; TIMED_STATISTIC S=true; ALTER SORT_AREA_SI ZE=131072 DEFER ALTER SYSTEM SYSTEM SET SET SORT_AREA_SIZE=131072 SORT_AREA_SIZE=131072 SORT_AREA_SIZE= 131072 DEFERRED; DEFERRED; DEFERRED; RED;
// èètre à à // DEFERRED DEFERRED Indique Indique que que le le param param tre ne ne sera sera modifi modifiéé qu' qu' partir prochain partir de de la la prochaine prochainee session. prochaine session. 59
D.BOUZIDI
Gestion des sessions
Activer Activer et et désactiver désactiver une une session session en en mode mode restreint restreint
Exécutez Exécutez la la commande commande STARTUP STARTUP pour pour restreindre restreindre l'accès à une base de données donnée s : l'accès à une base base de données données : STARTUP STARTUP RESTRICT RESTRICT
Exécutez Exécutez la la commande commande ALTER ALTER SYSTEM SYSTEM pour pour mettre mettre une instance en mode restreint : une instance en mode restreint : ALTER ENA BLE RESTRICTE D SESSION; ALTER SYSTEM SYSTEM ENABLE ENABLE ENA BLE RESTRICTED RESTRICTED SESSION;
Interrompre Interrompre une une session session Dans la vue dynamique Dans la vue dynamique des des performances performances V$SESSION, V$SESSION, identifiez sess ion àà interrompre identifiez la la session session interrompre :: SELECT SELECT sid, sid, serial# serial# FROM FROM v$session v$session WHERE WHERE username='SCOTT'; username='SCOTT';
Exécutez Exécutez la la commande commande ALTER ALTER SYSTEM SYSTEM :: ALTER KIL LL SESSION '7,15 '; ALTER SYSTEM SYSTEM KILL KILL KIL SESSION '7,15'; '7,15'; '7, 15';
D.BOUZIDI
60
Fichiers ALERT et TRACE
Les Les fichiers fichiers trace trace peuvent peuvent être être écrits écrits par par les les processus serveur et les processus d'arrièreprocessus serveur et les processus d'arrièreplan. plan. Le Le serveur serveur Oracle Oracle vide vide les les informations informations relatives relatives aux erreurs dans des fichiers trace. aux erreurs dans des fichiers trace. Le chronologiqu e Le fichier fichier ALERT ALERT est est le le journal journal chronologique chronologique des des messages messages et et des des erreurs. erreurs. La La fonction fonction trace trace par par processus processus serveur serveur peut peut être activée ou désactivée par : être activée ou désactivée par : une une commande commande ALTER ALTER SESSION, SESSION,
D.BOUZIDI
le le paramètre paramètre SQL_TRACE. SQL_TRACE.
61
Gestion du fichier de contrôle
Utiliser le fichier de contrôle
Petit Indiqu Petit fichier fichier binaire binaire :: Indique Indiquee au Indique au serveur serveur où où sont sont situés situés les fichiers constituant la base de données les fichiers constituant la base de données Nécessaire Nécessaire ::
au au montage montage au au fonctionnement fonctionnement de de la la base base de de données données
Lié seu lee base bbase ase ddonné es Lié àà une une seule seule seul ba se de de donné données onnées es Modifié fréquemme nt par ser orac Modifié fréquemment fréquemment fréquem ment par le le serveur serveur serveur veur oracle. oracle. oracle. le. Indispensable Indispensable pour la la restauration restauration de de la la base. base. Doit Doit être être multiplexé multiplexé
En En cas cas de de perte perte du du fichier fichier de de contrôle, contrôle, la la base base de de données données doit doit être être restaurée restaurée
Pour fic Pour afficher afficher les afficher les noms noms des des fichiers fichiers fichiers hiers de de CTL CTL show show parameter parameter control_files control_files
select sel ect valu fro mm v$para v$p aramet meter er whe re name=' nam e='con contr trol_ ol_fil files' es'; ; select sel ect value valuee from value from fro v$para v$p aramet meter er where where wh ere name=' nam e='con contro trol_f l_file iles'; s'; 63
D.BOUZIDI
Contenu du fichier de contrôle
Nom Nom et et identificateur identificateur de de la la base base de de données données Date Date de de création création de de la la base base de de données données Emplacement Emplacem ent des jjournalisation ournalisation Emplacement des fichiers fichiers de de données données et et de dejournalisation Noms table don nées (nom Noms des des tablespaces tablespaces tablespaces spaces et et les les fichiers fichiers de de données données données (nom de de fichier,statut lecture/écriture, en ligne ou non) fichier,statut lecture/écriture, lecture/écriture, en ligne ou non) Les Les fichiers fichiers log log en en ligne ligne Historique Historique de de journalisation journalisation archivés archivés Informations Informations de de sauvegarde sauvegarde Informations Informations sur sur les les blocs blocs corrompus corrompus …… ……
D.BOUZIDI
64
Multiplexer le fichier de contrôle control_files=("C: control_files=("C:\ =("C:\oracle\ oracle product\ \ product 10.1.0\oradata\ oradata\orcl\ orcl\control01.ctl", product\10.1.0\ "C:\ "C:\oracle\ oracle product\ \ product 10.1.0\oradata\ oradata\orcl\ orcl\control02.ctl", product\10.1.0\ "C:\ "C:\oracle\ oracle product\ \ product 10.1.0\oradata\ oradata\orcl\ orcl\control03.ctl") product\10.1.0\
Unité
control01.ctl
Unité 2 Unité 3
control02.ctl
control03.ctl
65
D.BOUZIDI
Multiplexer le fichier de contrôle
Deux Deux méthodes méthodes pour pour multiplexer multiplexer le le fichier fichier de de contrôle contrôle :: Méthode Méthode 11
Visualiser Visualiser les les fichiers fichiers de de contrôle contrôle existants existants Arrêter la base Arrêter la base Modifier Modifier le le paramètre paramètre CONTROL_FILES CONTROL_FILES dans dans le le fichier fichier init.ora init.ora Copier Copier le le fichier fichier de de contrôle contrôle en en utilisant utilisant les les commandes commandes OS OS Démarrer la base de données Démarrer la base de données
Méthode Méthode 22
Ajouter /disk3/orad ata/orcl/control04.ctl 4.ctl Ajouter le le nouveau nouveau fichier fichier de de contrôle contrôle /disk3/oradata/orcl/control0 /disk3/oradata/orcl/ /disk3/oradata/orcl/control04.ctl control04.ctl dans dans le le paramètre paramètre CONTROL_FILES CONTROL_FILES en en utilisant utilisant la la commande commande :: alter alter system system set set control_files= control_files= '/disk1/oradata/orcl/control01.ctl', '/disk1/oradata/orcl/control01.ctl', '/disk1/oradata/orcl/control02.ctl '/disk1/oradata/orcl/control02.ctl ', ', '/disk1/oradata/orcl/control03.ctl '/disk1/oradata/orcl/control03.ctl ', ', '/disk1/oradata/orcl/control04.ctl '/disk1/oradata/orcl/ control04.ctl' control04.ctl ' scope= sco pe=spf spfile ile; ; '/disk1/ora '/dis k1/oradata/ data/orcl/c orcl/contro ontrol04. l04.ctl' ctl' scope=spfil scope= spfile; e;
Arrêter Arrêter la la base base Copier Copier le le fichier fichier de de contrôle contrôle en en utilisant utilisant les les commandes commandes OS OS Démarrer la base de données Démarrer la base de données
Oracle con temp s, mais Oracle met met ààjours jours les les fichiers fichiers de de contrôle contrôle contrôle trôle en en même même temps, temps, mais seul seul le le premier premierfichier fichiercité cité dans dans le le paramètre paramètreCONTROL_FILES CONTROL_FILESest estconsulté. consulté.
//SCOPE=SPFILE:le //SCOPE=SPFI LE:le //SCOPE=SPFILE:le //SCOPE=SPFI LE:le changement changement de de paramètre paramètre est est enregistré enregistré dans dansle leSPFILE, SPFILE, et etne nesera sera pris prisen en compte compte qu’au déma rrage qu’au prochain prochain démarrage démar démarrage rage de de l'instance. l'instance. Utilisé Utilisé pour pourles lesparamètres paramètresnon non dynamique dynamique
D.BOUZIDI
66
sauvegarder le fichier de contrôle
Deux Deux méthodes méthodes ::
Commande sauvegardan t le n un Commande sauvegardant sauvegardant sauvegardant le fichier fichier de de contrôle contrôle een un fichier fichier binaire binaire ::
alte al ter rr data da taba base se ba ckup up co ntro rolf lfil ile ee to no m_du du_f _fic ichi hier er alte al ter data da taba base se back back ba ckup up cont cont co ntro rolf lfil ile to nom_ nom_ no m_du du_f _fic ichi hier er
Commande Commande créant créant un un fichier fichier en en format format texte texte dans dans le le répertoire répertoire USER_DUMP_DEST USER_DUMP_DEST
alter tra ce; alter database database backup backup controlfile controlfile to to trace; trace; trace;
C’est rec ommandéé de sauve fic hier de contrô le àà chaque chaqu e C’estt recommandé C’es recommand recommandé de sauvegarder sauvegarder sauvegarde garderr le le fichier fichier de contrôle contrôle contrôle chaque modification de la structure de la base (Ajout, renomme ou modification de la structure de la b ase (Ajout, renomme ou suppression suppression de de fichiers fichiers de de données données ou ou de de journalisation). journalisation).
67
D.BOUZIDI
Obtenir des informations V$CONTROLFILE : affiche tous les noms des fichiers de contrôle e t leur statut qui peut être NULL ou INVALID
V$CONTROLFILE_RECORD_SECTION : plusieurs infos sur le fichier de contrôle (TYPE : Type de la section, RECORD_SIZE : Taille d'u ne entrée en bits, RECORDS_TOTAL : Nombre d'entr ées allouées pour la section, RECORDS_USED : Nombres Nombres d'entrées utilis ées dans la section, FIRST_INDEX : Index de la premi ère entrée, LAST_INDEX : Index de la derni ère entrée)
Fichier de contrôle
V$KCCDI : affiche la valeur de MAXLOGMEMBERS (DIMLM)
Liste des vues qui lisent directement du fichier de contrôle : V$THREAD, V$DATABASE, V$DATAFILE, V$DATAFILE_HEADER V$LOGFILE, V$ARCHIVED_LOG, V$BACKUP, V$BACKUP_DATAFILE, V$BACKUP_PIECE, V$BACKUP_REDOLOG, V$BACKUP_SET, …
D.BOUZIDI
68
Gestion des fichiers de journalisation (les fichiers log)
Utiliser Utiliser des des fichiers fichiers de de journalisation journalisation Permettent Permettent àà la la base base de de garder garder une une trace trace de de toutes toutes les les altérations altérations de de données, données, En ccrash En cas cas de de crash crash rash de de la la base, base, ils ils permettent permettent de de rejouer rejouer les les modifications modifications apportées apportées àà la la base. base. Doivent Doivent être être multiplixés multiplixés au au moins moins au au nombre nombre de d e deux deux
Instance Zone de mémoire partagée
Mémoire SGA
LGW ARC
Fichiers de données
Fichiers de contrôle Base de données
D.BOUZIDI
Fichiers de journalisation Fichiers archivages des logs
70
Mode Mode de de fonctionnement fonctionnement des des fichiers fichiers de de journalisation journalisation Groupe 1
Groupe 2
Groupe 3
Membre1
Membre1
Membre1
Membre2
Membre2
Membre2
Unité Unité 1
Unité Unité 2 En lein est En mode mode NOARCHIVELOG, NOARCHIVELOG, un un fichier fichierlog logpplein est disponible disponible après après que que les les changements changements enregistrés enregistrés dedans dedans sont sont écrits écrits dans dans les les fichiers fichiers de de données. données.
En En ARCHIVELOG, ARCHIVELOG, un un fichier fichier log log plein plein est est disponible disponible après après que que les les changements changements effectués dedans sont écrits dans les fichiers de données et était archivé effectués dedans sont écrits dans les fichiers de données e t était archivé
Un aarchivé rchivé num séquenc e Un fichier fichier log log en en ligne ligne ou ou archivé ar chivé est est identifié identifié par par son son numéro numéro numéro éro de de séquence séquence
D.BOUZIDI
71
Obtenir Obtenir des des informations informations Sur l'l'arch vage àà part partir ir Sur l'archi l'archivage archivage ivage partir part ir ::
Ligne Ligne de de commande commande : ARCHIVE : ARCHIVE LOG LOG LIST; LIST;
V$DATABASE na me, log_m ode FROM v$d atabase; se; V$DATABASE (NAME,LOG_MODE) (NAME,LOG_MODE)SELECT SELECT name, na name, me, log_mode log_mode log_mode FROM v$databa v$database; v$databa se; V$INSTANCE (ARCHIVER) V$INSTANCE (ARCHIVER) SELECT aarchiv rchiver r FROM v$ instance; nce; SELECT archive a rchiver er FROM v$insta v$i v$instan nstance; ce;
Sur lles gro upes partir r :: Sur les les es groupes grou groupes pes àà parti partir part ir
V$THREAD (groups, current_group#, current_group#, sequence# sequence# )) //fichier //fichier log log en en co cours urs V$THREAD (groups, SELECT SELECT groups, groups, current_group#, current_group#, sequence# sequence# FROM FROM v$thread; v$thread;
Sur gr s et membr ess àà part partir ir Sur les les groupe groupes groupes oupes et les les membres les membre membres partir part ir ::
V$LOG V$LOG (group#, (group#, members, members, status, status, sequence, sequence, bytes) bytes) //les //les infos infos du du fichier fichier CTL CTL stat status us : unused (jamais écrit), current current (en ligne et en cours d'écriture), active (en ligne et nécessaires à la restauration de la base base ), inactive (en ligne non nécessaires à la restauration restauration de la base) SELECT byt es, members, SELECT group#, group#, sequence#, sequence#, bytes, bytes, members, status status FROM FROM v$log v$log ;;
V$LOGFILE V$LOGFILE (group#, (group#, type, type, status, status, member) member) stat status us : invalid (le fichier est inaccessible), stale (le fichier est incomplet), deleted (le fichier n’est pas utilisé), vide vide (le fichier est en cours d’utilisation)
D.BOUZIDI
SELECT SELECT ** FROM FROM v$logfile; v$logfile;
72
Changements Changements de de fichier fichier de de journalisation journalisation et et points points de de reprise reprise
Un swi poin arrê te d'écrire d'éc rire dans ll'un 'un des Un log log switch switch switch tch est est le le point pointt où point où la la base base arrête arrête arrête d'écrire d'écrire dans l'un dans des fichiers fichi ers eet t commence commenc ee àà écrire écrir ee dans aut fichiers fichi ers redo redo en en ligne ligne et commence commenc écrire écrir dans un un autre. autre. autre. re. Se Se déclanche déclanche ::
Le Le fichier fichier log log courant courant est est plein plein et et on on doit doit continuer continuer àà écrire écrire dans dans le le fichier fichier redo redo Configuré repro duit àà intervalles Configuré pour pour qu'il qu'ilse se reproduit reproduit reproduit intervallesréguliers réguliers
Manuellement Manuellement
Imposer Impose rr des change fichi journal isation onn àà l'aide l'aid e de Imposer Impose des changements changements changements ments de de fichier fichier fichier er de de journalisati journali journalisatio sation l'aide de la la commande : commande : ALTER SYS TEM ALTER SYSTEM SYST SYSTEM EM SWITCH SWITCH LOGFILE; LOGFILE;
La suivant e permet La commande commande suivante suivante permet d'archiver permet d'archiver le le redo redo log log courant courant et et d'activer d'activer le le redo log suiva s uivant. nt. redo log suiva suivant. nt. ALTER SYST EM CHECKPOINT; CHECKPO //archiv er le ALTER SYSTEM SYSTEM SYSTEM CHECKPOINT; CHECKPOINT; INT; //archiver //archiver //archiver le redo redo log log courant courant. .
Gérer Gérer les les points points de de reprise reprise àà l'aide reprise l'aide des des paramètres paramètres d'initialisation d'initialisation ::
LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOUT LOG_CHECKPOINT_TIMEOUT
FAST_START_IO_TARGET FAST_START_IO_TARGET
73
D.BOUZIDI
Processus Processus CKPT CKPT
Le Le CHECKPOINT CHECKPOINT est est un un évènement évènement qui qui se se déclencher déclencher lors lors ::
D'un grou pe de LLOG OG FILE. D'un changement changement de de groupe groupe groupe de REDO REDO LOG REDO FILE. D'un arrêt normal de la base de d e données (c'est D'un arrêt normal de la base de données (c'est àà dire dire sans sans l'option l'option ABORT) ABORT) D'une l'administra teur D'une demande demande explicite explicite de de l'administrateur l'administrateur l'administr ateur D'une limite définie par les paramètres d'initialisation D'une limite définie par les paramètres d'initialisation LOG_CHECKP OINT_INTERVAL :: spécifie LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_INTERVAL LOG_CHECK POINT_INTERVAL spécifie le le nombre nombre maximum maximum de de blocs Buf fer qui blocs du du REDO REDO LOG LOG Buffer Buffer Buffer qui seront seront alors alors lus lus lors lors d'une d'une d'une restauration restauration de de l'instance. l'instance. LOG_CHECKPOINT_TIMEOUT LOG_CHECKPOINT_TIMEOUT :: DDéfini éfini en en secondes. secondes. IlIl permet permet de de définir le temps maximum entre 2 CHECKPOINTS (ver définir le temps maximum entre 2 CHECKPOINTS (ver >> 8i 8i :: permet permet de de définir définir le le temps temps maximal maximal de de lecture lecture du du processus processus LGWR LGWR ).). FAST_START_IO_TARGET FAST_START_IO _TARGET FAST_START_IO_TARGET FAST_START_IO _TARGET :: en en secondes, secondes, ilil définit définit le le temps le temps maximum maximum pour pour restaurer restaurer une une instance. instance.
L'évènement L'évènement CHECKPOINT CHECKPOINT déclenche déclenche
Le Le LGWR LGWR :: vide vide le le REDO REDO LOG LOG Buffer. Buffer. l'écriture dd'un l'écriture d'un d''un un certain certain nombre nombre de de blocs blocs du du Database Database Buffer Buffer Cache Cache dans dans les mbre de les fichiers fichiers de de données données par par DBWn. DBWn. Le Le no nombre de blocs blocs écris écris par par DBWn DBWn est est défini défini avec avec le le paramètre p aramètreFAST_START_IO_TARGET FAST_START_IO_TARGET FAST_START_IO_TARGET
D.BOUZIDI
74
Manipulation des fichiers de log
Ajout Ajout des des groupes groupes
ALTER LOGFILE('/DISK1/Redo30.log','/ DISK2/Redo31.log') size ALTER DATABASE DATABASE ADD ADD LOGFILE('/DISK1/Redo30.log','/DISK2/Redo31.log') LOGFILE('/DISK1/Redo30.log','/DISK2/Redo31.log') LOGFILE('/DISK1/Redo30.log','/DISK2/Redo31.log') size 1M; 1M;
Ajout de s membres mem bres à un gro upe Ajout des des membres un groupe grou groupe pe ALTER ALTER DATABASE DATABASE ADD ADD LOGFILE LOGFILE MEMBER MEMBER '/DISK2/Redo11.log' '/DISK2/Redo11.log' TO TO GROUP GROUP 1, 1,
Groupe 1
Groupe 2
Groupe 3
Redo10.log
Redo20.log
Redo30.log
Redo11.log
Redo21.log
Redo31.log
'/DISK2/Redo21.log' '/DISK2/Redo21.log' TO TO GROUP GROUP 2; 2;
Modification d’emplacemen t Modification d’emplacement d’emplacement d’emplaceme nt
1. 1. Arrêtez Arrêtez la la base base de de données données 2. 2. Copiez Copiez les les fichiers fichiers de de log log en en ligne ligne vers vers un un nouvel nouvel emplacement. emplacement. emplacement. 3. lla a commande comma nde ALTER ALT D ATABASE RENAME MOU 3. Exécutez Exécutez la commande commande ALTER ALTER ER DATABASE DATABASE RENAME FILE FILE en en mode mode MOUNT. MOUNT. MOUNT. NT. ALTER TTO O '/DISK3/Redo11.log'; ALTER DATABASE DATABASE RENAME RENAME FILE FILE '/DISK2/Redo11.log' '/DISK2/Redo11.log' TO '/DISK2/Redo11.log' '/DISK3/Redo11.log';
Suppression Suppression des des groupes groupes :: ALTER ALTER DATABASE DATABASE DROP DROP LOGFILE LOGFILE GROUP GROUP 3; 3;
Suppression Suppression des des membres membres des des groupes groupes : ALTER '/DISK2/Red o21.log'; ALTER DATABASE DATABASE DROP DROP LOGFILE LOGFILE MEMBER MEMBER '/DISK2/Redo21.log'; '/DISK2/Redo21.log'; '/DISK2/Redo21.log';
Effacement Effacemen t des fich iers de Effacement Effaceme nt des fichiers fichiers fichiers de journalisation journalisation ::
ALTER '/DISK1/Redo20 .log'; ALTER DATABASE DATABASE CLEAR CLEAR LOGFILE LOGFILE '/DISK1/Redo20.log'; '/DISK1/Redo20. '/DISK1/Redo20.log'; log';
D.BOUZIDI
75
Erreurs possibles du processus LGWR Un Un membre membre d'un d'un groupe groupe contenant contenant au au moins moins deux fichiers fi chiers de journalisation journalisatio n n'est pas p as deux fichiers fichiers journalisation journalisati on n'est pas disponible. disponible. grou pe suivant Tous les les membres membres du du groupe groupe suivant ne suivant ne sont sont Tous pas pas disponibles. disponibles. grou pe en Tous les les membres membres du du groupe groupe en cours cours ne cours ne sont sont Tous pas pas disponibles. disponibles.
D.BOUZIDI
76
Obtenir des informations V$THREAD : affiche les infos sur le le fichier log courant
V$LOG : donne les informations en lisant lisant dans dans le fichier de cont rôle au
lieu de lire dans le dictionnaire de donn ées
Fichier de contrôle
V$LOGFILE : Pour voir les noms n oms des des membres d'un groupe ( GROUP# est le numéro du groupe Redo Log. STATUS prend la valeur : INVALID si le fichier est inaccessible, STALE si le fichier est incomplet , DELETED si le fichier n'est plus utilis é et VIDE si le fichier est en cours d'utilisation. MEMBER est le nom du membre Redo Log V$LOG_HISTORY : contient des infors concernant l'historique des fichiers redo à partir du fichier de contrôle. Le maximum que peut retenir la vue dépends du param ètre MAXLOGHISTORY. MAXLOGHISTORY.
77
D.BOUZIDI
Analyser les fichiers de journalisation en ligne
Suivi Suivi des des modifications modifications ::
dans dans la la base base de de données, données, dans dans une une table table spécifique, spécifique,
relatives u p articulier relatives àà un un utilisateur utilisateur utilisateur tilisateur particulier particulier
Application Application de de modèles modèles d'accès d'accès aux aux données. données. Annulation Annulatio n des Annulation des modifications modifications de de la la base base de de données. données. Utilisation Utilisation des des données données archivées archivées pour pour effectuer effectuer des des opérations opérations de de réglage réglage et et planifier planifier les les capacités. capacités.
D.BOUZIDI
78
LogMiner Le Le problème problème de de ces ces fichiers fichiers c'est c'est que que l'on l'on ne ne peut peut pas pas éditer éditer le le contenu contenu aussi aussi facilement facilement Oracle Oracle aa fournit fournit un un outil outil très très pratique pratique permettant permettant d'analyser d'analyser et et d’auditer d’auditer les les actions actions effectuées sur la base : LogMiner effectuées sur la base : LogMiner Deux script s sont po ur l’installer l’inst aller :: Deux scripts scripts sont utilisés utilisés pour utilisés pour l’installer
/rdb ms/admin/dbmslm.sql slm.sql /rdbms/admin/dbm home>/rdbms/admin/dbm home>/rdbm s/admin/dbmslm.sql slm.sql qui qui installe le package DBMS_LOGMNR qui vous installe le package DBMS_LOGMNR qui vous servira aainsi servira àà analyser analyser les les REDO REDO LOG, REDO LOG, ainsi ainsi insi que que des des procédures, procédures, vues vues et et tables tables publiques. publiques. /rdb ms/admin/dbmslmd.sql slmd.sql /rdbms/admin/dbm home>/rdbms/admin/dbm home>/rdbm s/admin/dbmslmd.sql slmd.sql qui qui installe installe le le package package DBMS_LOGMNR_D DBMS_LOGMNR_D qui qui servira servira àà construire construire le le dictionnaire dictionnaire de de données. données.
79
D.BOUZIDI
Utiliser LogMiner
Création avo ir les Création d’un d’un Snapshot Snapshot du Snapshot du DD DD (pour (pour avoir avoir avoir les définitions définitions définitions des objets présents dans la base de données) des objets présents dans la base de données)
Initialiser Initialiser le le paramètre paramètre UTL_FILE_DIR UTL_FILE_DIR ::
Alter ‘/oracle/tmp/' SCOPE=spfile; Alter system system SET SET utl_file_dir= utl_file_dir=‘/oracle/tmp/' SCOPE=spfile;
Création Création d’un d’un fichier fichier de de dictionnaire dictionnaire :: EXECUTE DBMS_LOGMNR_D.BUILD(‘monDictionnaire ', ‘/oracle '); ''); EXECUTE DBMS_LOGMNR_D.BUILD(‘monDictionnaire.ora DBMS_LOGMNR_D.BUILD(‘monDictionnaire DBMS_LOGMNR_D.BUILD(‘monDictionnair e.ora', .ora', .ora ', ‘/oracle/tmp/ ‘/oracle/tmp/ ‘/oracle/tmp/ /tmp/ '); );
Définition fichie rs de co nfigurant la Définition des des fichiers fichiers fichiers de log log àà analyser analyser en en configurant configurant configurant la vue vue vue V$LOGMNR_CONTENTS V$LOGMNR_CO NTENTS le s fichiers V$LOGMNR_CONTENTS V$LOGMNR_CONT ENTS (spécifie (spécifie les les fichiers log log àà analyser) analyser)
Initialisation Initialisation d’une d’une nouvelle nouvelle liste liste et et définition du du premier premier fichier fichi er de log à analyser analys er fichier fich ier de log à analys analyser er
EXECUTE EXECUTE DBMS_LOGMNR.ADD_LOGFILE(‘/Disk1/Redo10.log', DBMS_LOGMNR.ADD_LOGFILE(‘/Disk1/Redo10.log', DBMS_LOGMNR.NEW); DBMS_LOGMNR.NEW);
Ajout d’a utres fichie ffichi ichiers rs àà analyser analys er àà la list e :: Ajout d’autres d’autres d’autres fichiers ers analyser analy ser la liste liste liste
EXECUTE EXECUTE DBMS_LOGMNR.ADD_LOGFILE(‘/Disk1/Redo20.log', DBMS_LOGMNR.ADD_LOGFILE(‘/Disk1/Redo20.log', DBMS_LOGMNR.ADDFILE); DBMS_LOGMNR.ADDFILE);
Suppression Suppression de de fichiers fichiers log log de de la la liste liste ::
EXECUTE EXECUTE DBMS_LOGMNR.ADD_LOGFILE( DBMS_LOGMNR.ADD_LOGFILE(‘/disk1/Redo20.log',DBMS_LOGMNR.REMOVEFILE); ‘/disk1/Redo20.log',DBMS_LOGMNR.REMOVEFILE);
D.BOUZIDI
80
Utiliser LogMiner
Lancement Lancement de de l’analyse l’analyse
Initialisa Initi alisation tion d’une logMi Initialis Initi alisation ation d’une session session logMiner logMiner logMiner ner ::
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>‘/tmp/monDictionnaire.ora'); p/monDictionnaire.ora'); EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>‘/tm DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>‘/tmp/monDictionnaire.ora'); DBMS_LOGMNR.START_LOGMNR(DICTFILENA ME=>‘/tmp/monDictionnaire.ora');
Initialisation ssession ession logMiner Initialisation d’une d’une session logMiner pour logMiner pour une une période période données: données:
EXECUTE Dbms_Logmnr.Start_Logmnr(‘/oracle/temp/monDictionnaire.ora', tionnaire.ora', EXECUTE Dbms_Logmnr.Start_Logmnr(‘/oracle/temp/monDic Dbms_Logmnr.Start_Logmnr(‘/oracle/temp/monDictionnaire.ora', Dbms_Logmnr.Start_Logmnr(‘/oracle/te mp/monDictionnaire.ora', starttime =>to_date('01-Jan-2006 00:00:00','DD-MON-YYYY starttime =>to_date('01-Jan-2006 00:00:00','DD-MON-YYYY HH:MI:SS'),endtime HH:MI:SS'),endtime => => to_date('01-Jan-2006 to_date('01-Jan-2006 10:00:00', 10:00:00', 'DD-MON-YYYY 'DD-MON-YYYY HH:MI:SS')); HH:MI:SS'));
Identification ddes modific ation apportée s àà une Identification des des es modification modifica modification tion apportées apportées une table table SELECT ttimesta imestamp, mp, usern ame, sql_red sq l_redo oo FROM v$l ogmnr_conte contents nts WHERE SELECT timesta t imestamp, mp, username, username, username, sql_redo sql_red FROM v$logmnr_ v$logmnr_con v$logmn r_contents tents WHERE seg_na seg _name me 'E MP'; ; seg_na seg _name me == 'EMP' 'EMP'; 'EMP ';
Arrêt Arrêt de de la la session session logMiner logMiner
EXECUTE EXECUTE DBMS_LOGMNR.END_LOGMNR; DBMS_LOGMNR.END_LOGMNR;
81
D.BOUZIDI
Obtenir Obtenir des des informations informations sur sur les les fichier fich iers s de jjourna ournal nalisa isatio tion n analy alyser ser fichie fic hiers rs de jour de jou rnalisa lisatio tion n àà an analy an alyser ser IlIl existe existe 44 vues vues dynamiques dynamiques liées liées àà notre notre session session LogMiner LogMiner (celles-ci (celles-ci n'existent n'existent que que pendant pendant notre notre session session LogMiner) LogMiner)
V$LOGMNR_DICTIONARY V$LOGMNR_DICT IONARY V$LOGMNR_DICTIONARY V$LOGMNR_DICTION ARY :: contient contient les les infos infos sur sur le le dictionnaire de données de LogMiner. dictionnaire de données de LogMiner. V$LOGMNR_PARAMETERS V$LOGMNR_PARAMETERS :: contient contient les les infos infos concernant concernant la session se ssion LogMiner en cours. c ours. la session session LogMiner LogMiner en cours. cours. V$LOGMNR_CONTENTS V$LOGMNR_CONTENTS :: contient contient les les infos infos issues issues des des fichiers fichiers REDO REDO LOG. LOG. V$LOGMNR_LOGS V$LOGMNR_L OGS :: contient V$LOGMNR_LOGS contient les infos sur tous les les fichiers fichiers REDO qu sess REDO LOG LOG qui quii ont qui ont été été ajoutés ajoutés pour pour cette cette session cette session session ion
D.BOUZIDI
82
Gestion des structures de stockages
Hiérarchie de stockage de la base de données Base de données Tablespace
Logique
Segment
Fichier de données Ph ysique
Extent
Bloc Oracle
Bloc OS
Hiérarchie de stockage de la base de données
Une Une base base données données est est composée composée d’un d’un ensemble ensemble d’unités TABLESPACE.. d’unités logiques logiques appelées appelées TABLESPACE Un TABLE SPACE ccomposé omposé u Un TABLESPACE TABLES TABLESPACE PACE est est composé co mposé d'au d'au moins moins un un un n DATAFILE d e données donnée DATAFILE,, c'est c'est àà dire dire un un fichier fichier de fichier de donnéess qui données qui est est physiquement physiqueme nt présent sur le serveur ser veur à l'endroit stipulé stipu lé physiquement physiqueme nt présent sur le serveur serveur à l'endroit stipulé lors lors de de sa sa création. création. Chaque co nstitué SEGMENTS Chaque DATAFILE DATAFILE est DATAFILE est constitué est con constitué stitué de de SEGMENTS Un EXTENT (ou Un segment segment est est d'au d'au moins moins un un EXTENT (ou page) page) L'extent L'ex tent gro BLOCS contigus L'extent L'ex tent est est un un groupe groupe groupe upe de de BLOCS contigus pouvant pouvant accueillir de données , ilil est constitu é d'au accueillir des dess données, des données, est constitué est constitué d'au moins moins 33 blocs blocs Le Le bloc bloc est est le le plus plus petit petit élément élément de de stockage stockage d'une d'une base de données base de données
Les tablespaces
Une Une base base données données mémorisée mémorisée dans dans une une ou ou plusieurs plusieurs unités unités logiques TABLESPACE.. logiques appelées appelées TABLESPACE
Cette org anisation perm ett àà l’administrateur Cette organisation orga organisation nisation permet perme permet l’administrateur de de :: Contrôler Contrôler l’allocation l’allocation d’espace d’espace disque disque Assigner Assigner des des quotas quotas de de ressource ressource disque disque aux aux utilisateurs utilisateurs Contrôler donnée Contrôler la la disponibilité disponibilité des des données donnéess en données en rendant rendant les les tablespac table spaces es onlin ee ou offl ine tablespac table spaces es online online onlin ou offline offline offli ne Constituer Constituer des des unités unités de de sauvegarde sauvegarde ou ou de de restauration restauration partielle partielle de de la la base base Répartir Répartir les les zones zones de de stockage stockage entre entre plusieurs plusieurs disques disques pour pour accroître accroître les les performances performances
Quelques types de tablespaces t ablespaces
Tabl Tablespa espace cee SYSTE SYSTEM M Tablespac Table space SYSTEM SYSTE M::
Créé Créé lors lors de de la la création création de création de la la base base de de données données (avec (avec l’ordre (avec l’ordre CREATE CREATE DATABASE) DATABASE) Contient : les tables du dictionnaire de données, les procédures, les fonctions, Contient : les tables du dictionnaire de données, les procédures, les fonctions, les les packages, packages,les lestriggers triggers
Tabl Tablespa espace cee TEMP Tablespac Table space TEMP ::
Contient Contient :: les les segments segments temporaires temporaires utilisés utilisés par par Oracle Oracle lors lors d’opérations d’opérations de de tri tri (SORT_AREA_SIZE (SORT_AREA_SIZE insuffisante), insuffisante), de de création création d’index d’index et et de de tables tables temporaires,… temporaires,… Chaque co mporter un tabl espace temporaire affe cté aux Chaque BD BD doit doit comporter comporter un tablespace tablespace temporaire affecté affecté affecté aux utilisateurs utilisateurscomme comme tablespace ttablespace ablespace dési gné tablespace tabl espacetemporaire temporaire tempo raire(si (si aucun aucun tablespace tables pacetemporaire temporaire tempo rairen'est n'est désigné désig désigné né lors lors de de la la création création du du compte, compte,Oracle Oracle affecte affecte ce affecte cetablespace tablespace àà l'utilisateur l'utilisateur
CREATE CREATE DATABASE DATABASE ... ...
On tabl uutilisateur tilisateur On peut peut désigner désigner un un tablespace tablespace tablespace espace temporaire temporaire pour pour un un utilisateur utili sateur donné donné ALTER ALTER USER USER scott scott
TEMPORARY TEMPORARY TABLESPACE TABLESPACE temp01; temp01;
Tabl Tablespa espace cee USER USERS S :: Tablespac Table space USERS
DEFAULT DEFAULT TEMPORARY TEMPORARY TABLESPACE TABLESPACE temp01; temp01;
Contient Contient :: les les segments segments de de données données utilisateurs utilisateurs (tables,clusters, (tables,clusters, index,…) index,…) On On peut peut définir définir #ts #ts tablespaces tablespaces USERS USERS pour pour séparer séparer les les différentes différentes applications applications
Tabl Tablespa espace cee UNDO Tablespac Table space UNDO ::
Réservé de s commandes Réservé àà l'annulation l'annulation des des commandes DDL DDL (UPDATE, (UPDATE, INSERT, (UPDATE, INSERT,etc...). etc...). Lors etc...). Lorsd’une d’une suppression exemple , ORACLE suppression par par exemple, exemple, ORACLEcopie copieles leslignes lignesàà supprimer supprimer dans dans le le tablespace tablespace UNDO UNDO et et ensuite ensuite indique indique que que les les blocs blocs contenant contenant les les données données dans dans le le tablespace tablespace d'origine undo_management d'origine sont sontlibres libres(le (leparamètre paramètre undo_tablespace, undo_tablespace, undo_management) undo_management)) undo_management)
CREATE CREATE UNDO UNDO TABLESPACE TABLESPACE undotbs undotbs DATAFILE '\oradata\orcl\undotbs.dbf' '\oradata\orcl\undotb s.dbf' size DATAFILE '\oradata\orcl\undotbs.dbf' size 100M; 100M;
Création de tablespaces CREATE CREATE TABLESPACE TABLESPACE app_data app_data DATAFILE DATAFILE '/DISK1/app_data_01.dbf '/DISK1/app_data_01.dbf‘‘ SIZE SIZE 100M, 100M, '/DISK2/a '/DIS K2/app pp data_ 02.dbf 02.d bf SIZE ‘ '/DISK2/ '/DI SK2/app app data data_ _ 02.db 02.dbf f ‘ SIZE 100M 100M DEFAULT DEFAULT STORAGE STORAGE (( INITIAL INITIAL NEXT NEXT MAXEXTENTS MAXEXTENT SS MAXEXTENTS MAXEXTENT PPCCTTIINNCCRREEAASSEE
500K 500K 500K 500K 500 500 00 ));;
oo INITIAL prem ier exten t (Par Définit la la taille taille du du premier premi premier er extent extent (Par défaut défaut défaut INITIAL :: Définit 5*DB_BLOCK_SIZE) 5*DB_BLOCK_SIZE) oo NEXT rapporte orte ttaille e de dde e l'ext ll'ex 'extent ent suiv suivant. ant. se rapp rapporte rapp orte àà la la taill taille aille l'extent tent suivant. suiv ant. NEXT se oo MINEXTEN créatio MINEXTENTS TSS exprime exprime le le nombre le nombre d'extents d'extents alloués d'extents alloués lors lors de lors de la la création la créationn création MINEXTENTS MINEXTENT du ssegmen egment ntt (Par ((Par Par ddéfaul éfault ltt 1). du segme se gment (P ar défau dé fault 1). oo MAXEXTEB maxi mal d’ex tents êtr e allouées alloué es àà MAXEXTEBTS TSS est est le le nombre nombre maximal nombre maxim maximal al d’extents d’exte d’extents nts pouvant pouvant être être être allouées allouées MAXEXTEBTS MAXEXTEBT un segment un segment oo PCTINCREASE définie le le pourcentage pourcentage de de croissance croissance de de la la taille taille du du PCTINCREASE définie segment suivant segment suivant
Gestion de l’espace dans les tablespaces AA la exempl la création création d'un d'un objet objet (par (par exemple exemplee une exemple une table), table), ORACLE ORACLE crée crée un un extent extent .. Lors de remplissage (ajout/modification), Oracle rempli les blocs de Lors de remplissage (ajout/modification), Oracle rempli les blocs de données données qui const tt l'extent jusq u'à rempl ir l'exten entièr ement qui constituen constituent constituen ituent l'extent jusqu'à jusqu'à jusq u'à remplir remplir rempl ir l'extent l'extentt entièrement l'extent entièrement entièr ement et et crée crée un un nouvel pr écédent est nouvel extent extent si si le le précédent précédent est plein plein Deux Deux modes modes de de gestion gestion de de l’espace l’espace ::
Tablespaces dicti onnaire don nées: Tablespaces gérés gérés par par le le dictionnaire dictio dictionnaire nnaire de de données donn données ées::: ORACLE stocke les informat informations ions relatives à l'allocation d'espa ce dans ORACLE stocke les informations informations relatives à l'allocation d'espace d'espace d'espace dans le dictionnaire de données le dictionnaire de données Ceci supplémentair e pour Ceci induit induit une une charge charge supplémentaire supplémentaire supplémentai re pour toutes toutes les les opérations opérations sur 'un tablespace. L’administra teur doit sur les les objets objets dd'un tablespace. L’administrateur L’administrateur L’administrateur doit bien bien dimensionner taill e des fragm dimensionner la la taille taille taille des extents extents pour pour éviter éviter une une fragmentation fragmentation fragmentation entation excessive excessive Tablespac Table spaces es gérés éss localem loc alement ent (l ocally ly man managed aged Tablespac Table spaces es gér gérés géré loca localeme lement nt (local (loc (locally ally manage man aged d )) :: Les Les informations informations sont sont stockées stockées au au niveaux niveaux des des entêtes entêtes des des tablespaces, tablespaces, ceci ceci évite évite des des accès accès intempestifs intempestifs au au dictionnaire de données vversion données (option (option définit définit par pardéfaut défaut àà partir partir de de la la version version ersion 9i) 9i) Deux modes mod es de gestion des exten extents ts : Deux modes modes de gestion des extents extents :
Le impos ee àà Oracle Oracl ee de ex taill Le mode mode UNIFORM UNIFORM impose impose impos Oracle Oracl de créer créer des des extents extents extents tentsde de taille taillee taille
identique. identique. Ce Ce mode moden’est n’est pas pasutilisé utilisé pour pour un untablespace tablespace d’annulation d’annulation Le mode AUTOALLOCATE demande de créer des de s extents Le mode AUTOALLOCATE demande de créer des extents de de plus plus en en plus plus grands grands avec avec le lenombre nombre d'extents d'extentscréés. créés. Ce Ce mode modene ne peut peut être êtreutilisé utilisé pour utilisé pour un table space temp oraire e un tablespace tablespace table spacetemporair temporaire tempo raire
Gestion de l’espace dans les tablespaces CREATE CREATE TABLESPACE TABLESPACE user_data user_data DATAFILE '/DISK2/user_data_01.dbf' DATAFILE '/DISK2/user_data_01.dbf' SIZE SIZE 500M 500M EXTENT EXTENT MANAGEMENT MANAGEMENT LOCAL LOCAL UNIFORM UNIFORM SIZE SIZE 10M; 10M;
Création Cré ation tables pace temp oraire re loca lement nt Création Créa tion de de tablespace tablespa tablespace ce temporai temporai temp oraire re géré géré localeme localeme loca lement nt CREATE CREATE TEMPORARY TEMPORARY TABLESPACE TABLESPACE temp temp TEMPFILE '/DISK2/temp_01.dbf' '/DISK2/temp _01.dbf' TEMPFILE '/DISK2/temp '/DISK2/temp_01.dbf' _01.dbf' SIZE SIZE 500M 500M EXTENT MANAGEMENT LOCAL EXTENT MANAGEMENT LOCAL UNIFORM UNIFORM SIZE SIZE 10M; 10M;
Modification Modification des des paramètres paramètres de de stockage stockage ALTER TABL ESPACE ALTER TABLESPACE TABLESPACE TABLESPACE MINIMUM EEXTENT MINIMUM EXTENT EXTENT XTENT
app_data app_ data app_data app_data 2M; 2M;
On On peut peut modifier modifier un un ensemble ensemble de de paramètres paramètres ALTER TABL ESPACE app_data app_ data ALTER TABLESPACE TABLESPACE TABLESPACE app_data app_data DEFAULT STORAGE ( DEFAULT STORAGE ( IINNIITTIIAALL 22MM NEXT NEXT 2M 2M MAXEXTENTS MAXEXTENT S 999 MAXEXTENTS 999 ); );
Statut d’un tablespace
Tablespace dispo acc do Tablespace "offline" "offline" non non disponible disponible disponible nible pour pour accéder accéder accéder éder aux aux données données données nnées Certains tab lespaces doivent Certains tablespaces tablespaces doivent être être "online" être "online" ::
SYSTEM, SYSTEM, tablespaces tablespaces contenant contenant des des segments segments d'annulation d'annulation actifs. actifs.
Pour m u n tablespace tablespac e "offline" Pour mettre mettre mettre ettre un un tablespace "offline" :: ALTER TAB LESPACE app _data ALTER TABLESPACE TABL TABLESPACE ESPACE app_data app_data app_ data OFFLINE; OFFLINE;
Pour m u n tablespace tablespac e "online" Pour mettre mettre mettre ettre un un tablespace "online" :: ALTER TABL ESPACE app_data app_ data ONLINE; ALTER TABLESPACE TABLESPACE TABLESPACE app_data app_data ONLINE;
Tablespace pou opér ations de lec Tablespace disponible disponible pour pourr des pour des opérations opérations opérations de lecture lecture lecture ture (Impossible de supprimer les objets du tablespace) (Impossible de supprimer les objets du tablespace) Pour crée r un se ule lecte urr non Pour créer créer un tablespace un tablespace en en lecture lecture seule seu seule le sur sur un un lecteur lecteu lecteur non réinscriptible réinscriptible :: ALTER ALTER TABLESPACE…READ TABLESPACE…READ ONLY; ONLY;
Placez Placez le le fichier fichier de de données données sur sur le le lecteur lecteur non non réinscriptible réinscriptible ALTER ALTER TABLESPACE…RENAME TABLESPACE…RENAME DATAFILE…; DATAFILE…;
Manipulation des tablespaces
Suppression Suppression ::
Tablespace Tablespace supprimé supprimé du du dictionnaire dictionnaire de dictionnaire de données. données. Eventuellement, cont enu supprimé dde e données. Eventuellement, contenu contenu supprimé du supprimé du dictionnaire dictionnaire de dictionnaire données. Fichiers Fichiers de de système système d'exploitation d'exploitation non non supprimés. supprimés. DROP TAB ap p_data INCLUDING DATAFIL ES; DROP TABLESPACE TABLESPACE TABLESPACE LESPACE app_data app_data app_data INCLUDING CONTENTS CONTENTS AND AND DATAFILES; DATAFILES; DATAFILES;
Redimensi Redi mensionner onner tables pace Redimension Redim ensionner ner un un tablespace tablespa tablespace ce :: Modification Modification de de la la taille taille d'un d'un fichier fichier de de données données automatique automatique
ALTER TABL ESPACE app_data app_ data ADD '/DISK3/app _data_04.dbf' ' ALTER TABLESPACE TABLESPACE TABLESPACE app_data app_data ADDDATAFILE DATAFILE '/DISK3/app_data_04.dbf '/DISK3/app_data_04.dbf' '/DISK3/app_ data_04.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M; SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Modification Modification Manuellement Manuellement ALTER DAT ALTER DATABASE DATABASE DATABASE ABASE DATAFILE DATAFILE '/DISK5/app_data_02.dbf' '/DISK5/app_data_02.dbf' RESIZE RESIZE 200M; 200M;
Ajout Ajout d’un d’un datafile datafile ALTER TAB app ALTER TABLESPACE TABLESPACE TABLESPACE LESPACE app_data app_data app_data _data ADD DATAFILE '/DISK5/app_ ' /DISK5/app_data_03.dbf' data_03.dbf' SIZE ADD DATAFILE '/DISK5/app '/DISK5/app_data_03.dbf' _data_03.dbf' SIZE 200M; 200M;
Déplacement Déplacement des des datafiles datafiles
ALTER TABL ESPACE app_data app_ data RENAME DA TAFILE '/DI SK3/app_data_01.dbf' _01.dbf' ALTER TABLESPACE TABLESPACE TABLESPACE app_data app_data RENAME DATAFILE DAT DATAFILE AFILE '/DISK3/app_data '/DISK3/app_data_ '/DIS K3/app_data_01.dbf' 01.dbf' TO '/DISK5/app_data_01. '/DISK5/a pp_data_01.dbf'; dbf'; TO '/DISK5/ap '/DISK5/app_data_01.d p_data_01.dbf'; bf';
Types de segment On On distingue distingue cinq cinq types types de de segments segments :: 1. 1. Les Les segments segments de de données données
Ils utilis ateurs Ils servent servent àà stocker stocker les les données données des des tables tables utilisateurs utilisateurs et système et système Chaque Chaque table table aa un un et et un un seul segment qui est est crée crée automatiquement le. automatiquement lors lors de de la la création création de de la la tab table.
2. 2. Les Les segments segments d’index d’index
Servent donnée s d’index. Servent àà stocker stocker les les données données d’index. Ces Ces données données peuvent peuvent peuvent donc être stockées dans un tablespace distinct donc être stockées stockées dans un tablespace distinct des des données données données des des tables. tables. Un ccréé réé automatiquement Un segment segment d’index d’index est d’index est créé automatiquement lors lors de de la la création de l’index. On peut préciser lors de la création ’un création de l’index. On peut préciser lors de la créationdd’un index, index , le tablespace tables pace dans lequel le quel sera se ra créé le segment. segme nt. index, le tables tablespace pace dans lequel lequel sera sera créé le segment. segment.
93
D.BOUZIDI
Types de segment On On distingue distingue cinq cinq types types de de segments segments :: 3. 3. Les Les segments segments temporaires temporaires
Utilisés Utilisés par par Oracle Oracle pour pour le le traitement traitement des des requêtes requêtes SQL SQL nécessitant un espace disque temporaire. nécessitant un espace disque temporaire. Les Les segments segments temporaires temporaires sont sont créés créés en en cas cas de de besoin besoin et et supprimés supprimés après après l’exécution l’exécution de de la la commande. commande. Le ccrées rées ces Le tablespace tablespace dans dans lequel lequel sont sont crées ces segments segments est segments est défini défini lors lors de de la la création création et et modification modification d’un d’un utilisateur. utilisateur.Si Sice ce tablespace table space défi ni,i, alors c’es SYST EM tablespace table space n’est n’est pas pas défini, défin défini, alors c’est c’estt le c’est le tablespace tablespace SYSTEM SYSTEM SYSTE M qui es t utilisé utilis éé par défaut . qui est est utilisé utilis par défaut. défaut. défaut.
4. 4. Le Le segment segment d’amorçage d’amorçage (BOOTSTRAP) (BOOTSTRAP)
Ce segme ntt est table SYSTE M. contie Ce segment segmen segment est créé créé dans dans le le tablespace tablespace tablespace space SYSTEM. SYSTEM. SYSTE M. IlIl contient contient contient nt les les définitions définitions des des objets objets du du dictionnaire dictionnaire de de données données qui qui sont sont chargées chargées lors lors de de l’ouverture l’ouverture de de la la base base
5. 5. Les segments segments d’annulation d’annulation (ROLLBACK) (ROLLBACK)
D.BOUZIDI
Ces Ces segments segments (rollback (rollback segments) segments) contiennent contiennent les les données données avant avant modification modification due due àà une une transaction. transaction. Ils transaction. Ils permettent permettent d’annuler d’annuler leur leur effet effet en en cas cas de de besoin. besoin. 94
Notion d’ext Notion d ’exten entt et de bl oc d’ex ’exten tent de bloc bloc
Notion Notion d’exte d’extents nts :
Une ccontigu logiqu Une extent extent est est un un ensemble ensemble contigu contigu ontigu de de blocs blocs logiques logiques logiques es alloués alloués àà un segm s egment ent. . un segm segmen ent. t. Tout segm ent ccréé ex tent initiale Tout segment segme segment nt est est initialement initialement créé créé réé avec avec un un extent extent extent initiale (initial extent). (initial extent). Allocation Allocation lorsque lorsque le le segment segment est est créé, créé, étendu étendu ou ou modifié. modifié. Libération Libération lorsque lorsque le le segment segment est est supprimé, supprimé, modifié, modifié, tronqué, tronqué,
Notion de bloc :
Unité minim um d'ent rée/sor sortie tie Unité minimum minimum minim um d'entrée/ d'entrée/ d'ent rée/sort sortie ie Constitué bblocs locs systè Constitué d'un d'un ou ou de de plusieurs plusieurs blocs plusieurs blo cs de de système système système me d'exploitation d'exploitation Défini Défini par par DB_BLOCK_SIZE DB_BLOCK_SIZE Défini Défini lors lors de de la la création création de de la la base base de de données données 95
D.BOUZIDI
Contenu d'un bloc de base de données
En-tête (@ du bloc, Entype de segment,… segment,…) Espace libre : utilisé utilisé pour l’insertion de nouvelles lignes ou mise à jours né nécessitant d’espace sup. Données : lorsqu’ Donné lorsqu’une ligne ne peut tenir sur un seul bloc elle est stocké stockée dans deux ou +iers + iers Blocs (blocs (blocs cha î nés)
D.BOUZIDI
96
Paramètres d'utilisation de l'espace de bloc INITRANS La gestion gestion de de l’espace l’espace libre libre d’un d’un bloc bloc logique logique MAXTRANS La
PCTFREE
PCTUSED
se se fait fait en en fonction fonction des des valeurs valeurs des des paramètres PCTUSED. paramètres PCTFREE PCTFREE et PCTFREE et PCTUSED PCTUSED.. PCTUSED. PCTFREE PCTFREE fixe fixe le le % %d’espace d’espace du d’espace du bloc blocqui qui doit doit être être maintenu maintenu constamment constamment libre. libre. Sa Sa valeur valeur par par défaut défaut est est 10%. 10%. Quand peu utilis Quand un un bloc bloc ne ne peut peutt être peut être utilisé utiliséé car utilisé car la la valeur valeurPCTFREE PCTFREE PCTFRE E de de lui lui interdit, interdit,on on ne ne pourra pourra recommencer recommenceràà le le remplir remplir que quesi si l’espace desce nd en l’espace occupé occupé est est en en % % descend descend en dessous PCTUSED. . Sa dessous de de la la valeur valeur PCTUSED PCTUSED. Sa valeur valeur par défaut est 40%. par défaut est 40%.
97
D.BOUZIDI
Utilisation de l'espace de bloc PCTFREE=20 PCTUSED=40 80 % Insertions
Insertions 1
2
80 % 40 % Insertions 3
D.BOUZIDI
Insertions 3
98
Vues du dictionnaire de données
Informations Informa tions tablesp ace Informations Informat ions de de tablespace tablespace tablesp ace ::
DBA_TABLESPACES DBA_TABLESPACES V$TABLESPACE V$TABLESPACE
DBA_TABLESPACE_USAGE_METRICS DBA_TABLESPACE_USAGE_METRICS
Informations Informations de de fichier fichier de de données données ::
DBA_DATA_FILES DBA_DATA_FILES DBA_TABLES DBA_TABLES
V$DATAFILE V$DATAFILE
Informations Informations de de fichier fichier temporaire temporaire ::
Informations Informations sur sur les les segments segments ::
DBA_TEMP_FILES DBA_TEMP_FILES V$TEMPFILE V$TEMPFILE DBA_SEGMENTS DBA_SEGMENTS
Informations Informa tions ext Informations Informat ions sur sur les les extents extents extents ents ::
D.BOUZIDI
DBA_EXTENTS DBA_EXTE NTS DBA_EXTENTS DBA_EXTE NTS (Extents (Extents utilisés) utilisés) DBA_FREE_SPACE DBA_FREE _SPACE DBA_FREE_SPACE DBA_FREE_SPA CE (Extents (Extents libres) libres) 99
Administration des utilisateurs
Compte utilisateur
Pour Pour créer créer un un utilisateur, utilisateur, l'administrateur l'administrateur doit doit affecter affecter un un profil, profil, choisir choisir une une technique technique d'authentification d'authentification et et affecter aff ecter des des tablespaces tablespaces
Un uniq caract Un nom nom utilisateur utilisateur unique unique unique ue ou ou login login (< (< 30 30 caractères, caractères, pas pas de de caractères caractères caractères ères spéciaux spéciaux et et doit doit commencer commencer par par une une lettre) lettre) Une Une méthode méthode d’authentification d’authentification :: Autre Autre l’authentification l’authentification par par mot mot de de passe ORACLE fait l’authentification par certificats, par biométrie passe ORACLE fait l’authentification par certificats, par biométrie et et par par système système tier tier Un tablespa cee par l'utilisat eur Un tablespace tablespac tablespace par défaut défaut:: emplacement emplacement par par défaut défautoù où l'utilisateur l'utilisateur l'utilisa teur mettra n'indiqu tabl espace mettra ses ses objets objets s'il s'il n'indique n'indiquee pas n'indique pas d'autre d'autre tablespace tablespa tablespace ce (il (il faut faut lui lui accorder accorder les les privilèges privilèges et et les les quotas quotas nécessaires nécessaires pour pour qu’il qu’il puisse puisse créer créer des objets) des objets) Un l'ut ilisateur Un tablespace tablespace temporaire temporaire :: emplacement emplacement dans emplacement dans lequel lequel l'utilisateur l'utili l'utilisateur sateur peut peut créer créer des des objets objets temporaires, temporaires, tels tels que que des des tris tris et et des des tables tables temporaires temporaires Un Un profil profil utilisateur utilisateur :: c’est c’est l’ensemble l’ensemblede de restrictions restrictions de de ressources ressources concernant concernant
l’utilisation l’utilisation de de la la base base de de données données et le mot de passe affecté affe cté et le mot de passe affecté affecté àà l'utilisateur l'utilisateur (longueur, (longueur, période (longueur, période d’expiration, d’expiration, …) d’expiration, …)
101
D.BOUZIDI
Profil utilisateur
Le Le contrôle contrôle de de l’utilisation l’utilisation de de la la BD BD ::
CPU exprim é par sess ion ou CPU :: exprimé exprimé par session session session ou par par appel appel appel
Network/Memory Network/Memory :: une une session session utilisateur utilisateur consomme utilisateur consomme des des ressources ressources réseau réseau et et mémoire, mémoire, on on peut peut donc donc gérer gérer ::
CPU/Session pou r une CPU/Session (exprimé (exprimé en (exprimé en centièmes centièmes de de secondes) secondes) :: pour pour une valeur valeur égale utilisateu r, qui égale àà 1000, 1000, un un utilisateur, utilisateur, utilisateur, qui consomme consomme plus plus de de 10 10 secondes secondes de de temps temps de de CPU, CPU, sera sera déconnecté. déconnecté. CPU/Call global utilisateu CPU/Call :: au au lieu lieu de de limiter limiter la la session session globale globalee d’un globale d’un utilisateur, utilisateur, utilisateur, r, on on empêche empêche que que la la commande commande qui qui consomme consomme plus plus des des ressources ressources CPU CPU de l’utilisateu r de l’utilisateur l’utilisateur l’utilisat eur
Nombre Nombre de de minutes minutes pendant pendant lesquelles lesquelles un un utilisateur peut être connecté connect é avant avan t d'être automatiq automa tiqueme uement nt déconnect (Conn ectt Time). Time) .. connecté connect é avan avantt d'être automatiq automatiquemen uementt déconnecté déconnectéé (Connect déconnecté (Connect (Connec Time). Time) Nombre Nombre de de minutes minutes pendant pendant lesquelles lesquelles une une session utilisateur utilisateurr peut utilisateu peut rester aut omatiquement d éconnectée (Idle rester inactive inactive avant avant d'être d'être automatiquement automatiquement automatiquem ent déconnectée déconnectée (Idle Time). Time). Nombre simult anées pouvant êtr Nombre de de sessions sessions simultanées simultanées simultanées pouvant être êtree créées être créées àà l'aide l'aide d'un d'un compte utilisateur de base de données. compte utilisateur de base de données. Private Priva te limi qquanti ité d'espa ce consommé cons omméé dans mémoi re Private Priva te SGA SGA :: limite limite limite te la la quant quantité uantité té d'espace d'espace d'esp ace consommé consomm dans la la mémoire la mémoire mémoire pour pour le le tri, tri, création création d’index, d’index, etc. etc.
Disk limit e la qu antité é de donn qu 'un utilisa uti lisateur teur peut peu t Disk I/O I/O :: limite limite limite la quantit quantité quanti té de données données données ées qu'un qu'un qu'un utilisateur utili sateur peut lire, lire, par par session session ou ou par par appel. appel.
D.BOUZIDI
102
Profil utilisateur
Contrôle Contrôle de de l’authentification l’authentification ::
L’authenti L’aut hentificat fication ion l’ide ntité entit é qui L’authentif L’auth entificati ication on permet permet de de vérifier vérifier véri fier l’identité l’iden l’identité tité d’une d’une entité entité entité qui souhaite souhaite utiliser utiliser les les ressources ressources de de la la base base de de données données ::
Ce Ce mécanisme mécanisme permet permet d’établir d’établir une une relation relation de confiance confiance pour pour les les interactions interactionsultérieures. ultérieures. La responsabi lité :: permettant permett ant lilier aactions ctions La responsabilité responsabilité responsabilité permetta permettant nt de de lier lier er l’accès l’accès et et des des actions act ions àà des des entités entités spécifiques. spécifiques.
Trois Trois technique technique d’authentification: d’authentification:
Password (Authentificati on par Password (Authentification (Authentification (Authentificat ion par la la base base de de données) données) :: crée crée chaque chaque utilisateur as sociéé qui utilisateur avec avec un un mot mot de de passe passe associé associ associé qui doit doitêtre être fourni fourni lorsde lorsde la la connexion ((ex: ex: CREATE ttiger;) iger;) connexion (ex: CREATE USER CREATE USER scott scottIDENTIFIED IDENTIFIED by by tiger;) External (Authentificat ion d’ex ploitation) External (Authentification (Authentification (Authentificati on par par le le système système d’exploitation) d’exploit d’exploitation) ation) :: ce ce mode mode l’authentification repose sur celle définie par l’OS. Aucun mot l’authentification l’authentificat ion repose sur celle définie par l’OS. Aucun mot de de passe ddonnées onnées n'est passe de de base base de de données de n'est utilisé utilisé pour pour ce ce type type de de connexion. connexion. (ex: (ex:
))
CREATE OPS$N omUser IDENTIFIED EXTE CREATE USER USER OPS$NomUser USER OPS$NomUser IDENTIFIED EXTERNALLY; EXTERNALLY; EXTERNALLY; RNALLY; Global (Authentificat Global (Authentification (Authentification (Authentification ion globale) globale) :: permet pe rmet de de renforcer renforcer l’authentification l’authentification l’authentificat ion via via d’autre d’autre systèmes systèmes comme comme l'identification l'identification des des utilisateurs utilisateurs via via la la biométrie, biométrie, les les certificats certificats x509, x509, les les systèmes systèmes tiers tiers et et Oracle authentificat ion Oracle Internet Internet Directory. Directory. Avantage Avantage fait fait une une seul seul authentification authentification
Lorsqu'un utilisa teur verroui lléé ou Lorsqu'un utilisateur utilisat utilisateur eur est est créé, créé, son son statut statut peut peut être être verrouillé verrouill verrouillé ou déverrouillé. uti lisateur verrouill é, ne déverrouillé. Si Si un un compte compte utilisateur utili utilisateur sateur verrouillé, verrouillé, ne peut peut être être utilisé utilisé pour utilisé pour se connecter connect er à la base de données. don nées. se connecter connecter à la base de de données. 103 D.BOUZIDI Les Les comptes comptes des des nouveaux nouveauxutilisateurs utilisateurs sont utilisateurs sont verrouillés ve rrouilléspar pardéfaut défaut
Tables Tab lespac pace e :: Tables Tab lespace pace
Tabl Tablespa espaces ces schémas mas Table Ta blespa spaces ces et et de sché schémas mas
Un tablespa cee par tabl espace dans Un tablespace tablespac tablespace par défaut défaut est est le le tablespace tablespace tablespace dans lequel lequelles les objets objets sont sont créés si aucun a ucun tablespace tabl espace n'est désigné lors de la création créati on de ll'objet 'objet créés si aucun aucun tablespace tablespace n'est désigné lors de la création de l'objet
Si ppas as choisi leletablespace Si on on aa pas choisi de detablespace tablespace par tablespace par défaut, défaut, le tablespace permanent permanent par par défaut défaut défaut défini défini par parle lesystème systèmeest est utilisé utilisé Si ce lui défini es t Si on on n’a n’a pas pasindiqué indiqué de de tablespace tablespace temporaire, temporaire, celui celui celui défini par par le le système système est est utilisé utilisé
CREATE CREAT E USER ide ed PWDuser 01 CREATE USER user01 USER user01 identifi identified identifi ntified ed by by PWDuser01 PWDuser PWDuser01 01 DEFAULT TABLESPACE tbs_users TEMPORARY TABLESPACE TABL ESPACE tmp_users; tm p_users; DEFAULT TABLESPACE TABLESPACE tbs_users TEMPORARY TABLESPACE TABLESPACE tmp_users;
Schéma Schéma ::
L'ensemble L'ensemble des desobjets objets appartenant appartenantààun un utilisateur utilisateurest est appelé est appelé schéma schéma (Tables, (Tables, (Tables, Déclencheurs, Déclencheurs, Index, Index, Vues, Vues, Séquences, Séquences, Types Types de de données donnéesdéfinis définispar parl'utilisateur, l'utilisateur, …) …) Lors Lors de de la la création création d'un d'un utilisateur utilisateur de de base base de de données, données, un un schéma schéma correspondant correspondant portant no m est portant le le même même nom nom est créé créépour pour cet cet utilisateur utilisateur Les rési derr dans Les objets objets du du même même schéma schéma peuvent peuvent résider réside résider dans différents différents tablespaces tablespaces et et un un tablespace obje tss de sché mas tablespace peut peut contenir contenir des des objets objet objets de différents différents schémas schém schémas as Un uti lisateur Un utilisateur utilisateurne ne peut ne peut être êtreassocié associé qu'à qu'à un un seul seul schéma, schéma,le le nom nom utilisateur utili utilisateur sateur et et le le schéma schéma sont sont souvent souvent utilisés utilisés de de manière manière interchangeable. interchangeable. Pour accède a ccèder r aux objets obj ets de l’utilis l’ut ilisateur ateur user01 user 011 (si dr oon n précede préce de Pour accèder accèder aux objets objets de l’utilisat l’utilisateur eur user0 user01 (sion on aa le le droit) droit) droit) oit) on précede préce de le le nom nom de l’l’objet objet l’uti lisateur teur schém a) de l’objet l’o bjet par par le le nom le nom de de l’utilisa de l’utilisateur l’utilisa teur (equi (equiau au nom nomdu du schéma) schéma) schém a) Select Selec t ** from user01 .nomTable; able;; Select from user01.nomT user01.nom user0 1.nomTable Table;
D.BOUZIDI
104
Privilèges
Par Par défaut, défaut, lorsqu'un lorsqu'un utilisateur utilisateur est est créé, créé, aucun aucun privilège privilège ne ne lui est accordé, il ne peut effectuer aucune opération dans lui est accordé, il ne peut effectuer aucune opération dans la la base base de de données. données. Si Si l'utilisateur l'utilisateur ne ne dispose dispose d'aucun d'aucun quota quota dans dans aucun aucun tablespace, il ne pourra pas créer d'objets. tablespace, il ne pourra pas créer d'objets. Deux Deux types types de de privilège privilège ::
Systè Sys tème mee :: Syst Sy stèm ème
Accordé l'administ quelq p ermission Accordé par par l'administrateur l'administrateur l'administrateur rateur ou ou par par quelqu'un quelqu'un quelqu'un u'un àà qui qui la la permission permission d'administrer d'administrer le le privilège privilège aa été été accordé, accordé, Permet Permet aux aux utilisateurs utilisateurs d'effectuer d'effectuer des des actions actions particulières particulières dans dans la la base cr éer des Tablespace s). base de de données données (par (par exemple exemple créer créer des Tablespaces). Tablespaces). Tablespaces).
Obje Ob jet tt :: Obje Ob jet
D.BOUZIDI
permet utilisat spécifiqu e et permet aux aux utilisateurs utilisateurs utilisateurs eurs d'accéder d'accéder àà un un objet objet spécifique spécifique etde dele le manipuler (table, sequence, …) manipuler (table, sequence, …) Sans Sans permission permission spécifique, spécifique, les les utilisateurs utilisateurs ne ne peuvent peuvent accéder accéder qu'à qu'à leurs leurs propres propres objets. objets. Les Les privilèges privilèges objet objet peuvent peuvent être être accordés accordés par par le le propriétaire propriétaire d'un d'un objet, objet, l'administrateur l'administrateur d'acc order à à qui qui la la permission permission d'accorder d'accord d'accorder er des des privilèges privilèges sur sur l'objet l'objet aa été été 105 accordée explicitement. accordée explicitement.
Privilèges
DROP obje ctt :: le AN l'utilisateu r DROP ANY ANY object objec object le privilège privilège DROP DROP ANY ANYY autorise ANY autorise l'utilisateur l'utilisateur l'utilisateur àà supprimer supprime r des appartie nnent supprimer des objets objets qui qui ne ne lui lui appartiennent appartien appartiennent nent pas pas
CREATE, CREATE , MANAGE, DROP , ALTER TABL ESPACE CREATE, MANAGE, DROP, MANAGE, DROP, ALTER TABLESPACE ALTER TABLESP TABLESPACE ACE :: les les utilisateurs utilisateurs qui qui ne ne sont sont pas pas administrateurs administrateurs ne ne doivent doivent généralement généralement pas pas pouvoir pouvoir contrôler contrôler les les tablespaces. tablespaces. GRANT OB PRIVILEG E :: ce auto GRANT ANY ANY OBJECT OBJECT OBJECT JECT PRIVILEGE PRIVILEGE ce privilège privilège autorise autorise autorise rise les les utilisateurs à accorder des de s permissions sur s ur des objets qui qu utilisateurs à accorder des permissions permissions sur sur des objets quii ne ne leur leur appartiennent appartiennent pas. pas. ALTER DAT ABASE ALTE R SYSTEM qu ALTER DATABASE DATA DATABASE BASE et et ALTER ALTER SYSTEM :: les SYSTEM les utilisateurs utilisateurs qui quii ne qui ne sont sont pas pas administrateurs administrateurs ne ne doivent doivent généralement généralement pas pas être être autorisés bas donnée l'instance . autorisés àà modifier modifier la la base basee de base de données donnéess ou données ou l'instance. l'instance.
D.BOUZIDI
106
Exemples de privilèges Privilèges Privilèges système système
Catégorie
Exemples
INDEX
CREATE ANY INDEX ALTER ANY INDEX DROP ANY INDEX
TABLE
CREATE TABLE CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE DELETE ANY TABLE
SESSION
TABLESPACE TABLESPACE
Privilèges Privilèges Objet Objet OBJET
Table
CREATE SESSION ALTER SESSION RESTRICTED SESSION
Privilèges ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE
Séquence
ALTER, SELECT
vue
DELETE, INSERT, SELECT, UPDATE
Procédure
EXECUTE
CREATE TABLESPACE ALTER TABLESPACE DROP TABLESPACE UNLIMITED TABLESPACE
D.BOUZIDI
107
Affectation de quotas
Un Un quota quota est est une une allocation allocation d'espace d'espace dans dans un un tablespace tablespace donné. donné. Par Par défaut, défaut, un un utilisateur utilisateur ne ne dispose dispose d'aucun d'aucun quota quota sur sur aucun aucun des tablespaces. des tablespaces. Trois affecte u Trois options options pour pour affecter affecterr un affecter un quota quota utilisateur utilisateur utilisateur tilisateur
Unlimited Unlimited :: permet permet àà l'utilisateur l'utilisateur d'employer d'employer tout tout l'espace l'espace disponible dans le tablespace. disponible dans le tablespace. Valeur l'e space pouvant l'utilisate ur. Valeur :: indique indique l'espace l'espace l'espace pouvant être être employé employé par par l'utilisateur. l'utilisateur. Cette Cette valeur valeur ne ne garantit garantit cependant cependant pas pas que que l'espace l'espace est est réservé réservé pour pour l'utilisateur. l'utilisateur. Elle Elle peut peut être être supérieure supérieure ou ou inférieure inférieure àà l'espace l'espace actuellement disponible dans le tablespace. actuellement disponible dans le tablespace. Privilège Privilège système système UNLIMITED UNLIMITED TABLESPACE TABLESPACE :: remplace remplace tous tous les les quotas indi ls tablespa l'uti lisateur eur un quotas individue individuels individue viduels ls sur sur les les tablespaces tablespaces tablespaces ces et et accorde accorde àà l'utilisat l'utilis l'ut ilisateur ateur un quota illi tablespaces ces quota illimité illimité illimité mité sur sur tous tous les les tablespa tablespac tablespaces es ALTER ALTER USER USER user01 user01 QUOTA 10M QUOTA 10M ON ON AppData; AppData;
D.BOUZIDI
108
Manipulation des comptes user
Création Création d’un d’un utilisateur: utilisateur: CREATE CREATE USER USER user01 user01 IDENTIFIED IDENTIFIED BY BY PwdUser01 PwdUser01 DEFAULT TABLESPACE AppData DEFAULT TABLESPACE AppData
TEMPORARY TEMPORARY TABLESPACE TABLESPACE temp temp QUOTA 15M ON QUOTA 15M ON dAppDta dAppDta PASSWORD PASSWORD EXPIRE; EXPIRE;
Modification Modification des des paramètres paramètres utilisateur utilisateur :: ALTER ALTER USER USER user01 user01 QUOTA QUOTA 50M 50M ON ON AppData; AppData;
Suppression Suppress ion Suppression Suppress ion d’un d’un utilisateur utilisateur :: DROP DROP USER USER peter; peter; DROP CASCAD E; DROP USER USER peter peter CASCADE; CASCADE; CASCAD E;
Option Option cascade cascade :: Si Si le le schéma schéma contient contient des des tables, tables, Oracle Oracle effacera effacera alors toute les contraintes d'intégrités des tables et toutes alors toute les contraintes d'intégrités des tables et toutes les les contraintes contraintes d'intégrités d'intégrités dans dans les les schémas schémas d'autres d'autres utilisateurs utilisateurs qui qui faisaient faisaient références références aux aux contraintes contraintes UNIQUE UNIQUE et et PRIMARY PRIMARY KEY KEY du du schéma schéma qui qui est est en en cours cours de de suppression suppression Les Les vues vues principales principales permettant permettant de de surveiller surveiller des des utilisateurs utilisateurs dba_users dba_us ers dba_TS_ Quotas dba_users dba_use rs et et dba_TS_Quotas dba_TS_Quotas dba_TS_Quotas
109
D.BOUZIDI
Les Rôles
Problèmes Problèmes ::
IlIl est est trop trop fastidieux fastidieux d'accorder d'accorder de de manière manière individuelle individuelle les les privilèges uti lisateur privilèges nécessaires nécessaires àà chaque chaque utilisateur utilis utilisateur ateur le le risque risque d'erreur d'erreur est est trop trop important. important.
Solution Solution :: utilisation utilisation des des rôles rôles
Un ens emble de Un rôle rôle est est un un ensemble ensemble ensemble de privilèges privilèges pouvant pouvant être être accordés accordés àà des des utilisateu utili sateurs rs ou à d'autres d'autr es rôles. rôles . utilisateu utili sateurs rs ou à d'autre d'autres s rôles. Un Un Rôle Rôle est est utilisé utilisé pour utilisé pour administrer administrer les les privilèges privilèges de privilèges de base base de de données données On ajout er des privi lègess à un pui s accorder accorde rr le On peut peut ajouter ajouter ajouter des privilèges privilèges privilège un rôle, rôle, puis puis accorder accorde le rôle rôle àà un un utilisateur, utilisateur, L'utilisateur L'utilisateur peut peut alors alors activer activer le le rôle rôle et et exercer exercer les les privilèges privilèges octroyés octroyés par par ce ce rôle rôle
D.BOUZIDI
110
Les caractéristiques des des rôles rôles
Les Les privilèges privilèges sont sont accordés accordés aux aux rôles rôles (et (et révoqués) révoqués) comme comme si si le le rôle rôle était était un un utilisateur. utilisateur. Les util isateurs Les rôles rôles peuvent peuvent être être accordés accordés aux aux utilisateurs utili utilisateurs sateurs ou ou àà d'autres d'autres rôles rôles (et (et révoqués) comme s'il s'agissait de privilèges révoqués) comme s'il s'agissait de privilèges Un systè me et Un rôle rôle peut peut être être constitué constitué de constitué de privilèges privilèges système système système et objet. objet. Un ac désacti ut ilisateur teur auque Un rôle rôle peut peut être être activé activé activé tivé ou ou désactivé désactivé désactivé vé pour pour chaque chaque utilisa uti utilisat lisateur eur auquel auquell auquel le le rôle rôle est est accordé. accordé. L'activation L'activation d'un d'un rôle rôle peut peut nécessiter nécessiter un un mot mot de de passe. p asse. Les n'appartienne rési dent dans Les rôles rôles n'appartiennent n'appartiennent n'appartiennent nt àà personne personne et et ne ne résident résident résident dans aucun aucun schéma. schéma. Exemple Exemple ::
les les privilèges privilèges SELECT SELECT et et UPDATE UPDATE sont sont accordés accordés au au rôle rôle HR_CLERK HR_CLERK sur sur la la table table EMP EMP Les Les privilèges privilèges DELETE DELETE et et INSERT INSERT sur sur la la table table EMP, EMP, ainsi ainsi que que le le rôle rôle HR_CLERK, HR_CLERK, sont sont accordés accordés au au rôle rôle HR_MGR Le HR_M accor dé au mana ger, Le rôle rôle HR_MGR HR_MGR HR_MGR GR est est accordé accordé accordé au manager, manag manager, er, lequel lequel peut peut àà présent peut présent effectuer effectuer des des SELECT, SELECT, des des UPDATE UPDATE ,, des des DELETE DELETE et et des des INSERT INSERT sur sur la la table table EMP. EMP.
111
D.BOUZIDI
Exemple de rôles Prédéfinis ROLE
Privilèges Privilèges associés associés
CONNECT
CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE DATABASE LINK, CREATE CLUSTER, ALTER SESSION
RESOURCE
CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE TYPE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
DBA
La plupart des privilèges système et plusieurs plusieurs autres rôles. Ce rôle ne doit pas être accordé aux utilisateurs utilisateurs qui ne sont pas administrateurs. administrateurs.
Les Les vues vues permettant permettant de de surveiller surveiller des des utilisateurs utilisateurs
DBA_SYS_PRIVS DBA_SYS_PRIVS //privilèges //privilèges systèmes systèmes DBA_TAB_PRIVS DBA_TAB_PRIVS //privilèges //privilèges objets objets
Dba_TS_Quotas Dba_TS _Quotas //quot as ssur urr un ttables pace Dba_TS_Quotas Dba_TS _Quotas //quotas //quotas //quot as sur su un tables tablespace ablespace pace
D.BOUZIDI
112
Gestion des rôles
Rôles Utilisateurs
Rôles
B
HR_MGR
Privilèges Privilè
CREATE TABLE
D.BOUZIDI
A
C
HR_CLERK
SELECT ON EMP
INSERT ON EMP CREATE SESSION
UPDATE ON EMP
Avantages des rôles
Limitation Limitation de de l'octroi l'octroi de de privilèges p rivilèges privilèges Gestion Gestion dynamique dynamique des des privilèges privilèges
Disponibili Dispon ibilité té séle ctive privi s Disponibili Dispo nibilité té sélective sélective séle ctive des des privilège privillèges privilèges èges Pas Pas de de révocation révocation en en cascade cascade
Amélioration Amélioration des des performances
D.BOUZIDI
Créer des rôles
Créer Créer un un rôle rôle CREATE CREATE ROLE ROLE sales_clerk; sales_clerk;
Créer proté gé par Créer un un rôle rôle protégé rôle protégé par un un mot mot de de passe de passe CREATE CREATE ROLE ROLE hr_clerk hr_clerk IDENTIFIED IDENTIFIED BY BY bonus; bonus;
Créer Créer un un rôle rôle dont dont l’authentification l’authentification est est gérée gérée par par une une application externe application externe CREATE CREATE ROLE ROLE hr_manager hr_manager IDENTIFIED IDENTIFIED EXTERNALLY; EXTERNALLY;
D.BOUZIDI
Utiliser des rôles prédéfinis Nom du rôle
Description
CONNECT
Permet l'ouverture et la modification d'une session, la création de tables, vues, clusters, séquences, synonymes et liens de DB SESSION
RESOURCE
Permet de créer des types, tables clusters, opérateurs, séquences, index et procédures
DBA
Tous les privilèges système système avec l'option WITH ADMIN OPTION
EXP_FULL_DATABASE
Privilèges d'export de base de données.
IMP_FULL_DATABASE
Privilèges d'import de base de données
D.BOUZIDI
Manipulation des rôles
Modifier Modifier un un rôle rôle ALTER ALTER ROLE ROLE sales_clerk sales_clerk IDENTIFIED IDENTIFIED BY BY commission; commission; ALTER ALTER ROLE ROLE hr_clerk hr_clerk IDENTIFIED IDENTIFIED EXTERNALLY; EXTERNALLY; ALTER ALTER ROLE ROLE hr_manager hr_manager NOT IDENTIFIED; NOT IDENTIFIED;
Attribuer Attribuer un un rôle rôle GRANT sales_ clerk sc ott; GRANT sales_clerk sales_clerk sales_ clerk TO TO scott; scott; scott; GRANT GRANT hr_clerk, hr_clerk, TO TO hr_manager; hr_manager; GRANT hr_ma nager sscott cott GRANT hr_manager hr_manager hr_man ager TO TO scott s cott WITH ADMIN OPTION; WITH ADMIN OPTION;
D.BOUZIDI
Définir des rôles par défaut
Définir Définir des des rôles rôles par par défaut défaut
ALTER ALTER USER USER scott scott DEFAULT DEFAULT ROLE ROLE hr_clerk, hr_clerk, sales_clerk; sales_clerk; ALTER ALTER USER USER scott scott DEFAULT DEFAULT ROLE ROLE ALL; ALL; ALTER ALTER USER USER scott scott DEFAULT DEFAULT ROLE ROLE ALL ALL EXCEPT EXCEPT hr_clerk; hr_clerk; ALTER ALTER USER USER scott scott DEFAULT DEFAULT ROLE ROLE NONE; NONE;
Supprimer Supprimer des des rôles rôles REVOKE sales_ clerk sscott cott;; REVOKE sales_clerk sales_clerk sales_ clerk FROM FROM scott; scott;
REVOKE hr_man ager FROM PPUBLIC UBLIC; ;; REVOKE hr_manager hr_manager hr_ma nager FROM PUBLIC P UBLIC; DROP DROP ROLE ROLE hr_manager; hr_manager;
D.BOUZIDI
Activer et désactiver les rôles
Désactivez Désactivez un un rôle rôle pour pour le le révoquer révoquer temporair temp orairemen ement t à un utilisat util isateur. eur. temporaire tempo rairement ment à un util utilisat isateur. eur. Activez Activez un un rôle rôle pour pour l'accorder l'accorder temporairement. temporairement. La La commande commande SET SET ROLE ROLE permet permet d'activer d'activer et et de de désactiver désactiver les les rôles. rôles. Les Les rôles rôles par par défaut défaut sont sont accordés accordés aux aux utilisateurs utilisateurs lors lors de de la la connexion. connexion. Un Un mot mot de de passe passe peut peut être être nécessaire nécessaire pour pour activer activer un un rôle. rôle.
D.BOUZIDI
Instructions relatives à la créa créatio tion n de rôle rôles s Utilisateurs
Rôles utilisateur Rôles application
HR_CLERK
HR_MANAGER
PAY_CLERK
BENEFITS
PAYROLL
Privilèges Benefits
Privilèges Payroll
Privilèges Privilè d'application
D.BOUZIDI
Utilisation des rôles rôles protégés protégés par par mot de passe et des rôles par défaut
Protégé par mot de Proté passe (ne correspond pas à un rôle par dé défaut)
PAY_CLERK
Privilèges INSERT, UPDATE, Privilè DELETE et SELECT
D.BOUZIDI
Rôle par défaut
PAY_CLERK_RO
Privilèges SELECT Privilè
Afficher les informations sur les rôles Vue du rôle DBA_ROLES
Description Tous les rôles existants dans la base de données
DBA_ROLE_PRIVS
Rôles accordés aux utilisateurs et aux rôles
ROLE_ROLE_PRIVS
Rôles accordés aux rôles
DBA_SYS_PRIVS
Privilèges système accordés aux utilisateurs et aux rôles
ROLE_SYS_PRIVS
Privilèges système accordés aux rôles
ROLE_TAB_PRIVS
Privilèges de table accordés aux rôles
SESSION_ROLES
Rôles d'un utilisateur actuellement activés
D.BOUZIDI
Sauvegarde et restauration
IMPORT et EXPORT EXPORT
Permet Permet de de sauvegarder/restaurer sauvegarder/restaurer le le contenu logique logique d'une d'une base base de base de données données dans données dans un un fichier fichier de de transfert transfert Oracle Oracle au au format format binaire binaire Le ex porté permet perme tt de obj ets Le fichier fichier exporté fichier exporté permet perme de recréer recréer des des objets objets objets qu'il qu'il contient contient (portabilité (portabilité OS OS et et logiciel) logiciel) A aaugmente ugmente le A ne ne pas pas utiliser pas utiliser à distance distance :: augmente le trafic le trafic du du réseau réseau La La version version de de l'utilitaire l'utilitaire Import Import ne ne peut peut être être antérieure l'utilitai re Export antérieure àà celle celle de de l'utilitaire l'utilitaire
D.BOUZIDI
Mode d’Import et d’Export
Base Base de de données données complète complète (option (option FULL): FULL):
Tous oobjets expo rtéss àà l'exception Tous les les objets objets bjets de de la la base base sont sont exportés exporté exportés l'exception de certains utilisateurs : SYS, ORDSYS, CTXSYS, de certains utilisateurs : SYS, ORDSYS, CTXSYS, MDSYS MDSYS et et ORDPLUGINS ORDPLUGINS Lors Lors de de L'importation L'importation tous tous les les objets objets exportés exportés sont créés créés dans dans la la base base de de destination destination
Utilisateur Utilisateur
D.BOUZIDI
Que ob jets utilisateu r qui Que les les objets obje objets ts appartenant appartenant àà un un utilisateur utilisateur qui sont sont exportés exportés (OWNER) (OWNER) On On peut peut impoter, impoter, du du fichier fichier exporté, exporté, les les objets objets d’un d’un utilisateur donné d onné dans le schéma sc héma d’un d’u n utilisateur utilisateur donné donné dans le schéma schéma d’un d’un utilisateur (FROMUSER (FROMUSER // TOUSER) TOUSER) Le expo rec ob Le fichier fichier exporté exporté exporté rté permet permet de de recréer recréer recréer réer des des objets objets objets jets qu'il qu'il contient contient (portabilité (portabilité OS OS et logiciel) logiciel) logiciel)
Mode d’Import et d’Export
Table Table (option (option TABLES) TABLES)
Lors Lors de de l'exportation l'exportation d’une d’une table table tous tous ses ses objets objets (index, (index, contraintes, contraintes, déclencheurs, déclencheurs, privilèges privilèges …) …) sont sont sauvegardés sauvegardés dans dans le le fichier fichier d’export d’export Lors Lors de de L'importation L'importation les les tables tables doivent doivent être être nommées nommées grâce grâce au au paramètre paramètre TABLES TABLES (comme dans l’exportation) (comme dans l’exportation l’exportation))
Tablespace Tablespace (TABLESPACE) (TABLESPACE)
les les métas métas donnés donnés concernant concernant les les tablespaces tablespaces spécifiés spécifiés et et les les objets objets qu'ils qu'ils contiennent contiennent sont sont écrites écrites dans dans un un fichier fichier d’export d’export
D.BOUZIDI
Mode d’Import et d’Export
Privilèges Privilèges Actions
Privilège ou rôle
Exporter son propre schéma
CREATE SESSION
Exporter d'autres schémas
SYSDBA, DBA et EXP_FULL_DATABASE
Exporter la base entière ou tablespaces
EXP_FULL_DATABASE
Importer un objet du fichier
IMP_FULL_DATABASE
D.BOUZIDI
Paramètres de l’Export l’Export Paramètres
Description
Userid
chaîne chaîne de connexion à la base de données données
File
Nom du fichier de sauvegarde
Log
Nom du fichier de sortie du compte-rendu, pour voir les erreurs en particulier particulier
Full
Export de toute la base
Grants
Export des privilèges
Indexes
Export des index
Owner
Utilisat Utilisateur(s eur(s)) à exporter exporter
Parfile
Fichier contenant les paramètres d'export
Rows
Export des lignes
Query
Définit une condition de filtre pour exporter un sousensemble
Tables Statistics
Table(s) à exporter Analyse des objets exportés
D.BOUZIDI
Paramètres d’import Paramêtres
Description
Userid
chaîne chaîne de connexion à la base de données données
File
Nom du fichier de sauvegarde
Log
Nom du fichier de sortie du compte-rendu, pour voir les erreurs en particulier particulier
Fromuser
Utilisateur à exporter vers TOUSER TOUSER
Full
Export de tout le contenu du fichier de sauvegarde
Grants
Import des privilèges
Indexes
Import des index
Parfile
Fichier contenant les paramètres d‘import
Rows
Import des lignes
Show
Liste le contenu du fichier d'export, aucune opération n'est effectuée dans la base
Destroy
Détruit les objets s'ils existent avant de les importer
Tables Analyze Touser D.BOUZIDI
Table(s) à exporter Exécute la commande ANALYZE dans le fichier de sauvegarde Utilisateur destinataire
Exemple d’export
Exporter Exporter les les structures structures de de la la base base données données exp exp userid=system/manager userid=system/manager file=c:\save\exp_ORCL_struct_full.dump file=c:\save\exp_ORCL_struct_full.dump log=c:\saveLog\exp_ORCL_struct_full.log log=c:\saveL log=c: \saveLog\ex og\exp_ORCL p_ORCL_stru _struct_fu ct_full.log ll.log full=y full=y full= y rows=n rows=n
Exporter Exporter d’un d’un schéma schéma
exp exp userid=system/manager userid=system/manager file=c:\save\exp_scott_full.dump file=c:\save\exp_scott_full.dump log=c:\logsa log=c: \logsave\ex ve\exp_sco p_scott_fu tt_full.lo ll.log gg owner=scott owner= scottt log=c:\logs log=c: \logsave\e ave\exp_sco xp_scott_fu tt_full.lo ll.log owner=scot owner =scott
Exporter Exporter d’une d’une table table
exp exp userid=system/manager userid=system/manager file=c:\save\exp_emp_scott.dump file=c:\save\exp_emp_scott.dump log=c:\logsa log=c: \logsave\ex ve\exp_emp p_emp_scot _scott.log t.log tables =scott.emp t.emp log=c:\logs log=c: \logsave\e ave\exp_emp xp_emp_scot _scott.log t.log tables=scot tables=sco table s=scott.emp tt.emp
Exporter Exporter d’une d’une tablespace tablespace
exp exp userid=system/manager userid=system/manager file=c:\save\exp_nomTablespace.dump file=c:\save\exp_nomTablespace.dump log=c:\logsave\exp_nomTablespace.log log=c:\logsave\exp_nomTablespace.log tablespace=nomTablespace tablespace=nomTablespace
Exporter Exporter selon selon une une condition condition
exp system /manager ger file=\save file= \save\exp_O \exp_ORCL_qu RCL_query1. ery1.dmp dmp exp system/mana system/man syste m/manager ager file=\save file= \save\exp_O \exp_ORCL_qu RCL_query1. ery1.dmp dmp tables=scott tables =scott.emp .emp query="'wh query ="'where ere salair e >> 500'" tables=scot tables =scott.emp t.emp query="'whe query= "'where re salaire salaire salai re 500'"
D.BOUZIDI
Exemple d’import
Importer Importer tous tous les les schéma schéma sauvegardés sauvegardés imp imp userid=stystem/manager userid=stystem/manager file=c:\save\exp_ORCL_struct_full.dump file=c:\save\exp_ORCL_struct_full.dump log=c:\logsave\imp_ORCL_struct_full.log log=c:\logsave\imp_ORCL_struct_full.log
Importer Importer un un schéma schéma d’un d’un fichier fichier export export contenant contenant tt tt la la BD BD imp imp userid=scott/tiger userid=scott/tiger file=c:\save\exp_scott_full.dump file=c:\save\exp_scott_full.dump log=c:\logsa log=c: \logsave\im ve\imp_sco p_scott_fu tt_full.lo ll.log gg owner=scott owner= scottt log=c:\logs log=c: \logsave\i ave\imp_sco mp_scott_fu tt_full.lo ll.log owner=scot owner =scott
Importer Importer un un schéma schéma vers vers un un autre autre imp imp userid=scott/tiger userid=scott/tiger file=c:\save\exp_scott_full.dump file=c:\save\exp_scott_full.dump log=c:\logsa log=c: \logsave\i ve\imp_sco mp_scott_fu tt_full.lo ll.log gg fromuser=s fromus er=scott cott touse r=user01 r01 log=c:\logs log=c :\logsave\i ave\imp_sco mp_scott_fu tt_full.lo ll.log fromuser=sc fromus er=scott ott touser=use touser=use touse r=user01 r01
Importer Importer une une table table d’un d’un schéma schéma vers vers un un autre autre imp imp userid=system/manager userid=system/manager file=c:\save\exp_scott_full.dump file=c:\save\exp_scott_full.dump log=c:\logsa log=c: \logsave\im ve\imp_sco p_scott_fu tt_full.lo ll.log gg fromuser=sc fromus er=scott ott Tables=emp Table s=emp log=c:\logs log=c: \logsave\i ave\imp_sco mp_scott_fu tt_full.lo ll.log fromuser=s fromu ser=scott cott Tables=emp Table s=emp touser=user02 touser=user02
D.BOUZIDI
Exemple Exemple d’export d’export /import /import utilisant utilisant un un fichier fichier de de paramètres paramètres
Création Création du du fichier fichier de de paramètres paramètres d’export d’export expScott.prm expScott.prm
userid=system/manager userid=system/manager file=c:\save\exp_scott_full.dump file=c:\save\exp_scott_full.dump log=c:\exp_scott_full.log log=c:\exp_scott_full.log owner=scott owner=scott rows=y rows=y
Export Export un un schéma schéma exp exp parfile=c:\fichierParametre\expScott.prm parfile=c:\fichierParametre\expScott.prm parfile=c:\fichierParametre\expScott.pr m
Création Création du du fichier fichier de de paramètres paramètres d’import d’import userid=system/manager userid=system/manager file=c:\save\exp_scott_full.dump file=c:\save\exp_scott_full.dump
impScott.prm impScott.prm
log=c:\imp_scott_full.log log=c:\imp_scott_full.log owner=scott owner=scott rows=y rows=y
Export Export un un schéma schéma imp imp parfile=c:\fichierParametre\impScott.prm parfile=c:\fichierParametre\impScott.prm parfile=c:\fichierParametre\impScott.pr m
D.BOUZIDI
Sau Sauveg vegard arde ee àà Fr Froid oid Sauveg Sau vegard arde Froid Fr oid
Permet Permet de de faire faire une une sauvegarde sauvegarde d’une d’une Base Base de de données données en en arrêt arrêt Les Les fichiers fichiers (datafiles, (datafiles, logfiles, logfiles, controlfile, controlfile, etc) etc) peuvent peuvent être être sauvegardés sauvegardés sans sans corruption corruption Fortement Fortement utilisée utilisée en en mode mode noArchivelog noArchivelog Inconvénients Inconvénients :: n’est n’est valable valable pour pour des des enviro env ironne nnemen ments ts àà haute ha ute disp onibi ibilit lité é où l’a ctivit vité é enviro env ironn nneme ements nts haute hau te dispon dispo disponi nibil bilité ité où l’acti l’act l’ activi ivité té ne ne peut peut être être interrompue interrompue
D.BOUZIDI
Sau Sauveg vegard arde ee àà Fr Froid oid Sauveg Sau vegard arde Froid Fr oid
Les Les étapes étapes de de sauvegarde sauvegarde
Lister lles es noms sau vegarder der Listerr les Liste noms à sauvegar noms sauvegar sauv egarder der
Fichiers Fichiers de de données données Select Select ** from from dba_datafiles; dba_datafiles;
Fichiers Fichiers de de contrôles contrôles
Select Selec tt ** from v$ eter wher e name li Select Selec from v$param v$parameter v$param parameter eter where where name like name like like ke ‘‘control_files control_files’’;;
Les Les fichiers fichiers log log Select Select ** from from v$logfile; v$logfile;
Arrêter Arrêter de de la la base base de données données en en mode mode immediate immediate Shutdown Shutdown immediate immediate
Effectuer Effectuer une une copie copie des des fichiers fichiers àà sauvegarder sauvegarder par par une une commande commande OS OS Redémarrer Redémarrer la la base base de de données données
D.BOUZIDI
Sau Sauveg vegard arde e à Ch Chaud aud Sauvega Sau vegarde rde Chaud Ch aud
Permet Permet de de faire faire une une sauvegarde sauvegarde d’une d’une Base Base de de données données sans sans la la faire faire arrêter arrêter Utile Util e dans dan s un conte xtee àà haute hau tee disponi dis bilité éé où Utile dans un contexte contexte context haute haut disponibilit disponi ponibilit bilité où l’état l’état des des fichiers fichiers change change constamment constamment Fonctionnement Fonctionnemen t : Placer P lacer un Fonctionnement Placer un tablespace un tablespace dans tablespace dans le le mode mode de de sauvegarde sauvegarde et et de de sauvegarder sauvegarder les les fichiers fichiers de de données, données, puis puis de de rétablir rétablir le le tablespac tabl espace e dans le mode mo de normal norm al tablespace table space dans le mode mode normal normal La La base base de de données données doit doit être en en mode mode Archivelog Archivelog
D.BOUZIDI
Sau Sauveg vegard arde e à Ch Chaud aud Sauvega Sau vegarde rde Chaud Ch aud
Stratégie Stratégie de de sauvegarde: sauvegarde:
Sauvegarde Sauvegarde complète complète de de la la base base de de données données àà des des intervalles intervalles réguliers réguliers Sauvegarde Sauvegarde partielle partielle de de la la base base de de données données Archivage jou rnalisation Archivage des des fichiers fichiers de de journalisation jour journalisation nalisation (log) (log) Sauvegarde Sauvegarde du du fichier fichier de de contrôle contrôle en en cas cas de de modification dans la base de données modification dans la base de données
D.BOUZIDI
Sau Sauveg vegard arde e à Ch Chaud aud Sauvega Sau vegarde rde Chaud Ch aud
Sauvegarde Sauveg arde d’ tables offl ine Sauvegarde Sauveg arde d’un d’un d’un un tablespace tablespace tablespace pace mis mis en en offline offline offline
Identifier Ident ifier fich tab ce Identifier Identi fier les les fichiers fichiers fichiers iers du du tablesap tablesapce tablesap lesapce ce àà sauvegarder sauvegarder
Select Sel ect ffile _name me fro mm dba dba_da tafile iles s where wh ere Select Sel ect file file_na ile_na _name me from from fro dba_dataf dba_da _data tafil files es where whe re ‘ ’ tablespace_name= NOMTABLESPACE ‘ ’ tablespace_name= NOMTABLESPACE
Mettre Mett ree le tables pace offl Mettre Mettr le tablespace tablesp tablespace ace en en mode mode offline offline offline ine Alter NomTablespac e offline norma l Alter tablespace tablespace NomTablespace NomTablespace offline normal normal normal
Effectuer u ne copie copi e des Effectuer Effect uer une une copie des fichiers fichiers à sauvegarder fichiers sauvegard sauv egarder er par une commande OS par une commande OS Remettre lle e tablespace tablespa ce en onl Remettre Remet tre le tablespace tablespace en mode mode on on online online online ine Alter Alter tablespace tablespace NomTablespace NomTablespace online NomTablespace online
D.BOUZIDI
Procédure Procédure de de récupération récupération Fichier de journalisation Fichier de archivé journalisation archivéFichier de journalisation en ligne
Modifications appliquées
Informations d'annulation appliquées
2
4
Fichiers de données restaurés
Fichiers de données contenant contenant des transactions transactions validées et non validées
Fichiers de données récupérés
1
3
5
D.BOUZIDI
Procédures Procédures de de récupération récupération gérées gérées par par l'util l'u tilisa isateu teur r :: com comman mande de RECOV COVER ER l'utili l'u tilisat sateur eur comman com mande de RE RECOV RE COVER ER
Restaurer tous les fichiers de base de données à partir d'une d'une sauvegarde et récupérer la la base de don donné nées es :
SQL> RECOVER DATABASE
Restaurer les fichiers de données endommagés à partir partir d'une sauvegarde sauvegarde et récupérer récupérer les fichiers fichiers de donnée données s:
SQL> RECOVER TABLESPACE index_tbs
Ou SQL> RECOVER DATAFILE '/oradata/indx01.dbf'
D.BOUZIDI
Créer Créer un un nouveau fichier de contrôle contrôle
Fichier de paramètres
SYSTEM
USERS
SYSAUX
INDEX
UNDO
TEMP
Fichier de mots de passe
Fichier de Fichier de journalisation journalisation 1A 1B Fichier de Fichier de journalisation journalisation 2A 2B
Fichiers de contrôle
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
141
D.BOUZIDI
Situations Situations nécessitant nécessitant une une récupération récupération incomplète incomplète
La récupération complète échoue parce qu'un qu'un fichier fichier de journa journalisa lisation tion archivé archivé est manquant. Un ou plusieurs fichiers de journalisation non archivés et un fichier de données sont manquants. Une sauvegarde du fichier de contrôle est utilisée pour ouvrir ou récupérer la base de données.
D.BOUZIDI
Méthodes Méthodes recommandées recommandées pour pour la la récupération récupération incomplète incomplète
Suivez soigneusement toutes les étapes. Effectuez des sauvegardes totales de la base de données avant et après la récupération. Vérifiez toujours que la récupération a réussi. Sauvegardez et supprimez les fichiers de journalisation journalisation archivés.
D.BOUZIDI
Procédure Procédure de de récupération récupération UNTIL UNTIL TIME TIME 1. 2. 3. 4. 5.
Arrête Arrêtezz la base base de don donnée nées. s. Restaurez Restaurez les fichiers fichiers de donné données. es. Montez Montez la la base base de donnée données. s. Récupé Récupérez rez la la base base de donn données ées.. Ouvrez Ouvrez la base de données données avec avec l'optio l'option n RESETLOGS. 6. Sauveg Sauvegard ardez ez la base base de donné données. es. SQL> SQL> $ cp SQL> SQL> SQL> SQL> SQL> $ cp
shutdown shutdown immedi immediate ate /BACKUP/* /BACKUP/* /u01/db01/ /u01/db01/ORADA ORADATA TA startu startup p mount mount recover recover database database until time '2004-05'2004-05-28:11 28:11:44:0 :44:00'; 0'; alter alter databa database se open resetl resetlogs; ogs; shutdown; /u01/db01/ /u01/db01/ORADA ORADATA/* TA/* /BACKUP /BACKUP
D.BOUZIDI
144
