Réduire la taille d'un tablespace UNDO Oracle 9i/10g

1- Introduction

Pour les besoins d'une migration, il est nécessaire d'augmenter la taille du tablespace d'UNDO d'une instance Oracle le temps de la migration. Si l'option AUTOEXTEND a été appliquée sur un tablespace d'UNDO, celui-ci peut atteindre des tailles délirantes à la suite d'une transaction très volumineuse lancée malencontreusement. Quelque soit le cas de figure, un administrateur de bases Oracle est confronté un jour ou l'autre à devoir réduire la taille d'un tablespace d'UNDO.

La première idée qui vient à l'esprit consiste à utiliser la commande ALTER DATABASE avec l'option RESIZE pour réduire la taille du ou des fichiers sur lesquels repose le tablespace d'UNDO. Malheureusement, avec les versions 9i et 10g, cette opération n'est pas toujours permise si des segments d'annulation (rollback segments) occupent de l'espace au delà de la nouvelle taille désirée. L'erreur ORA-03297 est levée lorsque l'opération n'est pas autorisée :

alter database
datafile '/sop/oracle/RISK/UNDO_01.dbf' resize 500M;

alter database
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Dans cet exemple pratique, la méthodologie pour réduire un tablespace d'UNDO est présentée dans les deux cas de figure ci-dessous par rapport à la nouvelle taille souhaitée pour le tablespace d'UNDO :

• L'espace consommé par les segments d'annulation est inférieur (méthode #1).
• L'espace consommé par les segments d'annulation est supérieur (méthode #2).

2- Caractéristiques des tablespaces d'UNDO

2-1- Taille et fichiers des tablespaces d'UNDO (dba_tablespaces, dba_data_files)

Les vues dba_tablespaces (contents=UNDO) et dba_data_files permettent de retrouver les tablespaces d'UNDO d'une instance, leur taille et les fichiers associés.

Pour lister les tablespaces d'UNDO et la taille globale de chaque tablespace d'UNDO dans une instance :

select tablespace_name, 
        sum(bytes)/1024/1024  as "Taille (Mb)"
from dba_data_files 
where
  tablespace_name in 
         (select tablespace_name from dba_tablespaces where contents='UNDO')
group by tablespace_name

TABLESPACE_NAME                Taille (Mb)
------------------------------ -----------
UNDO2                                  300
UNDO                                 15000

Pour lister les tailles des fichiers associés aux tablespaces d'UNDO :

select tablespace_name,file_name,
        bytes/1024/1024  as "Taille (Mb)"
from dba_data_files
where
        tablespace_name in (select tablespace_name from dba_tablespaces where contents='UNDO')
order by tablespace_name, file_name

TABLESPACE_NAME     FILE_NAME                          Taille (Mb)
---------------     --------------------------------   ------------------
UNDO                /sop/oracle/RISK/UNDO_01.dbf       15000
UNDO2               /sop/oracle/RISK/UNDO2_01.dbf      100
UNDO2               /sop/oracle/RISK/UNDO2_02.dbf      200

2-2- Espace consommé par les segments d'annulation dans les tablespaces d'UNDO (dba_undo_extents)

La vue dba_undo_extents indique précisément ou de manière globale grâce à la colonne bytes la taille des segments d'annulation dans un tablespace d'UNDO.

Pour la vue globale :

select tablespace_name, sum(bytes)/1024/1024 as "Taille (Mb)"
from dba_undo_extents
group by tablespace_name
order by tablespace_name

TABLESPACE_NAME                Taille (Mb)
------------------------------ -----------
UNDO                                  4077
UNDO2                                4,375

Pour le détail de l'espace consommé par segment d'annulation (rollback segments) dans un tablespace d'UNDO :

select tablespace_name,  
       segment_name, 
       sum(bytes)/1024/1024 as "Taille (Mb)"
from dba_undo_extents
group by tablespace_name, segment_name
order by tablespace_name asc, 3 desc

TABLESPACE_NAME    SEGMENT_NAME                   Taille (Mb)
------------------ ------------------------------ -----------
UNDO               _SYSSMU20$                             592
UNDO               _SYSSMU3$                              552
UNDO               _SYSSMU13$                             480
UNDO               _SYSSMU1$                              480
UNDO               _SYSSMU16$                             464
UNDO               _SYSSMU26$                             216
UNDO               _SYSSMU6$                              208
 ...

3- Méthode de réduction #1 : l'espace consommé est inférieur à la taille souhaitée

Lorsque l'espace consommé par les segments d'annulation est inférieur à la nouvelle taille souhaitée pour le tablespace d'UNDO, la méthode ALTER DATABASE DATAFILE RESIZE est appliquable. Il n'y a aucun inconvénient à réduire le tablespace d'UNDO à 5Gb dans le cas pratique présenté dans cet article car l'espace consommé par les segments d'annulation est de 4 Gb.

alter database
datafile '/sop/oracle/RISK/UNDO_01.dbf' resize 5000M;

Database altered.

Dans ce cas de figure précis, la commande est lancée à chaud sans perturber les transactions en cours.

4- Méthode de réduction #2 : l'espace consommé est supérieur à la taille souhaitée

4-1- Les méthodes inutiles et vaines (drop rollback segment, alter rollback segment shrink to n Mb ...)

Pour réduire le tablespace d'UNDO à 500 Mb alors que les segments d'annulation consomment au total 4 Gb, il est inutile et vain d'essayer l'une des trois méthodes ci-dessous:

• Suppression des segments d'annulation. La suppression d'un segment d'annulation avec la commande drop rollback segment dans un tablespace d'UNDO est interdite.
  
  

  SQL> drop rollback segment "_SYSSMU13$";
  drop rollback segment "_SYSSMU13$"
  *
  ERROR at line 1:
  ORA-30025: DROP segment '_SYSSMU13$' (in undo tablespace) not allowed

  
  
• Réduction (shrink) des segments d'annulation. La réduction des segments d'annulation avec l'option shrink de la commande alter rollback segment est autorisée mais sans effet. Elle concerne uniquement les segments d'annulation en mode manuel et non les segments d'annulation en mode AUTO (paramètre d'initialisation undo_management = AUTO).
• Mise à 0 du paramètre système undo_retention. La réduction du paramètre système undo_retention à 0 avec la commande alter system set undo_retention=0 est également sans effet.

4-2- Réduction par recréation du tablespace d'UNDO

Recréer le tablespace d'UNDO avec une taille plus petite en passant par un tablespace d'UNDO tampon est la seule alternative lorsque les segments d'annulation consomment plus que la nouvelle taille souhaitée. Voici les étapes pour cette méthode :

1. Création d'un tablespace d'UNDO tampon :

create undo tablespace undo_tampon
datafile '/sop/oracle/RISK/undo_tampon.dbf' size 100M
extent management local
online;

Tablespace created.

2. Déclaration du nouveau tablespace d'UNDO tampon au niveau système :

alter system set undo_tablespace=undo_tampon;

System altered.

Sans cette modification système, le tablespace UNDO initial ne peut pas être supprimé avec l'erreur ORA-30013 levée (ORA-30013: undo tablespace 'UNDO' is currently in use)

3. Suppression du tablespace d'UNDO à réduire :

drop tablespace UNDO including contents;

Tablespace dropped.

L'option "including contents and datafiles" n'est pas donnée dans la commande drop tablespace afin de gagner du temps au niveau OS et réutiliser plus tard le fichier déjà existant sans le supprimer dans cette étape.

4. Recréation du tablespace d'UNDO avec la taille souhaitée (500 Mb) :

create undo tablespace UNDO
datafile '/sop/oracle/RISK/UNDO_01.dbf' size 500M reuse
extent management local
online;

Tablespace created.

5. Déclaration du tablespace d'UNDO au niveau système :

alter system set undo_tablespace=undo;

System altered.

6. Suppression du tablespace d'UNDO tampon (données et fichiers)

drop tablespace undo_tampon including contents and datafiles;

Tablespace dropped.

4-3- Recréation des tablespaces d'UNDO et transactions en cours

Dans cette méthode, si l'instance Oracle ne peut pas être arrêtée et que des transactions sont en cours pendant le lancement des commandes de suppression des tablespaces d'UNDO, l'erreur ORA-30013 est levée :

SQL> drop tablespace UNDO including contents;

drop tablespace UNDO including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO' is currently in use

Ce message indique qu'une transaction est en cours dans un segment d'annulation ou que des blocs dans un segment d'annulation pour une transaction validée (commit réalisé) n'ont pas encore expirés.

La requête ci-dessous permet de lister dans un tablespace d'UNDO les segments d'annulation en cours d'utilisation (ACTIVE) ou contenant des blocs n'ayant pas encore expirés (UNEXPIRED) :

select segment_name, 
    sum(bytes/1024/1024) as "Taille (Mb)",
    status
from dba_undo_extents
   where tablespace_name = 'UNDO'
   and status in ('ACTIVE','UNEXPIRED')
group by segment_name,status
order by 2 desc

SEGMENT_NAME                   Taille (Mb) STATUS
------------------------------ ----------- ---------
_SYSSMU27$                              30 ACTIVE

SEGMENT_NAME                   Taille (Mb) STATUS
------------------------------ ----------- ---------
_SYSSMU27$                              30 UNEXPIRED

Durant cette opération et dans un environnement hautement transactionnel, le démarrage de nouvelles transactions doit donc être temporairement gelé et il faut attendre que tous les blocs d'UNDO dans les segments d'annulation aient le statut EXPIRED. Gel à négocier...

Annexe