UNDO Oracle : Gestion des données non validées

UNDO Oracle : Comprendre et gérer les segments d’annulation

L’UNDO Oracle est un mécanisme fondamental qui permet d’annuler des transactions non validées, d’assurer la cohérence de lecture et de récupérer des données après une erreur. Dans tout environnement Oracle professionnel, maîtriser l’UNDO est indispensable pour garantir l’intégrité des données et la stabilité des applications critiques.

Définition et utilisation de l’UNDO Oracle

Dans Oracle Database, l’UNDO (ou segment d’annulation) désigne l’ensemble des informations stockées par le moteur pour permettre :

Le rollback de transactions : annuler les modifications d’une transaction non encore validée (non COMMITée).
La cohérence en lecture (Read Consistency) : chaque session lit une version cohérente des données à un instant donné, même si d’autres sessions modifient ces données en parallèle.
La récupération après incident : lors d’un crash instance, Oracle utilise les données UNDO pour annuler les transactions incomplètes au redémarrage.
Le Flashback : les fonctionnalités Oracle Flashback (Flashback Query, Flashback Table) s’appuient directement sur les données UNDO pour remonter dans le temps.

Mode de gestion : Automatic Undo Management (AUM)

Depuis Oracle 9i, Oracle recommande et utilise par défaut l’Automatic Undo Management (AUM). Dans ce mode, un tablespace UNDO dédié est créé, et Oracle gère automatiquement l’allocation des segments d’annulation. Il remplace l’ancien système des rollback segments manuels.

Cas d’usage en entreprise :

Application bancaire : annulation d’un virement mal saisi avant validation finale.
ERP industriel : garantir qu’un rapport lancé pendant une mise à jour de stock affiche des données cohérentes.
Conformité RGPD : utilisation du Flashback pour auditer des données à une date précise.

Syntaxe et paramètres essentiels liés à l’UNDO Oracle

La gestion de l’UNDO Oracle passe par plusieurs éléments de syntaxe : la création du tablespace UNDO, les paramètres d’initialisation et les commandes de supervision.

Création d’un tablespace UNDO


CREATE UNDO TABLESPACE undotbs_new
  DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs_new01.dbf'
  SIZE 500M
  AUTOEXTEND ON
  NEXT 100M
  MAXSIZE 2G;

Explication des paramètres :

CREATE UNDO TABLESPACE : déclare un tablespace de type UNDO.
DATAFILE : chemin physique du fichier de données associé.
SIZE : taille initiale allouée.
AUTOEXTEND ON : extension automatique activée si l’espace est insuffisant.
NEXT : incrément d’extension automatique.
MAXSIZE : taille maximale autorisée pour ce fichier.

Paramètres d’initialisation clés

UNDO_MANAGEMENT = AUTO : active le mode AUM (valeur recommandée).
UNDO_TABLESPACE = undotbs_new : désigne le tablespace UNDO actif pour l’instance.
UNDO_RETENTION = 900 : durée minimale (en secondes) pendant laquelle Oracle conserve les données UNDO, même après COMMIT. Essentiel pour le Flashback.


-- Modifier dynamiquement la rétention UNDO
ALTER SYSTEM SET UNDO_RETENTION = 1800 SCOPE = BOTH;

-- Changer de tablespace UNDO actif
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_new SCOPE = BOTH;

Exemples pratiques d’utilisation de l’UNDO Oracle

Exemple 1 – Annulation d’une transaction (ROLLBACK)

Contexte métier : Un opérateur d’une application de gestion des commandes met à jour par erreur le prix de tous les articles d’une table PRODUITS. Avant de valider (COMMIT), il réalise son erreur et annule la transaction grâce au mécanisme UNDO.


-- Début de la transaction (implicite sous Oracle)
UPDATE PRODUITS
SET PRIX = PRIX * 1.50  -- Hausse accidentelle de 50% sur tous les produits
WHERE 1 = 1;            -- Aucun filtre : tous les enregistrements sont modifiés

-- L'opérateur réalise son erreur avant le COMMIT
-- Oracle stocke les anciennes valeurs dans le segment UNDO

ROLLBACK; -- Oracle utilise les données UNDO pour restaurer l'état précédent

-- Vérification : les prix sont bien restaurés
SELECT PRODUIT_ID, NOM, PRIX FROM PRODUITS FETCH FIRST 5 ROWS ONLY;

Dans cet exemple, Oracle n’a jamais écrit les nouvelles valeurs de façon permanente. Le segment UNDO contenait les anciennes valeurs, permettant une annulation immédiate et complète.

Exemple 2 – Flashback Query grâce aux données UNDO

Contexte métier : Un administrateur d’une base RH constate qu’une suppression de données a été COMMITée par erreur il y a 10 minutes. Grâce à UNDO_RETENTION correctement configuré, il peut interroger les données telles qu’elles existaient avant la suppression.


-- Vérifier l'heure actuelle
SELECT SYSDATE FROM DUAL;
-- Résultat : 2024-05-15 14:30:00

-- Flashback Query : interroger la table EMPLOYES
-- telle qu'elle existait 15 minutes avant la suppression accidentelle
SELECT EMP_ID, NOM, PRENOM, SALAIRE
FROM EMPLOYES AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE DEPARTEMENT_ID = 10;

-- Réinsérer les lignes supprimées par erreur
INSERT INTO EMPLOYES
SELECT *
FROM EMPLOYES AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
WHERE DEPARTEMENT_ID = 10
  AND EMP_ID NOT IN (SELECT EMP_ID FROM EMPLOYES);

COMMIT;

Cette opération n’est possible que si les données UNDO sont encore disponibles, c’est-à-dire si UNDO_RETENTION est suffisamment élevé et que le tablespace UNDO n’a pas réutilisé ces segments.

Erreurs courantes liées à l’UNDO Oracle

Erreur ORA-01555 : snapshot too old

Message Oracle :

ORA-01555: snapshot too old: rollback segment number X with name "..." too small

Cause : Cette erreur survient lorsque Oracle n’est plus en mesure de fournir une image cohérente des données pour une requête longue. Les données UNDO nécessaires ont été écrasées car le tablespace UNDO est trop petit ou UNDO_RETENTION est trop faible par rapport à la durée des transactions.

Solutions :

Augmenter la valeur de UNDO_RETENTION : ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE = BOTH;
Agrandir le tablespace UNDO en ajoutant un datafile : ALTER TABLESPACE undotbs1 ADD DATAFILE '/u01/.../undotbs1_02.dbf' SIZE 1G AUTOEXTEND ON;
Activer la rétention garantie pour que Oracle ne réutilise jamais les segments UNDO avant expiration : ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
Optimiser les requêtes longues pour qu’elles s’exécutent plus rapidement.

Résumé : points clés de l’UNDO Oracle

Concept	Description
UNDO	Mécanisme de stockage des anciennes valeurs avant modification
AUM	Automatic Undo Management : gestion automatique recommandée depuis Oracle 9i
UNDO_RETENTION	Durée de conservation des données UNDO après COMMIT (en secondes)
ROLLBACK	Utilise les données UNDO pour annuler une transaction non validée
Flashback Query	Interroge les données à un instant passé grâce aux segments UNDO
ORA-01555	Erreur « snapshot too old » : données UNDO insuffisantes pour la cohérence de lecture
RETENTION GUARANTEE	Empêche Oracle de réutiliser les segments UNDO avant expiration

Bonnes pratiques Oracle

Dimensionner correctement le tablespace UNDO en fonction de la durée des transactions les plus longues de votre application. Utilisez le conseiller UNDO disponible dans Oracle Enterprise Manager ou via la vue V$UNDOSTAT pour obtenir des recommandations basées sur l’activité réelle de votre instance.
Activer RETENTION GUARANTEE pour les environnements utilisant le Flashback ou exécutant des requêtes analytiques longues. Cela garantit que les données UNDO restent disponibles pendant toute la durée définie par UNDO_RETENTION, évitant ainsi les erreurs ORA-01555 critiques en production.

Aller plus loin

Pour approfondir vos connaissances sur les mécanismes transactionnels et de récupération dans Oracle, nous vous recommandons les sujets suivants :

Transactions Oracle : COMMIT, ROLLBACK et SAVEPOINT — Maîtrisez le cycle de vie complet d’une transaction Oracle et les points de sauvegarde intermédiaires.
Flashback Oracle : requêtes et récupération de données — Découvrez toutes les fonctionnalités Flashback d’Oracle pour interroger et restaurer des données historiques.
Tablespaces Oracle : création et gestion — Apprenez à créer, dimensionner et administrer les tablespaces Oracle, dont le tablespace UNDO.