administration des bases de donnees avec oracle

ADMINISTRATION DES BASES DE DONNEES AVEC ORACLE
Chapitre 1 – Architecture d’ORACLE
A – Quel est le rôle d’un Administrateur de Base de Données (ou DBA) ?
L’administrateur de base de données doit :
-
Installer le SGBD (ainsi que les outils associés) et mettre à jour les produits
d’ORACLE
-
Allouer et planifier le stockage disque
-
Créer des utilisateurs
-
Sauver et restaurer la (les) base(s) de données
-
Maintenir la sécurité : l’administrateur doit mettre en place les structures et les
procédures permettant de faire face à tous les incidents et de retrouver la cohérence et
l’intégrité des données.
-
Surveiller et optimiser les performances : l’administrateur doit obtenir les
meilleures performances , pour cela, il doit tenir compte des utilisations qui seront
faites des données
-
Contrôler et surveiller l’accès des utilisateurs : l’administrateur attribue et retire des
privilèges sur les données aux utilisateurs de la base.
B – INSTANCE et BASE DE DONNEES – Définitions
Une instance ORACLE est composée de la System Global Area (SGA) et des processus
Oracle tournant en arrière plan. Elle est définie de façon unique par l’ORACLE_SID ou
l’ORA_SID suivant la plateforme (ce sont des variables du système d’exploitation).
L’instance est indépendante de la base de données mais représente la partie mémoire de cette
dernière. Elle ne peut monter qu’une seule base de données à la fois.
Une base de données ORACLE est composée de un ou plusieurs fichiers de données ,
associés avec deux ou plusieurs fichiers redo log online et un ou plusieurs fichiers de
contrôle. C’est ici que l’on stocke physiquement les informations de la base.
Elle est définie de façon unique par la variable ORACLE db_name dans init.ora (le fichier
de démarrage).
Elle peut être montée par une ou plusieurs intances. Bien entendu les données ne sont
accessibles qu’à la condition que la base soit ouverte.
RELATION INSTANCES – BASE DE DONNEES
Instance SID=TEST
Base de Données
Db_name= TEST
Base de données EXCLUSIVE
APPLICATION 1
APPLICATION 2
SERVEUR 1
SERVEUR 2
INSTANCE
SID = TEST1
INSTANCE
SID = TEST2
Base de données
Db_name=TEST
Base de données PARTAGEE
Architecture des processus et de la mémoire ORACLE
SGA
Process
Serveur
dédié
Process
serveur
partagé
Database
Buffer
Cache
Redo Log
buffer
LGWR
SMON
DBWR
RECO
Shared pool
ARCH
Java
pool
PMON
CKPT
Processus d’arrière
plan
Dispatcher D000
Processus
utilisateur
Processus listener
Fichiers de stockage
offline
Fichiers
redo log
Fichiers de
contrôle
Data
segment
Rollback
segment
Base de données
C – La mémoire
C – 1 Les zones réservées au code de l’applicatif.
Ces espaces pour le code des programmes en cours d’exécution sont accessibles uniquement
en lecture. Elles peuvent être partagées ou exclusives. On y trouve le code du noyau, des
outils ORACLE (SQL*Plus, ORACLE*FORMS, etc…) et des programmes d’applications
faisant appel à ORACLE. Plusieurs processus peuvent accéder au même segment de mémoire.
On évite, avec un tel partage d’avoir des copies multiples de code, d’où un gain d’espace
mémoire et de performance.
C – 2 La SGA (System Global Area)
Egalement appelée ‘shared global area’, cette dernière est propre à chaque instance et ne
peut donc être partagée entre plusieurs instances.
C’est une zone contenant les données et informations relatives à une instance ORACLE. Les
données contenues dans cette zone sont partagées entre tous les utilisateurs connectés à
l’instance.
La mémoire pour une SGA est allouée au démarrage de l’instance et désallouée à l’arrêt de
cette dernière. La taille est déterminée, au démarrage, dans le fichier paramètre init.ora.
Ces paramètres sont :

DB_BLOCK_SIZE : détermine la taille en octet d’un bloc de données et d’un buffer

DB_BLOCK_BUFFER : nombre de buffer de la base alloué à la SGA. L’espace total
du buffer est égal à DB_BLOCK_SIZE * DB_BLOCK_BUFFER

LOG_BUFFER : Nombre d’octets alloués au buffer REDO LOG

SHARED_POOL_SIZE: Taille en octets, de l’espace alloué aux instructions SQL et
PL/SQL partagées.
Nota :
 La SGA doit être aussi grande que possible, à condition qu’elle reste en mémoire
centrale, donc qu’elle ne doit pas être trop grosse.

Un BUFFER est un bloc en mémoire centrale
La SGA est composée :

Du BUFFER CACHE
Il s’agit d’une portion de la SGA contenant les copies des blocs de données les
plus récemment utilisés, lus à partir des fichiers de données.
ORACLE utilise 2 listes pour gérer l’espace de ce cache de données : la DIRTY
LIST et la LRU LIST (Least Recently Used).
La DIRTY LIST contient les modifications terminées et validées (commit), mais
non encore écrites sur le disque.
La LRU LIST contient la liste des buffers les moins utilisés récemment. On y voit
donc les buffers libres (pouvant être utilisés), les buffers utilisés actuellement par
des processus (select) et ceux modifiés, non encore transmis dans la liste
précédente (update).
Toutes les données, avant de pouvoir être lues, doivent être placées dans la
DATABASE BUFFER CACHE. De même, les mises à jour (update) sont
effectuées d’abord dans le BUFFER CACHE puis dans la base de données.
On peut ainsi augmenter les performances en lisant les données dans le BUFFER
avant de les lire dans le disque, à conditions que ces dernières soient fréquemment
lues.
La taille est contrôlée par DB_BLOCK_BUFFERS et DB_BLOCK_SIZE

Du BUFFER REDO LOG
Il s’agit d’une zone tampon circulaire de la SGA contenant les images avant et
après modification des données. On peut ainsi en cas de plantage restaurer la base
de données. Le contenu est écrit dans les fichiers REDO LOG dans les cas
suivants :
Un time out apparaît (3 secondes CPU par défaut)
Un checkpoint apparaît (ce qui permet d’écrire sur le disque)
Le BUFFER est plein à 1/3
Un COMMIT a été demandé.
La taille du BUFFER est donnée par le paramètre LOG_BUFFER.

Du SHARED POOL
Il s’agit de la partie contenant des espaces partagés :
 Les espaces SQL partagés
Cet espace est utilisé pour traiter chaque instruction SQL différente, en effet si
plusieurs applications soumettent une requête SQL identique, un seul espace
partagé est alloué. La taille de ce dernier est calculée dynamiquement au moment
de l’exécution de la requête SQL.
 les espaces SQL privés
Egalement appelé curseur, il contient l’information sur les buffers d’exécution. Il
est composé de 2 sections : l’espace persistant et l’espace d’exécution.
Il peut appartenir soit à la SGA ou à la PGA.
 le cache dictionnaire
Il contient les informations relatives au dictionnaire de données d’ORACLE. Le
dictionnaire de données contient des vues et des tables internes référençant les
différents objets de la base de données. Les informations sont mises à jour
dynamiquement par le noyau ORACLE. On doit avoir les tables système dans la
mémoire centrale.
La mémoire partageable est également gérée par l’algorithme LRU (Least
Recently Used).
C – 3 La PGA (Program Global Area)
Cette zone mémoire est allouée par ORACLE lors de l’ouverture d’une session. Elle contient
les données de l’information de contrôle relative à UN processus, que ce soit un processus
serveur ou d’arrière plan.
La taille de la PGA dépend de trois paramètres :




OPEN_LINKS
SAVEPOINTS
DB_FILES
LOG_FILES
C – 4 Les zones de tri
Elles existent dans la PGA des processus utilisateurs demandant un tri. Elles dépendent du
paramètre SORT_AREA_SIZE.
D – Les processus ORACLE
Ces processus d’arrière plan exécutent des tâches spécifiques permettant d’améliorer les
performances en présence de plusieurs utilisateurs, ainsi que la sécurité des données.
D – 1 LGWR (LoG WRiter)
Ce processus, propre à chaque instance, écrit sur les fichiers REDO LOG dans les cas
suivants :




Toutes les 3 secondes
Le tiers du REDO LOG BUFFER est plein
Un COMMIT (un process utilisateur valide d’une transaction)
Le processus DBWR écrit des buffers modifiés sur le disque
D – 2 DBWR (DataBase WRiter)
Ce processus est unique à chaque instance. Il est le seul à pouvoir écrire dans la base de
données. Il est chargé de gérer le DATABASE BUFFER en écrivant dans le fichier de base de
données les modifications qui y ont été faites. Il doit ainsi garder dans le DATABASE
BUFFER un minimum de buffers modifiés par les process utilisateurs.
Il écrit dans les cas suivants :
 Il s’est écoulé plus de 3 secondes depuis la dernière écriture.
 Un checkpoint apparaît, le processus LGWR active alors DBWR en lui signalant le
nombre de buffers modifiés qu’il doit écrire sur le disque.
 Un processus serveur parcourt un nombre équivalent au paramètre
DB_BLOCK_MAX_SCAN_CNT et ne trouve pas de buffers libres dans la LRU
LISTE.
 Un processus serveur, en essayant d’insérer un buffer dans la DIRTY LIST voit que
l’on a atteint le seuil fixé par la moitié du paramètre DB_BLOCK_WRITE_BATCH.
D – 3 CKPT (ChecK PoinT)
On appelle un CHECKPOINT le fait que DBWR écrit toutes les données modifiées contenues
dans le DATABASE BUFFER dans les fichiers de données . Cette opération est importante
car l’algorithme LRU dans le DATABASE BUFFER ne déclencherait pas l’écriture des
données fréquemment modifiées.
C’est donc CKPT qui prend en charge la gestion de ces CHECKPOINTS.
Le CHECKPOINT est déclenché dans les cas suivants :
 Le REDO LOG BUFFER boucle
 On a atteint la valeur du paramètre LOG_CHECKPOINT_INTERVAL (10 000 par
défaut) : soit le nombre de blocs écrits
 Cela fait plus de LOG_CHECKPOINT_TIMEOUT (3 secondes par défaut) depuis le
dernier checkpoint
 On a tapé la commande suivante : ALTER SYSTEM SWITCH LOGFILE
D – 4 SMON (System MONitor)
Ce processus permet au démarrage de l’instance, la restauration de cette dernière en cas de
panne de courant par exemple.
Il nettoie également les segments temporaires qui ne sont plus nécessaires.
D – 5 PMON (Process MONitor)
Ce processus permet la restauration d’un processus utilisateur en cas de problèmes.
Il libère également la mémoire cache qui n’est plus utilisée.
D – 6 RECO (RECOver)
Ce processus permet la restauration d’une transaction dans le cas d’une base de données
répartie (plusieures bases de données).
Pour
que
ce
processus
soit
activé,
il
faut
que
le
paramètre
DISTRIBUTED_TRANSACTIONS ait une valeur supérieure à 0 .
D – 7 ARCH (ARCHiver)
Ce processus est optionnel. Il est le seul à garantir une restauration totale de la base.
Il faut que la base soit utilisée en mode ARCHIVELOG.
Il crée des copies sur un support d’archivage des fichiers REDO LOG lorsqu’ils sont pleins.
D – 8 Dnnn (DISPATCHER)
Ce processus permet de partager un processus serveur entre plusieurs processus utilisateurs.
D – 9 LISTENER
Ce processus se met en écoute des connexions utilisateurs. Il les redirige vers un processus
DISPATCHER ou un processus listener de SQL*Net qui assurera la connexion vers un
serveur dédié.
D – 10 Processus utilisateurs
Ces processus sont créés à la connexion et doivent exécuter du code pour dialoguer avec le
serveur (exemples : SQL*DBA, SQLPLUS, POWER BUILDER …).
E – Les mécanismes d’entrées – sorties
E – 1 La lecture
Donnée dans
la SGA
NON
Lecture de la
donnée sur le
disque
OUI
Donnée
modifiée
NON
Lecture dans la SGA
OUI
Donnée
validée
(commit)
OUI
Lecture dans la SGA
NON
Lecture dans les
ROLLBACK
SEGMENTS ou les
DATA SEGMENTS
E – 2 La modification
Le processus de lecture est
appliqué sur les données
appropriées
Obtention d’un bloc de
ROLLBACK SEGMENT par
le processus utilisateur
Allocation du verrouillage
exclusif sur ligne
Marquage du BUFFER
comme ‘DIRTY’
Anciennes données placées
dans le ROLLBACK
SEGMENT et dans le REDO
LOG BUFFER
Les données modifiées sont
placées dans le DATABASE
BUFFER et le REDO LOG
BUFFER
E – 3 La validation
Une marque de commit est placée
dans le REDO LOG BUFFER par un
processus serveur
Le LGWR écrit le contenu du REDO
LOG BUFFER
Libération des verrous
Tant que le DBWR n’a pas écrit sur
le disque les BUFFERs sont marqués
comme propres mais ne le sont pas
vraiment
F – Les objets ORACLE
F – 1 Les TABLESPACES
Il y a au minimum un tablespace par base de données. Ce dernier est composé d’au moins un
fichier et permet le stockage des objets de la base de données ( tables, index, …).
Une taille lui est allouée lors de sa création
F – 2 Les SCHEMAS
Un schéma est une collection d’objets qui appartiennent à un utilisateur de la base de données.
De ce fait, un unique schéma est associé à un utilisateur.
Les types d’objets sont les suivants :









Les tables
Les index
Les clusters et hash clusters
Les vues
Les séquences
Les Unités de programme (procédures et fonctions stockées, triggers et packages)
Les synonymes
Les liens avec des bases de données
Les snapshots
F – 3 Les tables
Une table est composée de lignes et de colonnes. Ainsi les données sont stockées en colonne
dans les lignes et chaque colonne possède un nom et un type de donnée propre.
Les données d’une même colonne sont nécessairement du même type.
L’utilisateur peut donc accéder à ces données, les modifier, les supprimer.
Création d’une table :
CREATE TABLE ELEVE (
NUMERO
NOM
PRENOM
TELEPHONE
DATE_NAISSANCE
NUMBER(4) NOT NULL,
VARCHAR2(25),
VARCHAR2(15),
VARCHAR2(15),
DATE)
Nota : Vues associées






USER_TABLES
ALL_TABLES
DBA_TABLES
USER_TAB_COLUMNS
ALL_TAB_COLUMNS
DBA_TAB_COLUMNS
F – 4 Les index
Un index permet d’augmenter les performances pour les requêtes.
Il contient l’adresse physique de chaque ligne, permettant ainsi d‘aller plus rapidement
jusqu’à la donnée
Nota : les performances sont dégradées dans le cas de mise à jour, insertion et suppression.
F – 5 Les clusters (ou groupements)
Les clusters sont utilisés pour grouper des tables partageant des colonnes de même type et
souvent utilisées ensemble (jointures). Les données sont par conséquent stockées une fois.
Nota : on augmente la vitesse pour les accès, mais on ralentit toutes les opérations de mise à
jour, d’insertion et de suppression.
F – 6 Les hash-clusters (ou clusters hachés)
Les hash-clusters sont des clusters utilisant des clés de hachage afin de localiser et stocker
des lignes.
F – 7 Les vues
Une vue est une représentation virtuelle de une ou plusieurs tables. Elle obtient ses données
des tables sur lesquelles elle est basée.
On utilise des vues dans les cas suivants :
 La sécurité des données (on réduit l’accès à des lignes ou des colonnes).
 Masquage de la complexité des données.
 Simplification de la syntaxe des requêtes.
Création d’une vue:
CREATE VIEW NOM_ELEVE_MASCULIN (NOM,PRENOM) AS
SELECT NOM, PRENOM
FROM ELEVE WHERE SEXE=’M’
F – 8 Les séquences
Les séquences sont utilisées pour générer des nombres uniques
F – 9 Les procédure et fonctions
Ensemble d’instructions SQL et PL/SQL, groupées et stockées sous forme compilée dans la
base de données.
F – 10 Les packages
Ensemble de procédures, fonctions, variables et packages.
F – 11 DATABASE LINKS
Permet aux utilisateurs d’accéder aux données d’autres bases de données.
On décrit ainsi un chemin d’une base vers une autre base de données, quel que soit le système
d’exploitation. La seule condition est que le système impliqué possède SQL*NET.