Administration de bases de données ORACLE

Administration de bases de données
ORACLE Préparation à la certification Oracle OCA : Oracle Certified Associate"
Prof. Nissrine SOUISSI"
2015-2016
OBJECTIFS DU COURS
•  Acquérir une base solide de l'administration élémentaire d'une base
de données Oracle. "
•  Comprendre l'architecture de la base de données Oracle ainsi que la
façon dont ses composants fonctionnent et interagissent. "
•  Apprendre à créer, démarrer et arrêter correctement une base de
données Oracle."
•  Apprendre à gérer correctement et efficacement les différentes
structures de la base Oracle."
•  Apprendre à créer et gérer les utilisateurs de la base Oracle."
"
OCA – Oracle Certified Associate Database : confirme le niveau
d’initié dans l’administration de ORACLE.!
2
PLAN DU COURS
• Chapitre 1 : Architecture du serveur oracle"
• Chapitre 2 : Utilisation de la mémoire par oracle"
• Chapitre 3 : Les processus d’oracle"
• Chapitre 4 : Les fichiers d’une base oracle
"
"
• Chapitre 5 : Gestion des tablespaces"
"
• Chapitre 6 : Gestion des utilisateurs
3
CHAPITRE 1
Architecture du serveur Oracle
Architecture du serveur Oracle Introduction
Oracle DataBase : intervenants
•  utilisateurs naïfs "
•  utilisateurs traditionnels "
•  utilisateurs confirmés "
•  concepteurs de BD "
•  développeurs d'application "
•  administrateurs de BD"
•  développeurs de SGBD "
5
Architecture du serveur Oracle
Introduction Oracle : DBA
•  Définir le matériel requis (taille et nombre de disques, puissance
CPU, taille mémoire…)."
•  Ecrire le script de création de la base de données et l’installer
sur le serveur. (Recours éventuel à un outil graphique)"
•  Ecrire les scripts d’arrêt et de redémarrage de la base sur le
serveur. (Recours éventuel à un outil graphique)"
•  Déclarer les utilisateurs, fixer leurs autorisations d’accès sur la
base et mettre en place le contrôle des accès et la sécurité."
•  Organiser les opérations de sauvegarde, de restauration et de
récupération de la base."
•  Mesurer les performances et les temps de réponse des
demandes des utilisateurs pour optimiser le rendement.
6
Architecture du serveur Oracle
Introduction Oracle DataBase : serveur de données
•  Oracle a établi sa réputation comme
serveur de données, sur de multiples
plates-formes : Windows est l’une
d’entre elles. "
•  Par sa facilité d’installation et de
paramétrage, un serveur Windows relié
à des postes clients constitue une
configuration client-serveur à faible coût."
Dans cette architecture, les programmes
fonctionnent sur les clients et
interagissent avec la base de données.
Seules les données utiles aux
programmes sont échangées entre le
client et le serveur."
7
Architecture du serveur Oracle
Introduction Oracle DataBase : serveur de données
•  Le i d’Oracle8i et 9i signifie Internet, le g d’Oracle 10g et 11g signifie Grid, le c
d’Oracle 12c signifie Cloud."
•  L’orientation Grid d’Oracle 10g ou 11g permet :"
•  d’associer plusieurs machines "
•  obtenir plus de disponibilité, de puissance, gérer de plus gros volumes de
données."
•  de supporter la virtualisation des capacités de traitement."
•  La virtualisation consiste à faire fonctionner sur un seul ordinateur plusieurs
systèmes d'exploitation comme s'ils fonctionnaient sur des ordinateurs
distincts."
•  Oracle Database 12c permet aux clients d’améliorer la qualité et les performances
de leurs applications, gagner du temps avec une disponibilité maximale de
l’architecture et de la gestion du stockage, et simplifier la consolidation de la base
de données en gérant des centaines de bases de données comme s’il n’y en avait
qu’une. "
8
Architecture du serveur Oracle
Introduction Oracle DataBase: serveur de données
Vers une maintenance simplifiée "
• Auto-ajustement des caractéristiques de la base."
"
• L ’automatisation des tâches d’administration: un
ordonnanceur interne permet de programmer des tâches
d’administration dans des « fenêtres de maintenance ». Par
exemple, la collecte automatique des statistiques, pour un
fonctionnement optimum de l’optimiseur statistique. "
"
• Les alertes automatiques: le serveur envoie directement un
e-mail lorsqu’un incident survient ou qu’un seuil prédéfini est
franchi.
9
Architecture du serveur Oracle
Introduction Oracle DataBase : outils et assistants
•  Le logiciel DBCA (Oracle Database Configuration Assistant) offre
une interface graphique conviviale pour guider l’administrateur. Cet
utilitaire réclamé par tous les administrateurs Oracle depuis des
années est disponible depuis Oracle 8. Il s’avère très bien conçu et
ses versions 10g et 11g prennent en compte toutes les nouveautés
apportées par Oracle.
10
Architecture du serveur Oracle
Introduction Oracle DataBase : outils et assistants
• OEM (Oracle Enterprise Manager), la console d’administration graphique
d’Oracle : un fonctionnement 100 % à distance par interface Web."
• Accéder à distance de façon sécurisée à toutes les bases, à d’autres logiciels
Oracle ainsi qu’aux serveurs."
• Administrer, gérer la performance et superviser plusieurs bases et serveurs."
• Contrôler les scripts, tant pour la base que pour le serveur."
• Disposer instantanément d’indicateurs graphiques de performances sans qu’il
soit nécessaire de lancer des ordres SQL complexes."
• ..."
11
Architecture du serveur Oracle
Introduction Exemple
Oracle 10g : OEM
•  OEM
12
Architecture du serveur Oracle
Introduction Exemple
Oracle 11g : OEM
•  OEM
12
Architecture du serveur Oracle
Introduction Oracle DataBase : outils et assistants
• Oracle Administration Assistant for Windows permet de paramétrer les
options de démarrage et d’arrêt d’une base Oracle."
"
"
"
"
!
!
• Oracle SQL*Plus permet entre autres de démarrer et d’arrêter les bases Oracle.
C’est l’outil standard privilégié pour effectuer toutes les opérations sur une base
Oracle."
13
Architecture du serveur Oracle
Composants
Oracle Database : composants
•  Les composants « fichiers » "
•  Les fichiers de données (datafile)"
•  Les fichiers redo-log ou de journalisation (logfile)"
•  Les fichiers de contrôle (controlfile)"
•  Les fichiers d’administration (pfile, spfile, ...)"
•  Les composants « programmes » ou « processus »"
•  Les composants « mémoire »
14
Architecture du serveur Oracle
Composants Les composants fichiers
• Les fichiers de données (ex. Filename1.dbf) contiennent
les tables, index, procédures, fonctions et le dictionnaire de
données (conçu lors de la création d’une base de données)."
•  Le dictionnaire, géré à 100 % par Oracle, est le cœur de la
gestion interne de la base. "
• Les fichiers de données constituent plus de 90 % du volume
global d’une base Oracle."
""
" La taille, l’emplacement et le paramétrage de ces fichiers
sont de la responsabilité de l’administrateur de la base de
données.
15
Architecture du serveur Oracle
Composants Les composants fichiers
•  Les fichiers redo-log (ex. Redo1.log) mémorisent
l’historique de tous les ordres modifiant des données
ou la structure de la base de données. "
"
•  Leur taille est fixée lors de la création de la base de
données (modifiable ultérieurement)."
•  Leur fonctionnement est géré à 100 % par Oracle.
16
Architecture du serveur Oracle
Composants Les composants fichiers
•  Les fichiers de contrôle (control1.ctl) renseignent la base de
données sur son état précédent de l’arrêt, lors du
démarrage, et lors de certaines phases de fonctionnement. "
•  Par exemple, lors de son démarrage, leur consultation permet
de savoir quel type d’arrêt a eu lieu précédemment : normal,
brutal, etc. "
•  Ces fichiers contiennent les chemins d’accès et les noms de
tous les fichiers (de données et redo-log) qui composent la
base de données. "
•  Pour démarrer une base de données, il suffit que le logiciel
oracle sache où se trouvent les fichiers de contrôle. Il y puise
un ensemble de renseignements qui lui serviront à réaliser
l’opération. Leur taille, quelques centaines de kilo-octets
maximum et leur fonctionnement sont gérés à 100 % par
Oracle.
17
Architecture du serveur Oracle
Composants Les composants fichiers
•  Les fichiers d’administration servent à l’administration
courante d’Oracle : "
"
• Les fichiers d’initialisation utilisés lors du démarrage de la
base (*.ora)."
• Les fichiers servant à paramétrer certains outils et enfin
les différents fichiers de trace (*.trc) et d’alerte (*.log)
générés par Oracle.
18
Architecture du serveur Oracle
Composants Les composants programmes
•  Ces composants « programmes » sont les exécutables qui
assurent le fonctionnement de la base de données. Ils
réalisent toutes les actions sur les fichiers (données,
contrôle, redo-log)."
•  Sous Windows, les programmes englobent les «
exécutables », les « processus » et les « threads »."
•  L’architecture du système d’exploitation Windows permet
à l’exécutable d’Oracle d’être multitâche et multithread.
Ce sont ces différents threads qui interagissent avec les
composants mémoires, les fichiers.
19
Architecture du serveur Oracle
Composants Les composants programmes
•  Le serveur Oracle fonctionne sous Windows comme un
exécutable unique ayant de multiples threads. "
•  Chaque thread a ses propres tâches à réaliser. "
•  Des threads sont dus aux connexions des utilisateurs. "
•  Des threads réalisent le fonctionnement de la base de
données en tâche de fond."
20
Architecture du serveur Oracle
Composants Les composants mémoires
•  Une zone mémoire partagée par tous les utilisateurs et
threads Oracle "
•  Une zone mémoire privée pour chaque thread utilisateur
21
Architecture du serveur Oracle
BD et Instance Oracle BD et Instance
• Une base de données Oracle en action (démarrée) se
compose de :"
•  fichiers : données, redo-log, contrôle"
•  processus (exécutables) : pour faire fonctionner la base "
•  mémoire"
• Une base de données en arrêt est un ensemble de fichiers"
• Une instance regroupe la zone mémoire allouée et les
exécutables assurant le fonctionnement de la base. "
"
instance + fichiers = une base de données « en action »
22
Architecture du serveur Oracle
BD et Instance Oracle BD et Instance
23
Architecture du serveur Oracle
BD et Instance Oracle
Service et Instance
•  Le nom du service sera toujours OracleServiceSID,
où SID représente l’identifiant de l’instance Oracle.
Ce service est associé à un exécutable oracle.exe."
•  Le service OracleServiceSID se compose initialement
de quelques threads, qui ont pour tâche de gérer
l’ensemble des threads ultérieurs. C’est sous le
contrôle de ce service initial que s’effectuent des
opérations telles que le démarrage ou l’arrêt de la
base de données."
•  S’il y a plusieurs instances Oracle sur un serveur, il y
aura autant de services OracleServiceSID et
d’exécutables oracle.exe lancés que d’instances.
24
Architecture du serveur Oracle
BD et Instance Oracle Illustration
25
Architecture du serveur Oracle
BD et Instance Oracle Démarrer et arrêter une base Oracle
•  Le démarrage s’effectue base fermée -- Il n’est pas
possible de conserver les privilèges dans la base "
•  Les droits ou les mots de passe sont vérifiés et stockés hors de
la base de données."
•  Un privilège au niveau du système d’exploitation fournit à un
utilisateur Windows les droits nécessaires. C’est le cas de
l’appartenance au groupe Windows ORA_DBA (ce groupe
est créé automatiquement lors de l’installation d’Oracle)."
•  Un utilisateur déclaré dans la base, disposant du privilège
Oracle SYSDBA ou SYSOPER, peut démarrer ou arrêter la
base, si sa sécurité d’accès repose sur des fichiers mots
de passe stockés dans des fichiers extérieurs à la base.
26
Architecture du serveur Oracle
BD et Instance Oracle Démarrer et arrêter une base Oracle
• Le fonctionnement d’Oracle sous Windows impose que
le service Windows soit démarré avant de pouvoir
démarrer l’instance Oracle. L’ordre imposé est donc :"
"1. Démarrer le service « OracleServiceSID »."
"2. Démarrer l’instance Oracle « SID »."
35
Architecture du serveur Oracle
BD et Instance Oracle Démarrer et arrêter une base Oracle
• Le fonctionnement d’Oracle sous Windows impose que
l’instance Oracle soit arrêtée avant de pouvoir arrêter le
service Windows. L’ordre imposé est donc :"
"1. Arrêter l’instance Oracle SID."
!2. Arrêter le service OracleServiceSID."
36
Architecture du serveur Oracle
BD et Instance Oracle Démarrer et arrêter une base Oracle
•  Le STARTUP assure le démarrage de l’instance
(nomount) l'association de la base de données
(mount) et l’ouverture de la base de données (open)."
•  Instance lancée : Mémoire allouée + Threads démarrés."
•  Sql>Startup nomount"
•  BD montée : La base est associée à une instance
démarrée."
•  Sql>Alter database mount;"
•  BD ouverte : Les utilisateurs peuvent se connecter à la
base et accéder aux données."
•  Sql>Alter database open;"
44
Architecture du serveur Oracle
BD et Instance Oracle Démarrer et arrêter une base Oracle
•  Le SHUTDOWN assure la fermeture de la base, le
démontage de la base et l’arrêt de l’instance."
•  BD fermée : La base devient indisponible à l’ensemble des
utilisateurs. "
•  Sql>alter database close immediate;"
•  BD démontée : L’association entre les fichiers de la base
et l’instance est supprimée. "
•  Sql>alter database dismount ;"
•  Instance arrêtée : Les processus sont supprimés et la
mémoire est libérée."
•  Sql>shutdown;"
45
Architecture du serveur Oracle
BD et Instance Oracle Démarrer et arrêter une base Oracle
•  SHUTDOWN NORMAL;"
•  Le SHUTDOWN NORMAL attend que l’ensemble des utilisateurs soit
déconnecté pour fermer la base. L’inconvénient, c’est que l’on peut
attendre longtemps si, par exemple, un utilisateur s’est absenté sans
fermer son application : sa session est toujours active."
•  SHUTDOWN IMMEDIATE;"
•  Le SHUTDOWN IMMEDIATE déconnecte automatiquement tous les
utilisateurs, puis ferme correctement votre base de données."
•  SHUTDOWN TRANSACTIONAL;"
•  Le SHUTDOWN TRANSACTIONAL offre une alternative à ces
méthodes : il attend la fin de toutes les transactions en cours en
interdisant toute nouvelle transaction."
•  SHUTDOWN ABORT;"
•  Le SHUTDOWN ABORT est un arrêt violent qui correspond à
supprimer brutalement le processus oracle.exe, ses threads, la
mémoire et libérer tous les verrous sur les fichiers.
46
Atelier Pratique 1
Démarrer et arrêter une base Oracle
•  Création d’une base Oracle"
•  Démarrage d’une base Oracle"
1.  Démarrage du service Windows "
2.  Démarrage de l’instance Oracle "
•  Arrêt d’une base Oracle "
1.  Arrêt de l’instance Oracle "
2.  Arrêt du service Windows"
Tâche à effectuer !
Choix prioritaire !
Choix complémentaire!
Créer une base de données "
DBCA"
SQL*Plus"
Démarrer une base "
Gestionnaire de services de
Windows "
SQL*Plus"
Oracle Enterprise Manager"
Arrêter une base "
Gestionnaire de services de
Windows "
SQL*Plus"
Oracle Enterprise Manager"
Paramétrer le démarrage"
et l’arrêt d’une base"
Oracle Administration Assistant Regedit
for Windows "
27
Atelier Pratique 1
Création d’une base Oracle Démarrer et arrêter une base Oracle
•  L’utilitaire Oracle Database Configuration Assistant est
accessible depuis le menu :"
•  Démarrer>Programmes>Oracle–..... >Configuration and
Migration Tools.
28
Atelier Pratique 1
Création d’une base Oracle Démarrer et arrêter une base Oracle
29
Atelier Pratique 1
Création d’une base Oracle Démarrer et arrêter une base Oracle
30
Atelier Pratique 1
Création d’une base Oracle Démarrer et arrêter une base Oracle
31
Atelier Pratique 1
Création d’une base Oracle Démarrer et arrêter une base Oracle
32
Atelier Pratique 1
Création d’une base Oracle Démarrer et arrêter une base Oracle
33
Atelier Pratique 1
Création d’une base Oracle Démarrer et arrêter une base Oracle
34
Atelier Pratique 1
Le service OracleServiceSID
Démarrer et arrêter une base Oracle
•  Démarrer et arrêter le service OracleServiceSID!
•  Démarrer>Taper : services.msc "
•  Démarrer>Panneau de configuration>Système et
sécurité>Outils d'administration>services "
37
Atelier Pratique 1
Le service OracleServiceSID
Démarrer et arrêter une base Oracle
38
Atelier Pratique 1
L’instance Oracle
Démarrer et arrêter une base Oracle
•  Démarrer et arrêter l’instance Oracle « SID »"
et arrêter l’instance par le service
OracleServiceSID"
•  D é m a r r e r
•  Synchronisation du démarrage et de l’arrêt de l’instance avec le
service"
•  L’utilitaire Oracle Administration Assistant for Windows est
accessible depuis le menu :"
Démarrer>Programmes>Oracle–...>Configuration and Migration Tools."
•  Démarrer et arrêter l’instance avec SQL*Plus"
•  Démarrer>Programmes>Oracle–... >Application Development "
•  Démarrer et arrêter l’instance avec OEM"
•  http://localhost:n°port/em/"
39
Atelier Pratique 1
L’instance Oracle Démarrer et arrêter une base Oracle
1. Paramétrage du démarrage et de l’arrêt de l’instance
40
Atelier Pratique 1
Démarrage d’une instance Oracle Démarrer et arrêter une base Oracle
2. Démarrer l’instance avec SQL*Plus"
• Définir la valeur de la variable ORACLE_SID : elle
permet de définir une instance par défaut. Il est possible
de changer cette valeur ou de se connecter à une autre
instance : "
•  en modifiant ORACLE_SID dans la base de registre avec
regedit."
•  depuis l’Oracle Administration Assistant for Windows ."
•  depuis l’invite de commandes Windows avec cmd."
•  Set ORACLE_SID=nom_SID!
• Vérifier que le service OracleOraDb10g_home1TNSListener
est démarré."
•  Le listener Oracle est un service permettant d'utiliser TCP/IP pour
accéder à la base de données via le réseau.
41
Atelier Pratique 1
Démarrage d’une instance Oracle Démarrer et arrêter une base Oracle
42
Atelier Pratique 1
Démarrage d’une instance Oracle Démarrer et arrêter une base Oracle
43
Atelier Pratique 1
Démarrage et arrêt avec OEM
Démarrer et arrêter une base Oracle
Démarrer l’instance avec OEM"
• Les services Windows suivants doivent être lancés :"
•  OracleServiceSID"
•  Le service qui lance la base de données identifiée par SID"
•  OracleOraDb10g_home1TNSListener"
•  Le Listener Oracle "
•  OracleJobSchedulerSID "
•  L’agent d’OEM de la base identifiée par SID"
•  OracleDBConsoleSID"
•  Le gestionnaire de la console OEM pour la base identifiée par SID
47
Atelier Pratique 1
Démarrage et arrêt avec OEM Démarrer et arrêter une base Oracle
48
Démarrer et arrêter une base Oracle Atelier Pratique 1
•  Livret des exercices"
•  Outils utilisés"
•  DBCA"
•  Oracle Administration Assistant for Windows"
•  SQL*Plus"
•  OEM
49
Chapitre 2
Utilisation de la mémoire par Oracle
•  Introduction"
•  Paramètres d’allocation mémoire"
•  La mémoire partagée "
•  La zone SGA (System Global Area) "
•  La mémoire privée "
•  Mémoire allouée pour chaque thread utilisateur "
•  La zone PGA (Program Global Area) "
50
Utilisation de la mémoire par Oracle Introduction
• La mémoire désigne
la mémoire RAM (Read Access
Memory). C’est le type de mémoire le plus rapide, le
plus performant et tous les systèmes d’exploitation
l’utilisent : mémoire physique "
• Tous les systèmes d’exploitation attribuent à la
mémoire RAM un espace disque de débordement
(espace de pagination sous Windows) : mémoire
virtuelle"
L’échange continu entre la mémoire physique et la mémoire virtuelle
risque d’engorger le système.
51
Utilisation de la mémoire par Oracle Introduction
Oracle Database a besoin de mémoire pour :"
•  mettre à la disposition de multiples utilisateurs, un
maximum d’informations et de données provenant de
la base."
•  permettre aux programmes qui gèrent la base Oracle
(les threads) de fonctionner en mémoire."
•  assurer la transmission des données entre la base
Oracle et les threads des utilisateurs.
52
Utilisation de la mémoire par Oracle Paramètres d’allocation mémoire d’Oracle
•  Toutes
les bases Oracle utilisent un fichier
d’initialisation pour démarrer. "
• pfile : …\oracle\product\...\admin\SID\pfile\initSID.ora"
• spfile : …\oracle\product\...\db_1\database\spfileSID.ora"
"
•  La plupart des bases de données Oracle nécessitent
uniquement l’utilisation de quelques paramètres de
base pour fonctionner correctement."
53
Utilisation de la mémoire par Oracle Paramètres d’allocation mémoire d’Oracle
• Les paramètres d’initialisation dynamiques et statiques sont consultables par la
vue V$PARAMETER."
•  Show parameter;"
•  NAME
TYPE
VALUE"
•  Affichage par ordre alphabétique"
•  Show parameter mot"
•  Affiche la valeur des paramètres dont le nom contient ‘mot’"
• La commande ALTER SYSTEM permet de modifier les paramètres en mémoire,
dans le fichier SPFILE ou simultanément en mémoire et dans le SPFILE."
•  alter system set paramètre=valeur scope= memory ;"
•  alter system set paramètre=valeur scope= spfile ;"
•  alter system set paramètre=valeur scope= both ;"
"
"
"
54
Utilisation de la mémoire par Oracle Bloc de données
•  Un bloc Oracle est une unité exprimée en octets
qui sert d’unité d’échange entre les fichiers, la
mémoire et les processus."
•  La taille du bloc Oracle (DB_BLOCK_SIZE) est définie
lors de la création de la base de données. Ce
paramètre détermine la taille initiale pour le formatage
interne des fichiers de données, ainsi que celle de la
zone mémoire réservée à l'instance. Il précise aussi la
dimension des échanges entre la mémoire et les
disques. Ce paramètre est très important pour les
performances.
55
Utilisation de la mémoire par Oracle Bloc de données
•  Sa taille est déterminée selon la fonction que la base
aura à supporter. La répartition suivante donne une idée
de grandeur: "
•  2K : pour les applications réalisant beaucoup de
transactions, qui accèdent et manipulent peu de données à
la fois; "
•  4K : valeur correcte pour les applications transactionnelles
et des requêtes balayant beaucoup de blocs de données
sur disque; "
•  8K et 16K : pour les applications décisionnelles qui
accèdent toutes à de gros volumes de données. "
56
Utilisation de la mémoire par Oracle Zones mémoire
Mémoire allouée au processus Oracle.exe=1 SGA+ n PGA"
"n : nombre de threads utilisateurs (côté serveur)"
Œ

57
Utilisation de la mémoire par Oracle La zone SGA
•  La SGA (System Global Area) représente la zone
mémoire déterminante d’une instance, tant par sa
taille que par son rôle. C’est elle qui assure le
partage des données entre les utilisateurs. C’est une
mémoire partagée par les différents threads d’une
instance."
•  Toute donnée lue ou modifiée transite par la SGA.
Il est important d’en comprendre les fonctions
majeures, pour faire face à des problèmes de
performance ou un événement inattendu."
•  La SGA est allouée au démarrage de l’instance et
libérée à l’arrêt de l’instance.
58
Utilisation de la mémoire par Oracle La zone SGA
LOCK_SGA"
•  La SGA est un élément clé des performances
d’Oracle. Il faut absolument éviter qu’elle « sorte » de
la mémoire vive et qu’elle soit paginée. Le paramètre
d’initialisation LOCK_SGA permet « d’accrocher » la
SGA en mémoire vive."
•  Lock_sga=true ou false"
•  Cette zone mémoire est partagée et réside dans la
mémoire vive. Elle n’est ni swappée ni paginée pour
des raisons de performances.
59
Utilisation de la mémoire par Oracle La zone SGA : composants
Les composants obligatoires :"
• Cache de tampons de la base de données (Database Buffer)"
• Tampon de journalisation (Redo-Log Buffer) : Mémoire tampon
pour l’enregistrement des modifications apportées à la base de
données."
•  Zone de mémoire partagée (Shared Pool) : Zone de partage
des requêtes et du dictionnaire de données."
"
Les composants facultatifs :"
• Zone de mémoire Large Pool : Zone de mémoire optionnelle
utilisée par des threads dans des configurations particulières."
• Zone de mémoire Java (Java Pool) : Mémoire utilisée pour la
machine virtuelle Java.
60
Utilisation de la mémoire par Oracle Database Buffer
•  Cette zone comporte toutes les données en
provenance de la base ou destinées à y être écrites
(données lues par un SELECT ou modifiées par un
UPDATE, INSERT, DELETE)."
•  Cette zone mémoire est d’une taille fixe, bien
inférieure à la dimension de la base de données. Il est
nécessaire que des mécanismes libèrent de l’espace
pour permettre à de nouveaux blocs de données de «
monter » en mémoire.
61
Utilisation de la mémoire par Oracle Database Buffer : paramètres
•  DB_BLOCK_BUFFERS : ce paramètre défini le nombre de
blocs Oracle qui pourront être contenus dans le Database
Buffer."
•  Pour augmenter ou diminuer la taille du cache mémoire, il
faut modifier le paramètre DB_CACHE_SIZE. Ce
paramètre est dynamique. La valeur affectée est adaptée
pour être un multiple du DB_BLOCK_SIZE."
•  Ce paramètre définit la zone mémoire utilisée pour
conserver les données des tables et des index accédés.
Plus cette zone est grande, plus petite est la probabilité
d'accéder au disque à la suite d'un ordre SELECT. C'est lui
qui a le plus d'impact sur la taille totale de la SGA. "
62
Utilisation de la mémoire par Oracle Redo-Log Buffer
•  Dès qu’une modification intervient sur les données
(INSERT, UPDATE, DELETE), elle se répercute sur
les blocs de données en mémoire, qui sont
simultanément copiés dans les buffers redo-log."
•  L’utilisateur n’a pas à attendre l’écriture des
nouveaux éléments dans les fichiers de données (ce
qui peut être long). Aucune information ne sera
perdue, car toutes sont écrites dans les fichiers redolog."
•  Le Redo-Log Buffer stocke les modifications
apportées à la base de données, avant leur
écriture dans un fichier de journalisation.
63
Utilisation de la mémoire par Oracle Redo-Log Buffer : paramètres
•  Le paramètre statique LOG_BUFFER dimensionne
cette zone mémoire. Il s’exprime en octets et ne peut
être modifié dynamiquement."
•  Alloue une zone qui conserve les données à insérer
dans les fichiers Redo-Log. Augmenter cette zone
permet de réduire les entrées/sorties des fichiers
redo-log. En général, une valeur haute de ce
paramètre réduit les E/S des Redo-log,
particulièrement si les transactions sont longues et
nombreuses."
64
Utilisation de la mémoire par Oracle Shared Pool
•  Cette zone mémoire se découpe en 2 éléments : "
•  La Library Cache. Une zone mémoire qui va stocker 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. Cette
zone mémoire sera utilisée lorsqu'une requête sera exécutée
plusieurs fois, car Oracle n'aura plus alors à recréer la version
compilée de la requête ainsi que son plan d'exécution car ceux-ci
seront disponible en mémoire."
•  Le Dictionnary Cache. Une zone mémoire qui va contenir les
informations (dictionnaire des données) sur les objets de la
base de données ainsi que sur les droits et privilèges
accordés aux utilisateurs. Cette zone mémoire permettra au
serveur Oracle de ne pas avoir à aller chercher ces informations
sur le disque à chaque exécution d'une requête SQL"
65
Utilisation de la mémoire par Oracle Shared Pool : paramètres
•  Le paramètre SHARED_POOL_SIZE dimensionne
cette zone mémoire. Il s’exprime en octets et il peut
être modifié dynamiquement."
•  Cette zone mémoire est utilisée pour conserver les
plans d'exécution des requêtes ainsi que les
procédures PL/SQL traitées par le serveur. Elle peut
être diminuée si l’utilisation du PL/SQL est faible, car
c'est une des zones qui occupe le plus d'espace. "
66
Utilisation de la mémoire par Oracle Java Pool
•  Oracle propose un environnement Java intégré à la
base. Il nécessite de la mémoire pour fonctionner.
L’installation du moteur Java dans la base Oracle est
facultative. "
•  Cette zone comme son nom l'indique est utilisée pour
les applications Java dans la base de données.
67
Utilisation de la mémoire par Oracle Java Pool : paramètres
•  La mémoire allouée en SGA pour le fonctionnement du
moteur Java est précisée par la variable
JAVA_POOL_SIZE. Cette valeur est fixée à 50 Mo
minimum lors de l’installation du catalogue Java, mais
elle peut être augmentée ou diminuée par la suite."
•  Si la machine virtuelle Java intégrée à Oracle n’est
pas utilisée, ce paramètre peut être mis à zéro.
68
Utilisation de la mémoire par Oracle Large Pool
•  Le large pool est une zone mémoire optionnelle de la
SGA configurée uniquement dans un environnement
serveur partagé. Quand un utilisateur se connecte à
un serveur partagé, Oracle a besoin d’allouer de
l’espace supplémentaire dans les shared pool pour
stocker les informations de connexions entre les
différents processus utilisateurs. Le large pool permet
donc d’alléger certaines zones mémoires de la SGA."
•  LARGE_POOL_SIZE : La taille du large pool, par
défaut est 0.
69
Utilisation de la mémoire par Oracle La zone SGA : composants
• Pour visualiser la taille de la SGA:"
•  show sga;"
Total System Global Area 171966464 bytes"
Fixed Size
787988 bytes"
Variable Size
145750508 bytes"
Database Buffers
25165824 bytes"
Redo Buffers
262144 bytes"
•  select * from v$sga;"
NAME
VALUE"
-----------------------------"
Fixed Size
787988"
Variable Size
145750508"
Database Buffers
25165824"
Redo Buffers
262144"
"
•  Fixed Size : Mémoire réservée à l’ensemble des variables qui
comportent les valeurs des différents paramètres d’initialisation."
•  Variable Size : Shared Pool + Large Pool + Java Pool
70
Utilisation de la mémoire par Oracle La zone SGA : paramètres
SGA_TARGET"
• Ce paramètre est situé dans le fichier d’initialisation. Sa
valeur par défaut est sga_target = 0."
"Automatic Shared Memory Management (ASMM) : désactivé"
• Si sga_target>0 ==> ASSM : activé. "
• Ce paramètre permet de répartir automatiquement un
espace mémoire disponible entre le buffer de
données, la Shared Pool, la Large Pool et la Java
Pool. C’est une fonction très utile pour des bases dont
l’activité variable peut charger temporairement l’une
de ces zones mémoire."
71
Utilisation de la mémoire par Oracle La zone SGA : paramètres
• Pour utiliser ASMM, il suffit :"
•  de donner une taille mémoire à sga_target (autre que 0). "
•  de positionner les valeurs (taille minimale) définissant les
buffers de données, la Shared Pool, la Java Pool, etc., à 0.
Si une valeur autre que 0 est conservée, ASMM
l’interprétera comme une taille minimale de mémoire à
conserver pour cette zone."
SGA_MAX_SIZE"
• La taille maximale de mémoire SGA indique la quantité de
mémoire allouée lors du démarrage de la base de
données. En spécifiant la taille maximale de mémoire
SGA, il est possible de modifier ultérieurement et de façon
dynamique la taille totale de mémoire SGA.
72
Utilisation de la mémoire par Oracle La zone PGA
•  C e t t e
zone mémoire est allouée pour le
fonctionnement de chaque thread utilisateur."
•  Dans une configuration serveur dédié, une connexion
à une base Oracle engendre la création d’un thread
utilisateur et de sa mémoire associée, la PGA."
•  La PGA est toujours située sur le serveur qui
héberge la base de données. Elle stocke des
informations concernant les variables utilisées, la
session utilisateur et l’état des transactions en
cours. Elle contient également la zone mémoire
dans laquelle s’effectue le tri des données.
73
Utilisation de la mémoire par Oracle La zone PGA : paramètres
•  Depuis Oracle 10g, la gestion de la taille de la PGA
peut être automatique :"
•  PGA_AGGREGATE_TARGET donne la mémoire
maximale que vous allouez pour l’ensemble des PGA
utilisateurs."
•  Lorsque de nombreux utilisateurs accèdent à cette base,
la multiplication des threads utilisateur et de chacune de
leur PGA conduit à une surcharge du système."
•  L’administrateur d’une base Oracle n’a pas à se
soucier de l’existence et du fonctionnement de la PGA
(c’est pourquoi elle est si peu connue !).
74
Utilisation de la mémoire par Oracle Atelier pratique 2
•  Livret des exercices "
•  Outils utilisés : "
•  SQL*Plus"
•  OEM
75
Chapitre 3
Les processus d’Oracle
•  Côté client"
•  Processus utilisateur (ou client)"
"
•  Côté Serveur"
•  Processus Serveur (thread utilisateur)"
•  Thread dédié à un processus utilisateur"
•  Thread partagé entre plusieurs processus utilisateurs"
•  Processus Background (threads de Oracle.exe)"
•  Les threads indispensables "
•  Les threads optionnels "
76
Les processus d’Oracle Processus utilisateur
•  Il fonctionne sur la machine du client ou le serveur de
traitement"
•  Il démarre lors de l'appel de l'outil ou de l'application"
•  SQL*Plus, Oracle Entreprise Manager, etc."
•  Il se termine lorsque l'utilisateur quitte ou interrompt l’outil
ou l’application"
•  Il appelle le serveur Oracle (serveur de données)
77
Les processus d’Oracle Processus serveur (Thread utilisateur)
•  Il fonctionne sur la machine serveur de données"
•  Suite à la demande du processus utilisateur, le
processus serveur lit les données des fichiers à
l'intérieur du buffer de données"
•  Il envoie les résultats au client"
•  Il cherche des blocs clean pour y charger les données
qu’il faut (à la suite d’une interrogation utilisateur :
SELECT) à partir des fichiers de données.
78
Les processus d’Oracle Processus serveur/Processus utilisateur
•  Dans une configuration de serveur dédié"
•  1 processus utilisateur <--> 1 processus serveur"
• Thread dédié à un utilisateur (Thread utilisateur dédié)"
•  Nb de processus serveur = Nb de processus utilisateur"
"
•  Dans une configuration de serveur partagé"
•  Partage de processus serveur"
•  k processus utilisateurs <--> 1 processus serveur "
"
•  Le processus serveur utilise une PGA exclusive
79
Les processus d’Oracle Illustration
80
Les processus d’Oracle Les threads indispensables
Ce sont ces threads qui relient les fichiers de la base de
données, la zone mémoire réservée à l’instance
(SGA), ainsi que la mémoire allouée à chaque
processus serveur (PGA)"
•  DBW0/DBWR (Database Writer) "
•  LGWR (Log Writer) "
•  CKPT (Checkpoint) "
•  PMON (Process Monitor) "
•  SMON (System Monitor)
81
Les processus d’Oracle Les threads indispensables
•  Visualisation des threads indispensables :"
• Oracle Administration Assistant for Windows"
"
"
"
"
"
"
"
"
• select * from v$bgprocess;
82
Les processus d’Oracle Les threads indispensables
DBW0 (Database Writer)"
• Il transfert les blocs de données modifiés du database buffer
de la SGA dans les fichiers de données. Dès qu’un ordre
SQL de type INSERT, UPDATE, DELETE intervient, il
travaille prioritairement avec les buffers de données en
mémoire, pour plus de performance. "
• Le nombre de blocs existants en mémoire est fixe.
=>Chaque buffer modifié diminue le nombre de buffers
libres disponibles. Dans ce cas, si un ordre SQL a besoin
de lire des données depuis les fichiers de données pour les
placer dans les buffers de données, un algorithme LRU
(Least Recently Used) écrit les plus anciennes données
modifiées sur le disque pour libérer de la place mémoire.
83
Les processus d’Oracle Les threads indispensables
DBW0 (Database Writer)"
• Ce processus est déclenché par les événements :"
•  Un processus serveur ne trouve pas de blocs clean après
qu’il ait scanné le Database Buffer."
•  Après une certaine période pour faire avancer le point de
reprise. "
•  Le point de reprise est la position dans les fichiers redo-log à partir
de laquelle l’instance est récupérable. Cette position est
déterminée par le plus ancien bloc dans le cache de données."
• Le comportement de DBWR est contrôlé par le paramètre
d’initialisation DB_WRITERS, qui permet de démarrer
plusieurs threads DBWR, afin d’augmenter le taux
d’écriture sur disque dans les systèmes très fortement
sollicités.
84
Les processus d’Oracle Les threads indispensables
LGWR (Log Writer)"
• Dès qu’une transaction est validée, Oracle écrit les données
modifiées à deux emplacements différents, de façon à pouvoir «
repartir » si un problème survient. "
•  La 1ère copie est assurée par le DBWR dans les fichiers contenant
les données. Cette copie n’est pas forcément immédiate : pour
augmenter les performances et éviter des goulots d’étranglement,
un délai d’écriture peut exister."
•  Une seconde copie immédiate est assurée par le LGWR dans les
fichiers redo-log. "
• D ès qu’un COMMIT intervient, le thread LGWR écrit
immédiatement les données modifiées depuis la zone mémoire
redo-log dans les fichiers redo-log."
" --> Toute modification validée est immédiatement écrite sur le
disque, puis la zone mémoire redo-log occupée est libérée.
85
Les processus d’Oracle Les threads indispensables
CKPT (Checkpoint)"
• À intervalles réguliers, toutes les données modifiées et
présentes dans le Database Buffer sont écrites dans
les fichiers de données par le thread DBWR. Cet
événement se nomme un checkpoint. "
• Le thread CKPT signale les checkpoints au thread
DBWR et modifie l’ensemble des fichiers qui
composent la base de données, pour que le numéro
d’ordre du plus récent checkpoint soit inscrit en entête de fichier.
86
Les processus d’Oracle Les threads indispensables
CKPT (Checkpoint)"
• Une opération de CheckPoint est déclenchée par l'un de ces
événements :"
•  Lorsque le nombre maximal de blocs est atteint depuis le dernier
CHECKPOINT (Ce nombre est fixé par le paramètre
LOG_CHECKPOINT_INTERVAL. "
•  Le temps écoulé en secondes depuis le dernier CHECKPOINT
a t t e i n t l a v a l e u r d é fi n i e d a n s l e p a r a m è t r e
LOG_CHECKPOINT_TIMEOUT. Pour désactiver ce paramètre il
suffit de le mettre zéro."
•  Manuellement avec la commande : ALTER SYSTEM
CHECKPOINT"
87
Les processus d’Oracle Les threads indispensables
PMON (Process Monitor)"
•  Le thread PMON (Process Monitor) nettoie les
transactions défaillantes, comme celles d’un poste
distant arrêté brutalement durant une transaction. Ce
nettoyage libère les zones mémoire allouées, supprime
les verrous posés par les transactions et annule les
ressources affectées aux threads de la transaction."
• Chaque connexion à une base Oracle consomme
quelques mégaoctets de mémoire et du temps
processeur. Si un utilisateur arrête brutalement son PC au
cours d’une longue requête SQL, il peut ainsi bloquer
inutilement un ensemble de ressources.
88
Les processus d’Oracle Les threads indispensables
SMON (System Monitor)"
• Le thread SMON (System Monitor) surveille la base de
données lors de son démarrage puis au cours de son
fonctionnement."
•  Il vérifie si le dernier arrêt a été correctement effectué. Si tel
est le cas, il ne fait rien. Mais en cas d’arrêt brutal, il existe
certainement des transactions en cours qui n’ont été ni
validées, ni annulées. SMON annule les données en attente
de validation. "
•  Enregistrements validés : SMON récupère dans les fichiers
redo-log ceux qui ont été modifiés (par un COMMIT ou un
ROLLBACK) mais n’ont pas encore été écrits dans la base
Oracle, et ce afin de les y insérer. !
89
Les processus d’Oracle Les threads indispensables
SMON (System Monitor)"
•  SMON peut aussi être appelé par d’autres threads. "
•  SMON vérifie que des espaces libres subsistent dans les
fichiers de la base de données. Dans ce cas, il essaie de
les regrouper. En effet, même si on dispose de beaucoup
de place dans un fichier de la base, cet espace peut
ressembler à du « gruyère »."
•  Le fonctionnement de SMON est automatique : aucune
action de l’administrateur de la BD n’est requise.
C’est l’un des points forts d’Oracle par rapport à ses
concurrents."
•  Si SMON s’arrête, il faut redémarrer l’instance Oracle.
90
Les processus d’Oracle Les threads optionnels
ARC ou ARCn "
•  Le thread ARC n’existe que si la base de données est en
mode ARCHIVELOG. Il est responsable de la copie des
fichiers redo-log, lorsqu’ils sont saturés, vers leur
destination de stockage. "
•  Si la base est en mode ARCHIVELOG sans démarrer le
thread ARC, la base de données se bloquera dès qu’elle
aura besoin « d’historiser » un fichier redo-log saturé. Un
message sera alors écrit dans le fichier d’alerte."
•  Pour démarrer le thread ARC, le paramètre d’initialisation
LOG_ARCHIVE_START doit avoir pour valeur TRUE et
la base de données doit être en mode ARCHIVELOG.
91
Les processus d’Oracle Les threads optionnels
Listener "
• Le listener Oracle n’est généralement pas compris
dans la liste des processus indispensables au
fonctionnement d’Oracle. Pourtant, il doit être lancé
pour permettre d’établir des connexions client-serveur
avec la base de données."
• Son lancement n’est pas lié à celui de la base de
données et il possède ses propres fichiers de
configuration.
92
Configurer un processus d’écoute
Les processus d’Oracle
•  L’utilitaire Assistant Configuration Oracle Net est accessible depuis
le menu :"
•  Démarrer>Programmes>Oracle–... >Configuration and Migration
Tools."
93
Configurer un processus d’écoute
Les processus d’Oracle
94
Les processus d’Oracle
Atelier pratique 3
•  Livret des exercices "
•  Outils utilisés : "
•  SQL*Plus"
•  Oracle Administration Assistant for Windows
95
Chapitre 4
Les fichiers d’une base Oracle
"
"
"
"
"
• Les fichiers de données"
• Les fichiers redo-log "
• Les fichiers d’initialisation "
• Les fichiers de contrôle "
• L’architecture OFA proposée par Oracle
96
Les fichiers d’une base Oracle Les fichiers de données
•  Les fichiers de données (.dbf) contiennent toutes les
informations de la base dans un format spécifique à
Oracle. Il n’est pas possible d’en visualiser le
contenu avec un éditeur de texte. "
•  Le seul et unique moyen pour accéder et manipuler des
données stockées dans Oracle est d’utiliser SQL."
•  Les fichiers de données contiennent deux types
d’informations : celles du dictionnaire de données et
celles des utilisateurs.
97
Les fichiers d’une base Oracle Les fichiers de données
•  L’administrateur peut visualiser le nom des fichiers de
données dans la base en interrogeant la vue V
$datafile ou dba_data_files"
•  Name : nom du fichier"
•  Bytes : taille en octets"
•  Creation_date : date de création du fichier"
•  Block_size : taille du bloc"
•  Db_files (paramètre d'initialisation) : nombre maximal
de fichiers de données gérés dans l’instance"
"
98
Les fichiers d’une base Oracle Les fichiers redo-log
•  Les fichiers redo-log sont des fichiers de journalisation
utilisés pour le fonctionnement interne d’Oracle, ils
enregistrent et conservent toutes les modifications
successives de la base de données. Ils sont utiles lors
d’une récupération à la suite d’un problème d’instance
ou de disque. Cette récupération consiste à rejouer le
contenu des fichiers redo-log dans la base.
99
Les fichiers d’une base Oracle Les fichiers redo-log
• Avant la réutilisation d’un fichier redo-log, Oracle le sauvegarde à
un autre emplacement si la base fonctionne en mode archivelog
(ARCHIVELOG). Dans les autres cas, Oracle le réemploie en
supprimant son contenu sans sauvegarde préalable."
"
• Pour des raisons de sécurité, ces fichiers peuvent être multiplexés
(dupliqués) dans des groupes (Groups). Si chaque membre d’un
groupe est situé sur un disque différent, cela évite une perte
d’informations en cas de panne d’un disque.
100
Les fichiers d’une base Oracle Les fichiers redo-log archivés
•  Lorsqu’une base est en mode ARCHIVELOG, les fichiers
redo-log sont archivés à mesure de leur remplissage."
•  Si le répertoire de destination des archives est saturé, la base
de données se bloque et un message s’inscrit dans le fichier
d’alerte. "
•  L’emplacement et le nettoyage régulier des anciens
fi c h i e r s a r c h i v é s r e p r é s e n t e n t l e s p r i n c i p a l e s
préoccupations de l’administrateur d’une base en mode
ARCHIVELOG.
101
Les fichiers d’une base Oracle Gestion des fichiers redo-log
Informations sur le mode ARCHIVELOG"
•  V$DATABASE (log_mode): identifie si la base de données
est en mode ARCHIVELOG ou NOARCHIVELOG "
•  V$ARCHIVED_LOG : affiche des informations issues du
fichier de contrôle concernant les fichiers redo-log
archivés "
•  V$ARCHIVE_DEST : décrit la destination des fichiers
archivés "
•  V$LOG et V$LOGFILE : décrivent les fichiers redo-log et
indiquent celui qui est actif
102
Les fichiers d’une base Oracle Gestion des fichiers redo-log
• Comment activer l’archivage des fichiers redo-log ?"
1. Démarrer le thread ARC (log_archive_start) et définir un
répertoire destination (log_archive_dest) des fichiers de
journalisation archivés.!
•  log_archive_start = true"
•  log_archive_dest = ‘chemin+nom du répertoire’"
"
2. Arrêtez la base. Le thread ARC sera lancé aux prochain
démarrage."
"
3. Démarrez la base en mode mount avec SQL*Plus et
positionnez-la en mode archivelog :"
•  shutdown immediate ;"
•  startup mount;"
•  alter database archivelog ;"
•  alter database open;
103
Les fichiers d’une base Oracle Gestion des fichiers redo-log
• Comment basculer la base en mode NOARCHIVELOG ?"
•  alter database noarchivelog ;"
"
• Comment ajouter des fichiers redo-log à un groupe ?"
•  alter database add logfile member 'chemin+nom du redo-
log ' to group n°groupe; "
"
• Comment ajouter un groupe de fichiers redo-log ?"
•  ALTER DATABASE ADD LOGFILE GROUP n°groupe
'chemin+nom du redo-log' size nombreM;
104
Les fichiers d’une base Oracle Gestion des fichiers redo-log
•  Comment supprimer un groupe de fichiers redo-log ?"
• ALTER DATABASE DROP LOGFILE GROUP n°groupe;"
"
•  Comment supprimer un fichier redo-log ?"
• ALTER DATABASE DROP LOGFILE MEMBER 'chemin
+nom du redo-log ';
105
Les fichiers d’une base Oracle Les fichiers d’initialisation
•  Toute instance Oracle nécessite des paramètres
d’initialisation utilisés à chaque démarrage. Ils peuvent
être conservés sous forme d’un fichier texte (initSID.ora)
ou enregistrés dans un fichier d’initialisation persistant
(spfileSID.ora)."
•  instance_name : nom de l’instance qui doit correspondre
à la variable d’environnement ORACLE_SID. Souvent
nommé SID."
•  db_name : nom interne de la base de données. Il est
recommandé d’attribuer à la base de données et au SID
le même nom.
106
Les fichiers d’une base Oracle Les fichiers d’initialisation
• control_files : noms et emplacements des fichiers de contrôle
de la base. Ils sont tous identiques et doivent être
disponibles à chaque démarrage. Si l’un d’eux est perdu
(destruction, défaillance disque...), il faut le supprimer de la
liste des fichiers pour démarrer l’instance ;"
• db_files-db_block_size-sga_target-db_cache_size-db_block_buffers-
log_buffer-shared_pool_size-large_pool_size-java_pool_sizepga_aggregate_target-sessions"
"
• Créer un fichier d’initialisation à partir d’un autre :"
•  create pfile='D:\oracle\product\...\admin\SID\pfile\initSID.ora'"
!
from spfile='D:\oracle\product\...\Db_1\database\spfileSID.ora';
107
Les fichiers d’une base Oracle Les fichiers de contrôle
•  Les fichiers de contrôle sont créés en même temps que la base
de données. Ils sont principalement utilisés à chaque
démarrage de celle-ci, puis mis à jour automatiquement par
Oracle. "
•  Il est possible de créer plusieurs fichiers de contrôle, mais ils
sont tous identiques."
•  Le fichier d’initialisation utilisé pour lancer l’instance situe
l’emplacement des fichiers de contrôle. Chacun d’eux
précise la localisation de tous les autres fichiers (données
et redo-log) qui composent la base."
•  Les fichiers de contrôle indiquent si la base de données a été
correctement fermée et si une récupération est nécessaire. Il
est impossible de les visualiser pour en exploiter le contenu.
108
Les fichiers d’une base Oracle Gestion des fichiers de contrôles
• La vue V$CONTROLFILE permet de connaître la liste des fichiers de contrôle d’une
instance en cours de fonctionnement."
"
"
"
"
"Ou « SHOW PARAMETER CONTROL_FILES »"
• Sauvegarder un fichier de contrôle d’une base « en marche »"
•  ALTER DATABASE BACKUP CONTROLFILE TO 'CHEMIN+NOM DU
CONTROL FILE' ;"
•  ALTER DATABASE BACKUP CONTROLFILE TO TRACE;"
•  Cette commande crée un fichier en format texte dans le répertoire UDUMP,
qu'on peut modifier pour reconstruire un nouveau fichier de contrôle. "
"
109
Les fichiers d’une base Oracle Gestion des fichiers de contrôles
• Multiplexage des fichiers de contrôle en utilisant le spfile"
"1. Ajouter le nouveau fichier de contrôle dans le paramètre CONTROL_FILES en
utilisant « Alter system … scope=spfile »"
"2. Arrêter la base"
"3. Copier le fichier de contrôle en utilisant les commandes OS"
"4. Démarrer la base"
• Multiplexage des fichiers de contrôle en utilisant le pfile"
"1. Arrêter la base"
"2. Modifier le paramètre CONTROL_FILES dans le fichier initSID.ora (pfile)"
"3. Copier le fichier de contrôle en utilisant les commandes OS"
" 4. Démarrer la base"
110
Les fichiers d’une base Oracle Les fichiers d’alertes
•  Chaque base Oracle possède son fichier d’alerte. Il ajoute des
informations durant la vie d’une base. Pendant le démarrage de la
base, si le fichier d’alerte n’existe pas, Oracle en crée un pour y
écrire des informations.
111
Les fichiers d’une base Oracle Architecture OFA
•  OFA (Optimal Flexible Architecture). C’est un ensemble de règles
d’installation et de configuration qui vous donneront des bases Oracle
rapides, fiables, faciles à installer et nécessitant peu de maintenance."
•  Pour une meilleure organisation, OFA propose de ne pas mélanger les
fichiers des bases de données avec ceux des logiciels Oracle."
•  Pour bien séparer les fichiers de données des logiciels Oracle
(ORACLE_BASE\db_1\bin), les fichiers de données sont placés
directement sous ORACLE_BASE\oradata."
112
Les fichiers d’une base Oracle Architecture OFA
•  Comment nommer les fichiers des bases de
données ?
113
Les fichiers d’une base Oracle Architecture OFA
• Où placer les fichiers d’administration des bases ? (ex.
Oracle10g)"
"
"
"
"
• Pour bien structurer les fichiers d’administration de chaque base,
Oracle crée sous ORACLE_BASE\admin\SID les répertoires :"
•  \bdump : trace des threads de la base "
•  \cdump : fichier d’erreur du « noyau Oracle » "
•  \create : fichiers de création de la base "
•  \exp : fichiers d’export de la base (optionnel, à ajouter) "
•  \pfile : fichier d’initialisation de la base (initSID.ora) "
•  \udump : fichiers trace d’ordres SQL
114
Les fichiers d’une base Oracle Atelier pratique 4
•  Livret des exercices "
•  Outils utilisés : "
•  SQL*Plus"
•  OEM
115
Chapitre 5
Gestion des tablespaces
•  Structure logique : Concepts"
•  Tablespace"
•  Création "
•  Modification"
•  Suppression"
•  Ajout de fichiers"
•  Suppression de fichiers"
•  …
116
Gestion des tablespaces
Structure logique : Concepts
•  Extension (Extent) = ensemble de blocs contigus"
•  Segment = ensemble d’extensions."
•  Fichier = ensemble de segments"
Entête du segment 1"
Liste de tous les extents "
Taille de tous les extents
117
Gestion des tablespaces
Structure logique : Concepts
•  Types de segment"
•  Segment de données (TABLE) comporte l’ensemble
des données d’une table."
•  Segment d’index (INDEX) comporte un index."
•  Segment temporaire (SORT) où sont effectués les tris."
•  Segment d’annulation (ROLLBACK) comporte les
données en attente de validation ou d’annulation."
118
Gestion des tablespaces
Structure logique : Concepts
•  A chaque tablespace sont associés un ou plusieurs fichiers. Tout objet
(table, index, …) est placé dans un tablespace, sans précision du
fichier de destination. Le tablespace effectue ce lien.
119
Gestion des tablespaces
Tablespaces : exemple
Tablespace "
utilisateur
120
Gestion des tablespaces Tablespaces : caractéristiques
•  Pour connaître les caractéristiques de chaque
tablespace, les vues utiles du dictionnaire de données
sont :"
•  SYS.DBA_DATA_FILES : détail des fichiers de
données."
•  SYS.DBA_TEMP_FILES : détail des fichiers des
tablespaces temporaires."
•  SYS.DBA_TABLESPACES : détail des tablespaces."
•  SYS.DBA_FREE_SPACE : espace disponible restant
dans les tablespaces.
121
Gestion des tablespaces
Création de tablespace : Mode Local
• La gestion locale stocke tous les aspects d’allocation de segments à
l’intérieur de chaque tablespace."
"
• Création d’un tablespace"
• 
• 
• 
• 
CREATE TABLESPACE test1"
DATAFILE ‘D:\oracle\product\10.1.0\oradata\TEST\test1_01.dbf’ SIZE 20M"
EXTENT MANAGEMENT LOCAL !
DEFAULT STORAGE ( INITIAL 100 K"
!
!
!
NEXT 100 K"
!
!
!
MINEXTENTS 2"
!
!
!
MAXEXTENTS 100"
!
!
!
PCTINCREASE 0 );"
"
• INITIAL précise la taille de la première extension. "
• NEXT précise la taille de la deuxième extension. Lorsque la première
est remplie, Oracle alloue automatiquement une seconde de NEXT
octets. Toutes les extensions futures se fondent sur cette valeur."
• PCT_INCREASE est un paramètre qui dirige l’incrément de taille des
extensions suivantes. Par exemple, INITIAL = 1 Mo, NEXT = 1 Mo et
PCT_INCREASE = 50 %. La première extension sera de 1 Mo, la
deuxième de 1 000 + 50 % (1 000) = 1,5 Mo, la troisième de 2,25 Mo,
etc.
122
Gestion des tablespaces Options du mode local
•  CREATE TABLESPACE test1"
•  DATAFILE 'oracle_home\oradata\TEST\test1_01.dbf' SIZE 20M"
•  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;"
• AUTOALLOCATE : c’est l’option par défaut. Oracle gère automatiquement
toutes les allocations d’espace dans le tablespace. Elles peuvent être de
tailles différentes ;"
•  CREATE TABLESPACE test1"
•  DATAFILE 'oracle_home\oradata\TEST\test1_01.dbf' SIZE 20M"
•  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;"
• UNIFORM SIZE : toute allocation d’espace sera de cette taille (minimum 1
Mo). Le tablespace TEMPORARY utilise cette option par défaut. "
"
•  SPACE MANAGEMENT AUTO|MANUAL;
123
Gestion des tablespaces
Création de table
CREATE TABLE emp ("
..."
liste des colonnes de la table..."
..."
)"
STORAGE ("
"INITIAL 100K"
"NEXT 100 K"
"PCTINCREASE 0 )"
"TABLESPACE user_data;
124
Gestion des tablespaces
Clause Storage d’une table
•  L’ordre SQL suivant visualise le nombre d’extensions de tous les
segments propriété de l’utilisateur SYS."
•  select segment_name,tablespace_name,
segment_type,extents,max_extents,bytes,owner"
•  from sys.dba_segments!
•  where owner = ‘SYS’"
"
•  L’ordre SQL suivant fournit le détail de chacune des extensions de la
table TEST de l’utilisateur SYS."
•  select owner, segment_name, segment_type, tablespace_name,
extent_id, bytes!
•  from sys.dba_extents!
•  where segment_name = ‘TEST’ and owner = ‘SYS'
125
Gestion des tablespaces
Clause Storage d’une table
•  L’ordre SQL suivant donne le détail de la clause STORAGE de la table
TEST."
•  select owner, table_name, initial_extent, next_extent, min_extents,
max_extents, pct_increase"
•  from sys.dba_tables!
•  where table_name = ‘TEST’ and owner = ‘SYS’"
126
Gestion des tablespaces Opérations sur les tablespaces
•  Définir un tablespace par défaut"
•  Modifier un tablespace"
•  Renommer un tablespace"
•  Supprimer un tablespace
127
Gestion des tablespaces Définir un tablespace par défaut
•  Oracle apporte la notion de tablespace par défaut pour
toute la base. Il suffit de l’indiquer une seule fois au
plus haut niveau :"
•  ALTER DATABASE"
•  DEFAULT TABLESPACE tablespace_utilisateur;"
•  Tout nouvel utilisateur créé qui n’aura pas de
tablespace par défaut utilisera alors celui précisé au
plus haut niveau. C’est un avantage qui limite les
erreurs lors de la création d’utilisateurs.
128
Gestion des tablespaces Modifier/Renommer un tablespace
•  Modifier un tablespace"
•  Un tablespace peut être online (actif, accessible à tous les
utilisateurs) ou offline (inactif, inaccessible aux
utilisateurs, même administrateur)."
•  alter tablespace user_data online ;"
•  Renommer un tablespace"
•  A l’exclusion des tablespaces SYSTEM et SYSAUX
réservés par Oracle."
•  alter tablespace test rename to test1 ;
129
Gestion des tablespaces Supprimer un tablespace
• Pour supprimer un tablespace ne contenant aucun
segment de données ou d’index, l’ordre DROP
TABLESPACE suffit :"
•  drop tablespace test ;"
"
• Si le tablespace contient des tables et des index, vous
devez le préciser sinon l’ordre SQL précédent échoue."
•  drop tablespace test including contents ;"
•  drop tablespace test including contents and datafiles ;"
130
Gestion des tablespaces Gestion des fichiers d’un tablespace
Ajouter un fichier de données"
• Il existe deux possibilités : la première consiste à créer un nouveau
tablespace et à lui associer le nouveau fichier, la seconde à ajouter un
nouveau fichier à un tablespace existant."
• alter tablespace user_data"
"add datafile ’...\oradata\TEST\user02.dbf' size 50M;"
• NB : Il est possible d’ajouter un fichier à un tablespace mais il est
impossible de supprimer un seul fichier d’un tablespace. Il faut alors
supprimer le tablespace entier."
"
Modifier la taille d’un fichier de données"
• alter database datafile ’...\oradata\TEST\user_01.dbf'"
"resize 100M"
131
Gestion des tablespaces Gestion des fichiers d’un tablespace
Extension automatique d’un fichier de données"
• Que faire lorsqu’un segment (de données, d’index, de tri, de
rollback) cherche à s’étendre dans un tablespace et ne dispose
plus de place dans les fichiers pour y créer un nouvel extent ?"
Oracle permet que les fichiers des tablespaces augmentent
automatiquement de taille :"
• 
• 
• 
• 
• 
alter database"
datafile ‘...\oradata\TEST\user01.dbf'"
autoextend on"
next 25M"
maxsize 2000M; "
• D ans cet exemple, la taille du fichier peut augmenter
automatiquement, par palier de 25 Mo jusqu’à la limite
maximale de 2 000 Mo
132
Gestion des tablespaces Gestion des fichiers d’un tablespace
Déplacer/renommer les fichiers de données"
1. Positionnez le tablespace concerné offline ;"
•  alter tablespace users offline;"
Espace de tables (TABLESPACE) modifié."
2. Copiez/renommez sous Windows les anciens fichiers dans leur
nouvelle destination."
3. Indiquez au dictionnaire de la base ce changement
d’emplacement :"
•  alter tablespace users rename datafile"
•  ‘c:\oracle\oradata\TEST\user_old.dbf'"
•  to ‘d:\oracle\oradata\TEST\user_new.dbf' ;"
4. Mettez le tablespace concerné online :"
•  alter tablespace users online;"
Espace de tables (TABLESPACE) modifié."
5. Vérifiez par la vue DBA_DATA_FILES la prise en compte des
modifications, puis supprimer sous Windows l’ancien fichier.
133
Tablespace temporaire (Temporary)
Gestion des tablespaces
•  Si un tablespace temporaire n’est pas créé, les tris
sont effectués dans le tablespace SYSTEM qui
contient le dictionnaire. Cela n’est pas son rôle et il
convient de toujours créer un tablespace temporaire."
•  create temporary tablespace temp_data!
"
•  tempfile ’...\oradata\TEST\temp_data01.dbf’ size 50M
"
•  extent management local ;
•  Une fois le tablespace temporaire créé, Oracle l’utilisera pour tous les
tris des utilisateurs, sans qu’il soit nécessaire d’indiquer pour chaque
utilisateur quel est son tablespace de tri."
"
•  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
•  Après avoir exécuté cette commande, tous les utilisateurs créés après
utiliseront le tablespace temporaire indiqué.
134
Gestion des tablespaces Tablespace temporaire (Temporary)
• Ajouter un fichier temporaire"
•  alter temporary tablespace temp_data"
"
"add tempfile ’...\oradata\TEST\temp_data02.dbf' "
"
"size 50M"
"
• L es vues V$TEMPFILE et DBA_TEMP_FILES
permettent d’accéder à toutes les caractéristiques des
tablespaces temporaires."
135
Gestion des tablespaces Gestion des données en attente ou d’annulation
•  Le fichier d’initialisation de la base doit préciser le mode de
gestion des données en attente de validation ou d’annulation
choisi. Le paramètre UNDO_MANAGEMENT accepte les
valeurs :"
"
• UNDO_MANAGEMENT=AUTO : la gestion par tablespace
UNDO est retenue. Le paramètre UNDO_TABLESPACE doit
alors indiquer quel tablespace de type UNDO il doit utiliser ;"
"
• UNDO_MANAGEMENT=MANUAL : la gestion par rollback
segment est retenue. (Tablespace System)"
136
Gestion des tablespaces Tablespace d’annulation (UNDO)
•  Un tablespace de type UNDO est un nouveau type de
tablespace destiné à remplacer l’ancien mode, les
rollback segments. C’est dans cet espace que sont
gérées par Oracle les données en attente de
validation ou d’annulation."
•  Create UNDO TABLESPACE UNDOTBS"
•  DATAFILE 'c:\oracle\oradata\TEST\undotbs01.dbf‘"
•  SIZE 50M"
•  AUTOEXTEND ON"
•  NEXT 5120K"
•  MAXSIZE 1000M
137
Gestion des tablespaces Rollback segment
Création d’un rollback segment"
•  create public rollback segment rb1"
•  Storage (initial 200K"
•  next 200K)"
•  tablespace system;"
"
• Dès qu’il est créé, le rollback segment est OFFLINE,
c’est-à-dire qu’aucune transaction ne peut l’utiliser. "
•  alter rollback segment RB1 online ;
138
Gestion des tablespaces Rollback segment
Visualiser les rollback segments"
• select segment_name, owner, tablespace_name,"
• initial_extent , next_extent,pct_increase,status"
• from sys.dba_rollback_segs;!
!
Suppression d’un rollback segment"
• alter rollback segment RB1 offline ;"
• drop rollback segment RB1 ;
139
Atelier pratique 5
Gestion des tablespaces
•  Livret des exercices"
•  Outils utilisés : "
•  SQL*Plus"
•  OEM
Chapitre 6
Gestion des utilisateurs et de la sécurité
•  Création des profils"
•  Création des rôles"
•  Création des utilisateurs
141
Création des profils
Gestion des utilisateurs et de la sécurité
• Un PROFIL est un ensemble de limitations. Une fois qu'un PROFIL a été
assigné à un utilisateur celui-ci ne pourra plus dépasser les limitations
imposées."
"
• Deux types de limitations:
"
•  Les limitations du mot de passe (onglet MOT DE PASSE de EM)"
•  Les limitations des ressources système (onglet GENERAL de EM)"
"
• Mise en place
"
•  Etablir les limitations de mot de passe et les limitations système."
•  Créer le profil"
•  Attribuer le profil aux utilisateurs qui devront être limités."
"
Création des profils
Limitations système
Gestion des utilisateurs et de la sécurité
Administration>…>Profils>Bouton « Créer »
La valeur DEFAULT: Oracle
ira récupérer la valeur de la
limitation dans le profil
DEFAULT.
Nom du profil
à
à
Le temps de processeur max en centièmes de secondes
qu'une session pourra utiliser.
-Le temps de processeur max en centièmes de secondes qu'un "appel
serveur" pourra utiliser. "
-Le temps (min) pour la durée de connexion max d'une session. "
-Le temps (min) pour la durée d'inactivité maximale d'une session.
Le nombre de session max qu'un utilisateur pourra ouvrir.
à
à
-Le nombre max de bloc lus durant une session."
-Le nombre max de bloc lus durant un «appel serveur» "
-La taille que pourra utiliser une session"
-Le coût total des limitations autorisées pour une session
Création des profils
Limitations Mot de passe
Gestion des utilisateurs et de la sécurité
Si le paramètre Nb de jours de conservation a été initialisé avec
une valeur numérique, alors le paramètre Nb de PWD à conserver
(PASSWORD_REUSE_MAX) devra être à UNLIMITED et
inversement. Si les deux paramètres possèdent la valeur
UNLIMITED, alors Oracle n'utilisera aucune de ces deux limitations
de mot de passe.
Définir le nom d'une fonction PL/SQL qui servira à vérifier
les mots de passe saisis. Sinon, la valeur NULL.
Définir le nombre maximal de tentatives de connexion. Si
le nombre de connexion est atteint le compte sera alors
verrouillé pendant une période.
Création des rôles
Gestion des utilisateurs et de la sécurité
• Un rôle est un regroupement de privilèges. Une fois créé il peut être assigné à un
utilisateur ou à un autre rôle ."
1. Les privilèges de niveau système"
"Ils permettent la création, modification, suppression, exécution de groupes d'objets . Le
privilège CREATE TABLE, par exemple permet à l'utilisateur qui l’a reçu de créer des
tables."
2. Les privilèges de niveau objet"
" Ils permettent les manipulations sur des objets spécifiques. Les privilèges SELECT,
INSERT, UPDATE, DELETE sur la table SCOTT.EMP par exemple permettent à
l'utilisateur qui les a reçu de sélectionner, ajouter, modifier et supprimer des lignes dans
la table EMP appartenant à l'utilisateur SCOTT."
• Les privilèges objets sont typiquement les droits que peuvent accorder des utilisateurs
sur les objets dont ils sont les auteurs alors que les privilèges système sont réservés au
DBA."
"
• 
Création des rôles
Gestion des utilisateurs et de la sécurité
• Exemples de Rôles"
•  CONNECT : Autorise la connexion à une base Oracle. Parmi les
privilèges associés à cette commande : CREATE TABLE, CREATE VIEW, CREATE SESSION ..."
"
• 
RESOURCE : Permet, en plus de la création de table et de vue,
l'utilisation de trigger et de procédure (CREATE PROCEDURE,
CREATE TRIGGER ...). "
"
•  DBA : Ce rôle regroupe tous les privilèges système pour la gestion des
utilisateurs et de leurs tables.
Création des rôles
Gestion des utilisateurs et de la sécurité
Administration>…>Rôles>Bouton « Créer »"
Nom représente le nom du rôle"
• Authentification"
"
•  Mot de passe : indique qu'un mot de passe est nécessaire pour activer le rôle"
•  EXTERNE indique que l'autorisation provient d'une source externe "
•  Aucune : indique qu'aucun mot de passe n'est nécessaire pour activer le rôle
•  GLOBAL pour un user GLOBAL géré par exemple par Enterprise Directory Service
Création des utilisateurs
Gestion des utilisateurs et de la sécurité
Administration>…>Utilisateurs>Bouton « Créer »
Création des profils
Privilèges système
Gestion des utilisateurs et de la sécurité
•  Ils permettent la création, modification, suppression, exécution de
groupes d'objets (tables, vues, index…) .
Création des profils
Privilèges objet
Gestion des utilisateurs et de la sécurité
•  Ils permettent les manipulations sur des objets spécifiques. On
choisit d’abord un objet et ensuite on définit les privilèges.
Atelier pratique Gestion de la sécurité
•  Livret des exercices"
•  Outil utilisé : OEM