ORACLE 11G – Administration

ORACLE 11G – Administration
XEROX formation
Page 1
ORACLE 11G – Administration
Table des Matières
I. GENERALITES .................................................................................................................................... 7
I.1 DEFINITION............................................................................................................................................ 7
I.1.1 LES DISTRIBUTIONS D'ORACLE 11G ..................................................................................................... 7
I.2 NUMEROTATION DE VERSION (DEPUIS ORACLE9I RELEASE 2)........................................................... 8
I.3 L'ORGANISATION EN COUCHES D'ORACLE11G .................................................................................... 8
I.4 ORGANISATION DU DICTIONNAIRE DE DONNEES .................................................................................. 9
I.5 TACHES DE L’ADMINISTRATEUR (DBA) ................................................................................ 10
I.6 LES OUTILS D'ADMINISTRATION............................................................................................. 10
I.7 INSTALLATION DES COMPOSANTS ORACLE (11G) ............................................................................. 11
I.7.1 PREREQUIS MATERIELS ET LOGICIELS ................................................................................................ 11
I.7.2 INSTALLATION DU LOGICIEL ORACLE (ORACLE HOME) ...................................................................... 11
I.7.3 TACHES DE POST-INSTALLATION ....................................................................................................... 11
I.8 ORGANISATION DES COMPOSANTS PHYSIQUES D’ORACLE .......................................... 12
I.8.1 OPTIMAL FLEXIBLE ARCHITECTURE .................................................................................................. 12
I.8.2 LE REPERTOIRE DE L’ ORACLE_BASE .............................................................................................. 12
I.8.3 LE REPERTOIRE DE L’ ORACLE_HOME ............................................................................................ 12
I.8.4 LE REPERTOIRE ORADATA .............................................................................................................. 12
I.8.5 LE RÉPERTOIRE FLASH_RECOVERY_AREA ................................................................................ 13
I.8.6 LE RÉPERTOIRE ADMIN .................................................................................................................... 13
I.9 LA STRUCTURE ADR (AUTOMATIC DIAGNOSTIC REPOSITORY) ...................................................... 14
I.9.1 LES SOUS-REPERTOIRES DU ADR HOME ............................................................................................ 15
I.9.2 LE FICHIER ALERT ........................................................................................................................... 15
I.9.3 LES FICHIERS TRACES ..................................................................................................................... 15
I.9.4 LES FICHIERS CORE .......................................................................................................................... 15
I.10 L’UTILITAIRE ADRCI (ADR COMMAND INTERPRETER) ............................................................... 16
II. CONNEXION A UN SERVEUR ORACLE .................................................................................... 17
II.1 TYPES DE CONNEXION A UN SERVEUR ORACLE ............................................................................. 17
II.1.1 CONNEXION LOCALE......................................................................................................................... 18
II.1.2 CONNEXION RESEAU : MODE "SERVEUR DEDIE" ............................................................................... 19
II.1.3 CONNEXION RESEAU : MODE "SERVEUR PARTAGE" .......................................................................... 20
II.1.4 ROLE DU "PROCESS SERVER" ............................................................................................................ 21
II.1.5 MODIFICATION DES PARAMETRES D'UNE SESSION ............................................................................. 21
II.1.6 STRUCTURE DE LA PGA (PROGRAM GLOBAL AREA)........................................................................ 22
III. STRUCTURE D’UNE INSTANCE ................................................................................................ 24
III.1
III.2
III.2.1
III.2.2
III.2.3
III.2.4
III.2.5
III.2.6
III.3
III.3.1
COMPOSANTS D'UNE INSTANCE ........................................................................................... 24
STRUCTURE DE LA S.G.A ................................................................................................................. 25
LA FIXED SGA ................................................................................................................................. 25
LE DATABASE BUFFER CACHE .................................................................................................. 26
LE SHARED POOL ....................................................................................................................... 28
LE REDO LOG BUFFER ............................................................................................................... 30
LE LARGE POOL .......................................................................................................................... 30
LE JAVA POOL ............................................................................................................................. 30
TUNING AUTOMATIQUE DE LA MEMOIRE ALLOUEE A L’INSTANCE ................................................ 31
TUNING GLOBAL DE LA SGA ET DES PGA (AMM) ......................................................................... 32
XEROX formation
Page 2
ORACLE 11G – Administration
Table des Matières
III.3.2
III.4
III.4.1
III.4.2
III.4.3
III.4.4
III.4.5
III.5
III.5.1
III.5.2
III.5.3
III.5.4
TUNING PERSONNALISE DE LA SGA ET DES PGA (ASMM) ............................................................ 33
LES PROCESSUS OBLIGATOIRES .......................................................................................... 34
LE PROCESSUS DATABASE WRITER (DBWN) ................................................................................. 35
LE PROCESSUS LOG WRITER (LGWR) ........................................................................................... 35
LE PROCESSUS CHECK POINT (CKPT) ........................................................................................... 36
LE PROCESSUS SYSTEM MONITOR (SMON) ................................................................................... 36
LE PROCESSUS PROCESS MONITOR (PMON) ................................................................................. 37
LES PROCESSUS FACULTATIFS ............................................................................................. 37
LE PROCESSUS ARCHIVER (ARCN) ................................................................................................ 37
LE PROCESSUS LISTENER ............................................................................................................. 37
LE PROCESSUS RECO ..................................................................................................................... 37
PROCESSUS DIVERS ......................................................................................................................... 37
IV. DEMARRAGE/ARRET D'UNE INSTANCE ............................................................................... 38
IV.1 MISE EN ŒUVRE DU COUPLE INSTANCE/BASE ................................................................................. 39
IV.1.1 DEMARRAGE DE L'INSTANCE (NOMOUNT) ....................................................................................... 40
IV.1.2 MONTAGE DE LA BASE DE DONNEES (MOUNT) ................................................................................ 40
IV.1.3 OUVERTURE DE LA BASE DE DONNEES (OPEN) ................................................................................. 40
IV.2 FERMETURE DE BASE ET ARRET D'INSTANCE ................................................................. 41
IV.3 LES PARAMETRES D'INSTANCE............................................................................................. 42
IV.3.1 CREATION ET MISE A JOUR DES FICHIERS DE PARAMETRES .............................................................. 42
IV.3.2 MODIFICATION DES PARAMETRES D'INSTANCE DYNAMIQUES .................................................... 43
IV.3.3 MODIFICATION DES PARAMETRES D'INSTANCE STATIQUES ......................................................... 43
V. ARCHITECTURE D'UNE BASE ORACLE .................................................................................. 44
V.1 LES STRUCTURES PHYSIQUES ............................................................................................................ 45
V.2 LES STRUCTURES LOGIQUES .............................................................................................................. 46
VI. LES STRUCTURES PHYSIQUES ................................................................................................. 47
VI.1 LE FICHIER DE CONTROLE ..................................................................................................... 47
VI.1.1 CREATION DU FICHIER DE CONTROLE .............................................................................................. 47
VI.1.2 MANIPULATIONS DE FICHIER(S) DE CONTROLE ................................................................................ 47
VI.1.3 CREATION DE NOUVEAU(X) FICHIER(S) DE CONTROLE .................................................................... 48
VI.1.4 SUPPRESSION DE FICHIER(S) DE CONTROLE ..................................................................................... 48
VI.2 LES JOURNAUX DE REPRISE EN LIGNE ............................................................................... 49
VI.2.1 CONTENU ET FONCTIONNEMENT DES JOURNAUX EN LIGNE ............................................................. 50
VI.2.2 MANIPULATIONS DES REDO LOG FILES ........................................................................................... 51
VI.3 LES JOURNAUX DE REPRISE ARCHIVES ............................................................................. 52
VI.4 LES FICHIERS DE DONNEES ..................................................................................................... 53
VII. LES STRUCTURES LOGIQUES D'UNE BASE ORACLE ...................................................... 54
VII.1 LES TABLESPACES .................................................................................................................... 54
VII.1.1 ORGANISATION DES DONNEES A TRAVERS LES TABLESPACES ........................................................ 54
VII.1.2 METHODES DE GESTION DE L’ESPACE DES TABLESPACES ............................................................... 56
XEROX formation
Page 3
ORACLE 11G – Administration
Table des Matières
VII.1.3 ESPACE DES TABLESPACES GERES LOCALEMENT............................................................................ 56
VII.2 GESTION DES TABLESPACES PERMANENTS.................................................................... 57
VII.2.1 LES TABLESPACES "UTILISATEUR" ................................................................................................. 57
VII.2.2 LES TABLESPACES SYSTEM ET SYSAUX.................................................................................... 59
VII.2.3 LE TABLESPACE "UNDO" ............................................................................................................. 59
VII.3 LES TABLESPACES TEMPORAIRES ..................................................................................... 60
VII.3.1 ROLE DE TABLESPACES TEMPORAIRES ........................................................................................... 60
VII.3.2 NOTION DE GROUPE DE TABLESPACES TEMPORAIRES (V10G) ....................................................... 60
VII.3.3 GESTION DE TABLESPACES TEMPORAIRES ...................................................................................... 60
VII.4 MANIPULATIONS DE FICHIERS DE DONNEES ................................................................. 62
VIII. CREATION D'UNE BASE ORACLE 11G................................................................................. 63
VIII.1
VIII.1.1
VIII.1.2
VIII.1.3
VIII.2
VIII.3
VIII.4
VIII.5
ETAPES PREALABLES .................................................................................................................... 63
L'OUTIL ORADIM ....................................................................................................................... 63
L'OUTIL ORAPWD ...................................................................................................................... 64
LE FICHIER DE PARAMETRES INITSID.ORA ..................................................................................... 65
CONNEXION ET DEMARRAGE DE LA NOUVELLE INSTANCE ................................... 66
CREATION DE LA BASE DE DONNEES ............................................................................... 66
TACHES SUPPLEMENTAIRES.......................................................................................................... 67
TACHES DE "POST-INSTALLATION" ............................................................................................. 67
IX. GESTION DE L’ESPACE DISQUE............................................................................................... 68
IX.1 LE SCHEMA ET SES OBJETS............................................................................................................... 68
IX.2 NOTION DE SEGMENT ................................................................................................................... 69
IX.3 LES SEGMENTS UTILISATEUR ................................................................................................ 69
IX.3.1 TYPES DE SEGMENTS UTILISATEUR.................................................................................................. 69
IX.3.2 CREATION D’UN SEGMENT .............................................................................................................. 70
IX.4 GESTION DE L’ESPACE D’UN SEGMENT UTILISATEUR ................................................. 72
IX.4.1 STRUCTURE DU BLOC ORACLE ........................................................................................................ 72
IX.4.2 STRUCTURE D’UNE LIGNE DE DONNEES ............................................................................. 73
IX.4.3 STRUCTURE DU ROWID ................................................................................................................. 73
IX.4.4 ALLOCATION/DESALLOCATION D'EXTENTS ..................................................................................... 74
IX.5 LES SEGMENTS UNDO ................................................................................................................ 77
IX.6 LES SEGMENTS TEMPORAIRES .............................................................................................. 77
IX.6.1 « ETAT DE SANTE » DE LA BASE : HEALTH CHECK MONITOR (DEPUIS V11G) ................................. 78
X. COMMUNICATION RESEAU ET REPARTITION .................................................................... 79
X.1 ORACLE NET : ARCHITECTURE GENERALE .................................................................................. 79
X.1.1 ORACLE NET EN SERVEUR DEDIE..................................................................................................... 80
X.2 BASES DE DONNEES REPARTIES (DATABASE LINK)............................................................................ 82
X.2.1 CREATION D'UN DATABASE LINK ...................................................................................................... 82
X.2.2 LES TRANSACTIONS DISTRIBUEES ..................................................................................................... 83
XI. ADMINISTRATION DE LA SECURITE D'UNE BASE ORACLE .......................................... 84
XEROX formation
Page 4
ORACLE 11G – Administration
Table des Matières
XI.1 LES TACHES DU DBA. ...................................................................................................................... 84
XI.2 LES ROLES ........................................................................................................................................ 85
XI.2.1 EXPLOITATION DES ROLES .............................................................................................................. 87
XI.2.2 MANIPULATIONS DE ROLES ............................................................................................................. 88
XI.3 GESTION DES PRIVILEGES ................................................................................................................ 89
XI.3.1 LES PRIVILEGES SYSTEME ............................................................................................................... 89
XI.3.2 LES PRIVILEGES SUR OBJETS ............................................................................................................ 90
XI.4 LES COMPTES UTILISATEUR (SCHEMAS) .......................................................................................... 91
XI.5 GESTION DES RESSOURCES PAR LES PROFILS .................................................................................. 93
XEROX formation
Page 5
ORACLE 11G – Administration
Oracle 11G - ADMINISTRATION
XEROX formation
Page 6
ORACLE 11G – Administration
Généralités
I. GENERALITES
I.1
Définition
Oracle11g est un Système de Gestion de Bases de Données Relationnel Orienté Objet (SGBDR/OO),
proposé sur différentes plates-formes (Unix/Linux, Windows, VMS ...).
I.1.1
Les distributions d'Oracle 11G
Oracle Database 11g Standard Edition ONE : Utilisable sur un serveur hébergeant au maximum 2
processeurs, elle inclut une base de données pré-configurée, les services de réseau standards, la console
Oracle Entreprise Manager et certains utilitaires Oracle. Elle permet le développement d'applications
client/serveur et intranet classiques.
Oracle Database 11g Standard Edition : Utilisable sur des serveurs « seuls » ou en «cluster» (4
processeurs maxi). Cette distribution inclut désormais l’option Oracle Real Application Cluster (sans
coût supplémentaire).
Oracle Database 11g Entreprise Edition : Utilisable sur des serveurs « seuls » ou en «cluster» (nombre
de processeurs illimité), cette édition propose toutes les options Oracle soumises à licence, dont certaines
sont disponibles pour un coût supplémentaire : RAC (Real Application Cluster), Oracle Partitioning,
OLAP (Informatique Décisionnelle), Datawarehousing, Oracle Tuning Pack, Advanced Security....
Express Edition : Edition gratuite qui inclut une base de données pré-configurée, assortie de quelques
limites (un seul processeur utilisé, une seule base dont la taille est limitée à 4Go, pas d'OEM, taille
mémoire limitée à 1Go, pas d’upgrade ni de support technique prévus...).
Personal Edition : Identique à Enterprise Edition, mais limitée à un poste de travail (sur plateforme
windows, uniquement).
Les produits Oracle sont vendus selon 2 modes de licences :
 Utilisateurs nommés, prenant en compte le nombre de personnes se connectant aux bases de données.
 Processeurs, prenant en compte le nombre de processeurs du serveur hébergeant le logiciel Oracle. Ce
mode est particulièrement adapté aux architectures « trois tiers » dont le nombre de clients est
difficilement connu, ou lorsque le nombre de personnes accédant à la base de données est très
important. Un tarif spécifique est appliqué aux puces dite « multi-core » (Power, Intel, AMD, etc…).
(Documentation de référence : Oracle® Database Licensing Information)
XEROX formation
Page 7
ORACLE 11G – Administration
Généralités
I.2
Numérotation de Version (depuis Oracle9i Release 2)
11.2.0.2.0
N° de Version
N° de Release
(serveur BD)
N° de Release
(serveur Appli.)
Les 2 derniers chiffres correspondent, aux « patchs » Oracle et O.S. La vue du dictionnaire
PRODUCT_COMPONENT_VERSION permet d’afficher les informations relatives aux composants installés.
PRODUCT
-----------------------------------------NLSRTL
Oracle Database 11g Enterprise Edition
PL/SQL
TNS for 32-bit Windows:
I.3
VERSION
------------11.2.0.1.0
11.2.0.1.0
11.2.0.1.0
11.2.0.1.0
STATUS
----------Production
Production
Production
Production
L'organisation en couches d'Oracle11g
Noyau
Noyau Oracle
Dictionnaire
de Données
Référence l'ensemble des objets et ressources constituant une base de
données. Il appartient au schéma SYS.
SQL
Structured Query Language : langage de requêtes.
Outils et
langages
Moyens mis en œuvre pour adresser des requêtes SQL au noyau.
XEROX formation
Page 8
ORACLE 11G – Administration
Généralités
Organisation du dictionnaire de données
Le dictionnaire de données est créé par le CREATE DATABASE. C'est un ensemble de tables qui permet de
répertorier tous les objets et les ressources d'une base de données. Il est accessible grâce à des vues et des
synonymes. Les segments du dictionnaire de données sont stockés dans le tablespace SYSTEM. Tous les
objets du dictionnaire de données appartiennent au schéma SYS.
Il existe deux types de vues :
 Les VUES STATIQUES, référencées dans le dictionnaire. Elles regroupent trois familles :
 USER_XXXX, fournissant des informations sur les ressources et objets dont l'utilisateur connecté est
propriétaire.
 ALL_XXXX, fournissant des informations sur les ressources et objets pour lesquels l'utilisateur
connecté dispose de droits.
 DBA_XXXX, fournissant des informations sur toutes les ressources et objets (rôle DBA requis).
 Les VUES DYNAMIQUES, qui sont créées au démarrage de l’instance. Elles pointent sur la SGA
ou sur le fichier de contrôle. Elles sont accessibles même si la base est fermée.
Elles regroupent deux familles :
 V$xxxx, pour les vues spécifiques à une instance.
 GV$xxx, pour les vues globales dans le cas du R.A.C.
Il existe trois vues spécifiques fournissant des informations sur les vues du dictionnaire :
 V$FIXED_TABLE pointant sur la liste des vues dynamiques (depuis la V11G).
 DICTIONARY pointant sur la liste des vues statiques.
 DICT_COLUMNS pointant sur la liste des colonnes des vues dynamiques et statiques.
Exemple 01 : Nombre de vues dynamiques
SELECT COUNT(*)
FROM
V$FIXED_TABLE
WHERE REGEXP_LIKE(name, '^V\$') -- WHERE name LIKE 'V$%';
Exemple 02 : Recherche de vues statiques
SELECT table_name FROM DICTIONARY
WHERE REGEXP_LIKE(table_name, 'TABLESPACES$') -- WHERE name LIKE '%TABLESPACES';
Exemple 03 : Liste des colonnes d'une vue
SELECT column_name, comments FROM DICT_COLUMNS
WHERE table_name = 'DBA_TABLESPACES';
XEROX formation
Page 9
ORACLE 11G – Administration
Généralités
I.4
TACHES DE L’ADMINISTRATEUR (DBA)
L'administrateur de Base de Données est, en général, impliqué dans les tâches suivantes :
 Installation du logiciel Oracle.
 Contrôle et de la configuration des instances (structures mémoire...).
 Création des bases de données.
 Gérer la sécurité et les comptes utilisateurs (schémas).
 Gérer certains objets de la base (tables, index...).
 Gérer les performances (tuning).
 Gérer les sauvegardes et, les restaurations.
 Organiser et assurer les migrations (releases ou versions).
 Diagnostiquer et rendre compte des erreurs critiques, auprès du support technique Oracle.
I.5
LES OUTILS D'ADMINISTRATION

SQL*Plus et Sql Developer
Depuis la version 11G, Sql*Plus doit être utilisé pour la création des bases de données, ainsi que
pour toutes les manipulations liées au démarrage/arrêt et modification d'état de l'instance.
Toute autre tâche peut être effectuée via Sql Developer.
En version 11G, iSqlPlus (version graphique de Sql*Plus) disparait...

Oracle Universal Installer et Database Upgrade Assistant
OUI est l’utilitaire permettant d’installer le logiciel Oracle et ses options.
DUA est l’utilitaire d’aide à l’installation d’une nouvelle release.

DataBase Configuration Assistant
DBCA est l’utilitaire d’aide à la création d’une base de données.

Net Configuration Assistant
NETCA est l’utilitaire d’aide à la configuration du réseau (serveur et client).

Oracle Enterprise Manager
OEM est un outil graphique, permettant d'administrer une ou plusieurs base de données. Il propose
également une interface permettant d’accéder aux conseillers de performances, ainsi qu’à des outils
comme Recovery Manager (RMAN) ou SQL*Loader.
Plusieurs versions sont proposées :
 OEM Database Control, qui permet d'accéder à une base de données (en fait, une URL par base
existante sur le serveur).
 OEM Database Grid Control, qui permet d'accéder simultanément à toutes les bases Oracle,
existantes sur le serveur.
XEROX formation
Page 10
ORACLE 11G – Administration
Généralités
I.6
Installation des composants Oracle (11G)
I.6.1
Prérequis matériels et logiciels
L’installation d’Oracle 11G, nécessite les ressources suivantes :
 Processeur 32-Bit ou 64-Bit.
 Mémoire physique (RAM) : 1 GB minimum (2 GB pour Windows 7).
 Mémoire virtuelle : Taille correspondant à 2 fois la taille de la RAM.
 Espace disque : 5,39 GB (pour une installation typique), dont 3,5GB pour le Oracle Home.
Il est conseillé que les disques ne servent qu’à Oracle.
 Sous Windows, système de fichiers NTFS recommandé (plutôt que FAT32).
I.6.2
Installation du logiciel Oracle (oracle Home)
L’installation d’Oracle 11G, requiert les droits système d’administration. Elle comporte, dans les grandes
lignes, les étapes suivantes :
 Choix du type d’installation (Logiciel et Base de données ou Logiciel uniquement).
 Choix de la classe système (Desktop ou Serveur).
 Choix des langages à installer.
 Choix de la distribution à installer.
 Spécification du répertoire de l’ORACLE_BASE.
Les directives OFA ayant (encore !!!) changées, le répertoire proposé par défaut est :
lecteur:\app\compte_OS_installation
 Choix du type de base à créer.
I.6.3
Tâches de Post-installation
Après installation du logiciel et création de la base, Oracle apporte les recommendations suivantes :
 Installation du patch le plus récent.
 Recompilation des modules PL/SQL grâce au script utlrp.sql.
 Installation de la dernière version de SQL Developer
(Documentation de référence : Oracle® Database Installation Guide)
XEROX formation
Page 11
ORACLE 11G – Administration
Généralités
I.7
ORGANISATION DES COMPOSANTS PHYSIQUES D’ORACLE
I.7.1
Optimal Flexible Architecture
Le « standard » OFA est une structuration intelligente des répertoires, recommandée par Oracle, afin de
faciliter et uniformiser le travail des administrateurs. Ce standard s’avère indispensable lorsque plusieurs
bases de données et/ou plusieurs versions, cohabitent sur un même serveur.
OFA a subit d’importantes modifications en version 11G.
I.7.2
Le répertoire de l’ ORACLE_BASE
Ce répertoire représente la « racine » (Top-Level Directory) à partir de laquelle seront stockés les
composants des différentes versions d’Oracle Database installées sur le serveur.
La variable ORACLE_BASE identifie le chemin d’accès de ce répertoire :
 lecteur:\app\utilisateur_OS (version 11G).
 lecteur:\oracle\product\10.2.0 (version 10G, dont le Top Level Directory était le oracle_home plutôt
que le oracle_base).
 lecteur:\oracle (jusqu'à la version 9i).
C’est à partir de ce répertoire que l’on trouve :
 Le répertoire de chaque version (ORACLE_HOME).
 Les répertoires des fichiers base de données (ORACLE_BASE\oradata\db_unique_name).
 Les répertoires des fichiers de configuration (ORACLE_BASE\admin\db_unique_name).
 Le répertoire de la Flash Recovery Area (ORACLE_BASE\flash_recovery_area).
 Le répertoire du Automatic Diagnostic Repository (ORACLE_BASE\diag).
I.7.3
Le répertoire de l’ ORACLE_HOME
Chaque version est installée dans son propre répertoire, localisé par la variable ORACLE_HOME :
 ORACLE_BASE\product\11.2.0\dbhome_1 (version 11g).
 ORACLE_BASE\db_1 (version 10g).
 ORACLE_BASE\ora92 (version 9.2i).
I.7.4
Le répertoire ORADATA
Le répertoire ORADATA contient un sous–répertoire par base de données créée sur le serveur :
 ORACLE_BASE\oradata\db_unique_name).
Chaque sous répertoire contient :
 Des fichiers de données (extension .DBF)
 Des membres de journaux (extension .LOG)
 Un exemplaire (ou tous, s’il n’y a pas de F.R.A) du fichier de contrôle (extension .CTL)
XEROX formation
Page 12
ORACLE 11G – Administration
Généralités
I.7.5
Le répertoire FLASH_RECOVERY_AREA
Ce répertoire, localisé par le paramètre d’instance db_recovery_file_dest, contient un sousrépertoire par base créée sur le serveur. On peut y stocker les fichiers issus des sauvegardes effectuées par
RMAN. Dans ce cas, l’espace disque alloué à la F.R.A, est géré automatiquement par RMAN.
Depuis la V11G, la F.R.A contient l’un des exemplaires du fichier de contrôle (si la base a été créée via
DBCA).
I.7.6
Le répertoire ADMIN
Le répertoire ADMIN contient un sous–répertoire par base de données créées sur le serveur :
 ORACLE_BASE\admin\db_unique_name).
Rôle de chaque sous répertoire :
 adump contient les fichiers issus des opérations d’audit (paramètre audit_file_dest).
 dpdump est la destination par défaut, pour les « Data Pump ».
 pfile, créé par DBCA, vide depuis la V9i (il contenait l’init.ora).
 scripts, créé par DBCA, qui contient les scripts et les traces de création de la base.
En V11g
Les répertoires bdump, cdump, et udump, sont remplacés par le répertoire
ORACLE_BASE\diag\rdbms\db_unique_name\instance_name\trace.
de Automatic Diagnostic Repository (ADR)
XEROX formation
Page 13
ORACLE 11G – Administration
Généralités
I.8
La structure ADR (Automatic Diagnostic Repository)
A.D.R. est un référentiel dans lequel sont automatiquement enregistrées toutes données permettant de
prévenir, diagnostiquer et traiter les erreurs critiques.
Ces données sont stockées sous la forme du fichier d’alerte (alert_sid.log), des fichiers de trace (extension
.trc) et des rapports « Health Monitor ». Le répertoire racine (ADR base) de cette arborescence, est
localisé par le paramètre d’instance DIAGNOSTIC_DEST.
Le répertoire racine peut contenir plusieurs ADR Home (répertoire racine pour le stockage des données
d’erreurs d’un composant : instance, Oracle Net, ASM, RAC etc...).
L’arborescence suivante est automatiquement créée lors du premier démarrage de l’instance, avant la
création de la base de données.
La vue dynamique V$DIAG_INFO permet d’afficher les répertoires de destination des fichiers d’alerte et de
traces.
SELECT name, value FROM v$diag_info;
NAME
VALUE
------------------ -----------------------------------------------------Diag Enabled
TRUE
ADR Base
c:\app\formation
ADR Home
c:\app\formation\diag\rdbms\xrx11g\xrx11g
Diag Trace
c:\app\formation\diag\rdbms\xrx11g\xrx11g\trace
Diag Alert
c:\app\formation\diag\rdbms\xrx11g\xrx11g\alert
Diag Incident
c:\app\formation\diag\rdbms\xrx11g\xrx11g\incident
Diag Cdump
c:\app\formation\diag\rdbms\xrx11g\xrx11g\cdump
Health Monitor
c:\app\formation\diag\rdbms\xrx11g\xrx11g\hm
Default Trace File
c:\app\formation\diag\rdbms\xrx11g\xrx11g\trace\xrx11g_ora_2064.trc
Active Problem Count
0
Active Incident Count
0
XEROX formation
Page 14
ORACLE 11G – Administration
Généralités
I.8.1
Les sous-répertoires du ADR Home
alert
cdump
incident
trace
I.8.2
:
:
:
:
Fichier alert au format XML.
Fichiers core.
Sous-répertoires des incidents (un pour chaque incident).
Fichiers alert au format texte et fichiers trace des processus server et background.
Le fichier ALERT
Le fichier alert contient la chronologie des messages liés à des évènements ou erreurs survenus dans la
base :
 Liste des paramètres d’instance dont la valeur n’est pas celle prévue par défaut, lors du STARTUP.
 Manipulations d’instance (STARTUP, SHUTDOWN, ARCHIVE LOG, RECOVER).
 Erreurs internes (ORA-600), de corruption de bloc (ORA-1578) ou de « deadlock » (ORA-60).
 CREATE, ALTER, DROP DATABASE.
 CREATE, ALTER, DROP TABLESPACE.
 Erreurs liées au rafraîchissement automatique d’une vue matérialisée.
I.8.3
Les fichiers TRACES
Les processus server et background écrivent régulièrement dans des fichiers traces, des informations
relatives à leur état, leur activité ou leurs erreurs.
Les traces de type dump, sont propres à un évènement spécifique.
Les fichiers dont l’extension est .trm, sont les traces map associés à certains fichiers trace. Ils contiennent
des informations liées à la structure des traces, et permettent des opérations de recherche et navigation via
ADRCI, par exemple.
I.8.4
Les fichiers CORE
Les fichiers core sont des « dump » mémoire. Ils ne sont exploités que par le support technique Oracle.
XEROX formation
Page 15
ORACLE 11G – Administration
Généralités
I.9
L’utilitaire ADRCI (ADR Command Interpreter)
Cet outil, dont on déclenche l’exécution à partir d’une ligne de commande O.S., permet de :
 Consulter les données relatives aux problèmes et incidents, stockées dans ADR.
 Consulter les rapports « Health Monitor ».
 De regrouper et transmettre toutes les informations relatives à un incident, au support Oracle, sous
forme d’un fichier compressé.
On appelle PROBLEME, une erreur critique (erreur interne de type ORA-600, ou autre erreur « sévère ») se
produisant dans la base de données. Il est associé à une problem key
Lorsque qu’un PROBLEME se (re)produit, Oracle ouvre alors un INCIDENT daté par problème, identifié
par un incident ID numérique. ADR envoie une alerte à OEM.
XEROX formation
Page 16
ORACLE 11G – Administration
Connexion à un serveur base de données
II. CONNEXION A UN SERVEUR ORACLE
II.1
Types de connexion à un serveur ORACLE
Tout programme utilisateur (C, Java, perl) ou application (Sql Developer, Sql*plus) devant manipuler des
données, doit préalablement se connecter à l'instance permettant d'accéder à une base de données.
Il existe deux types de connexion :
 La connexion locale (sans réseau).
 La connexion client-serveur, proposant deux modes :
 Le mode "serveur dédié" (mono-thread server).
 Le mode "serveur partagé" (multi-thread sever).
Une connexion est le.mécanisme (O.S. pour une connexion locale ou software réseau pour une connexion
C/S) permettant au process utilisateur et au process serveur de communiquer.
Une session représente l’ensemble des éléments (dont le username et le mot de passe) permettant à un
processus d’être connecté à l'instance.
XEROX formation
Page 17
ORACLE 11G – Administration
Connexion à un serveur base de données
II.1.1 Connexion locale
Dans ce cas, la base de données et l'application (ou programme) y accédant, se trouvent sur la même
machine. La connexion est établie grâce au mécanisme inter-processus de l’O.S (protocole IPC).
Elle nécessite un compte et son mot de passe.
Dans cette configuration, chaque "process user" est associé :
 A un "process server", qui lui est dédié.
 A une zone mémoire privée, la PGA (Private Global Area), intégrant une zone de tri (sort area).
SG A
Buffers de données
PGA
PGA
Process
Serv er
Process
Serv er
DBWR
Process
User
Process
User
Base de
données
INCONVENIENTS
Les applications utilisateur et la base de données se trouvent sur la même machine.
Par conséquent, le nombre de processus engendrés peut dégrader les performances.
XEROX formation
Page 18
ORACLE 11G – Administration
Connexion à un serveur base de données
II.1.2 Connexion réseau : Mode "serveur dédié"
Dans ce cas, le serveur base de données (back end) et l'application cliente (front end), se trouvent sur
des machines différentes. La connexion est établie via un processus Oracle Net, le listener. Elle nécessite
un compte, son mot de passe et une chaine hôte. Le lien entre les deux process (serveur et utilisateur) est
assuré par le middleware de communication Oracle Net.
Dans cette configuration, chaque "process user" est associé à un "process server", qui lui est dédié et à
une PGA.
SGA
Buffers de données
PGA
PGA
Process
Server
Process
Server
Base de
données
MiddleWare
Oracle Net
Process
User
DBWR
Process
User
AVANTAGES
Cette configuration permet d'équilibrer les charges entre différentes machines.
Les applications utilisateur peuvent fonctionner sous des O.S. différents.
XEROX formation
Page 19
ORACLE 11G – Administration
Connexion à un serveur base de données
II.1.3 Connexion réseau : Mode "serveur partagé"
La connexion et le lien entre les deux process sont identiques au mode "server dédié".
Avec la configuration MTS (Multi-Thread Server), chaque couple "process server"-PGA n'est plus dédié
à un "process user ", il est partagé. La répartition est effectuée par des processus appelés dispachers.
SGA
Buffers de données
LARGE POOL
PGA
PGA
PGA
Process
DISPATCHER
Server
Process
Server
Process
DBWR
Server
Base de
données
MiddleWare
Oracle Net
Process
User
Process
User
REMARQUES
Cette configuration permet de limiter le nombre de processus sur le serveur, mais nécessite beaucoup plus
de ressources mémoire en SGA:
 La large pool, qui contient les files d'attente des requêtes et des résultats.
 La shared-pool dont la taille augmente (partie des PGA partagées).
Oracle conseille d'opter pour cette configuration lorsque le nombre de sessions concurentes est supérieur
à ce que le système d’exploitation peut supporter (par exemple, les applications web dont les traitements
sont plutôt courts et n’occasionnent que peu d’I/O sur le serveur.).
XEROX formation
Page 20
ORACLE 11G – Administration
Connexion à un serveur base de données
II.1.4 Rôle du "process server"
Lors de la demande d'exécution d'une requête SQL par un processus utilisateur, le processus serveur
assure les tâches suivantes :

Le "PARSING", comportant :
 L’analyse de syntaxe.
 L’analyse sémantique (tests d'existence) grâce aux informations stockées dans le Dictionary
Cache.
 Le choix du plan d'exécution qui est conservé dans la sqlarea partagée.

Lecture des blocs base de données contenant les lignes demandées par le processus utilisateur.
Ces blocs sont placés dans les database buffers.
En cas de mise à jour (insert, update, delete) :
 Copie des images-avant dans le segment d’annulation alloué à la transaction.
 Mise à jour des blocs de données.
 Au commit (ou rollback), écriture des images avant et après dans les redolog buffers, en vue de leur
écriture par le LOGWR dans les journaux de reprise.
II.1.5 Modification des paramètres d'une session
Chaque paramètre d'une session dont l' attribut ISSES_MODIFIABLE est positionné à 'TRUE', peut
être modifié dynamiquement par ALTER SESSION. Cette modification est temporaire (jusqu’à la fin de
session).
La commande ALTER SESSION
ALTER SESSION SET paramètre=valeur
Exemple : Modification du format d'affichage de la date
ALTER SESSION
SET nls_date_format='YYYY-MM-DD:HH24:MI:SS'
XEROX formation
Page 21
ORACLE 11G – Administration
Connexion à un serveur base de données
II.1.6 Structure de la PGA (Program Global Area)
La PGA est une zone mémoire non partagée, allouée à un "process server". Son contenu dépend de la
configuration adoptée (serveur partagé ou dédié). Elle comporte différentes régions.

L’UGA (User Global Area), qui contient des données relatives à l’état de la session (variables,
contextes, informations de login, état d’un package, contenu des variables d’un package, etc...).

La private SQL area (ou curseur), qui recueille et maintient les informations relatives à l’exécution
d’une requête. Le nombre de curseurs pouvant être alloué à une session, est limité par le paramètre
d’initialisation OPEN_CURSORS (300 par défaut). Elle se subdivise en 2 régions :
 La run-time area, qui contient des informations relatives à l’état de la requête en cours
d’exécution (nombre de lignes traitées, par exemple).
 La persistent area, la valeur des bind variables (valeurs apportées à une requête SQL, au moment
de son exécution).

Les SQL working areas, qui permettent à certaines opérations internes déclenchées par une requête,
de se dérouler en mémoire. Elles comportent 3 régions :
 La sort area, pour les tris déclenchés par ORDER BY ou GROUP BY.
 La hash area, pour la création d’une table de hashage, lors d’une jointure de type HASH JOIN ;
 La bitmap merge area, pour les opérations impliquant des index bitmap.
XEROX formation
Page 22
ORACLE 11G – Administration
Connexion à un serveur base de données
Tuning de la mémoire alloué aux PGA
Depuis la version 11g, Oracle gère automatiquement et dynamiquement la répartion mémoire entre SGA
et PGA, en fonction de la quantité globale de mémoire mise à sa disposition (Automatic Memory
Management), grâce au paramètre MEMORY_TARGET.
Dans le cas contraire, la gestion de la taille des PGA peut être automatisée indépendament de celle de la
SGA (Automatic Shared Memory Management), à condition que la valeur du paramètre MEMORY_TARGET
soit 0. Dans ce cas, 2 paramètres doivent être renseignés :
 PGA_AGGREGATE_TARGET indiquant la taille maximale pouvant être allouée à l'ensemble des PGA
utilisateur (par défaut, une valeur représentant 20% de la taille de la SGA).
 WORKAREA_SIZE_POLICY avec la valeur AUTO (ou MANUAL pour une gestion manuelle).
Tuning automatique de la PGA (avec memory_target à 0)
PARAMETRE
----------------------------------pga_aggregate_target
workarea_size_policy
VALUE
-----------------------------95420416
AUTO
ISSYS_MOD
--------IMMEDIATE
IMMEDIATE
Vues du dictionnaire de données (PGA)
V$PGASTAT
V$SESSTAT
V$STATNAME
: Statistiques sur la gestion des PGA
: Valeurs des statistiques des process user
: Nom des statistiques
Statistiques sur les PGA : V$PGASTAT (avec memory_target à 1232M)
NAME
VALUE UNIT
------------------------------------------------------------ ---------- -------aggregate PGA target parameter
520093696 bytes
aggregate PGA auto target
448017408 bytes
global memory bound
104017920 bytes
total PGA inuse
22384640 bytes
total PGA allocated
26411008 bytes
maximum PGA allocated
47020032 bytes
total freeable PGA memory
0 bytes
process count
24
max processes count
30
PGA memory freed back to OS
0 bytes
total PGA used for auto workareas
0 bytes
maximum PGA used for auto workareas
1816576 bytes
total PGA used for manual workareas
0 bytes
maximum PGA used for manual workareas
0 bytes
over allocation count
0
bytes processed
1465468928 bytes
extra bytes read/written
0 bytes
cache hit percentage
100 percent
recompute count (total)
89334
XEROX formation
Page 23
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III. STRUCTURE D’UNE INSTANCE
III.1
COMPOSANTS D'UNE INSTANCE
Une instance Oracle représente l'ensemble de ressources permettant d'accéder à une base de données.
Elle peut porter le même nom que sa base. Elle se compose :
 De zones mémoire constituant la SGA (System Global Area), dont le contenu est accessible (partagé)
par toutes les sessions connectées à l’instance.
 De processus background.
Les paramètres de l'instance sont stockés en mémoire, dans la fixed sga.
Leurs valeurs initiales proviennent du fichierde paramètres utilisé lors du démarrage de l'instance
(SPFILEsid.ORA ou INITsid.ORA).
Vues du dictionnaire de données (Instance/Base)
V$DATABASE
V$INSTANCE
V$PARAMETER
: Informations sur la base de données.
: Informations sur l'instance.
: Valeurs des paramètres de l'instance.
Instance
Shared Pool
fixed
sga
.Lib.Cache (pl/sql)
Req . Queue
Log
Buffer Java Pool
SQLAREA
DB Dictionary
Cache
SMON
Large Pool DB Buffer
Resp. Queue
Cache
Dnnn
Snnn
Reco
ARCn
LGWR
CKPT
DBWn
ArcLogFile
PMON
Listener
Process
Server
PGA
initSID. ora
Control. ctl
spfileSID. ora
Logfiles
pwdfile
Datafiles
Base Données
XEROX formation
Process
User
Page 24
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.2
Structure de la S.G.A
Vues du dictionnaire de données (SGA)
V$SGAINFO (10G)
V$SGA
V$SGASTAT
V$SQLAREA
V$SQL
:
:
:
:
:
Informations sur les constituants de la SGA
Taille des pools de la SGA (moins complet que V$SGAINFO)
Informations détaillées sur la SGA
Statistiques sur les SQL AREAS partagées.
Statistiques sur chaque requête exécutée (child cursor).
Exemple : Affichage d'informations sur la SGA
SELECT * FROM v$sgainfo;
NAME
BYTES RES
-------------------------------- ---------- --Fixed SGA Size
1374808 No
Redo Buffers
5259264 No
Buffer Cache Size
536870912 Yes
Shared Pool Size
209715200 Yes
Large Pool Size
8388608 Yes
Java Pool Size
8388608 Yes
Streams Pool Size
0 Yes
Shared IO Pool Size
0 Yes
Granule Size
8388608 No
Maximum SGA Size
778387456 No
Startup overhead in Shared Pool
58720256 No
Free SGA Memory Available
8388608
III.2.1 La Fixed Sga
Elle contient des informations relatives à l’état de la base, la valeur actuelle des paramètres d’instance,
ainsi que des informations inter-processus (les verrous en cours, par exemple).
XEROX formation
Page 25
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.2.2 Le DATABASE BUFFER cache
Cette partie de la SGA contient les blocs qui ont été lus depuis les fichiers physiques.
Ils sont accessibles (partageables) par tout processus connecté à l'instance.
Lorsqu’à travers l’exécution d’une requête, un processus user tente d’accèder à des données, le processus
serveur procède comme suit :
 Il cherche les données dans le database buffer cache. S’il les trouve (cache hit), les données sont
directement lues en mémoire. L’accés est rapide , puisqu’il n’entraîne que des lectures logiques
(logical reads).
 S’il ne les trouve pas (cache miss), les données sont lues depuis un data file. L’accés est nettement
moins performant, puisqu’il entraîne des lectures physiques (physical reads) ainsi que des
interrogations dictionnaire (recursive calls).
Structure du database buffer cache
Chaque bloc du database buffer cache est dans l’un des états suivant :
 FREE (ou unused), s’il est vide et qu’il n’a jamais été utilisé (après démarrage d’instance).
 CLEAN, s’il est disponible (les données qu’il contient et la base de données sont synchrones).
 PINNED, s’il est en cours d’utilisation.
 DIRTY, si les données modifiées qu’il contient, n’ont pas encore été écrites dans la base de
données. Elles le seront lors du prochain check-point, et le bloc passera à l’état CLEAN.
L'ensemble des blocs du cache de données est organisé en deux listes, mises à jour par les process server:
 La LRU list (Least Recently Used) qui organisent les blocs (pinned, dirty, clean et free) du plus
récemment utilisé au moins récemment utilisé.
 La Checkpoint Queue, qui pointe sur les dirty buffers en attente d'écriture dans les data files.
XEROX formation
Page 26
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
Configuration du database buffer cache
Le database buffer cache, peut être divisé en plusieurs caches (pools) :




Le default pool (obligatoire). La taille de ses blocs, qualifiée de standard, est celle spécifiée par le
paramètre d’instance DB_BLOCK_SIZE (par défaut 8K). Il héberge les données provenant du tablespace
SYSTEM, ainsi que celles des tablespaces créés sans clause BLOCKSIZE.
Les nK pool (facultatif). La taille des blocs est celle spécifiée par un paramètre d’instance
DB_nK_CACHE_SIZE (depuis la V9i). Ils hébergent les données provenant de tablespaces créés avec une
clause BLOCKSIZE nK.
Le keep pool (facultatif). La taille des blocs est standard. Il héberge les données provenant de
segments créés avec la clause STORAGE (BUFFER_POOL KEEP) et dont les données ont de fortes
chances d’être réutilisées. Sa taille est spécifiée par le paramètre DB_KEEP_CACHE_SIZE.
Le recycle pool (facultatif La taille des blocs est standard. Il héberge les données provenant de
segments créés avec la clause STORAGE (BUFFER_POOL RECYCLE) et dont les données n’ont que peu
de chances d’être réutilisées. Sa taille est spécifiée par le paramètre DB_RECYCLE_CACHE_SIZE.
PARAMETRAGE MANUEL DU DATABASE BUFFER CACHE
db_block_size
db_cache_size
db_nk_cache_size
: Taille des blocs standards.
: Taille du cache regroupant les blocs standards.
: Taille des caches regroupant des blocs spécifiques (n : 2, 4, 8, 16 ou 32)
REINITIALISATION DU DATABASE BUFFER CACHE
Depuis la version 10G, il est possible de vider le cache de données, afin de mesurer les performances de
certaines requêtes. Cette opération n'est pas recommandée sur les bases de production.
ALTER SYSTEM FLUSH BUFFER_CACHE
XEROX formation
Page 27
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.2.3 Le SHARED POOL
Cette partie de la SGA contient les informations et données relatives aux traitements en cours.
Impliquée dans une majorité des opérations effectuées sur la base, elle est divisée en plusieurs régions.
Le library cache
Il contient le code exécutable (forme « parsée ») des requètes SQL et des blocs PL/SQL en cours
d’exécution, ainsi que les plans d’exécution des requêtes SQL.
Pour chaque requête ou chaque unité de traitement d’un bloc PL/SQL (bloc anonyme, procédure, fonction,
package ou déclencheur) exécuté, Oracle alloue un espace mémoire dans le library cache, que l’on appelle
une SHARED SQL AREA. Son contenu est conservé pour être réutilisé, en cas d'exécutions multiples
d'une même requête (utilisateurs connectés à la même application, par exemple…).
Le Data Dictionary cache
Il permet de conserver en mémoire les données provenant des interrogations dictionnaire (recursive calls).
Le Pool réservé
Il constitue une « réserve », pour les traitements nécessitant d’ importantes quantités de mémoire (curseur
pl/sql, Java...).
XEROX formation
Page 28
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
Le Server result cache (depuis la V11G)
Il se divise en 2 parties :
 Le SQL Query Result Cache, qui permet de maintenir en mémoire des résultats de requêtes.
L’utilisation du cache est conditionnée par la valeur du paramètre d’instance RESULT_CACHE_MODE :
 MANUEL, pour une mise en cache uniquement si la requête présente le hint RESULT_CACHE.
 FORCE, pour une mise en cache systématique, sauf si la requête présente le hint NO_RESULT_CACHE.
 Le SQL Function Result Cache, qui permet de maintenir en mémoire des résultats de requêtes, si la
fonction a été créée avec l’option RESULT_CACHE.
PARAMETRAGE MANUEL DU SHARED POOL
shared_pool_size
: Taille totale du cache partagé (par défaut 32M sur les plateformes 32 bits, et
84M sur les plateformes 64 bits.
REINITIALISATION DU SHARED POOL
Cette opération est nécessaire avant de démarrer une analyse de performances. Elle n'affecte pas les
requêtes en cours d'exécution (sqlarea).
ALTER SYSTEM FLUSH SHARED_POOL
XEROX formation
Page 29
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.2.4 Le REDO LOG BUFFER
Cette partie de la SGA contient les informations relatives aux mises à jour portées à la base de données
(requêtes DML et DDL). Elles sont organisées en postes (redo entries) qui sont écrits par le LGWR dans
les membres du journal de reprise current.
PARAMETRAGE MANUEL DU REDO LOG BUFFER
log_buffer
: Taille du cache utilisé pour écrire dans les journaux de reprise.
III.2.5 Le LARGE POOL
Cette partie optionnelle de la SGA est utilisée pour les Entrées/Sorties de certains processus.
 E/S des opérations de sauvegardes et restaurations RMAN (Recovery MANager).
 E/S des processus Snnn et Dnnn (connexions en mode "serveur partagé") pour les accès aux files
d'attente de requêtes et de résultats.
 E/S des process-serveur exécutant les requêtes parallèles (échanges de données entre les processserveur et le process coordinateur).
PARAMETRAGE MANUEL DU LARGE POOL
large_pool_size
: Taille du cache optionnel.
III.2.6 Le JAVA POOL
Cette partie de la SGA est utilisée par la JVM (Java Virtual Machine). Elle permet de stocker les états
d'objets et classes java en cours d'exécution.
PARAMETRAGE MANUEL DU JAVA POOL
java_pool_size
XEROX formation
: Taille du cache utilisé par la JVM.
Page 30
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.3
Tuning automatique de la mémoire allouée à l’instance
XEROX formation
Page 31
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.3.1 Tuning global de la SGA et des PGA (AMM)
Depuis la version 11g, Automatic Memory Management, permet la gestion automatique et dynamique
de la répartion mémoire entre la SGA et les PGA, en fonction de la quantité globale de mémoire prévue
par le paramètre d’instance MEMORY_TARGET.
Les différents composants de la SGA, sont également gérés automatiquement.
PARAMETRAGE DU TUNING GLOBAL DE LA SGA et des PGA
: La valeur spécifiée active AMM et indique la quantité de mémoire globale
mise à la disposition de l'instance.
Si la valeur est 0, le tuning est manuel ou assuré par ASMM.
MEMORY_MAX_TARGET
: Indique la quantité de mémoire globale maximale.
SGA_TARGET
: La valeur devrait être 0 si AMM est active.
Dans le cas contraire, la valeur spécifiée indique à AMM, la taille minimale
devant être affectée à la SGA.
PGA_AGGREGATE_TARGET : La valeur devrait être 0 si AMM est active.
Dans le cas contraire, la valeur spécifiée indique à AMM, la taille minimale
devant être affectée aux PGA.
MEMORY_TARGET
Tuning global de la mémoire
SELECT component, current_size, min_size, max_size, user_specified_size
FROM
V$MEMORY_DYNAMIC_COMPONENTS
ORDER BY component;
COMPONENT
CURRENT_SIZE
MIN_SIZE
MAX_SIZE USER_SPECIFIED_SIZE
----------------------------------- ------------ ---------- ---------- ------------------ASM Buffer Cache
0
0
0
0
DEFAULT buffer cache
536870912 536870912 536870912
0
DEFAULT 16K buffer cache
0
0
0
0
DEFAULT 2K buffer cache
0
0
0
0
DEFAULT 32K buffer cache
0
0
0
0
DEFAULT 4K buffer cache
0
0
0
0
DEFAULT 8K buffer cache
0
0
0
0
java pool
8388608
8388608
8388608
0
KEEP buffer cache
0
0
0
0
large pool
8388608
8388608
8388608
0
PGA Target
520093696 520093696 520093696
0
RECYCLE buffer cache
0
0
0
0
SGA Target
771751936 771751936 771751936
0
Shared IO Pool
0
0
0
0
shared pool
209715200 209715200 209715200
0
streams pool
0
0
0
0
Vues du dictionnaire de données (MEMOIRE)
V$MEMORY_DYNAMIC_COMPONENTS :
V$MEMORY_TARGET_ADVICE
:
XEROX formation
Tailles des composants dynamiquement ajustées par AMM.
Conseils sur des valeurs estimées du MEMORY_TARGET et leur
impact sur les performances.
Page 32
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.3.2 Tuning personnalisé de la SGA et des PGA (ASMM)
Depuis la version 10g, Automatic Storage Memory Management, permet la gestion automatique et
dynamique personnalisée de la SGA et des PGA, grâce aux valeurs des paramètres SGA_TARGET et
PGA_AGGREGATE_TARGET. Dans ce cas, la valeur du paramètre MEMORY_TARGET doit être 0.
Les paramètres indiquant la taille d'un pool, doivent être positionnés à 0. Dans la cas contraire, la taille
spécifiée représente la taille minimum à conserver pour le pool.
Dans tous les cas, les paramètres suivants doivent être gérés manuellement :
 LOG_BUFFER
 DB_nK_CACHE_SIZE
 DB_KEEP_CACHE_SIZE
 DB_RECYCLE_CACHE_SIZE
Ce tuning personnalisé est assuré par 2 processus :
 MMON, qui est en charge de la collecte de statistiques relatives à l’utilisation de la SGA. Ces
informations collectées fréquemment (intervalle de quelques minutes), sont utilisées pour modifier
dynamiquement la taille des différents composants de la SGA, selon les besoins.
 MMAN, qui est en charge de la coordination de la modification dynamique de la taille des différents
composants.
PARAMETRAGE DU TUNING PERSONNALISE DE LA SGA et des PGA
: La valeur spécifiée doit être 0.
: La valeur spécifiée active ASMM et indique la quantité de mémoire allouée
à la SGA.
Si la valeur est 0, le tuning est manuel.
SGA_MAX_SIZE
: Indique la quantité de mémoire maximale.
PGA_AGGREGATE_TARGET : La valeur spécifiée indique à ASMM, la quantité de mémoire allouée aux
PGA.
MEMORY_TARGET
SGA_TARGET
Tuning automatique personnalisé de la SGA
PARAMETRE
----------------------------------java_pool_size
large_pool_size
sga_max_size
sga_target
shared_pool_size
DISPLAY_VALUE
--------------0
0
276M
276M
0
ISSYS_MOD
--------IMMEDIATE
IMMEDIATE
FALSE
IMMEDIATE
IMMEDIATE
DISPLAY_VALUE
--------------4M
4M
276M
0
92M
ISSYS_MOD
--------IMMEDIATE
IMMEDIATE
FALSE
IMMEDIATE
IMMEDIATE
Tuning manuel de la SGA
PARAMETRE
----------------------------------java_pool_size
large_pool_size
sga_max_size
sga_target
shared_pool_size
XEROX formation
Page 33
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.4
LES PROCESSUS OBLIGATOIRES
Vues du dictionnaire de données (PROCESSUS)
V$SESSION
V$BGPROCESS
V$SESSTAT
V$STATNAME
:
:
:
:
Informations sur les sessions en cours.
Informations processus background.
Informations statistiques sur les process user.
Nom des statistiques (pour V$SESSTAT et V$SYSSTAT).
Colonnes de jointure : STATISTIC#
Exemple : Affichage des processus actifs
-SELECT
FROM
WHERE
AND
-NoThread
---------1612
1616
1636
1640
1652
1656
1660
1672
1676
1744
1784
2100
3968
1732
5444
2952
1688
2064
768
4304
3392
1004
p.spid
AS "NoThread",
b.name
AS "ORA Proc",
s.username AS "ORA User",
s.osuser
AS "OS User",
s.status
AS "Etat",
s.serial# AS "NoSes",
s.program AS "OS Proc"
v$process p, v$bgprocess b, v$session s
s.paddr
= p.addr
b.paddr (+) = p.addr;
ORA Proc
ORA User
---------- ---------PMON
VKTM
GEN0
DIAG
DBRM
PSP0
DIA0
MMAN
DBW0
LGWR
CKPT
SMON
SMCO
RECO
CJQ0
SYS
QMNC
MMON
MMNL
XEROX formation
SYS
OS User
-------------------SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
formation
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
S06-06795\formation
SYSTEM
Etat
NoSes OS Proc
-------- ---------- -----------------ACTIVE
1 ORACLE.EXE (PMON)
ACTIVE
1 ORACLE.EXE (VKTM)
ACTIVE
1 ORACLE.EXE (GEN0)
ACTIVE
1 ORACLE.EXE (DIAG)
ACTIVE
1 ORACLE.EXE (DBRM)
ACTIVE
1 ORACLE.EXE (PSP0)
ACTIVE
1 ORACLE.EXE (DIA0)
ACTIVE
1 ORACLE.EXE (MMAN)
ACTIVE
1 ORACLE.EXE (DBW0)
ACTIVE
1 ORACLE.EXE (LGWR)
ACTIVE
1 ORACLE.EXE (CKPT)
ACTIVE
1 ORACLE.EXE (SMON)
ACTIVE
15 ORACLE.EXE (SMCO)
ACTIVE
1 ORACLE.EXE (RECO)
INACTIVE
375 SQL Developer
ACTIVE
2 ORACLE.EXE (QMNC)
ACTIVE
1 ORACLE.EXE (MMON)
ACTIVE
1 ORACLE.EXE (MMNL)
ACTIVE
256 ORACLE.EXE (W000)
ACTIVE
433 ORACLE.EXE (Q004)
ACTIVE
610 sqlplus.exe
ACTIVE
2 ORACLE.EXE (Q000)
Page 34
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.4.1 Le processus DataBase WRiter (DBWn)
Le processus DBW0 écrit les dirty buffers dans les fichiers physiques de la base de données.
Chaque nouveau dirty buffer est placé en tête de la checkpoint queue.
L'écriture (multi-blocs) cible les dirty buffers les plus anciens (qui figurent en queue des listes LRU ou
checkpoints). Elle se produit dans les cas suivants :
 Nombre insuffisant de buffers disponibles (clean) dans le cache.
 Toutes les 3 secondes (permet d’optimiser le prochain checkpoint).
 Exécution d'un checkpoint.
 Tablespace mis read only ou offline
 DROP ou TRUNCATE d'un segment.
Sur une plateforme multi-processeurs, il est possible de prévoir des processus DBWn supplémentaires, afin
d'optimiser les écritures.
PARAMETRAGE DES PROCESSUS DBWn
db_writer_processes
: Nombre de processus d'écriture prévu.
 1 à 36 (DBW0 à DBW9 puis DBWa à DBWz).
 Par défaut 1 ou cpu_count/8
III.4.2 Le processus LoG WRiter (LGWR)
Le process LGWR gère le contenu du log buffer. Il écrit dans le(s) membre(s) du journal de reprise en
cours, dans l’un des cas suivants :
 COMMIT (écriture des données et du numéro SCN associés à la transaction).
 Lorsqu’un « switch de log » se produit.
 1/3 du redo log buffer est plein (écriture temporaire jusqu'à fin de transaction)
 Toutes les 3 secondes (écriture temporaire jusqu'à fin de transaction).
 Lorsque le DBW0 écrit des données dans les fichiers physiques.
XEROX formation
Page 35
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.4.3 Le processus ChecK PoinT (CKPT)
Le mécanisme de « check point » permet de valider le fait que toute mise à jour enregistrée dans les
journaux de reprise, le soit également dans la base de données. Ceci permet, entre autre, de reduire le
temps d’un éventuel RECOVERY, après un arrêt d’instance inopiné.
Il existe différents types de checkpoints :

Le CHECKPOINT D’INSTANCE.
Le DBW0 écrit tous les dirty buffers du buffer cache, dans les fichiers physiques.
Le process CKPT écrit les informations de checkpoint (SCN, adresse et séquence du journal...) dans le
fichier de contrôle et dans l’entête de chaque fichier de données.
Ce type de checkpoint se produit dans l’un des cas suivants :
 Switch de journal (logfile plein ou ALTER SYSTEM).
 Arrêt d’instance (sauf abort).
 ALTER SYSTEM checkpoint
 ALTER DATABASE begin backup
 Délai spécifié par le paramètre LOG_CHECKPOINT_TIMEOUT dépassé (par défaut, 1800 s).

Le CHECKPOINT DE FICHIER DE DONNEES.
Ce type de checkpoint se produit dans l’un des cas suivants :
 ALTER TABLESPACE offline (ou read-only)
 ALTER TABLESPACE begin backup

Le CHECKPOINT INCREMENTAL.
Du au fait que le DBW0 vérifie s’il a des buffers à écrire toutes les 3 secondes.
Dans ce cas, le process CKPT écrit les informations de checkpoint dans le fichier de contrôle mais pas
dans les entêtes des fichiers de données.
III.4.4 Le processus System MONitor (SMON)
Le process SMON intervient au démarrage de la base :
 Démarrage après arrêt normal, SMON ouvre la base (état cohérent).
 Démarrage après arrêt anormal, SMON tente d'effectuer un crash recovery :
 Lecture des segments d’annulation, afin d'annuler les transactions non cohérentes au moment
de l'arrêt.
 Lecture des redo logfiles, afin de finaliser les transactions cohérentes au moment de l'arrêt.
 Libération des ressources allouées au moment de l'arrêt (verrous, segments temporaires…).
Une fois la base ouverte, SMON surveille son fonctionnement :
 Recyclage des segments temporaires devenus inutiles.
 Fusion des extents libres et contigus, dans les fichiers physiques (coalesce).
XEROX formation
Page 36
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
III.4.5 Le processus Process MONitor (PMON)
Le processus PMON surveille l'activité des autres processus serveur.
 Libération des ressources utilisées par un processus client qui se termine (cache, verrous…).
 Annulation de la transaction (rollback) d'un processus qui s'arrête anormalement.
 Supervision de l'activité des dispatcher Dnnn et serveur Snnn (mode serveur partagé).
III.5
LES PROCESSUS FACULTATIFS
III.5.1 Le processus ARCHiver (ARCn)
Le processus ARCn archive un journal de reprise, libéré par un switch.
Depuis la version 10G, ce process démarre automatiquement dès que l'on passe en mode archivelog (le
paramètre archive_log_start n'est plus supporté).
Si la destination des archives est saturée, la base de données se bloque (trace dans le fichier d'alertes).
III.5.2 Le processus LISTENER
Le process listener permet les connexions client-serveur avec l'instance. Il possède son propre fichier
de configuration, et son démarrage n'est pas lié à celui de l'instance (service indépendant).
III.5.3 Le processus RECO
Le process RECO gère les problèmes liés aux transactions distribuées (bases de données réparties).
III.5.4 Processus divers
Processus CJQ0 et Jnnn.
Ces processus gèrent des tâches batch planifiées.
Processus MMNL.
Ces processus gèrent certaines émissions d'alerte et capture de statistiques.
XEROX formation
Page 37
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
IV. Démarrage/Arrêt d'une instance
Une connexion utilisant les privilèges SYSDBA (schéma SYS) ou SYSOPER (schéma PUBLIC) est
indispensable aux manipulations d'instance suivantes :
 STARTUP
 ALTER DATABASE
 SHUTDOWN
Ces manipulations peuvent être effectuées via Sql*Plus, Enterprise Manager ou Recovery MANager.
Connexion à Sqlp*Plus pour démarrage/manipulation/arrêt d'instance
1. Démarrage de Sql*Plus (variable oracle_sid positionnée, si connexion locale)
SQLPLUS /NOLOG
2. Connexion à l'instance
CONNECT SYS/xxxx[@chaine_hôte] AS SYSDBA
3. STARTUP ou ALTER DATABASE ou SHUTDOWN
Les différents états du couple instance/base
alter database open
open
Base ouverte
alter database
mount
mount
Base montée
Base fermée
startup nomount
alter
database
close
nomount
Instance démarrée
Base fermée
alter
database
dismount
shutdown
instance arretée
XEROX formation
Page 38
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
IV.1
Mise en œuvre du couple instance/base
La mise en œuvre d'une instance et de sa base de données, se décline en 3 étapes :
 Le démarrage de l'instance (état nomount).
 Le montage de la base de données (état mount).
 L'ouverture de la base de données (état open).
L'ensemble peut être réalisé grâce aux commandes STARTUP et ALTER DATABASE
La commande STARTUP
STARTUP [ FORCE ] [ RESTRICT ] [ PFILE=chemin initxx.ora ] [ QUIET ]
[ NOMOUNT ou MOUNT ou OPEN [ open_options ] ]
FORCE
RESTRICT
PFILE
QUIET
NOMOUNT
MOUNT
OPEN (défaut)
 READ WRITE
 READ ONLY


RECOVER
UPGRADE
: Arrêt avec un shutdown abort, pui redémarrage de l'instance.
A éviter.
: Seuls les utilisateurs dotés du privilège RESTRICTED SESSION peuvent se
connecter. Cet état peut être annulé grâce à la commande :
ALTER SYSTEM DISABLE RESTRICTED SESSION;
Il peut également être rétabli grâce à la commande :
ALTER SYSTEM ENABLE RESTRICTED SESSION;
: Chemin d'accès et nom du fichier de paramètres (client, non persistant).
Par défaut :
 Recherche du fichier persistant SPFILEoracle_sid.ORA.
 Recherche d'un SPFILE.ORA.
 Recherche du fichier texte INIToracle_sid.ORA.
Les fichiers recherchés par défaut, doivent se trouver dans :
 $ORACLE_SID/dbs pour Unix/linux.
 %ORACLE_SID%\database pour Windows.
: Supprime l'affichage des messages d'allocation mémoire (SGA).
: Pour démarrer l'instance.
: Pour démarrer l'instance et monter la base de données.
: Pour démarrer l'instance, monter et ouvrir la base de données.
: Ouverture en lecture /écriture (défaut).
: Ouverture en lecture seule.
Ce mode n'affecte que les écritures dans les data files et les redo logfiles.
: Déclenche un media recovery avant le STARTUP.
: (Depuis la V11G) Pour une 1ère ouverture de la base, avec une nouvelle version
d’Oracle.
Si la base de données n'est pas ouverte par le STARTUP, il est nécessaire d'exécuter la commande
ALTER DATABASE autant de fois qu'il reste d'étapes à franchir (MOUNT/OPEN).
XEROX formation
Page 39
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
IV.1.1 Démarrage de l'instance (nomount)
xrx11g-SYS> STARTUP NOMOUNT
xrx11g-SYS> select instance_name, status from v$instance;
INSTANCE_NAME
STATUS
---------------- -----------xrx11g
STARTED
Le démarrage d’instance consiste en les étapes suivantes :
 Recherche d’un fichier de paramètres
 Allocation de la SGA.
 Démarrage des processus background.
 Ouverture du fichier d’alerte et des fichiers de traces.
La base de données n'est pas accessible.
IV.1.2 Montage de la base de données (mount)
xrx11g-SYS> ALTER DATABASE MOUNT;
xrx11g-SYS> SELECT INSTANCE_NAME as instance, HOST_NAME as serveur, STATUS,
NAME as base, CREATED as creation, LOG_MODE, OPEN_MODE
FROM
V$INSTANCE, V$DATABASE;
INSTANCE SERVEUR
STATUS
BASE
CREATION LOG_MODE
OPEN_MODE
-------- ------------ ---------- -------- -------- ------------ ---------xrx11g
ALAIN-11G
MOUNTED
XRX11G
02/02/12 NOARCHIVELOG MOUNTED
Le montage de la base consiste en les étapes suivantes :
 Ouverture de tous les exemplaires du fichier de contrôle, identifiés par le paramètre control_files.
La base de données n'est pas accessible. Les seules opérations possibles sont celles qui permettent de
mettre à jour le fichier de contrôle.
IV.1.3 Ouverture de la base de données (open)
L’ouverture de la base consiste en les étapes suivantes :
 Ouverture des fichiers physiques (DBF et LOG) identifiés par le fichier de contrôle.
La base de données est entièrement disponible.
xrx11g-SYS> ALTER DATABASE OPEN;
xrx11g-SYS> SELECT INSTANCE_NAME as instance, HOST_NAME as serveur, STATUS,
NAME as base, CREATED as creation, LOG_MODE, OPEN_MODE
FROM
V$INSTANCE, V$DATABASE;
INSTANCE SERVEUR
STATUS
BASE
CREATION LOG_MODE
OPEN_MODE
-------- ------------ ---------- -------- -------- ------------ ---------xrx11g
ALAIN-11G
OPEN
XRX11G
02/02/12 NOARCHIVELOG READ WRITE
XEROX formation
Page 40
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
IV.2
FERMETURE DE BASE ET ARRET D'INSTANCE
L'ensemble consiste en 3 étapes (connexion en tant que SYSDBA ou SYSOPER) :
 La fermeture de la base de données (close).
 Le démontage de la base de données (dismount).
 L'arrêt de l'instance.
L'ensemble peut être réalisé grâce aux commandes SHUTDOWN et ALTER DATABASE
La commande SHUTDOWN
SHUTDOWN [ NORMAL ou IMMEDIATE ou TRANSACTIONAL ou ABORT ]
NORMAL
:
TRANSACTIONAL
:
IMMEDIATE
:
ABORT
:
XEROX formation
C’est le mode par défaut :
 Aucune nouvelle connexion n’est acceptée.
 Oracle attend la fin d’exécution de tous les processus client.
 Tous les buffers (database et redo) sont écrits sur disque.
 La base est démontée et fermée dans un état cohérent.
 Le prochain démarrage ne nécessitera pas de recovery.
Ce mode garantit la préservation des transactions en cours :
 Aucune nouvelle transaction ne peut être démarrée (donc pas de connexion).
 Oracle attend la fin de chaque transaction en cours, pour déconnecter le
processus client.
 Tous les buffers (database et redo) sont écrits sur disque.
 La base est démontée et fermée dans un état cohérent.
Le prochain démarrage ne nécessitera pas de recovery.
Ce mode est utilisé par défaut, par Enterprise Manager :
 Aucune nouvelle connexion n’est acceptée.
 Les requêtes SQL en cours d’exécution, sont interrompues.
 Les transactions en cours sont annulées (rollback).
 Oracle n’attend pas la fin d’exécution des processus client. Ils sont
déconnectés.
 Tous les buffers (database et redo) sont écrits sur disque.
 La base est démontée et fermée dans un état cohérent.
Le prochain démarrage ne nécessitera pas de recovery.
Ce mode implique un crash recovery lors du prochain démarrage:
 Aucune nouvelle connexion n’est acceptée.
 Les requêtes SQL en cours d’exécution, sont interrompues.
 Les transactions en cours ne sont pas annulées.
 Les processus clients sont immédiatement déconnectés.
 Aucun buffer (database et redo) n’est écrit sur disque.
 La base n’est ni démontée, ni fermée (état incohérent).
Lors de la tentative de redémarrage, les redos seront utilisés pour appliquer
les mises à jours et les segments d’annulation pour annuler les transactions
incomplètes.
Page 41
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
IV.3
LES PARAMETRES D'INSTANCE
Il existe 2 types de fichiers de paramètres :
 Le fichier Server Parameter File (SPFILEsid.ORA).
C’est un fichier binaire, persistant qui peut être modifié par le noyau Oracle (lors d’un ajustement
automatique des pools mémoire, par exemple). Il permet des modifications de paramètres dynamiques
permanentes, via un ALTER SYSTEM.
 Le fichier Parameter File (INITsid.ORA).
C’est un fichier texte, qui ne peut pas être modifié par le noyau Oracle. Les modifications de
paramètres dynamiques via un ALTER SYSTEM, ne sont que temporaires.
Il est fortement recommandé que ces deux fichiers restent cohérents.
Les paramètres de l'instance peuvent être divisés en 3 catégories :
 Les paramètres dynamiques, qui peuvent être modifiés par ALTER SYSTEM.
Leur attribut ISSYS_MODIFIABLE est positionné à 'IMMEDIATE' ou 'DEFERRED'.
Ils peuvent également être modifiés par ALTER SESSION, si leur attribut
ISSES_MODIFIABLE est positionné à 'TRUE'.
 Les paramètres statiques, dont la modification passe par celle du fichier de paramètres, et un
arrêt/redémarrage de l’instance. Leur attribut ISSYS_MODIFIABLE est positionné à 'FALSE'.
 Les paramètres non modifiables.
Vues dynamiques (PARAMETRES)
V$PARAMETER
:
V$SPPARAMETER
:
V$SYSTEM_PARAMETER :
Valeur en cours des paramètres de la session (héritage de
v$system_parameter).
Contenu du spfile.
Valeur en cours des paramètres de l’instance (fixed sga).
On peut également obtenir ces caractéristiques grâce à la commande Sql*plus SHOW PARAMETER.
IV.3.1 Création et mise à jour des fichiers de paramètres
Le fichier de paramètres INITsid.ORA, est créé grâce à un éditeur de textes (vi ou bloc-notes).
Il permet :
 Le démarrage d'instance (création de la base), tant que le SPFILEsid.ORA n'existe pas.
 La création du fichier de paramètres SPFILEsid.ORA.
Chacun de ces fichiers peut être créé à partir de l'autre.
CREATE SPFILE
FROM
PFILE='chemin accès/initsid.ora';
CREATE PFILE='chemin accès/initsid.ora'
FROM
SPFILE;
XEROX formation
Page 42
ORACLE 11G – Administration
L'instance : Structure mémoire et Processus
IV.3.2 Modification des paramètres d'instance DYNAMIQUES
La commande ALTER SYSTEM
ALTER SYSTEM SET paramètre=valeur [ DEFERRED ] SCOPE={ BOTH
| MEMORY
| SPFILE };
BOTH
:
MEMORY
:
SPFILE
:
Affecte la valeur en cours du paramètre en mémoire ET sa valeur initiale dans le fichier
spfilesid.ora (défaut).
Cette option n'est autorisée que pour les paramètres dont l'attribut ISSYS_MODIFIABLE est
positionné à 'IMMEDIATE' ou 'DEFERRED' (dans ce cas, l'option DEFERRED doit être
spécifiée).
La mise à jour du fichier initsid.ora est fortement recommandée.
Affecte la valeur en cours du paramètre en mémoire. L’effet est donc temporaire.
Cette option n'est autorisée que pour les paramètres dont l'attribut ISSYS_MODIFIABLE est
positionné à 'IMMEDIATE'.
Affecte la valeur initiale du paramètre dans le fichier spfilesid.ora. La modification ne
sera effective que lors du prochain démarrage de l'instance.
Cette option est la seule autorisée pour les paramètres statiques (l'attribut
ISSYS_MODIFIABLE est positionné à 'FALSE').
La mise à jour du fichier initsid.ora est fortement recommandée.
Exemple : Attribut ISSYS_MODIFIABLE d'un paramètre
NAME
----------------------------------sort_area_size
shared_pool_size
large_pool_size
java_pool_size
log_archive_start
DISPLAY_VALUE
--------------65536
0
0
0
FALSE
ISSYS_MODIFIABLE
---------------DEFERRED
IMMEDIATE
IMMEDIATE
IMMEDIATE
FALSE
IV.3.3 Modification des paramètres d'instance STATIQUES
Pour modifier un PARAMETRE STATIQUE (ISSYS_MODIFIABLE positionné à 'FALSE') :
 Soit mise à jour du fichier texte initsid.ora,
puis mise à jour du fichier persistant spfilesid.ora par create spfile from pfile=…
 Soit mise à jour du fichier persistant spfilesid.ora par alter system … scope=SPFILE,
puis mise à jour du fichier texte initsid.ora par create pfile=… from spfile.
XEROX formation
Page 43
ORACLE 11G – Administration
Architecture de la base
V. ARCHITECTURE D'UNE BASE ORACLE
Pour gérer l’ensemble des données d’une base, Oracle utilise 2 types de structures de stockage
indépendantes l’une de l’autres :
 Les structures physiques, dont les composants sont stockés sur disque.
 Les structures logiques, dont les caractéristiques (métadonnées) sont stockées dans le dictionnaire.
XEROX formation
Page 44
ORACLE 11G – Administration
Architecture de la base
V.1
Les structures physiques

Le BLOC PHYSIQUE O.S.
Les données de la base sont stockées sur des disques formatés en blocs, dont la taille est dépendante de
l’O.S. Le bloc système (physique) est l’unité de lecture/écriture pour le système d’exploitation.

Les FICHIERS BASE DE DONNEES
Ce sont des espaces physiques réservé à Oracle. On distingue :
 Le fichier de contrôle, qui contient des informations sur les autres fichiers physiques de la base.
 Les membres de journaux, qui contiennent des informations relatives aux transactions.
 Les fichiers de données, qui contiennent les données utilisateur (data files) ou des données
temporaires (temp files).
Il existe trois méthodes de gestion des emplacements et des tailles des fichiers physiques.

Gestion manuelle
C’est le système de fichiers de l’O.S. qui est utilisé pour le stockage des fichiers physiques.
L'administrateur doit spécifier, l'emplacement, le nom, la taille et les conditions d'extension des
fichiers physiques. C’est la méthode la plus utilisée.

Gestion par O.M.F. (Oracle Managed Files, depuis la 9i)
C’est le système de fichiers de l’O.S. qui est utilisé pour le stockage des fichiers physiques.
Oracle se charge de nommer, créer et gérer les fichiers physiques, grâce à des paramètres
d'initialisation. Cette méthode, ancienne, est peu utilisée. Elle est cependant adoptée par RMAN.

Gestion par AS.M. (Automatique Storage Management depuis la 10G)
C’est un système de fichier spécifique à Oracle qui est utilisé.
Certains fichiers "externes" sont également utilisés.
 Les fichiers de paramètres.
 Les fichiers d'alertes et de traces.
 Le fichier de mot de passe externe.
 Les fichiers de configuration du réseau (listener.ora et tnsnames.ora).
XEROX formation
Page 45
ORACLE 11G – Administration
Architecture de la base
V.2
Les structures logiques

Le BLOC LOGIQUE est l'unité de lecture/écriture utilisée par Oracle.
Sa taille (de 2K à 32K) doit être un multiple de celle du bloc physique.
Elle est spécifiée par le paramètre DB_BLOCK_SIZE (8K par défaut).

Le TABLESPACE est un objet ("structure logique de stockage") permettant d'administrer un
espace physique constitué d’un ensemble de blocs logiques (extent) :
 Réparti sur plusieurs fichiers de données (1 à 1022 data files), pouvant contenir chacun jusqu'à 4
millions de blocs, pour les tablespaces "smallfile".
 Groupé dans un unique fichier de données, pouvant contenir jusqu'à 4 billions de blocs, pour les
tablespaces "bigfile" et dont la taille peut atteindre les 128 To (depuis la version 10G).

L'EXTENT est un ensemble de blocs logiques contigus :
 Alloué à un objet Oracle (segment) pour y stocker des données.
 Libre.

Le SEGMENT représente l'ensemble des extents, alloué à un objet Oracle.
Un segment est un objet (tables, index…) dont les données sont stockées dans un « data file ».
Il n’appartient qu’à un, et un seul, tablespace.
XEROX formation
Page 46
ORACLE 11G – Administration
Architecture de la base : STRUCTURES PHYSIQUES
VI. LES STRUCTURES PHYSIQUES
VI.1
LE FICHIER DE CONTROLE
Le fichier de contrôle est un fichier binaire (non éditable) contenant des informations sur les structures
physiques de la base et sur son état :
 Le nom et le timestamp de la base (généré lors de la création de la base).
 Le DBID (identifiant unique affecté à la base)
 Le nom et les chemins d'accès des fichiers de données et des membres de journaux en ligne et
archivés.
 Le numéro de séquence du journal de reprise en cours.
 Des informations de check point relatives aux entrées dans les journaux de reprise.
Ces informations sont écrites par le processus CKPT toutes les trois secondes. Elles permettent de
déterminer les données écrites dans les journaux mais non écrites dans les fichiers de données. Ces
entrées seraient nécessaires lors d'un "recovery" de la base.
VI.1.1 Création du fichier de contrôle
Etant donné son rôle essentiel dans le fonctionnement de la base, il est conseillé de multiplexer le fichier
de contrôle, c'est à dire d'en créer plusieurs exemplaires sur des disques différents. Ceux-ci sont identifiés
par le paramètre d'initialisation CONTROL_FILES, et seront créés par la commande CREATE DATABASE.
Dans ce cas :
 Tous les exemplaires devant être identiques, Oracle les utilise tous lors d'opérations d'écriture.
 Seul le premier d'entre eux est utilisé pour les opérations de lecture.
 L'instance ne peut fonctionner qu'avec l'ensemble des exemplaires identifiés dans le fichier de
paramètres.
Oracle conseille de stocker un exemplaire du fichier de contrôle sur chaque unité physique accueillant
des membres de journaux de reprise.
PARAMETRAGE DU FICHIER DE CONTRÔLE
control_files=("exemplaire1","exemplaire2",…) : Liste des fichiers de contrôle associés à la base.
Vues du dictionnaire de données (Fichiers de contrôle)
V$CONTROLFILE : Chemins d’accès des exemplaires du fichier de contrôle.
V$DATABASE
: Informations contenues dans le fichier de contrôle.
VI.1.2 Manipulations de fichier(s) de contrôle
Il est possible d'ajouter, de renommer et de déplacer le(s) fichier(s) de contrôle.
1.
2.
3.
4.
Arrêt de l'instance
Copie ou Déplacement ou changement de nom (commandes de l'O.S.)
Mise à jour des fichiers de paramètres
Redémarrage de l'instance
XEROX formation
Page 47
ORACLE 11G – Administration
Architecture de la base : STRUCTURES PHYSIQUES
VI.1.3 Création de nouveau(x) fichier(s) de contrôle
En cas de perte partielle des fichiers de contrôle, il suffit d'utiliser l'un de ceux restant, pour récupérer ceux
manquant ou endommagés (copie avec la commande O.S.).
Par contre, en cas de perte totale, il faut exécuter la commande CREATE CONTROLFILE.
Celle-ci peut-être générée par une commande qu'il convient d'exécuter à chaque modification physique de
la base de données (ajout d'un fichier de données, d'un membre de journal etc…) :
ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
La trace ainsi générée, puis "nettoyée", peut être conservée sous forme d'un script.
-STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XRX11G" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\APP\FORMATION\ORADATA\XRX11G\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'C:\APP\FORMATION\ORADATA\XRX11G\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'C:\APP\FORMATION\ORADATA\XRX11G\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'C:\APP\FORMATION\ORADATA\XRX11G\SYSTEM01.DBF',
'C:\APP\FORMATION\ORADATA\XRX11G\SYSAUX01.DBF',
'C:\APP\FORMATION\ORADATA\XRX11G\UNDOTBS01.DBF',
'C:\APP\FORMATION\ORADATA\XRX11G\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\APP\FORMATION\ORADATA\XRX11G\TEMP01.DBF'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
VI.1.4 Suppression de fichier(s) de contrôle
Pour supprimer un fichier de contrôle, il convient de respecter les étapes suivantes.
1.
2.
3.
4.
Arrêt de l'instance
Mise à jour des fichiers de paramètres
Suppression du fichier (commande de l'O.S.)
Redémarrage de l'instance
XEROX formation
Page 48
ORACLE 11G – Administration
Architecture de la base : STRUCTURES PHYSIQUES
VI.2
LES JOURNAUX DE REPRISE EN LIGNE
Un journal de reprise est constitué d'un groupe de 1 à n fichiers physiques (members).
Une base Oracle doit comporter au moins deux journaux.
Ces journaux contiennent les traces des mises à jour apportées aux données de la base.
Etant donné leur rôle essentiel dans l'intégrité et la cohérence des données, il est conseillé de multiplexer
le membre d'un groupe, c'est à dire d'en créer plusieurs exemplaires sur des disques différents.
Les groupes et leur(s) membre(s) sont créés par le CREATE DATABASE.
Log
Buffer
SGA
LGWR
Groupe1
Groupe2
Groupe3
Membre1
Membre1
Membre1
Disque1
Membre2
Membre2
Membre2
Disque2
GROUP#
SEQUENCE#
MEMBERS STATUS
ARC
------- ---------- ---------- ---------------- --1
40
1 ACTIVE
YES
2
41
1 CURRENT
NO
3
39
1 INACTIVE
YES
GROUP#
------1
1
2
2
3
3
MEMBER
STATUS
------------------------------------------------------------ -----C:\APP\FORMATION\ORADATA\XRX11G\REDO01.LOG
C:\APP\FORMATION\ORADATA\XRX11G\REDO01_2.LOG
C:\APP\FORMATION\ORADATA\XRX11G\REDO02.LOG
C:\APP\FORMATION\ORADATA\XRX11G\REDO02_2.LOG
C:\APP\FORMATION\ORADATA\XRX11G\REDO03.LOG
C:\APP\FORMATION\ORADATA\XRX11G\REDO03_2.LOG
XEROX formation
Page 49
ORACLE 11G – Administration
Architecture de la base : STRUCTURES PHYSIQUES
VI.2.1 Contenu et fonctionnement des journaux en ligne
Un membre de journal est constitué d'entrées. Chacune d'entre elles, contient la trace des modifications
apportées aux données d'un bloc d'un segment (images avant et après). Un numéro SCN (System Change
Number) est asssigné à l'ensemble des entrées d'une transaction validée.
Ces données pourront être utilisées :
 Lors d'un recovery d’instance, pour écrire dans les fichiers de données tout ce qui n'avait pas encore
été écrit au moment de l'arrêt inattendu de l'instance.
 Lors d'une récupération de base, afin de retrouver un état cohérent de la base.
C'est le processus LGWR, qui écrit les entrées du log buffer dans le(s) membre(s) du journal en cours :
 Lorsqu'une transaction est validée.
 Lorsque le log buffer est plein.
Lorsque le journal en cours est plein, le LGWR commence à écrire dans le prochain journal disponible
(celui présentant le numéro de séquence le plus petit). Le précédent est alors disponible pour l'archivage.
Un journal peut se trouver dans l'un des états suivant :
 CURRENT, pour le journal alloué au LGWR.
 ACTIVE, pour les journaux nécessaires à un recovery d'instance, c'est à dire ceux dont les données
n'ont pas encore été écrites pas le DBWR dans les fichiers de données.
C'est également l'état d'un journal en cours d'archivage.
 INACTIVE, pour les journaux qui ne sont plus nécessaires au recovery d'instance ou qui ont été
archivés.
PARAMETRAGE DES JOURNAUX (dans le CREATE DATABASE)
MAXLOGFILES
:
MAXLOGMEMBERS
:
ARCHIVE_LAG_TARGET :
Nombre maximum de journaux (groupes)
La valeur par défaut dépend du système d'exploitation.
Depuis le version 10G, le paramètre est mis à jour si le nombre de
journaux dépasse celui prévu (recréation du fichier de contrôle dans les
autres cas).
Nombre maximum de membres par journaux.
La valeur par défaut dépend du système d'exploitation.
Permet de forcer un switch du journal en cours, à intervalle régulier.
La valeur est exprimée en secondes.
Vues du dictionnaire de données (REDO LOGS)
V$LOG
: Informations sur les journaux (groupes).
V$LOGFILE
: Informations sur les membres de journaux.
V$LOG_HISTORY (10G) : Historique d'utilisation des journaux.
XEROX formation
Page 50
ORACLE 11G – Administration
Architecture de la base : STRUCTURES PHYSIQUES
VI.2.2 Manipulations des Redo Log Files
AJOUT D'UN GROUPE
ALTER DATABASE ADD LOGFILE GROUP n
('chemin\membre1', 'chemin\membre2') SIZE s
[ REUSE ]
La taille peut être fournie en Ko (K) ou en Mo (M).
REUSE permet de détruire et de ré-créer les fichiers physiques, s'ils existent déjà.
SUPPRESSION D'UN GROUPE
ALTER DATABASE DROP LOGFILE GROUP n
Le groupe doit être inactif.
AJOUT D'UN MEMBRE
ALTER DATABASE ADD LOGFILE MEMBER 'chemin\membre' TO GROUP n
SUPPRESSION D'UN MEMBRE
ALTER DATABASE DROP LOGFILE MEMBER 'chemin\membre'
Le groupe doit être inactif.
RENOMMER/DEPLACER UN MEMBRE
ALTER DATABASE RENAME FILE 'chemin\membre'
TO 'nouveau_chemin\membre'
Cette commande ne doit être exécutée qu'après avoir effectivement déplacé/renommé le membre, avec les
commandes de l'O.S.
Le groupe doit être inactif.
FORCER UN SWITCH OU UN CHECKPOINT
ALTER SYSTEM SWITCH LOGFILE
ALTER SYSTEM CHECKPOINT
XEROX formation
Page 51
ORACLE 11G – Administration
Architecture de la base : STRUCTURES PHYSIQUES
VI.3
LES JOURNAUX DE REPRISE ARCHIVES
Log
Buffer
SGA
Archive
LGWR
ARC
Groupe1
Groupe2
Groupe3
Membre1
Membre1
Membre1
Disque1
Membre2
Membre2
Membre2
Disque2
Ces archives sont des sauvegardes (copies) de journaux online switchés. Elles sont créées par le
processus ARCn dans le répertoire spécifié par le fichier de paramètres. Elles participeront à une
éventuelle récupération de base, au même titre que les journaux en ligne.
XEROX formation
Page 52
ORACLE 11G – Administration
Architecture de la base : STRUCTURES PHYSIQUES
VI.4
Les FICHIERS DE DONNEES
Les fichiers de données (Data Files) sont des espaces disques réservés à Oracle, pour le stockage des
données des segments de la base.
Les premiers blocs d’un data file contiennent l’entête du fichier. On y trouve :
 Le numéro absolu du fichier (de 1 à n, identifiant unique dans la base).
 Le numéro relatif du fichier (de 0 à n, identifiant unique dans le tablespace).
Il est préférable de stocker les data files sur des disques différents de ceux des journaux.
PARAMETRAGE DES FICHIERS DE DONNEES (dans le CREATE DATABASE)
MAXDATAFILES
:
Nombre maximum de fichiers de données. Il permet de calculer la taille
initiale réservée à la portion « datafile » dans le fichier de contrôle.
Par défaut : 100. Si l'on ajoute un fichier dont le numéro est supérieur à la
valeur spécifiée, mais est inférieure à celle du paramètre d'initialisation
DB_FILES, alors le fichier de contrôle est mis à jour.
PARAMETRAGE DES FICHIERS DE DONNEES (fichier de paramètres)
DB_FILES
:
Nombre maximum de fichiers de données pouvant être ouverts, et pour
lesquels il faut réserver de l'espace en SGA.
Par défaut : 200.
Vues du dictionnaire de données (Fichiers de données)
V$DATAFILE
V$DATAFILE_HEADER
V$TEMPFILE
DBA_DATA_FILES
DBA_TEMP_FILES
DBA_EXTENTS
DBA_FREE_SPACE
Numéro
Absolu
------1
1
2
3
4
:
:
:
:
:
:
:
Informations sur les fichiers associés aux tablespaces permanents.
Informations sur les entêtes des fichiers.
Informations sur les fichiers associés aux tablespaces temporaires.
Informations sur les fichiers associés aux tablespaces permanents.
Informations sur les fichiers associés aux tablespaces temporaires.
Description des extents alloués aux segments.
Description des extents libres.
NAME
------------------------------------------------------C:\APP\FORMATION\ORADATA\XRX11G\SYSTEM01.DBF
C:\APP\FORMATION\ORADATA\XRX11G\TEMP01.DBF
C:\APP\FORMATION\ORADATA\XRX11G\SYSAUX01.DBF
C:\APP\FORMATION\ORADATA\XRX11G\UNDOTBS01.DBF
C:\APP\FORMATION\ORADATA\XRX11G\USERS01.DBF
XEROX formation
Page 53
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
VII. Les STRUCTURES LOGIQUES d'une base Oracle
VII.1
LES TABLESPACES
VII.1.1 Organisation des données à travers les tablespaces
Il existe trois catégories de tablespaces :
 Les tablespaces permanents, pour le stockage des segments permanents Oracle et utilisateur.
 Les tablespaces temporaires, pour le stockage de segments temporaires (opérations de tri, création
d’index…).
 Les tablespaces "undo", dédiés à la gestion automatique des segments d'annulation.
Deux critères déterminent le nombre et le contenu des tablespaces : les performances et l'organisation.
L'utilisation de plusieurs tablespaces offrent les avantages suivants :
 Séparation des données Oracle (system et sysaux) de celles des utilisateurs (users).
 Séparation des données de type différent (tables, index,…).
 Séparation des données d'une application de celles d'une autre application.
 Répartition des données sur différents disques, afin d’optimiser les temps d’accès et de limiter les
risques de contention.
 Souplesse d'organisation des sauvegardes physiques individuelles.
Oracle recommande d'utiliser au moins un tablespace par type de segment. Le nombre de tablespaces
par base de données n'est pas limité (contrairement aux datafiles).
Les tablespaces suivants sont créés lors de la création de la base de données :
 SYSTEM et SYSAUX pour les segments de données Oracle (obligatoire).
 UNDOTBS1 pour les segments d’annulation (obligatoire si undo_management=auto).
 TEMP, un tablespace temporaire PAR DEFAUT.
 USERS, un tablespace permanent PAR DEFAUT (facultatif mais conseillé) (depuis la V10G).
Pour les segments utilisateur, la configuration minimum recommandée, est la suivante :
 Un tablespace pour les segments tables, si celui-ci n’a pas été créé lors de la création de la base.
 Un tablespace pour les segments index.
D'autres tablespaces peuvent être créés pour y stocker des segments présentant des caractéristiques
spécifiques (cluster de données, tables partitionnées,...).
XEROX formation
Page 54
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
Tablespaces créés par le CREATE DATABASE
CREATE DATABASE "AE"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'F:\app\formation\oradata\AE\system01.dbf' SIZE 700M REUSE AUTOEXTEND ON
NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'F:\app\formation\oradata\AE\sysaux01.dbf' SIZE 600M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'F:\app\formation\oradata\AE\temp01.dbf' SIZE 20M REUSE AUTOEXTEND
ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'F:\app\formation\oradata\AE\undotbs01.dbf' SIZE 200M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('F:\app\formation\oradata\AE\redo01.log') SIZE 51200K,
GROUP 2 ('F:\app\formation\oradata\AE\redo02.log') SIZE 51200K,
GROUP 3 ('F:\app\formation\oradata\AE\redo03.log') SIZE 51200K
Vue du dictionnaire de données (tablespaces)
V$SYSAUX_OCCUPANTS
: Informations sur les occupants du tablespace SYSAUX, ainsi que sur
V$TABLESPACE
DBA_TABLESPACES
DBA_TABLESPACE_GROUPS
: Informations sur les tablespaces.
: Informations sur les tablespaces.
: Informations sur les groupes de tablespaces temporaires.
les procédures de déplacements.
XEROX formation
Page 55
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
VII.1.2 Méthodes de gestion de l’espace des tablespaces
Au moment de la création d’un tablespace, l’une des deux méthodes permettant de gérer ses espaces libres
et occupés, doit être choisie :

Tablespace géré localement. (EXTENT MANAGEMENT LOCAL)
Les repères d’espaces libres ou occupés, sont gérées via un index bitmap stocké dans l’entête de
chaque data file. C’est la méthode proposée par défaut, et celle qui devrait être choisie.
Les avantages de cette méthode, plus performante, sont nombreux :
 La mise à jour de l’index bitmap ne génère pas de données d’annulation (pas de mise à jour de
tables).
 Réduction d’espace disque utilisé dans le dictionnaire.
 Les opération de « coalesce » des extents libres contigus, deviennent inutiles.
 La taille des extents des segments peut être gérée automatiquement par Oracle.

Tablespace géré par dictionnaire. (EXTENT MANAGEMENT DICTIONARY)
Les informations relatives aux espaces libres ou occupés, sont stockées dans le dictionnaire.
C’était la méthode utilisée avant la V9i.
VII.1.3 Espace des tablespaces gérés localement
Pour les tablespaces dont l’espace est géré localement, le mode d’allocation des extents des segments et le
mode de gestion de l’espace à l’intérieur des segments, doivent être spécifiés.

Allocation des extents.(EXTENT MANAGEMENT LOCAL AUTOALLOCATE ou UNIFORM)
Avec AUTOALLOCATE, la taille des extents est automatiquement déterminée par Oracle (64K minimum).
C’est un bon choix pour les tablespaces destinés à héberger des objets nécessitant de nombreux extents
de tailles différentes. Il est par contre possible qu’un peu d’espace soit perdu.
Cette méthode n’est pas autorisée pour les tablespaces temporaires.
Avec UNIFORM, la taille des extents, spécifiée par la clause SIZE , est fixe (1M par défaut).

Espace des segments.(SEGMENT SPACE MANAGEMENT AUTO ou MANUAL)
Avec AUTO (ASSM, Atomatic Segment Space Management), les espaces libres d’un segment sont
gérés à l’aide d’un index bitmap.
Avec MANUAL, les espaces libres d’un segment sont gérés à l’aide des clauses PCTUSED, FREELISTS et
FREELIST GROUP. Cette méthode n’est plus recommandée par Oracle. Elle pourrait devenir obsolète,
dans les versions à venir.
XEROX formation
Page 56
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
VII.2
GESTION DES TABLESPACES PERMANENTS
VII.2.1 Les tablespaces "utilisateur"
CREATION D'UN TABLESPACE PERMANENT
CREATE [ SMALLFILE ou BIGFILE] [ UNDO ] TABLESPACE nom_tablespace
DATAFILE 'chemin\fichier' SIZE s [ REUSE ]
[ AUTOEXTEND OFF ou ON ] [ NEXT n ] [ MAXSIZE n ]
[ BLOCKSIZE nK ]
[ LOGGING ou NOLOGGING ]
[ EXTENT MANAGEMENT LOCAL option_local ou DICTIONARY ]
[ SEGMENT SPACE MANAGEMENT AUTO ou MANUAL ]
[ DEFAULT STORAGE ( options_default_storage ) ]
SMALLFILE
BIGFILE
UNDO
:
:
:
Pour un espace physique pouvant comporter plusieurs fichiers.
Pour un espace physique ne comportant qu'un fichier unique.
Pour créer un tablespace UNDO.
DATAFILE
SIZE
:
:
REUSE
AUTOEXTEND
NEXT
MAXSIZE
BLOCKSIZE
LOGGING/NOLOGING
:
:
:
:
:
:
Chemin d'accès et nom du fichier de données.
Taille de l'extension initiale du fichier de données.
Exprimée en Ko (K), en Mo (M), en Go (G) ou en To (T)
Pour détruire le fichier de données, s'il existe déjà.
Pour une extension automatique du fichier de données (défaut OFF).
Taille d'une extension supplémentaire.
Limite de taille de l'ensemble des extensions.
Taille du bloc Oracle (défaut taille de bloc standard).
Clause logging par défaut des segments créés dans le tablespace.
EXTENT MANAGEMENT
 LOCAL (défaut)
 AUTOALLOCATE
:
:
:


UNIFORM SIZE s
DICTIONARY
:
:
SEGMENT SPACE MANAGEMENT :

AUTO (défaut)
:

MANUAL
:
XEROX formation
Mode de gestion des extents des segments créés dans le tablespace.
Les informations d'espace sont stockées dans l'entête du datafile.
Les extents des segments sont gérés automatiquement
(défaut pour les tablespaces permanents et undo)
Les extents ont tous la même taille (si UNIFORM SIZE s)
(défaut pour les tablespaces temporaires)
Les informations d'espace sont stockées dans le dictionnaire. Les
extents des segments sont gérés grâce à la clause STORAGE des
CREATE de segment ou à la clause DEFAULT STORAGE du
CREATE TABLESPACE.
(uniquement pour les tablespaces permanents et gérés localement).
Les blocs libres et occupés d'un segment sont gérés par des
"bitmaps". Les options PCTUSED et FREELIST de la clause
STORAGE des CREATE, sont ignorées.
Les blocs libres et occupés d'un segment sont gérés par via les "free
lists". Cette option est déconseillée par Oracle.
Page 57
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
MODIFICATION DES CARACTERISTIQUES D'UN TABLESPACE
ALTER [ UNDO ] TABLESPACE nom_tablespace
[ AUTOEXTEND OFF ou ON ] [ NEXT n ] [ MAXSIZE n ]
[ ONLINE ou OFFLINE option_offline ]
[ BEGIN BACKUP ou END BACKUP ]
[ READ WRITE ou READ ONLY ]
[ COALESCE ]
[ LOGGING ou NOLOGGING ] [ FORCE LOGGING ]
Les conditions d'extension AUTOEXTEND, NEXT et MAXSIZE ne peuvent être modifiées par ALTER
TABLESPACE que pour les tablespaces "bigfile". Pour les autres, il convient d'utiliser la commande
ALTER DATABASE.
OFFLINE
 NORMAL (défaut)
:
:

TEMPORARY
:

IMMEDIATE
:
ONLINE
BEGIN ou END BACKUP
:
:
READ WRITE ou ONLY
FORCE LOGGING
:
:
Pour empêcher l'accès au tablespace.
D'éventuels "dirty buffers" sont écrits dans les fichiers de données
Pas de recovery nécessaire lors de la remise ONLINE.
Un check point est exécuté mais les écritures ne sont pas assurées.
Recovery possible lors de la remise ONLINE.
Oracle ne s'assure pas que les fichiers soit disponibles et n'exécute
pas de check point. Recovery nécessaire lors de la remise ONLINE.
Pour rétablir l'accès au tablespace.
Au début ou à la fin d'une sauvegarde à chaud sans mise OFFLINE
du tablespace.
Modification de l'état du tablespace lecture/écriture ou lecture.
Force le logging quelle que soit la valeur de chaque segment.
RENOMMER UN TABLESPACE (Depuis la V10G)
ALTER TABLESPACE ancien_nom RENAME TO nouveau_nom
AJOUT D'UN FICHIER PHYSIQUE A UN TABLESPACE
ALTER TABLESPACE nom_tablespace
ADD DATAFILE ou TEMPFILE 'chemin\fichier' SIZE n REUSE
[ AUTOEXEND OFF ou ON ] [ NEXT n ] [ MAXSIZE n ]
SUPPRESSION D'UN FICHIER PHYSIQUE
ALTER TABLESPACE nom_tablespace
DROP DATAFILE ou TEMPFILE 'chemin\fichier'
SUPPRESSION D'UN TABLESPACE
DROP TABLESPACE nom_tablespace
[ INCLUDING CONTENTS [ AND DATAFILES ] [ CASCADE CONSTRAINTS ] ]
XEROX formation
Page 58
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
Exemple : Création d'un tablespace permanent
CREATE TABLESPACE tbs_loc
DATAFILE 'C:\app\formation\oradata\xrx11g\tbs_perm.dbf'
SIZE 50M REUSE
AUTOEXTEND on NEXT 10M MAXSIZE 100M
Exemple : Création d'un tablespace associé à 2 fichiers physiques
CREATE TABLESPACE tbs_2datf
DATAFILE 'C:\app\formation\oradata\xrx11g\tbs_2datf_1.dbf' SIZE 20M,
'C:\app\formation\oradata\xrx11g\tbs_2datf_2.dbf' SIZE 30M
AUTOEXTEND on NEXT 10M MAXSIZE 80M;
VII.2.2 Les tablespaces SYSTEM et SYSAUX
Ces deux tablespaces sont obligatoires. Ils sont créés par le CREATE DATABASE.
Le tablespace SYSTEM contient les tables du dictionnaire de données.
Depuis V10G, le tablespace SYSAUX contient les données (référentiels) de certains utilitaires Oracle.
VII.2.3 Le tablespace "UNDO"
Ce tablespace est obligatoire, si la gestion des segments d’annulation est automatique (conseillé). Il est
dans ce cas créé par le CREATE DATABASE. L'instance ne peut être associée qu'à un seul tablespace
undo. Aucun segment ne peut être créé dans ce tablespace, hormis ceux créés par Oracle.
Le tablespace undo est assigné à l'instance :
 Au startup. Il est soit désigné par le fichier de paramètres, soit choisi par Oracle (tbs disponible).
 Soit grâce à la commande ALTER SYSTEM SET UNDO_TABLESPACE=nom_tbs
PARAMETRAGE DE LA GESTION DES SEGMENTS D’ANNULATION
undo_management=auto ou manual : Mode de gestion des segments d’annulation.
undo_tablespace= nom_tbs
: Indique le nom du tablespace à activer, dans le cas où il y en
aurait plusieurs (facultatif).
rollback_segments=(tbs1, tbs2…)
: Liste des segments d’annulation mis ON-LINE au démarrage
de l'instance (si undo_management=manual).
Exemple : Création d'un tablespace undo
CREATE UNDO TABLESPACE tbs_undo
DATAFILE 'C:\app\formation\oradata\xrx11g\tbs_undo.dbf'
SIZE 100M AUTOEXTEND on;
XEROX formation
Page 59
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
VII.3
LES TABLESPACES TEMPORAIRES
VII.3.1 Role de tablespaces temporaires
Les tablespaces temporaires fournissent un espace supplémentaire aux opérations de tri ne disposant plus
d'assez d'espace en mémoire (sort_area insuffisante). Le tablespace temporaire associé à un compte est :
 Celui prévu par la commande CREATE USER.
 Par défaut :
 Celui prévu par la commande CREATE DATABASE (obligatoire si l'espace du tablespace
system est géré localement).
 Le tablespace system, si l'espace de celui-ci est géré par dictionnaire.
VII.3.2 Notion de groupe de tablespaces temporaires (V10G)
Depuis la version 10G, il est possible d'affecter à un compte un groupe de tablespaces temporaires. Ceci
permet d'améliorer les performances d'opérations de tri simultanées, exécutées par un utilisateur. Le
groupe existe dès l'attribution d'un premier tablespace. Il disparaît dès le retrait de son dernier tablespace.
VII.3.3 Gestion de tablespaces temporaires
CREATION D'UN TABLESPACE TEMPORAIRE
CREATE TEMPORARY TABLESPACE nom_tbs TEMPFILE 'chemin\fichier' SIZE s
[ TABLESPACE GROUP groupe_tablespace ]
TEMPFILE
SIZE
REUSE
TABLESPACE GROUP
Options imposées
:
:
:
:
:
[ REUSE ]
Chemin d'accès et nom du fichier de données temporaire.
Taille de l'extension initiale (Ko (K), Mo (M), Go (G) ou To (T)
Pour détruire le fichier de données déjà existant.
Pour intégrer le tablespace à un groupe.
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE taille_block_standard
AJOUT/RETRAIT D'UN TABLESPACE TEMPORAIRE A UN GROUPE
ALTER TABLESPACE nom_tablespace TABLESPACE GROUP groupe_tablespace
Pour retirer un tablespace de son groupe, il suffit de spécifier '' à la place du nom de groupe.
MODIFICATION DU TABLESPACE TEMPORAIRE PAR DEFAUT
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE nom_tablespace ou groupe
RECUPERATION D’ESPACE (EXTENTS) DANS UN TABLESPACE TEMPORAIRE
ALTER TABLESPACE nom_tablespace SHRINK SPACE KEEP nM
ALTER TABLESPACE nom_tablespace SHRINK TEMPFILE ‘chemin\fichier’
XEROX formation
Page 60
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
Exemple : Création d'un tablespace temporaire
CREATE TEMPORARY TABLESPACE tbs_temp
TEMPFILE 'C:\app\formation\oradata\xrx11g\tbs_temp.dbf'
SIZE 100M
TABLESPACE GROUP tbs_temp_group;
Vue du dictionnaire de données (tablespaces temporaires)
V$SORT_SEGMENT
:
V$TEMP_USAGE
:
DBA_TEMP_FREE_SPACE :
XEROX formation
Informations sur les segments des tablespaces temporaires.
Informations sur les utilisateurs utilisant les segments temporaires.
Informations sur extents des tablespaces temporaires.
Page 61
ORACLE 11G – Administration
Architecture de la base :STRUCTURES LOGIQUES
VII.4
MANIPULATIONS DE FICHIERS DE DONNEES
MODIFICATION DES CONDITIONS D'EXTENSION D'UN FICHIER DE DONNEES
ALTER DATABASE
DATAFILE ou TEMPFILE 'chemin\fichier'
[ AUTOEXEND OFF ou ON ] [ NEXT n ] [ MAXSIZE n ]
AUTOEXEND
NEXT
MAXSIZE
:
:
:
Modificationdu mode d'extension du fichier de données.
Modification de la taille d'une extension supplémentaire.
Modification de la limite de taille de l'ensemble des extensions.
MODIFICATION DE LA TAILLE D'UN FICHIER DE DONNEES
ALTER DATABASE
DATAFILE ou TEMPFILE 'chemin\fichier'
RESIZE nouvelle_taille
RENOMMER/DEPLACER UN FICHIER DE DONNEES D'UN TABLESPACE "NON-SYSTEM"
Il est indispensable d'effectuer l'opération physique (commandes de l'O.S.) avant de mettre à jour le fichier
de contrôle par ALTER TABLESPACE. Pour cela il faut mettre le tablespace OFFLINE.
ALTER TABLESPACE nom_tablespace
RENAME DATAFILE 'ancien_chemin\nom' TO 'nouveau_chemin\nom'
RENOMMER/DEPLACER LE FICHIER DE DONNEES DU TABLESPACE SYSTEM
Il est indispensable d'effectuer l'opération physique (commandes de l'O.S.) avant de mettre à jour le fichier
de contrôle par ALTER DATABASE. Pour cela il faut arrêter l'instance.
ALTER DATABASE
RENAME FILE 'ancien_chemin\nom' TO 'nouveau_chemin\nom'
XEROX formation
Page 62
ORACLE 11G – Administration
CREATION D'UNE BASE DE DONNEES
VIII. CREATION D'UNE BASE ORACLE 11G
La création d'une base de données consiste en un certain nombre d'étapes, permettant :
 De définir les ressources mémoire constituant la nouvelle instance.
 De créer les différentes structures physiques constituant la nouvelle base.
 D'intégrer la base dans l'environnement de travail.
Il est conseillé d'utiliser l'interface graphique DBCA (DataBase Configuration Assistant)
VIII.1 Etapes préalables
Ces étapes permettent de mettre en place toutes les ressources nécessaires au CREATE DATABASE.
 Créer les répertoires (commandes DOS ou SHELL)
 Créer un fichier de paramètres initsid.ora
 Créer et démarrer le service OracleserviceSID, avec oradim (sous windows uniquement)
 Créer un fichier de mots de passe externe, avec orapwd
VIII.1.1 L'outil ORADIM
Chaque instance Oracle doit être associée à un service Windows OracleServiceSID (ou un daemon unix).
L'outil oradim permet de créer et de paramétrer ce service. Il peut également être utilisé pour
démarrer/arrêter le service et/ou l'instance (le démarrage et l'arrêt du service et de l'instance peuvent être
synchronisés).
Exemples de création et de suppression de service
ORADIM –new –sid XRX –startmode AUTO –srvcstart SYSTEM –spfile
ORADIM –delete –sid XRX
-startmode :
-srvcstart
:
Indique si l'instance démarre après démarrage du service (AUTO) ou pas (MANUAL)
Indique si le service démarre au démarrage de l'ordinateur (SYSTEM) ou pas (DEMAND)
XEROX formation
Page 63
ORACLE 11G – Administration
CREATION D'UNE BASE DE DONNEES
VIII.1.2 L'outil ORAPWD
Cet outil permet de créer le fichier de mots de passe externe (%ORACLE_HOME%\database\PWDsid).
Ce fichier permet le démarrage et l'arrêt de l'instance à partir de postes distants (qui ne peuvent pas hériter
du groupe ORA_DBA qui se trouve sur le serveur). Il contient les informations concernant les utilisateurs
autorisés à se connecter avec les privilèges SYSDBA et SYSOPER.
Attention, depuis la V11G, les mots de passe sont case-sensitive, par défaut.
Exemple de création du fichier de mots de passe externe
ORAPWD file=chemin\PWDsid password=mot de passe [ entries=nombre ]
file
:
password :
entries
:
Chemin d'accès complet du fichier de mot de passe.
Mot de passe de l'utilisateur SYS.
Nombre d'utilisateurs autorisés à se connecter en utilisant le fichier de mots de passe.
PARAMETRAGE D'UTILISATION DU FICHIER DE MOTS DE PASSE EXTERNE
remote_login_passwordfile :
 NONE
:


EXCLUSIVE
SHARED
:
:
Seuls les membres du groupe ORA_DBA sont autorisés à démarrer/arrêter
l'instance (connexion locale).
L'utilisation du fichier de mots de passe est autorisée.
Le fichier de mots de passe est partagé entre plusieurs instances (RAC).
Vues du dictionnaire de données
V$PWFILE_USERS : Liste des utilisateurs pouvant se connecter avec sysdba/sysoper.
L'ajout de nouveaux utilisateurs autorisés à utiliser le fichier de mots de passe, se fait grâce à un GRANT.
GRANT SYSDBA ou SYSOPER TO compte
SELECT * FROM V$PWFILE_USERS;
USERNAME
-----------------------------SYS
SYSTEM
XEROX formation
SYSDBA
----TRUE
TRUE
SYSOPER
----TRUE
FALSE
Page 64
ORACLE 11G – Administration
CREATION D'UNE BASE DE DONNEES
VIII.1.3 Le fichier de paramètres initsid.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# File Configuration
###########################################
control_files=("C:\app\formation\oradata\xrx11g\control01.ctl",
"C:\app\formation\flash_recovery_area\xrx11g\control02.ctl")
db_recovery_file_dest=C:\app\formation\flash_recovery_area
db_recovery_file_dest_size=5218762752
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=C:\app\formation
memory_target=1287651328
###########################################
# Database Identification
###########################################
db_domain=""
db_name=xrx11g
###########################################
# NLS
###########################################
nls_language="FRENCH"
nls_territory="FRANCE"
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
###########################################
# Security and Auditing
###########################################
audit_file_dest=C:\app\formation\admin\xrx11g\adump
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
XEROX formation
Page 65
ORACLE 11G – Administration
CREATION D'UNE BASE DE DONNEES
VIII.2 CONNEXION ET DEMARRAGE DE LA NOUVELLE INSTANCE
Sous SQL*Plus
Positionnement de la variable ORACLE_HOME (choix de la version)
 set oracle_home=chemin_version
(DOS)
 export ORACLE_HOME=chemin_version
(SHELL)
Positionnement de la variable ORACLE_SID (identification de l'instance)
 set oracle_sid=instance
(DOS)
 export ORACLE_SID=instance
(SHELL)
Connexion à l'instance sous SQL*PLUS
 sqlplus sys/mot de passe AS SYSDBA
Démarrage de l'instance
 STARTUP NOMOUNT PFILE="chemin\initSID.ora"
VIII.3 CREATION DE LA BASE DE DONNEES
Exécution du CREATE DATABASE qui permet :
 La création du fichier de contrôle.
 La création des tablespaces SYSTEM et SYSAUX.
 La création d'un undo tablespace (si gestion automatique des segments d’annulation).
 La création des redo log files.
 La création d'un tablespace temporaire par défaut.
 La création des tables du dictionnaire de données.
 Création du schéma SYSTEM.
XEROX formation
Page 66
ORACLE 11G – Administration
CREATION D'UNE BASE DE DONNEES
Exemple de commande CREATE DATABASE
CREATE DATABASE "XRX11G"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE 'C:\app\formation\oradata\XRX11G\system01.dbf' SIZE 300M REUSE AUTOEXTEND
ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SYSAUX
DATAFILE 'C:\app\formation\oradata\XRX11G\sysaux01.dbf' SIZE 120M REUSE
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS
DATAFILE 'C:\app\formation\oradata\XRX11G\user01.dbf' SIZE 5M
REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'C:\app\formation\oradata\XRX11G\temp01.dbf' SIZE 20M
REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE 'C:\app\formation\oradata\XRX11G\undotbs01.dbf' SIZE 200M
REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
SET DEFAULT TABLESPACE SMALLFILE
CHARACTER SET WE8ISO8859P15
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('C:\app\formation\oradata\XRX11G\redo01.log') SIZE 51200K,
GROUP 2 ('C:\app\formation\oradata\XRX11G\redo02.log') SIZE 51200K,
GROUP 3 ('C:\app\formation\oradata\XRX11G\redo03.log') SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword";
VIII.4 Tâches supplémentaires
Exécution de procédures fournies par Oracle, permettant :
 La création des vues du dictionnaire de données.
 L'installation des packages pl/sql.
 La création des tablespaces supplémentaires.
 L'installation de ressources nécessaires à certaines options ou outils (textes d'aide de sqlplus).
VIII.5 Tâches de "Post-installation"
Exécution de procédures fournies par Oracle ou par l'utilisateur :
 Création du spfileSID.ora à partir de l'initSID.ora.
 Configuration du réseau (service lié au listener…).
 Création de schémas.
 Création des différents objets qui permettront d'accueillir les données de la base.
 Sauvegarde de la base de données.
Vues du dictionnaire de données
V$DATABASE
: Informations sur la base de données.
DATABASE_PROPERTIES : Affichage de certaines propriétés de la base de données.
XEROX formation
Page 67
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX. GESTION DE L’ESPACE DISQUE
IX.1
Le schéma et ses objets
On appelle "SCHEMA" l'ensemble des objets Oracle dont un utilisateur de la base est propriétaire.
Le compte de l'utilisateur et le schéma porte le même nom. Il peut être constitué des objets suivants :
 SEGMENTS (tables, index, …)
 Vues et synonymes
 Séquences
 Liens de base de données (database links)
 Traitements stockés (procédures, fonctions et déclencheurs)
 Les packages PL/SQL
 Ressources, classes et sources Java
Certains objets de la base, n'appartiennent pas à un schéma :
 Comptes utilisateur (schémas...)
 Tablespaces
 Roles
 Profiles
 Directories
Il est possible de créer les tables et les vues d’un utilisateur en une opération unique (considérée comme
une transaction), grâce à la requête CREATE SCHEMA. Cette opération ne créé pas le schéma (qui doit
exister), mais des objets dont celui-ci sera propriétaire.
La requête CREATE SCHEMA ne peut contenir que des CREATE TABLE (sans clause STORAGE), CREATE
VIEW, ou GRANT.
CREATE SCHEMA AUTHORIZATION scott
CREATE TABLE dept (
deptno NUMBER(3,0) PRIMARY KEY,
dname VARCHAR2(15),
loc VARCHAR2(25))
CREATE TABLE emp (
empno NUMBER(5,0) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5,0),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3,0) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
CREATE VIEW brut_annuel AS
SELECT d.dname, e.empno, e.ename, e.sal*13 AS "BRUAN"
FROM e.emp, d.dept
WHERE e.deptno = d.deptno
GRANT SELECT ON brut_annuel TO cours1;
XEROX formation
Page 68
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX.2
Notion de SEGMENT
Le segment est l'unité logique de stockage de données dans le(s) fichier(s) de données d’un tablespace.
Il est composé d'extents dans lesquels sont stockées les données. La taille d'un segment est la somme des
tailles de ses extents. Certains segments (tables et index) peuvent être partitionnés sur plusieurs
tablespaces.
Vues du dictionnaire de données (segments et extents)
DBA_EXTENTS
DBA_FREE_SPACE
DBA_SEGMENTS
IX.3
: Informations sur les espaces occupés des tablespaces
: Informations sur les espaces libres des tablespaces
: Informations sur les segments
Les SEGMENTS UTILISATEUR
IX.3.1 Types de segments utilisateur





Les tables non partitionnées ne sont constituées que d’un seul segment.
Les tables partitionnées présentent un segment par partition.
Les index non partitionnés ne sont constitués que d’un seul segment.
Les index partitionnés présentent un segment par partition.
Le cluster de données comporte un seul segment, dans lequel sont stockées les données de chaque
table du cluster.
Depuis la Release 11.2.0.1, la création du segment est différée, c'est-à-dire qu’aucun espace disque initial
n’est alloué lors de la création de l’objet (table ou index non partitionnés).
Ceci est du au paramètre d’initialisation DEFERRED_SEGMENT_CREATION=TRUE ainsi qu’à la clause
SEGMENT CREATION DEFERRED par défaut du CREATE TABLE.
Depuis la Release 11.2.0.2, la création du segment est différée pour les tables ou index partitionnés.
La colonne segment_created des vues DBA_TABLES, DBA_INDEXES, DBA_TAB_PARTITIONS ou
DBA_IND_PARTITIONS, permet de vérifier si un segment est créé ou pas.
XEROX formation
Page 69
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX.3.2 Création d’un segment
Clauses communes à tous les «create» de segment
Certaines options sont modifiables par ALTER.
SEGMENT CREATION DEFERRED|IMMEDIATE
TABLESPACE nom_tablespace
PCTFREE pourcentage_espace_update
PCTUSED pourcentage_espace_insert
INITRANS nombre_entrées_transactions
STORAGE (
voir page suivante
)
SEGMENT CREATION :

DEFERRED
:

IMMEDIATE
TABLESPACE
:
:
PCTFREE
:
PCTUSED
:
INITRANS
:
Pour différer la création du segment de celle de l’objet (table, index ou
partition), et donc l’allocation d’espace disque. Celui-ci sera alloué dès le 1er
INSERT effectué dans la table (défaut).
Pour créer le segment immédiatement.
Identifie le tablespace «dans lequel» les données du segment seront stockées.
Si cette clause n’est pas renseignée, c’est le tablespace par défaut du schéma qui
est utilisé.
Sa valeur détermine le pourcentage d’espace devant être réservé pour les
UPDATE dans chaque bloc Oracle (défaut 10).
Un bloc est candidat aux INSERT, si son pourcentage d’espace utilisé est en
dessous de la valeur spécifiée. (défaut 40).
Ce paramètre est ignoré en cas de gestion automatique de l’espace du segment.
Nombre minimum d’entrées réservé aux transactions concurrentes, dans chaque
bloc. (défaut 2). Oracle préconise de ne pas modifier cette valeur.
Remarques concernant PCTFREE


Pour les tables qui ne sont jamais mises à jour (update), prévoir un PCTFREE=0 pour économiser de
l'espace disque.
Pour les tables dont plusieurs colonnes sont à NULL, mais fréquemment mises à jour (UPDATE) par la
suite, prévoir un grand PCTFREE pour limiter le risque de lignes chaînées.
XEROX formation
Page 70
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
Création d’un segment (suite)
STORAGE ( INITIAL
NEXT
BUFFER POOL
INITIAL
:
 AUTOALLOCATE
:
 UNIFORM
:
NEXT
:
BUFFER POOL
:



KEEP
KEEP
RECYCLE
:
:
:
taille_extent_initial
taille_extent_supplémentaire
DEFAULT ou KEEP ou RECYCLE )
Si le tablespace est géré localement, la valeur spécifiée permet à Oracle de
déterminer la quantité initiale d’espace disque allouée à l’objet.
Oracle procède par extents de 64K, 1M, 8M ou 64M. Il choisit la taille
inférieure ou égale à la valeur spécifiée dans INITIAL et créé autant d’extents
que nécessaire. Par exemple, pour un INITIAL à 4M, 4 extents de 1M seront
alloués.
Il se peut que la taille allouée soit supérieure à celle spécifiée. Ainsi pour un
INITIAL à 14M, 2 extents de 8M seront alloués.
Oracle utilise la valeur spécifiée dans INITIAL ainsi que la taille des extents
spécifiée lors de la création du tablespace, pour déterminer le nombre d’extents à
créer.
Si le tablespace est géré par dictionnaire, la valeur spécifiée est arrondie à un
multiple de 5 blocs.
Pour un tablespace est géré localement, la valeur est déterminée par Oracle.
Pour un tablespace est géré par dictionnaire, la taille par défaut est de 5
blocs.
Indique dans quelle partie du DB Buffer Cache, les blocs seront stockés.
Pour stocker les blocs dans le default pool.
Pour stocker les blocs dans le keep pool.
Pour stocker les blocs dans le recycle pool.
Si la clause MINEXTENTS est utilisée pour un segment créé dans un tablespace géré localement, Oracle
utilise également les clauses INITIAL, NEXT et NEXT pour calculer la taille de l’extent initial.
XEROX formation
Page 71
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX.4
GESTION DE L’ESPACE D’UN SEGMENT UTILISATEUR
IX.4.1 Structure du bloc Oracle
Les premiers octets d’un bloc, contiennent les métadonnées du bloc (non accessibles aux utilisateurs) :
 L’entête du bloc (block header), qui contient l’adresse du bloc, le type de segment auquel il est
alloué et l’espace réservé aux transactions (transaction entries).
 (pour les tables uniquement) La table directory, qui contient les métadonnées relatives à la table
dont les données sont stockées dans le bloc.
 Le row directory, qui contient les adresses des lignes (rowid) stockées dans le bloc.
L’ensemble occupe entre 84 et 107 octets, dont 23 pour les transaction entries.
L’espace libre (free space), correspond au pourcentage prévu par l’option PCTFREE (espace réservé aux
updates). Une valeur adaptée permet limiter les lignes chaînées ou migrées entre plusieurs blocs.
L’espace restant permet de stocker les données sous forme de lignes.
XEROX formation
Page 72
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX.4.2 STRUCTURE D’UNE LIGNE DE DONNEES
La ligne de données se compose de l'entête de ligne (row header) suivi, pour chaque colonne, de la taille
de la colonne (column length) et de la valeur de la colonne (column value).
IX.4.3 Structure du ROWID
Le ROWID est l’identifiant unique d’une ligne dans la base de données. Il n’est pas physiquement
stocké dans la base, mais il est construit grâce aux métadonnées contenues dans le fichier et le bloc dans
lesquels la ligne est stockée. Il est constitué des éléments suivant :




L’identifiant (data object number) affecté au segment.
Le numéro relatif du datafile dans son tablespace.
Le numéro du bloc contenant la ligne (relatif au datafile et non au tablespace).
Le numéro de la ligne dans son bloc.
XEROX formation
Page 73
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX.4.4 Allocation/Désallocation d'extents
Un extent est un ensemble de blocs Oracle contigus. L’espace initial est alloué à la création du segment
selon le mode d’allocation spécifié, puis ponctuellement selon les besoins.
En temps normal, l’espace alloué à un segment n’est «rendu» au tablespace que lorsque l’objet est droppé.
Depuis la Release 11.2.0.2, il est possible de supprimer le segment (après un DELETE de toutes les lignes,
par exemple) grâce au package DBMS_SPACE_ADMIN.
Certaines opérations permettent la désallocation manuelle d'extents :
 Le coalesce des extents libres d’une table (inutile si l’espace est géré automatiquement) ou d’un index.
 Le SHRINK de table ou d’index.
 Le TRUNCATE de table ou de cluster.
 La récupération d’espace par ALTER... DEALLOCATE
SHRINK d’un segment table
Cette opération comporte 2 phases :
 La première réorganise les lignes de la table.
 Le seconde ajuste le HWM (High-Water-Mark) et libère l’espace libre.
Dans la mesure où les rowids des lignes peuvent être modifiés par l’opération, le mouvement de lignes
doit être autorisé.
ALTER TABLE emp ENABLE ROW MOVEMENT;
ALTER TABLE emp SHRINK SPACE CASCADE;
XEROX formation
Page 74
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
RECUPERATION D'EXTENT LIBRE
L'espace libéré se situe au dessus du "High Water Mark".
ALTER TABLE | INDEX schéma.nom_objet
DEALLOCATE UNUSED [ KEEP valeur ];
KEEP
Taille à conserver au dessus du HWM.
Si KEEP n'est pas spécifié alors :
 Si HWM > INITIAL ou MINEXTENTS, tout l'espace en plus du HWM est libéré.
 Si HWM < INITIAL ou MINEXTENTS, tout l'espace en plus du MINEXTENTS est libéré.
Vidage d'un segment : TRUNCATE
Permet de vider une table, en gardant ou en supprimant les extents alloués.
TRUNCATE TABLE schéma.nom_table
[ DROP ou REUSE STORAGE ]
Suppression d'un segment : DROP
DROP TABLE | INDEX schéma.nom_objet
XEROX formation
Page 75
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
Evaluation des espaces inutilisés
SQL> desc dbms_space
...
PROCEDURE UNUSED_SPACE
Nom d'argument
-----------------------------SEGMENT_OWNER
SEGMENT_NAME
SEGMENT_TYPE
TOTAL_BLOCKS
TOTAL_BYTES
UNUSED_BLOCKS
UNUSED_BYTES
LAST_USED_EXTENT_FILE_ID
LAST_USED_EXTENT_BLOCK_ID
LAST_USED_BLOCK
PARTITION_NAME
Type
----------------------VARCHAR2
VARCHAR2
VARCHAR2
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
VARCHAR2
E/S par defaut ?
------ -------IN
IN
IN
OUT
OUT
OUT
OUT
OUT
OUT
OUT
IN
DEFAULT
Exemple
SQL> VAR TOTAL_BLOCKS NUMBER
SQL> VAR TOTAL_BYTES NUMBER
SQL> VAR UNUSED_BLOCKS NUMBER
SQL> VAR UNUSED_BYTES NUMBER
SQL> VAR VAR5 NUMBER
SQL> VAR VAR6 NUMBER
SQL> VAR VAR7 NUMBER
SQL> EXECUTE DBMS_SPACE.UNUSED_SPACE('SCOTT','EMP','TABLE',
:TOTAL_BLOCKS,:TOTAL_BYTES,:UNUSED_BLOCKS, :UNUSED_BYTES,:VAR5,:VAR6,:VAR7)
Procédure PL/SQL terminée avec succès.
SQL> PRINT TOTAL_BLOCKS TOTAL_BYTES UNUSED_BLOCKS UNUSED_BYTES
TOTAL_BLOCKS
-----------16
TOTAL_BYTES
----------65536
UNUSED_BLOCKS
------------14
UNUSED_BYTES
-----------57344
XEROX formation
Page 76
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX.5
Les SEGMENTS UNDO
Les données d’annulation (undo data) sont stockées dans les segments d’annulation (undo segments),
eux-mêmes regroupés dans un tablespace spécifique (undo tablespace).
Ces données sont utilisées :
 Pour annuler une transaction en cours (ROLLBACK).
 Pour assurer la pertinence des lectures.
 Lors d’un recovery (crash ou media).
 Pour certaines opérations de flashback.
L’allocation et la désallocation d’espace dans les segments d’annulation, sont entièrement automatiques.
IX.6
Les SEGMENTS TEMPORAIRES
Certaines étapes de l’exécution d’une requête SQL, nécessitent parfois un espace de travail temporaire
(typiquement tri, création d’index, opération de hashage, données d’une table temporaire, etc...).
Dans ce cas, Oracle créé un segment temporaire dans un tablespace temporaire (celui affecté à
l’utilisateur exécutant la requête), exclusivement si l’opération ne peut pas se dérouler en mémoire.
L’allocation et la désallocation des segments temporaires associés à une session, sont entièrement
automatiques.
XEROX formation
Page 77
ORACLE 11G – Administration
Gestion de l’espace disque alloué aux objets d’un schéma
IX.6.1 « Etat de santé » de la base : Health Check Monitor (depuis V11g)
L’outil HEALTH MONITOR remplace l’outil dbverify, qui est obsolète.
Il permet de vérifier, grâce à des opérations appelées CHECKS, l’état de divers composants de la base
(fichiers physiques, mémoire, intégrité des transactions, dictionnaire de données,...) mais également
d’établir un diagnostic et d’apporter des éléments aidant à la résolution des problèmes.
Un CHECK peut être actif (exécution automatique liée à une erreur) ou pro-actif (exécution manuelle ou
planifiée).
La liste des CHECKS disponibles, peut être consultées grâce à la vue V$HM_CHECK.
Les paramètres d’execution des CHECKS, peuvent être consultés grâce à la vue V$HM_CHECK_PARAM.
Les rapports générés par les CHECKS sont des fichiers XML, stockés dans la structure ADR.
Ils peuvent être consultés grâce à la vue V$HM_RUN, au package DBMS_HM, ou via les outils ADRCI ou EM.
XEROX formation
Page 78
ORACLE 11G – Administration
Oracle NET : Configuration du réseau
X. COMMUNICATION RESEAU ET REPARTITION
X.1
ORACLE NET : Architecture générale
Oracle Net est l'ensemble des moyens de communication mis en œuvre entre une application cliente et un
serveur de données Oracle.
Oracle Net (instant client) sur les postes clients
Oracle Net Server sur le serveur
tnsnames.ora
listener.ora
Configuration du Client :

Un fichier de paramètres des instances auquelles se connecter
(ORACLE_HOME\network\admin\tnsnames.ora).
Configuration du Serveur :



Un module d'écoute (listener) qui écoute les connexions clientes et les dirige vers les instances
écoutées.
Un fichier de paramètres du module d'écoute (ORACLE_HOME\network\admin\listener.ora).
La commande OS de contrôle du module d'écoute (lsnrctl).
XEROX formation
Page 79
ORACLE 11G – Administration
Oracle NET : Configuration du réseau
X.1.1 Oracle Net en Serveur Dédié
Les fichiers des paramètres Oracle Net sont stockés par défaut dans le répertoire :
ORACLE_HOME\network\admin
S'ils sont stockés dans un répertoire différent, celui-ci doit être spécifié dans la variable d'environnement
TNS_ADMIN.
Connexion distante
C:\>sqlplus scott/tiger@xrx11g
Le fichier de paramètres tnsnames (clients)
# tnsnames.ora Network Configuration File:
C:\app\formation\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
XRX11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = S06-01567)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = xrx11g)
)
)
Le fichier de paramètres du listener (serveur dédié)
# listener.ora Network Configuration File:
C:\app\formation\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = xrx11g)
(ORACLE_HOME = C:\app\formation\product\11.2.0\dbhome_1)
(SID_NAME = xrx11g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = S06-01567)(PORT = 1521))
)
ADR_BASE_LISTENER = C:\app\formation\product\11.2.0\dbhome_1\log
XEROX formation
Page 80
ORACLE 11G – Administration
Oracle NET : Configuration du réseau
Commande et options de gestion du listener : lsnrctl
lsnrctl command [ listener_name ]
Cette commande doit être exécutée sous DOS (windows) ou SHELL (unix)
 START pour démarrer le listener.
 STOP pour arrêter le listener.
 STATUS pour.
 HELP pour afficher l'aide.
 EXIT pour sortir (si l'on exécute lsnrctl en mode interactif).
XEROX formation
Page 81
ORACLE 11G – Administration
Oracle NET : Configuration du réseau
X.2
Bases de données réparties (database link)
X.2.1 Création d'un database link
Le database link est un objet Oracle, permettant d'accéder à un objet distant.
CREATE [ PUBLIC ] DATABASE LINK nom_dblink
CONNECT TO nom_compte IDENTIFIED BY mot_de_passe
USING 'entrée_du_tnsnames'
PUBLIC
CONNECT TO
USING
Pour un database link accessible à tous.
Compte et mot de passe associé au database link.
Chaîne hôte (nom de service) du fichier tnsnames.ora
Exemple
SQL> create public database link lnk_cours1
2 connect to cours1 identified by cours1
3 using 'XRX11G';
SQL> select * from dept@lnk_cours1;
DEPTNO
---------10
20
30
40
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON ;
SQL> insert into dept@lnk_cours1 values (55, 'LOISIRS', 'PARIS');
SQL> select * from dept@lnk_cours1;
DEPTNO
---------10
20
30
40
55
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOISIRS
XEROX formation
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
PARIS
Page 82
ORACLE 11G – Administration
Oracle NET : Configuration du réseau
X.2.2 Les transactions distribuées
La validation d'une transaction utilisant un database link, comporte deux phases (TPC pour Two Phase
Commit). Ce type de validation permet de gérer automatiquement, la cohérence de la transaction
distribuée. Le journal de reprise en ligne de chaque base est mis à jour avec les informations de reprise de
la transaction de la base « locale ». Les deux phases sont :
 Prepare phase : Le noeud qui a initialisé la transaction, dit coordinateur global, demande aux autres
de préparer le COMMIT.
 Commit phase : Si toutes les bases ont répondu favorablement à l'étape précédente, le coordinateur
global demande l'exécution du COMMIT. Dans le cas contraire (problème sur une base) le coordinateur
demande l'exécution du ROLLBACK.
Exemple de transaction distribuée
UPDATE scott.dept@lnk_hq
SET loc = 'REDWOOD SHORES'
WHERE deptno = 10;
UPDATE scott.emp
SET deptno = 11
WHERE deptno = 10;
UPDATE scott.bldg@lnk_maint
SET room = 1225
WHERE room = 1163;
COMMIT;
XEROX formation
Page 83
ORACLE 11G – Administration
Administration de la sécurité
XI. Administration de la sécurité d'une base Oracle
Depuis la V11g, toute base créée via DBCA, adopte par défaut, un certain nombre de nouvelles
règles de sécurité :
 Mots de passe case-sensitives.
 Interdiction, pour les comptes pré-définis (SYSTEM/manager, SYS/change_on_install), d’utiliser leur
mot de passe par défaut.
 Imposition d’un certains nombre de contraintes lors du changement de mot de passe.
 Nouvelles valeurs par défaut, pour certains paramètres du profile DEFAULT.
XI.1






Les tâches du DBA.
Gestion des comptes utilisateur et des schémas.
Chaque utilisateur de la base de données (être humain ou application) doit disposer :
 D’un compte qui lui propre (user).
 D’une méthode d’authentification (généralement un mot de passe).
 D’un tablespace par défaut.
 D’un tablespace temporaire.
 D’un profile.
Le schéma représente l’ensemble des objets dont un utilisateur est propriétaire.
Gestion des comptes utilisateur pré-définis SYS, SYSTEM, DBSNMP et SYSMAN.
Gestion des rôles (par métier et/ou par fonction et/ou par application).
Attribution des privilèges système et des privilèges sur objet à travers les rôles ou des privilèges
individuels.
Gestion des profiles.
Protection au niveau système d'exploitation du fichier de mots de passe externe pwdSID.ora et de
l'exécutable Oracle orapwd.
XEROX formation
Page 84
ORACLE 11G – Administration
Administration de la sécurité
XI.2
Les rôles
Le rôle est un ensemble prédéfini de privilèges système et/ou de privilèges sur objet, dont les
caractéristiques sont les suivantes :
 Un rôle peut être attribué à des utilisateurs et à des rôles.
 Un rôle peut être activé ou désactivé par SET ROLE...
 Un rôle n'appartient pas à un schéma.
 Les rôles et les privilèges sont attribués et retirés aux utilisateurs par les commandes : GRANT et
REVOKE. Tous les privilèges qui sont dans le rôle sont ainsi automatiquement attribués à l'utilisateur.
Avantages de l'utilisation des rôles :
 Réduire les manipulations de droits individuels.
 Gestion globale et centralisée de la sécurité lors des contrôles des privilèges des utilisateurs.
 Gestion dynamique des privilèges. La mise à jour des privilèges d'un rôle affecte automatiquement les
utilisateurs associés à ce rôle, sans se reconnecter ou passer la moindre commande.
 Gestion de la sécurité par activation ou désactivation des éventuels rôles en changeant tout simplement
leur mot de passe.
Liste des rôles existant
SQL> select ROLE from DBA_ROLES;
ROLE
-----------------------------CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
RECOVERY_CATALOG_OWNER
GATHER_SYSTEM_STATISTICS
LOGSTDBY_ADMINISTRATOR
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
GLOBAL_AQ_USER_ROLE
OEM_MONITOR
HS_ADMIN_ROLE
WM_ADMIN_ROLE
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
EJBCLIENT
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
AUTHENTICATEDUSER
SALES_HISTORY_ROLE
27 ligne(s) sélectionnée(s).
XEROX formation
Page 85
ORACLE 11G – Administration
Administration de la sécurité
Rôles prédéfinis
CONNECT
Descriptions
Permet de se connecter et de créer des objets.
SELECT * FROM DBA_SYS_PRIVS WHERE
GRANTEE
PRIVILEGE
---------- ---------------------CONNECT
CREATE SESSION
GRANTEE='CONNECT';
ADM
--NO
Permet de compléter le rôle CONNECT pour un usage orienté
développement en PL/SQL. Intègre le privilège système UNLIMITED
TABLESPACE.
RESOURCE
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';
GRANTEE
PRIVILEGE
ADM
------------ ----------------------RESOURCE
CREATE TYPE
NO
RESOURCE
CREATE TABLE
NO
RESOURCE
CREATE CLUSTER
NO
RESOURCE
CREATE TRIGGER
NO
RESOURCE
CREATE OPERATOR
NO
RESOURCE
CREATE SEQUENCE
NO
RESOURCE
CREATE INDEXTYPE
NO
RESOURCE
CREATE PROCEDURE
NO
...
Attribue tous les privilèges système avec WITH ADMIN OPTION.
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DELETE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
SELECT_CATALOG_ROLE
XEROX formation
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='DBA'
ORDER BY 2 ;
GRANTEE
PRIVILEGE
------------- --------------------------------DBA
ADMINISTER DATABASE TRIGGER
DBA
ADMINISTER RESOURCE MANAGER
DBA
ALTER ANY CLUSTER
DBA
ALTER ANY DIMENSION
DBA
ALTER ANY EVALUATION CONTEXT
DBA
ALTER ANY INDEX
DBA
ALTER ANY INDEXTYPE
DBA
ALTER ANY LIBRARY
DBA
ALTER ANY OUTLINE
DBA
ALTER ANY PROCEDURE
DBA
ALTER ANY ROLE
. . .
139 ligne(s) sélectionnée(s).
ADM
--YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
YES
Permet les exports complets de la base de données.
Permet les imports complets de la base de données.
Permet de vider les tables d'audit.
Permet d'exécuter les packages PL/SQL système.
Permet la consultation du dictionnaire de données.
Page 86
ORACLE 11G – Administration
Administration de la sécurité
XI.2.1 Exploitation des rôles
Il faut (si cela est possible) :
 Répertorier les différents types d'utilisateurs (selon leurs métiers, applications, besoins formulés,
hiérarchie...).
 Créer un rôle par type d'utilisateur.
 Répertorier les privilèges nécessaires à chaque type d'utilisateur.
 Attribuer les privilèges répertoriés à leur(s) rôle(s).
 Attribuer le(s) rôle(s) à chaque utilisateur.
 Attribuer les rôles d'exploitation SYSOPER et SYSDBA.
SYSOPER
SYSDBA
STARTUP, SHUTDOWN, ALTER DATABASE {OPEN | MOUNT | BACKUP
CONTROLFILE | ARCHIVELOG | RECOVER, RESTRICT SESSION }
SYSOPER + DBA (CREATE DATABASE, ALTER DATABASE ...)
C'est le paramètre REMOTE_LOGIN_PASSWORDFILE conditionne l'utilisation du fichier de mots de passe.
Emplacement par défaut du fichier d'authentification pwdSID.ora :
 ORACLE_HOME\database pour window
 ORACLE_HOME/dbs
pour Unix
XEROX formation
Page 87
ORACLE 11G – Administration
Administration de la sécurité
XI.2.2 Manipulations de rôles
CREATION D'UN ROLE
CREATE ROLE nom_role
[ NOT IDENTIFIED ou
IDENTIFIED { BY passwd | USING [schema.]package }
] ;
NOT IDENTIFIED
IDENTIFIED BY
USING package
L'utilisateur qui possède ce rôle peut l'activer sans donner de mot de passe.
L'utilisateur doit donner le mot de passe pour activer ce rôle.
Pour créer un rôle qui n'est activé qu'à travers l'utilisation d'un package.
AFFECTATION DE PRIVILEGES A UN ROLE
GRANT privilege1,...,role1,... TO nom_role;
AFFECTATION DES PRIVILEGES D'UN ROLE A UN COMPTE
GRANT nom_role TO nom_user;
ATTRIBUTION D'UN ROLE PAR DEFAUT A UN UTILISATEUR
Par défaut, tous les rôles octroyés à un compte sont activés, lorsque ce compte se connecte.
ALTER USER nom_user
DEFAULT ROLE role1, role2 ou
ALL [ EXCEPT role1, role2.] ou
NONE
ACTIVATION D'UN ROLE
Permet à un utilisateur d'activer/désactiver de manière sélective, les rôles qui lui sont octroyés.
SET ROLE role1 [ IDENTIFIED BY password ], role2...
ALL [ EXCEPT role1, role2.]
NONE
ou
ou
SUPPRESSION D'UN ROLE
DROP ROLE
nom_rôle ;
XEROX formation
Page 88
ORACLE 11G – Administration
Administration de la sécurité
XI.3
Gestion des privilèges
Un privilège est un droit d’exécuter un certain type de requête SQL, ou d’accéder à un objet dont on n’est
pas propriétaire. Il existe deux catégories de privilèges :
 Privilèges système.
 Privilèges sur objets.
XI.3.1 Les privilèges système
Permettent à l'utilisateur d'effectuer des opérations systèmes (comme créer un objet Oracle : table, vue,
procédure...) dans la base de données. Les privilèges système ne sont pas dépendants de l'existence d'un
schéma à l'inverse des privilèges d'objets.
Liste des privilèges système
SQL> SELECT NAME FROM SYSTEM_PRIVILEGE_MAP ORDER BY 1;
NAME
---------------------------------------ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
ADMINISTER SECURITY
ALTER ANY CLUSTER
ALTER ANY DIMENSION
ALTER ANY EVALUATION CONTEXT
ALTER ANY INDEX
...
UNDER ANY TYPE
UNDER ANY VIEW
UNLIMITED TABLESPACE
UPDATE ANY TABLE
WRITEDOWN
WRITEDOWN DBLOW
WRITEUP
WRITEUP DBHIGH
157 ligne(s) sélectionnée(s).
ATTRIBUTION D'UN PRIVILEGE SYSTEME
GRANT privilège_système1, …
TO
user ou role ou PUBLIC
[ WITH ADMIN OPTION ]
RETRAIT D'UN PRIVILEGE SYSTEME
REVOKE privilège_système1, …
FROM
user ou role ou PUBLIC
XEROX formation
Page 89
ORACLE 11G – Administration
Administration de la sécurité
Vues du dictionnaire de données
DBA_SYS_PRIVS, DBA_ROLE_PRIVS, system_privilege_map...
XI.3.2 Les privilèges sur objets
Ces privilèges permettent à l'utilisateur d'accéder à des objets d'autre schéma de la base de données (table,
une vue, procédure, fonction...). Le propriétaire possède tous les droits sur "ses" objets. Par défaut, un
utilisateur n'a pas le droit d'accèder à un objet dont il n'est pas propriétaire (sauf si l'objet appartient au
schéma PUBLIC).
SELECT
INSERT
UPDATE
DELETE
REFERENCES
ALTER
INDEX
EXECUTE
READ
TABLE
X
X
X
X
X
X
X
VUE
X
x
x
x
SEQUENCE
X
PROCEDURE
DIRECTORY
X
X
X
ATTRIBUTION D'UN PRIVILEGE SUR OBJET
GRANT ALL,
privilège1, …
ON
[schéma.]objet
TO
user ou role ou PUBLIC
[ WITH GRANT OPTION ]
Le privilège UPDATE peut être restreint aux colonnes dont le nom figure entre parenthèses.
WITH GRANT OPTION permet au receveur d'accorder à d'autres utilisateurs.
RETRAIT D'UN PRIVILEGE SUR OBJET
GRANT ALL,
privilège1, …
ON
[schéma.]objet
FROM
user ou role ou PUBLIC
XEROX formation
Page 90
ORACLE 11G – Administration
Administration de la sécurité
XI.4
Les comptes utilisateur (schémas)
CREATION DES UTILISATEURS
Toutes ces options peuvent être modifiées par ALTER USER.
CREATE USER
nom_compte
IDENTIFIED BY mot_de_passe ou EXTERNALLY ou GLOBALLY
DEFAULT TABLESPACE
tablespace_par_défaut
TEMPORARY TABLESPACE tablespace_temporaire_par_défaut
QUOTA q ON tablespace, …
PROFILE nom_profile
PASSWORD EXPIRE
ACCOUNT UNLOCK ou LOCK
IDENTIFIED BY
 mot de passe
:

:
EXTERNALLY
(déconseillé par
Oracle)

GLOBALLY
DEFAULT TABLESPACE
:
:
TEMPORARY TABLESPACE :
QUOTA
PROFILE
PASSWORD EXPIRE
ACCOUNT
:
:
:
:
Définit la façon dont on vérifie l’identité de l’utilisateur.
L’utilisateur est identifié par un mot de passe, limité à 30 caractères et
case-sensitive par défaut depuis la V11g.
Oracle suppose que si l'authentification de l'utilisateur est validée au niveau
du système d'exploitation, elle l'est alors également au niveau de la base.
Cette clause impose la présence de 2 paramètres :
 REMOTE_OS_AUTHENT=TRUE (par défaut FALSE)
 OS_AUTHENT_PREFIX="xxx" (en général OPS$)
qui servira de préfixe au nom de compte autorisé à ce type de connexion
Pour une authentification au niveau réseau (service d'annuaire…).
Tablespace par défaut de l'utilisateur
(par défaut celui prévu dans le create database)
Tablespace temporaire par défaut de l'utilisateur
(par défaut celui prévu dans le create database)
Permet de limiter la quantité d'espace accordé à l'utilisateur.
Identifie le profil associé à l'utilisateur.
Pour forcer l'utilisateur à changer son mot de passe à la première connexion.
Pour déverrouiller (UNLOCK) ou verrouiller (LOCK) le compte.
SUPPRESSION DES UTILISATEURS
DROP USER nom_compte [ CASCADE ]
CASCADE
Obligatoire si l'utilisateur est propriétaire d'objets
XEROX formation
Page 91
ORACLE 11G – Administration
Administration de la sécurité
GESTION DES MOTS DE PASSE

La vue DBA_USERS_WITH_DEFPWD, permet de lister les comptes Oracle utilisant leur mot de passe
par défaut (scott/tiger, par exemple).

Pour activer les règles de gestion des mots de passe de la V11g (après avoir créé une base
manuellement, par exemple), il faut exécuter la procédure
$ORACLE_HOME/rdbms/admin/secconf.sql. Cette procédure affecte la gestion des mots de passe et
la mise en œuvre de l’audit.

Pour désactiver les règles de gestion des mots de passe de la V11g (après avoir créé une base via
DBCA, par exemple), il faut exécuter la procédure $ORACLE_HOME/rdbms/admin/undopwd.sql. Cette
procédure n’affecte que la gestion des mots de passe et ne concerne pas l’audit.

La vérification de la complexité des mots de passe est effectuée par une fonction
(password_verify) dont le script se trouve dans $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.
Cette fonction impose les contraintes suivantes lors du choix d’un mot de passe :
 8 caractères minimum et 30 caractères maximun.
 Le nom de l’utilisateur ou du serveur sont interdits (y compris avec un nombre de 1 à 100 au bout).
 Le mots « simples » sont interdits (par exemple, oracle, oracle666,password2, abcdef, etc...).
 Le mot de passe doit inclure au moins une lettre et un chiffre.
 Le mot de passe doit présenter au moins 3 lettres différentes, par rapport au précédent.

La sensibilité à la « casse » des mots de passe est contrôlée par le paramètre d’initialisation
dynamique : SEC_CASE_SENSITIVE_LOGON=TRUE|FALSE.
La colonne PASSWORD_VERSIONS de la vue DBA_USERS permet de détecter si le mot de passe d’un
utilisateur est « case-sensitive » ou pas.
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
USERNAME
-----------------------------JONES
ADAMS
CLARK
PRESTON
BLAKE




PASSWORD_VERSIONS
----------------10G 11G
10G 11G
10G 11G
11G
10G
Les mots de passe des comptes Jones, Adams et Clark, ont été créés en V10g, puis modifiés en
V11g, après import. Ils sont donc « case-sensitives ».
Le mot de passe du compte Preston a été créé en V11g. Il est donc « case-sensitive ».
Le mot de passe du compte Blake a été créé en V10g, mais n’a pas été modifié en V11g, après
import. Il est donc « case-insensitive ».
La sensibilité à la « casse » des mots de passe du fichier de mot de passe externe est contrôlée par
l’arguement IGNORECASE=N|Y.
XEROX formation
Page 92
ORACLE 11G – Administration
Administration de la sécurité
XI.5
Gestion des ressources par les profils
Un profil permet de limiter l'utilisation de certaines ressources de la base. Plusieurs profils peuvent être
créés. Après avoir élaboré une politique de gestion des ressources, l’administrateur créé et assigne le profil
adéquat à chaque groupe d’utilisateurs de la base.
A l'inverse des rôles, un utilisateur ne peut être associé qu'à un seul profil.
Le profil DEFAULT est assigné par Oracle par défaut à un compte lors de sa création. Depuis la V11g, ce
profil établit un certain nombre de limites, par défaut.
PARAMETRE D'ACTIVATION DES PROFILS
resource_limit


FALSE
TRUE
:
:
:
(Paramètre dynamique)
Les profils ne sont pas actifs (défaut).
Les profils sont actifs.
CREATION DE PROFIL
Chaque paramètre admet l'une des 3 valeurs :
 UNLIMITED pour une utilisation sans limite.
 DEFAULT pour utiliser la valeur prévue dans le profil DEFAULT.
 valeur (entier) pour affecter une valeur spécifique.
Pour les paramètres dont l’unité est le jour, il est possible d’utiliser des fractions (1/24 pour une heure,
1/1440 pour 1 minute, ...).
Toutes les options peuvent être modifiées par ALTER PROFILE.
XEROX formation
Page 93
ORACLE 11G – Administration
Administration de la sécurité
CREATE PROFILE nom_profile LIMIT
-- Paramètres ressources
SESSIONS_PER_USER valeur
CPU_PER_SESSION valeur
CPU_PER_CALL valeur
CONNECT_TIME valeur
IDLE_TIME valeur
LOGICAL_READS_PER_SESSION valeur
LOGICAL_READS_PER_CALL valeur
PRIVATE_SGA valeur
FAILED_LOGIN_ATTEMPTS valeur
-- Paramètres mots de passe
PASSWORD_LIFE_TIME valeur
PASSWORD_REUSE_TIME valeur
PASSWORD_REUSE_MAX valeur
PASSWORD_LOCK_TIME valeur
PASSWORD_GRACE_TIME valeur
PASSWORD_VERIFY_FUNCTION fonction
SESSIONS_PER_USER
Nombre maximum de sessions simultanées par utilisateur.
CPU_PER_SESSION
Temps CPU maximum par session (en 1/100 de seconde).
CPU_PER_CALL
Temps CPU maximum par appel (parse,execute,fetch) (en 1/100 de
seconde).
IDLE_TIME
Temps d'inaction maximum (en minutes) du process serveur. La
transaction en cours est annulée et la session déconnectée.
CONNECT_TIME
Temps de connexion maximum (en minutes). La transaction en cours
est annulée et la session déconnectée.
LOGICAL_READS_PER_SESSION
Nombre maximum de lectures blocs (mémoire et disque) par session.
LOGICAL_READS_PER_CALL
Nombre maximum de lectures blocs par appel (parse,execute,fetch)
pour traiter une requête SQL.
PRIVATE_SGA
FAILED_LOGIN_ATTEMPTS
PASSWORD_LIFE_TIME
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
PASSWORD_LOCK_TIME
PASSWORD_GRACE_TIME
PASSWORD_VERIFY_FUNCTION
XEROX formation
Taille de l’espace mémoire privé que la session peut s’allouer dans la
shared pool (valide uniquement pour les configuration MTS.
Nombre de tentatives autorisées de connexion (10 par défaut).
Durée de vie (jours) du mot de passe (180 par défaut).
Nombre de jours avant q'un mot de passe puisse être réutilisé.
Nombre de changement de mot de passe, avant qu'un mot de passe
puisse être réutilisé.
Nombre de jours de verrouillage du compte lorsque le
FAILED_LOGIN_ATTEMPTS est atteint (1 par défaut).
Nombre de jours à partir duquel un warning est émis, avant que le
PASSWORD_LIFE_TIME ne soit atteint (7 par défaut).
Fonction effectuant des vérifications supplémentaires sur le mot de
passe (verify_function par défaut).
Page 94
ORACLE 11G – Administration
Administration de la sécurité
Remarques sur l’utilisation de PASSWORD_REUSE_TIME ET PASSWORD_REUSE_MAX
Pour être efficace, les 2 paramètres doivent être renseignés. Par exemple :
 Si PASSWORD_REUSE_TIME=60 et PASSWORD_REUSE_MAX=5, alors l’utilisateur pourra réutiliser le mot de
passe au bout de 60 jours, si toutefois il a déjà été changé 5 fois.
 Si PASSWORD_REUSE_TIME=60 et PASSWORD_REUSE_MAX=UNLIMITED, ou l’inverse, alors l’utilisateur ne
pourra jamais réutiliser le mot de passe.
La valeur par défaut pour les 2 paramètres est UNLIMITED.
ATTRIBUTION D'UN PROFIL A UN COMPTE
ALTER USER compte PROFILE nom_profile
SUPPRESSION D'UN PROFIL
DROP PROFILE nom_profile [ CASCADE ]
CASCADE est obligatoire si le profil est assigné à des utilisateurs. Après suppression, c'est le profil
DEFAULT qui est utilisé.
XEROX formation
Page 95
ORACLE 11G – Administration
Index
A
M
ADR .............................................................................................13
Allocation/Désallocation d'extents .............................................73
ALTER SESSION .....................................................................20
MANIPULATIONS DE FICHIERS DE DONNEES ............................. 61
Montage de la base de données ................................................ 39
B
bloc Oracle ..................................................................................71
C
CKPT ............................................................................................35
comptes utilisateur .....................................................................90
connexion ...................................................................................16
Création d’un segment ...............................................................69
création d'une base de données ................................................62
D
database buffer cache................................................................25
database link...............................................................................81
DBW ............................................................................................34
démarrage de l'instance .............................................................38
Démarrage de l'instance .............................................................39
dictionnaire de données ..............................................................8
distributions d'Oracle 11G ............................................................6
O
OF 11
Oracle Net .................................................................................. 78
ORACLE_BASE ......................................................................... 11
ORACLE_HOME ......................................................................... 11
ORADIM...................................................................................... 62
ORAPWD .................................................................................... 63
P
PARAMETRES D'INSTANCE ......................................................... 41
PGA ............................................................................................ 21
PMON ......................................................................................... 36
privilèges .................................................................................... 88
processus serveur ...................................................................... 20
profils ......................................................................................... 92
R
REDO LOG BUFFER ..................................................................... 29
rôles ........................................................................................... 84
F
S
FICHIER DE CONTROLE ................................................................46
FICHIERS DE DONNEES................................................................52
fichiers de paramètres ................................................................41
schéma ....................................................................................... 67
SEGMENT ................................................................................... 68
SEGMENTS TEMPORAIRES ......................................................... 76
SEGMENTS UNDO....................................................................... 76
SEGMENTS UTILISATEUR ............................................................ 68
session ....................................................................................... 16
SGA ............................................................................................. 24
SHARED POOL ............................................................................ 27
SHUTDOWN................................................................................ 40
SMON ......................................................................................... 35
STARTUP ..................................................................................... 38
STRUCTURE D’UNE LIGNE DE DONNEES..................................... 72
Structure du ROWID ................................................................... 72
structures physiques .................................................................. 44
G
GESTION DES MOTS DE PASSE ....................................................91
I
installation d’Oracle 11G ............................................................10
instance ......................................................................................23
J
JAVA POOL ..................................................................................29
JOURNAUX DE REPRISE ...............................................................48
L
LARGE POOL ...............................................................................29
Les structures logiques ...............................................................45
LGWR ..........................................................................................34
listener ........................................................................................79
XEROX formation
T
tablespace "UNDO" .................................................................... 58
TABLESPACES ............................................................................. 53
tablespaces gérés localement .................................................... 55
TABLESPACES PERMANENTS ...................................................... 56
tablespaces SYSTEM et SYSAUX ................................................. 58
TABLESPACES TEMPORAIRES ..................................................... 59
tnsnames .................................................................................... 79
Tuning PGA ................................................................................. 22
Tuning SGA ................................................................................. 30
Page 96