Solución a ORA-01555: instantánea demasiado antigua

Meses atrás comenté que llevaba unos días atascado con un par de errores que me estaban dando en la BBDD de la que puede decirse que soy el DBA.

Al final, después de mucha tarea de investigación, pruebas y algún tiempo para ver que nada se había roto ya puedo dar por cerrado el problema.

El problema en sí se manisfestaba porque determinados procesos daban los siguientes errores de ejecución (sin un patrón fijo, a veces pasaba y a veces no):

• ORA-30036: no se ha podido ampliar el segmento en 8 en el tablespace de deshacer 'UNDOTBS1'
• ORA-01555: instantánea demasiado antigua: número de segmento de rollback 3 con nombre "_SYSSMU3$" demasiado pequeño

La investigación me llevó a culpar a que no se libera el espacio de UNDO. Si es vuestro caso, podéis comprobarlo con la siguiente sentencia:

SELECT DISTINCT status, SUM (BYTES) / (1024 * 1024) AS size_mb, COUNT (*) FROM dba_undo_extents GROUP BY status

STATUS | SIZE_MB | COUNT(*)

UNEXPIRED | 54435,1875 | 56526
ACTIVE | 8,0983 | 1
EXPIRED | 3,4375 | 25

Los STATUS que pueden tener los segmentos son:

• ACTIVE: son transacciones con commit todavía pendientes
• EXPIRED: son transacciones commiteadas y que ya se pueden sobreescribir
• UNEXPIRED: son transacciones commiteadas pero que se guardan por un tiempo para dar consistencia de lectura a los datos.

Los dos errores que nos dan son:

• ORA-30036: Este se produce cuando nos quedamos sin espacio de UNDO_TBS (tenemos 96GB que son suficientes)
  
• ORA-01555: Este se produce cuando se sobreescribe el espacio UNEXPIRED y una consulta intenta recuperarlo
  

Con la versión 10g de Oracle, el espacio de undo se maneja automáticamente por Oracle a partir de un parámetro inicial (UNDO_RETENTION = 900 segundos) que dice cuanto tiempo se guardan las transacciones (tiempo que permanecen en estado UNEXPIRED) y cálculos estadísticos para modificarlo en función de la carga del sistema.

En nuestro caso, este tiene el siguiente valor:

SELECT TO_CHAR (begin_time, 'MM/DD/YYYY HH24:MI:SS') begin_time,
TO_CHAR (end_time, 'MM/DD/YYYY HH24:MI:SS') end_time,
tuned_undoretention AS tuned_undoretention
FROM v$undostat
WHERE ROWNUM <= 1;

BEGIN_TIME | END_TIME | TUNED_UNDORETENTION
02/03/2009 09:30:35 | 06/15/2009 09:45:34 | 345600

Viendo el valor del parámetro calculado automáticamente por Oracle es 345600s (4 DIAS!!!) que es muy, pero que muy alto. Esto significa que cada vez que hacemos un commit la información de UNDO se mantiene 4 días completos.

Se supone que los valores óptimos se pueden calcular con la siguiente query, en función de la carga de nuestro sistema:

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION [Sec]",
ROUND ((d.undo_size / (TO_NUMBER (f.VALUE) * g.undo_block_per_sec))
) "OPTIMAL UNDO RETENTION [Sec]"
FROM (SELECT SUM (a.BYTES) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.CONTENTS = 'UNDO'
AND c.status = 'ONLINE'
AND b.NAME = c.tablespace_name
AND a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(SELECT MAX (undoblks / ((end_time - begin_time) * 3600 * 24)
) undo_block_per_sec
FROM v$undostat) g
WHERE e.NAME = 'undo_retention' AND f.NAME = 'db_block_size'


ACTUAL SIZE [MByte] | UNDO RETENTION [Sec] | OPTIMAL UNDO[Sec]
98303,95 | 900 | 32087

Que son aproximadamente 9 horas.

Una vez vista esta incoherencia, investigué un poco porque nuestra BBDD calcula tan mal el parámetro de TUNED_UNDORETENTION y parece que es un BUG de Oracle en las versiones de Oracle 10g anteriores a la 10.0.2.4) [http://www.freelists.org/post/oracle-l/Poor-Performance-of-undo-space-management-in-102-bug-info].

La solución consiste es pasar de la gestión automática de Oracle y poner fijo el valor óptimo calculo por la sentencia anterior.

alter system set undo_retention = 32000;
alter system set "_undo_autotune" = false;

Después de estos cambios, no han vuelto ocurrir estos errores ni hemos apreciado otros daños colaterales por lo que podemos decir, que la solución ha funcionado correctamente.