A partir de Oracle9i, los segmentos de rollback son re-nombrados como UNDO logs. Tradicionalmente la información de una transacción para deshacerla era almacenada en segmentos de Rollback hasta que una instrucción COMMIT o ROLLBACK era ejecutada.
La parte más destacada de la nueva funcionalidad es que permite la gestión automática de UNDO, el DBA puede especificar el tiempo que la información de UNDO debe mantenerse después de un COMMIT, previendo errores de "snapshot too old" en consultas que tengan una duración muy larga.
Esto se hace estableciendo el parámetro UNDO_RETENTION. El valor por defecto es 900 segundos (5 minutos), y puedes cambiar este parámetro para garantizar que Oracle mantiene UNDO logs por períodos largos de tiempo.
En lugar de tener que definir y gestionar los segmentos de rollback, sólo tienes que definir un tablespace UNDO y dejar que Oracle se encargue de lo demás. Para hacer que Oracle se encargue de forma automática del UNDO log tienes que tener creado un tablespace de UNDO (artículo Como crear un tablespace UNDO en Oracle 11g) y establecer el parámetro UNDO_MANAGEMENT = AUTO.
Para establecer el parámetro UNDO_MANAGEMENT a AUTO seguiremos el procedimiento:
Si tenemos implementado spfile nos conectamos a la base de datos Oracle mediante sqlplus y como usuario sys ejecutamos lo siguiente:
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
Si utilizamos init.ora
Modificamos el init.ora correspondiente añadiendo la línea,
UNDO_MANAGEMENT=AUTO
En ambos casos una vez realizado el cambio tenemos que rearrancar la base de datos para que el cambio tome efecto.
Tanto el tamaño del tablespace de UNDO como el valor del parámetro UNDO_RETENTION habrá que ajustarlos.
¿Como Calcular UNDO_RETENTION para un determinado tamaño del tablespace de UNDO?
Puedes optar por asignar un tamaño específico para el tablespace de UNDO a continuación, establezca el parámetro UNDO_RETENTION a un valor óptimo en función del tamaño del tablespace de UNDO y la actividad de base de datos. Si el espacio en disco es limitado y no deseas que asignar más espacio de lo necesario para el tablespace UNDO, esta es la manera de proceder. La siguiente consulta te ayudará a optimizar el parámetro UNDO_RETENTION:
Debido a que las consultas siguientes utilizan la vista V$UNDOSTAT, no ejecutar las consultas hasta después que la base de datos haya estado funcionando con UNDO durante un tiempo significativo y representativo.
- Obtener el tamaño actual del tablespace de UNDO.
SELECT (SUM(a.bytes)/1024)/1024 "Size UNDO en MB"
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#;
Size UNDO en MB
---------------
800.992188
- Bloques de UNDO por segundo.
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"Bloques UNDO por seg."
FROM v$undostat;
Bloques UNDO por seg.
---------------------
4.5466667
- Tamaño del DB Block (parámetro db_block_size)
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KB]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
8192
- Calcular el UNDO_RETENTION óptimo.
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE MB",
SUBSTR(e.value,1,25) "UNDO RETENTION Seg",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "UNDO_RETENTION optimo en Seg"
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 UNDO SIZE MB
-------------------
800.992188
UNDO RETENTION [Seg]
--------------------
900
UNDO_RETENTION optimo en Seg
----------------------------
98112
- Calcular el tamaño necesario para el tablespace UNDO sobre la actividad de base de datos.
Este cálculo es solo valido para dimensionar el tablespace de UNDO en una base de datos con un funcionamiento normal, en el caso de que vayamos a tener procesos no habituales que generen gran volumen de transacciones, por ejemplo una carga mensual, en estos casos podemos tener un tablespace de UNDO alternativo de mayor tamaño o ampliar para que el proceso funcione correctamente y posteriormente recrear el tablespace UNDO.
Para determinar el tamaño apropiado para el espacio del tablspace UNDO de acuerdo a la actividad de base de datos:
Ejecutamos la siguiente consulta:
SELECT d.undo_size/(1024*1024) "Actual size UNDO MB",
SUBSTR(e.value,1,25) "UNDO RETENTION Seg",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"Size UNDO necesario MB"
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 UNDO MB
-------------------
800.992188
UNDO RETENTION Seg
------------------
900
Size UNDO necesario MB
----------------------
334.3125
Si el size de UNDO necesario es mayor que el actual tendrás que ampliar el tablespace de UNDO, por el contrario, si es menor deberemos recrear el tablespace de UNDO al tamaño adecuado.
