Es bastante común encontrarse con que el tablespace de UNDO, debido a que algún proceso se cuelgue, empiece a crecer de manera indiscriminada y si a esto le sumas que se tenga configurado en autoextend=ON con un límite mayor de la capacidad libre existente en ese momento en el disco donde se encuentre el datafile del tablespace, circunstancia, que puede provocar que el disco se llene y por tanto, un posible crash del sistema.
Para evitar esto, un correcto dimensionamiento del tablespace UNDO nos puede evitar más de un quebradero de cabeza. Empezaré con un poco de teoría para situarnos ….
El tablespace de UNDO es lo que se conoce como un tablespace de sistema. Como lo son el de SYSTEM, SYSAUX o el TEMP. El objetivo de un tablespace de UNDO es almacenar información que permita deshacer cambios, crear imágenes antiguas de bloques de datos e incluso almacenar información útil que la funcionalidad del Flashback pueda requerir en algún momento. El tablespace UNDO debe crearse como UNDO en el proceso de creación y puede perfectamente hacerse con 2 datafiles y en filesystems distintos entre sí y distintos de donde esté ubicado el tablespace TEMP.
Ejemplo muy básico de creación de UNDO:
CREATE UNDO TABLESPACE undotbs01 DATAFILE '/undotbs01.dbf' SIZE 200M AUTOEXTEND ON MAXSIZE 8192M;
El tablespace de tipo UNDO debe existir para que la base de datos pueda almacenar allí los registros propios “de deshacer”. Si no existe, la instancia usará el tablespace SYSTEM para tal fin. Cosa nada recomendable y que es más que probable que en el archivo ALERT_.log se genere el error:
ORA-01552: cannot use system rollback segment for non-system tablespace ‘%s’.
Por otro lado, existe un parámetro de inicialización “UNDO_TABLESPACE” que permite indicarle a la instancia cual es el tablespace UNDO a utilizar, útil en los casos en los que existen varios tablespaces UNDO.
¿ Como gestiona ORACLE el tablespace UNDO ?
Oracle lo puede gestionar de dos maneras distintas:
1.- SMU (System Managed Undo)- (AUTO*) o lo que es lo mismo, modo automático de gestión, donde no se utilizan los segmentos de rollback externos, la información de UNDO se almacena en un tablespace especial dedicado exclusivamente a este objetivo.
2.- RBU (modo manual de gestión del UNDO) – (MANUAL*) . Se almacena de forma externa en segmentos de rollback. Este era el único método que existía en versiones anteriores de Oracle.
Ambos modos de gestión se establecen mediante el parámetro dinámico UNDO_MANAGEMENT que libera a los DBA´s de la administración y monitoreo si lo configuras en modo AUTO.
SQL> alter system set undo_management = <valor>;
Ejemplo para setear el parámetro en modo AUTO:
SQL> alter system set undo_management = AUTO;
¿ Cómo ver que valores de UNDO tenemos configurados en la BBDD ?
SQL> show parameters undo
NAME TYPE VALUE ------------------------------------ ----------- -------------------------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
¿ Que significan ?
undo_management
explicado en el punto anterior.
undo_retention
Parámetro dinámico que indica en segundos (900 por defecto) cuanto tiempo ha de permanecer, al menos, la información de UNDO disponible.
Ej: alter system set undo_retention=<valor>;
undo_tablespace
Parámetro dinámico que indica el tablespace de almacenamiento de “UNDO” a usar en el arranque.
Hasta aquí un poco de teoría, pondré el problema que me he encontrado alguna vez y su solución y ampliaré información durante la explicación para entender mejor otros parámetros que no he explicado anteriormente.
Problema: El Tablespace de UNDO crece indiscriminadamente y llega a llenar el filesystem donde estaba ubicado el datafile asociado. Este crecimiento indiscriminado puede ser debido a una transacción muy grande, un import de alguna tabla muy grande, etc…
Solución:
Volver a poner en el tamaño adecuado el tablespace de UNDO una vez realizada la transacción. Lamentablemente, no se puede reducir un tablespace de UNDO, tenemos que crear un nuevo tablespace tipo undo con el tamaño deseado, asignar este a la BBDD, y eliminar el antiguo. Luego si queremos volver a ponerlo todo igual, repetiremos la operación creando de nuevo otro tablespace tipo undo con el mismo nombre que tenía originalmente el primer tablespace undo y asignárselo a la BBDD. Finalmente borraremos el auxiliar creado. Por último, comprobaremos que realmente el tablespace de UNDO está correctamente definido.
NOTA: Realmente NO es que no se pueda reducir el tablespace de undo haciendo un RESIZE. Lo cierto es que si se puede, pero sólo hasta donde lo permita la HWM (High water mark).
Pasos para solucionar el problema:
Como el datafile llenó el filesystem, lo primero que tenía que hacer es hacer espacio en el filesystem, pero asegurándome de que cuando haga espacio, el tablespace de UNDO no siga creciendo de manera que lo vuelva a llenar. Para ello, primero quitaré el autoextend del tablespace UNDO.
Conectado como sys o system ….
SQL> ALTER DATABASE DATAFILE '/oracle/oradata/SID/undotbs01.dbf' AUTOEXTEND OFF;
Una vez asegurado de que el datafile no crecerá más, haremos espacio en el filesystem. Como se trata del filesystem donde están ubicados los tablespaces de sistema de Oracle como system, temp, etc... y no tenía otros ficheros que pudiese eliminar para hacer espacio. Lo que hice fue reducir el tamaño del tablespace TEMP que posteriormente, cuando ya haya conseguido volver a redefinir el tablespace UNDO volveré a poner a su tamaño original.
SQL> ALTER DATABASE TEMPFILE '/oracle/oradata/SID/temp01.dbf' RESIZE 2048M;
NOTA: Reducir TEMP a un tamaño que nos permita ganar espacio en el filesystem, pero que permita también trabajar a la base de datos.
Ahora podríamos probar de intentar reducir el TS UNDO con resize a ver si Oracle nos lo permite. Esto nos ahorraría el trabajo de crear el auxiliar, etc…. Pero tenemos que saber que tamaño nos deja reducirlo por encima de la HWM. (En mi caso, no lo conseguí, y tuve que crear el TS UNDO auxiliar).
Imaginemos que quiero reducir el datafile a 1000MB …
SQL> ALTER DATABASE DATAFILE ‘/oracle/oradata/SID/undotbs01.dbf’ RESIZE 1000M;
Al ejecutar la instrucción aparece el siguiente error:
ORA-03297: file contains used data beyond requested RESIZE value
Pues me veo obligado a crearme el TS Auxiliar para poder reducir el TS de Undo….
CONSULTAS ÚTILES RELACIONADAS CON TODAS LAS TAREAS A REALIZAR
Primero saber el tamaño actual del tablespace de UNDO desde SQLPLUS:
SQL> SELECT Sum(v$datafile.bytes / 1024 / 1024) AS "Tamaño Actual TS UNDO [MB]" FROM v$datafile INNER JOIN v$tablespace ON v$datafile.ts# = v$tablespace.ts# INNER JOIN dba_tablespaces ON v$tablespace.NAME = dba_tablespaces.tablespace_name WHERE dba_tablespaces.contents = 'UNDO' AND dba_tablespaces.status = 'ONLINE' /
Podemos visualizarlo también directamente desde la consola de OEM:
Iremos a la pestaña “servidor” y pulsamos en el enlace “Gestión automática de deshacer” y sale esta pantalla donde podemos ver la información del tablespace Undo incluído su tamaño actual.
La siguiente consulta nos puede ayudar a determinar un valor adecuado para el TS Undo
SELECT Substr(e.VALUE,1,25) "UNDO RETENTION [Sec]", d.undo_size / (1024 * 1024) "Tamaño Actual UNDO [MByte]", (To_number(e.VALUE) * To_number(f.VALUE) * g.undo_block_per_sec) / (1024 * 1024) "Tamaño Optimo UNDO [MByte]" 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' /
Devuelve algo como:
UNDO RETENTION [Sec] Tama??o Actual UNDO [MByte] ------------------------- --------------------------- Tama??o Optimo UNDO [MByte] --------------------------- 900 12288 1779.82031
Aún así, yo recomiendo crearlo con el tamaño original e ir controlando durante el trabajo habitual de la BBDD que necesidades tiene el UNDO.
Seguimos … Creamos el nuevo TS Undo auxiliar con el tamaño mínimo de este óptimo sacado anteriormente, por ejemplo.
SQL>CREATE UNDO TABLESPACE "UNDOTBS_AUX" DATAFILE ‘/oracle/oradata/undotbs_aux.dbf’ SIZE 1800M;
Ahora debemos establecerlo en la BBDD. (Yo prefiero hacerlo desde OEM), pero desde SQL se hace así:
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS_TEMP’ scope=both;
Ahora ya podríamos eleminar el primer tablespace de Undo (“el original” que creció desmesuradamente):
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Aquí nos puede pasar dos cosas, una que lo borre sin problemas y otra es que nos dé el siguiente error:
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS1′ is currently in use
Pues tenemos que poner offline los segmentos de rollback que están online en el UNDO. Para saber que segmentos son, usaremos la siguiente consulta:
SELECT owner, segment_name, status FROM dba_rollback_segs WHERE tablespace_name='UNDOTBS1' ORDER BY 3;
Sale algo como:
OWNER SEGMENT_NAME STATUS ------ ------------------------------ ---------------- PUBLIC _SYSSMU1_3129330276$ OFFLINE PUBLIC _SYSSMU2_1303119469$ OFFLINE PUBLIC _SYSSMU3_1479839839$ OFFLINE PUBLIC _SYSSMU4_3642025306$ OFFLINE PUBLIC _SYSSMU10_1545059069$ OFFLINE PUBLIC _SYSSMU6_1524158477$ OFFLINE PUBLIC _SYSSMU7_1101254083$ OFFLINE PUBLIC _SYSSMU8_4223133350$ OFFLINE PUBLIC _SYSSMU9_3952001453$ OFFLINE PUBLIC _SYSSMU5_2891492079$ ONLINE
10 rows selected.
Pues para cambiar el estado haremos:
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU5_2891492079$" OFFLINE;
Seguidamente volveremos a ejecutar la sentencia:
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped.
y no debería dar problemas.
Ahora tan sólo deberíamos volver a crear el tablespace de UNDO con el nombre original y tamaño original antes del incidente y por último, volver a asignar a la Base de datos este tablespace de UNDO que crearemos. (Y en este ejemplo, también volveremos a poner el tamaño que tenía originalmente al tablespace TEMP usando “resize”). Se hace así:
SQL> CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE ‘/oracle/oradata/SID/undotbs01.dbf’ SIZE 1000M;
Tablespace created.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS1' SCOPE=BOTH;
System altered.
SQL> DROP TABLESPACE UNDOTBS_TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
NOTA: si no deja borralo, proceder como en el ejemplo anterior.
SQL> show parameters undo
NAME TYPE VALUE
------------------------------------ ------- --------
_undo_autotune boolean TRUE
undo_management string AUTO
undo_retention integer 3000
undo_tablespace string UNDOTBS1
CONSULTAS ÚTILES RELACIONADAS CON EL UNDO
La siguiente consulta busca una sesión de usuario que está en el undo para poderla matar…
column username format a30 SELECT a.name,b.status , d.username , d.sid , d.serial# FROM v$rollname a,v$rollstat b, v$transaction c , v$session d WHERE a.usn = b.usn AND a.usn = c.xidusn AND c.ses_addr = d.saddr AND a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = 'UNDOTBS1' );
Para matar la sesión ejecutaremos: SQL> alter system kill session '147,4'; donde 147 es el SID y 4 el SERIAL#
# Con la siguiente query podemos ver el estado de los segmentos de UNDO: # UNEXPIRED significa que estos segmentos de UNDO no contienen ninguna transacción activa, # pero estos contienen transacciones que todavía son requeridos para FLASHBACK. # EXPIRED significa que estos segmentos no son requeridos después del periodo de retención definido en undo_retention. # ACTIVE significa que estos segmentos de UNDO contienen transacciones activas, o sea, no se ha realizado commit. # Los valores son en MB. SELECT SYSDATE AS fecha, unexpired.unexpired, expired.expired, active.active FROM (SELECT Sum(bytes / 1024 / 1024) AS unexpired FROM dba_undo_extents WHERE status = 'UNEXPIRED') unexpired, (SELECT Sum(bytes / 1024 / 104) AS expired FROM dba_undo_extents tr WHERE status = 'EXPIRED') expired, (SELECT CASE WHEN Count(status) = 0 THEN 0 ELSE Sum(bytes / 1024 / 1024) END AS active FROM dba_undo_extents WHERE status = 'ACTIVE') active /
# Buscar la sesión que está colgada o pendiente de ponerse a OFFLINE en TS UNDO para luego matarla set lines 10000 column name format a10 SELECT a.name,b.status FROM v$rollname a,v$rollstat b WHERE a.usn = b.usn AND a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = 'UNDOTBS1' );
# Transacciones activas en TS UNDO SELECT NAME, XACTS "ACTIVE TRANSACTIONS" FROM V$ROLLNAME, V$ROLLSTAT WHERE STATUS = 'PENDING OFFLINE' AND V$ROLLNAME.USN = V$ROLLSTAT.USN;