2è PARTIE : GESTION DE L'ESPACE D'ANNULATION SOUS ORACLE9i
(SUITE ET FIN)
III - MODE AUTOMATIQUE DE GESTION DE L'ESPACE D'ANNULATION
Une instance Oracle est dite opérationnelle en mode automatique de gestion de l'annulation lorsque l'administrateur utilise la méthode "undo tablespace" de gestion de l'espace d'annulation.

III-1) Démarrage d'une instance en mode automatique de gestion d'annulation
Le positionnement du paramètre d'initialisation UNDO_MANAGEMENT à la valeur AUTO fait que la commande STARTUP démarre l'instance en mode automatique de gestion des annulations (en anglais : Automatic Undo Management mode ou en bref AUM).
Un undo tablespace dans lequel Oracle stockera les enregistrements d'annulation doit être disponible.
Quand l'instance démarre, Oracle sélectionne automatiquement le premier undo tablespace disponible. Et s'il n'y en a pas de disponible, l'instance utilise le rollback segment SYSTEM et écrit un message d'alerte dans le fichier d'alertes pour avertir que le système s'exécute sans un undo tablespace. Ceci est fortement déconseillé dans les circonstances normales.
Optionnellement, l'administrateur peut spécifier au démarrage qu'il souhaite que l'instance utilise un undo tablespace spécifique en positionnant le paramètre d'initialisation UNDO_TABLESPACE comme ci-après (ce qui suppose que le undo tablespace spécifié a été préalablement créé, sinon la commande startup échoue) :
UNDO_TABLESPACE = undotbs_01
Dans les versions ultérieures d'Oracle9i, si l'on crée une base de données sans demander la création d'un undo tablespace en même temps (clause UNDO TABLESPACE de la commande CREATE DATABASE), Oracle en crée automatiquement un, appelé SYS_UNDOTBS avec un fichier de données placé dans le répertoire $ORACLE_HOME/dbs et dont le nom est assigné par le système ; sa taille par défaut est de 100Mo.
On peut créer plusieurs undo tablespaces par base de données. Mais un seul peut être actif à un moment donné par instance.
A tout moment, l'administrateur peut basculer dynamique sur un undo tablespace différent en changeant en ligne le paramètre UNDO_TABLESPACE (commande ALTER SYSTEM). Cependant, s'il veut basculer entre les modes d'annulation automatique et manuel, il doit redémarrer la base de données.
On peut effectuer des opérations DDL (create, alter, drop) sur les undo tablespaces ; cependant, on ne peut pas effectuer des opérations DDL sur les undo segments. Toute tentative de ce genre se solde par le message "ORA-30019: Illegal rollback Segment opération in Automatic Undo mode". Pour éviter l'affichage de tels messages, il suffit de mettre le paramètre UNDO_SUPPRESS_ERRORS à TRUE.
Nous récapitulons dans les deux tableaux ci-dessous les principaux paramètres utilisés pour le mode automatique de gestion des annulations et ce qu'il faut en retenir.

Description des paramètres de gestion automatique d'annulation

PARAMETRE	DESCRIPTION ET COMMENTAIRES
UNDO_MANAGEMENT	Détermine si la gestion automatique des annulations (AUM) est activée dans la base de données. La valeur AUTO active l'AUM et la valeur MANUAL le désactive.
UNDO_RETENTION	Définit la longueur minimale de temps pendant lequel Oracle retient les données d'annulation après leur génération et après l'achèvement de la transaction les ayant générées. Oracle fait le meilleur effort pour retenir les données d'annulation pendant le temps demandé mais il n'y a aucune garantie.
UNDO_SUPPRESS_ERRORS	Permet de contrôler l'affichage de messages d'erreurs qui résulte de certaines commandes SQL quand la base de données est en mode automatique de gestion des annulations. Il faut être prudent lorsque l'on met ce paramètre à TRUE dans la mesure où il supprime tout message d'erreur émis lors de tentatives d'opérations manuelles quand on est en mode automatique.
UNDO_TABLESPACE	Définit un ou plusieurs undo tablespaces qui seraient utilisés par Oracle pour la gestion automatique des annulations. Si ce paramètre est positionné et UNDO_MANAGEMENT est mis à AUTO quand on exécute la commande "CREATE DATABASE", alors on doit inclure tous les tablespaces listés dans ce paramètre dans la commande "CREATE DATABASE" ; sinon cette dernière échoue. On peut lister plusieurs tablespaces pour ce paramètre mais seul le dernier tablespace listé sera utilisé (Oracle ne permet l'utilisation que d'un undo tablespace à un moment donné).

Spécificités des paramètres de gestion automatique d'annulation

PARAMETRE	VALEUR PAR DEFAUT	VALEURS VALIDES	DYNAMIQUE ?
UNDO_MANAGEMENT	MANUAL	AUTO, MANUAL	NON
UNDO_RETENTION	900 secondes	0 à la valeur maximale autorisée par 32 bits	OUI (immediat pour le système)
UNDO_SUPPRESS_ERRORS	TRUE	TRUE, FALSE	Immediat pour le système ; autorisé pour la session
UNDO_TABLESPACE	On peut utiliser ce paramètre pour spécifier le nom du undo tablespace à utiliser par l'instance. Si ce paramètre n'est pas spécifié, alors Oracle choisira le premier undo tablespace disponible sys_undotbs, ou le rollback segment system si aucun aucun undo tablespace n'est disponible.	Nom de undo tablespace valide. Plusieurs undo tablespaces ne sont pas supportés bien qu'Oracle ne génère pas une erreur.	Immediat pour le système

III-2) Informations relatives aux undo segments

VUE/TABLE	DESCRIPTION
DBA_SEGMENTS	Vue stockant les informations relatives aux segments créés dans la base de données : leur taille, tablespace, type, paramètres de stockage, etc.
DBA_ROLLBACK_SEGS	Vue stockant des informations relatives à tous les segments d'annulation : leur status, nom de tablespace, tailles, etc.
V$ROLLNAME	Vue stockant les numéros et noms de tous les segments d'annulation en ligne.
V$ROLLSTAT	Vue contenant les statistiques relatives aux annulations : taille de segment, valeur OPTIMAL, nombre de "morceaux depuis le démarrage de l'instance, nombre de transactions actives, extents, status, etc.
V$UNDOSTAT	Vue collectant des "photographies/instantanés" qui reflètent la performance du tablespace d'annulation comme aide à l'ajustement de sa taille pour supporter les exigences de charge du système qui évolue.

Quelques exemples de requêtes
- Les segments d’annulation de la base de données
SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs ;
- Statistiques sur les segments d’annulation actuellement utilisés
SQL> select n.name, s.extents, s.rssize, s.hwmsize, s.xacts, s.status frm v$rollname n, v$rollstat s
where n.usn = s.usn ;
- Vérification que les transactions en cours utilisent un segment d’annulation
SQL> select s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk from v$session s, v$transaction t
Where s.saddr = t.ses_addr ;

IV - BIBLIOGRAPHIE
1) Oracle9i DBA Fundamentals I – par BijuThomas et Bob Bryla – Chez Sybex ,Alameda (USA) 452 pages.
2) Support de cours – par R. MISSINHOUN – Vauréal 2004.
3) Automatic Undo Management (AUM) – http://www.orafaq.com/node/73
4) Talking tuning : Tuning Undo Tablespace – By Kimberly Floss – http://www.oracle.com/technology/oramag/05-jul/o45tuning.html.
5) Managing Undo Space – Oracle9i Database reference – http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a9651/undo.htm

1ère PARTIE : GESTION DE L'ESPACE D'ANNULATION SOUS ORACLE9I

I – INTRODUCTION ET GENERALITES
Jusqu'à la version 8i de son produit phare (la base de données Oracle) l'éditeur Oracle n'offre qu'un seul mode de gestion de l'espace d'annulation. Il s'agit du mode manuel. Dans ce mode, l'administrateur doit :
- Créer et gérer un nombre optimal de segments d'annulation (en anglais rollback segments ; c'est la terminologie utilisée dans le mode manuel de gestion de l'espace d'annulation) ;
- Assigner les transactions "volumineuses/longues", s'il en existe dans son environnement, à un ensemble particulier de segments d'annulation. Ceci suppose d'avoir préalablement procéder à une estimation, à défaut d'une mesure exacte, de la longueur de la transaction la plus longue devant s'exécuter dans la base de données ;
- Mettre des segments additionnels en ligne en fonction des changements des demandes des processus de traitement ;
- Effectuer une multitude de tâches de gestion liées aux segments d'annulation.
Les administrateurs savent que cette série de tâches est coûteuse en termes de temps (c'est-à-dire chronophage). L'éditeur Oracle aussi.
C'est pourquoi ce dernier a mis en place, à partir de la version Oracle9i, un deuxième mode, le mode automatique de gestion de l'espace d'annulation. Il s'agit d'un mode qui facilite relativement bien la vie aux administrateurs comparativement au mode manuel. Cependant ce dernier reste utilisable pour des raisons de compatibilité arrière.
Dans la terminologie Oracle9i, les segments d'annulation s'appellent "undo segments".
Contrairement aux "rollback segments", l'administrateur ne crée plus et ne gère plus des undo segments individuels. La base de données Oracle le fait pour l'administrateur lorsque ce dernier crée le tablespace d'annulation (undo tablespace dans la terminologie Oracle9i). Toutes les transactions dans une instance partagent un seul undo tablespace. Toute transaction qui s'exécute peut consommer de l'espace libre dans le undo tablespace et une fois la transaction finie, son espace d'annulation est libéré. C'est dire que l'espace pour les undo segments est dynamiquement alloué, consommé, libéré et réutilisé sous le contrôle de la base de données Oracle (et non plus sous le contrôle de l'administrateur comme c'est le cas en mode manuel).
Le présent article est destiné à faire le point sur les deux modes, en les mettant en perspective et aussi à montrer comment on peut faire le passage du mode manuel au mode automatique sur une base Oracle9i.


II - MODE MANUEL DE GESTION DE L'ESPACE D'ANNULATION
Une instance Oracle est dite opérationnelle en mode manuel de gestion de l'annulation lorsque l'administrateur utilise la méthode "rollback segment" de gestion de l'espace d'annulation.

II-1) Démarrage d'une instance en mode manuel de gestion d'annulation
Le positionnement du paramètre d'initialisation UNDO_MANAGEMENT à la valeur MANUAL fait que la commande STARTUP démarre l'instance en mode manuel de gestion des annulations (en anglais : manual undo management mode).
Si le paramètre d'initialisation UNDO_MANAGEMENT n'est pas spécifié, l'instance démarre en mode manuel de gestion des annulations.
Quand l'instance démarre, elle met un certain nombre de rollback segments en ligne tel que déterminés par :
- le paramètre d'initialisation ROLLBACK_SEGMENTS ;
- les paramètres d'initialisation TRANSACTIONS et TRANSACTIONS_PER_ROLLBACK_SEGMENT.
Le tableau ci-dessous décrit brièvement ces paramètres.

PARAMETRE D'INITIALISATION	DESCRIPTION
ROLLBACK_SEGMENTS	Spécifie les rollback segments à "acquérir" au démarrage de l'instance
TRANSACTIONS	Spécifie le nombre maximal de transactions concurrentes
TRANSACTIONS_PER_ROLLBACK_SEGMENT	Spécifie le nombre de transactions concurrentes que chaque rollback segment espère "supporter".
MAX_ROLLBACK_SEGMENTS	Spécifie le nombre maximal de rollback segments qui peuvent être en ligne pour une instance.

II-2) Détermination du nombre et des tailles des rollback segments
Un ou plusieurs tablespaces peuvent être créés pour les rollback segments. Et ces derniers sont créés manuellement dans ces tablespaces par l'administrateur qui doit préalablement calculer/estimer leur nombre et tailles.
La taille d'un rollback segment est déterminée par les valeurs du paramètre de stockage du rollback segment (Voir plus loin les commandes "CREATE ROLLBACK SEGMENT" et "ALTER ROLLBACK SEGMENT"). Cette taille peut affecter les performances de la base de données Oracle.
Pour le nombre de rollback segments, Oracle fournit les recommandations générales que nous reproduisons dans le tableau ci-dessous.

Nombre de transactions concurrentes (N)	Nombre de rollback segments recommandés
N inférieur à 16	4
16 inf. ou égal à N et N inf. à 32	8
32 inf. ou égal à N	N/4

En ce qui concerne les tailles, Oracle donne les règles qui suivent :
- Assigner de grands rollback segments aux transactions qui modifient des données pendant que ces dernières sont sélectionnées (commande SQL select) par des requêtes longues qui en général requièrent l'accès à des rollback segments pour reconstruire une version consistante (en lecture) des données modifiées.
- Assigner de grands rollback segments aux transactions qui modifient de grandes quantités de données et qui du coup, génèrent beaucoup d'entrées volumineuses d'annulation, à des fins d'amélioration des performances de telles transactions.
- Assigner les transactions OLTP (Online Transaction Processing) à de petits rollback segments qui ont une probabilité plus forte de rester stockés dans le cache de tampon où ils peuvent être accédés rapidement. Un rollback segment typique OLTP doit avoir deux extents et une taille de 10 Ko environ. La meilleure façon d'éviter les contentions, c'est de créer beaucoup de rollback segments et d'assigner chaque transaction à sa propre rollback segment.
- Utiliser la commande SET TRANSACTION pour assigner des transactions aux rollback segments appropriés comme ci-après :
SET TRANSACTION USE ROLLBACK SEGMENT rbs_oltp13

II-3) Types et règles de fonctionnement des rollback segments
Une transaction ne peut utiliser qu'un seul rollback segment pour stocker des enregistrements de rollback. En revanche, plusieurs transactions peuvent écrire dans un même rollback segment.
Oracle distingue les rollback segments SYSTEM et non-SYSTEM.
Les modifications apportées aux objets du tablespace SYSTEM sont stockées dans le rollback segment SYSTEM qui est créé automatiquement lors de la création de la base de données.
Une base de données qui contient des tablespaces non-SYSTEM nécessite au moins deux rollback segments non-SYSTEM.
Les rollback segments non-SYSTEM peuvent être privés, publics ou en état différé.
Par défaut, tous les rollback segments créés sont privés, ce qui signifie que seule l'instance courante y a accès.
Un rollback segment privé n'est disponible que pour une seule instance et pour tous les utilisateurs de l'instance. Il est automatiquement acquis par l'instance Oracle s'il est spécifié au travers du paramètre d'initialisation ROLLBACK_SEGMENTS. Mais il peut également être acquis manuellement lorsque l'administrateur exécute la commande "ALTER ROLLBACK SEGMENT ONLINE ;"
Un rollback segment public est disponible pour toute instance d'un environnement Real Application Cluster par exemple. Mais un tel rollback segment ne peut être utilisé que par une instance Oracle à la fois. Il est créé dans la base de données à l'aide de la commande "CREATE PUBLIC ROLLBACK SEGMENT".
Oracle détermine le nombre rollback publics à accéder en se basant sur les paramètres d'initialisation TRANSACTIONS et TRANSACTIONS_PER_ROLLBACK_SEGMENT.
Les rollback segments en état différé sont automatiquement créés lorsqu'un utilisateur annule une transaction qui a modifié des données dans un tablespace qui est actuellement hors ligne (offline). Lorsque le tablespace est mis en ligne (online), les entrées de rollback sont utilisées pour restaurer les anciennes valeurs de lignes et le rollback en état différé utilisé est supprimé.

II-4) Création et modification des rollback segments
Création d'un rollback segment
La syntaxe de base de la commande "CREATE ROLLBACK" pour la création d'un rollback segment est :
CREATE [PUBLIC] ROLLBACK SEGMENT
STORAGE(INITIAL [KM]
NEXT [KM]
MINEXTENTS
MAXEXTENTS
OPTIMAL [KM])

Les paramètres et options de la commande sont synthétisés dans le tableau ci-dessous.

PARAMETRE	COMMENTAIRES
INITIAL	Oracle recommande que INITIAL=NEXT afin que les extents soient toujours de même taille ; le paramètre PCTINCREASE ne pouvant être spécifié, il vaut toujours 0.
NEXT	-
MINEXTENTS	Ne peut avoir une valeur inférieure à 2 pour les rollback segments.
MAXEXTENTS	Oracle recommande d'éviter d'attribuer la valeur UNLIMITED à ce paramètre car cela pourrait provoquer une extension inutile d'un rollback segment et même de fichiers de données à la suite d'une erreur de programme.
OPTIMAL	Est utilisé pour spécifier la taille à laquelle un rollback segment se rétrécira après s'être étendu automatiquement. Définir la valeur de ce paramètre en fonction de l'espace nécessaire pour une transaction moyenne. Si l'on ne dispose pas des informations nécessaires, indiquer une valeur correspondant à la taille initiale (MINEXTENTS * EXTENT_SIZE). C'est la valeur minimale recommandée.

Quand on crée un rollback segment, il est initialement hors ligne (offline en anglais) et non disponible pour les transactions. Mais il peut être mis dynamiquement en ligne (voir la commande ALTER ROLLBACK SEGMENT" ci-dessous).

Modification d'un rollback segment
La syntaxe complète de modification d'un rollback segment se présente comme indiqué ci-dessous :
ALTER ROLLBACK SEGMENT
{ONLINE OFFLINE
STORAGE(NEXT MAXEXTENTS OPTIMAL )
SHRINK [TO [KM]]
La nature d'un rollback segment, public ou privé ne peut être modifié par la suite. La solution consiste à le supprimer et à le recréer.
L'option SHRINK permet au rollback segment de se rétrécir à la taille spécifiée ou à la valeur à laquelle le paramètre OPTIMAL a été positionné. Toutefois le rétrécissement s'arrête si un extent ne peut pas être libéré parce qu'il est actif.
Lors de la mise hors ligne d'un rollback segment, si des transactions l'utilisent encore, l'état du rollback segment passe à "PENDING OFFLINE" et est consultable dans la vue de performance dynamique V$ROLLSTAT. Une fois les transactions terminées, le rollback segment est mis hors ligne.
La suppression d'un rollback segment se fait à l'aide de la commande
DROP ROLLBACK SEGMENT

II-5) Informations et statistiques relatives aux rollback segments
Les informations générales sur les rollback segments sont stockées dans la vue DBA_ROLLBACK_SEGS. On y trouve, entre autres, des informations d'identification, d'emplacement, le type et l'état des rollback segments.

Les statistiques sont stockées dans la vue de performance dynamique V$ROLLSTAT. On y trouve entre autres, le numéro de rollback segment (colonne USN), la taille actuelle (colonne RSSIZE), le nombre de transactions utilisant le rollback segment (colonne XACTS), la valeur OPTIMAL du segment (colonne OPTSIZE) et l'état du rollback segment (colonne STATUS). On fait souvent une jointure entre cette vue et V$ROLLNAME afin d'avoir les noms de rollback segment dans les requêtes.

On détermine souvent l'activité courante des rollback segments en identifiant les sessions qui les utilisent à l'aide d'une requête qui fait recours aux vues V$SESSION, V$TRANSACTION et V$ROLLNAME et est similaire à celle qui suit :
SQL> select s.username, r.name, t.used_ublk "nbre rb blocs generes"
2 from v$session s, v$rollname r, v$transaction t
3 where s.saddr = t.ses_addr
4 and t.xidusn = r.usn ;

[A SUIVRE ...]