Reducir tamaño en el tablespace UNDO

Nuevo problema en nuestras bases de datos, y nuevamente debido a una mala gestión por nuestra parte en el proceso de postcreación de la base de datos. En este caso estamos en un entorno de pruebas, por lo que el problema no es tan serio, pero hay que solucionarlo cuanto antes. Estamos trabajando con un cluster de dos nodos Oracle 10g (10.2.0.3) que contienen dos bases de datos. Los tablespaces de undo de una de ellas (una base de datos solo puede tener un tablespace de undo activo en un momento determinado; pero al estar en un entorno RAC, tenemos un tablespace de undo para cada instancia) se han ido de madre: han crecido más de la cuenta y nos han llenado el disco. El problema es similar al que tuvimos con el del tablespace temporal que creció hasta los 32 Gb; se nos coló el autoextend sin límite en la creación de la base de datos.

Tenemos localizado el proceso responsable: se trataba de un Import Data Pump (impdp) sobre datos de una nueva aplicación que se iba a situar en este entorno. De modo que, el primer paso es simple: eliminar el esquema importado, que de momento no se está usando, para liberar espacio en el disco, dejarme a mi una zona en la que poder arreglar el desaguisado, y permitir que el resto de aplicaciones sigan funcionando sin problemas.

Ahora viene lo bueno. Un tablespace no se puede reducir de tamaño. Lo que voy a hacer es eliminarlo y volver a crearlo con un tamaño adecuado, añadiendo además un límite a su crecimiento. El problema que tengo ahora es que estoy tratando con un tablespace de undo, el cual tiene un tratamiento especial. No se puede poner offline (y menos aún borrar) mientras tenga segmentos activos. De modo que, lo primero es crear un nuevo tablespace de undo (las instrucciones que indico a continuación las ejecuto como usuario SYS, con lo que ¡ojo!):

CREATE UNDO TABLESPACE UNDO_TEMP DATAFILE SIZE 100M;

Ahora en la primera instancia del cluster (el orden da igual) se indica el parámetro que indica el tablespace de undo por defecto. La suerte en este caso es que se trata de un parámetro dinámico, y por lo tanto no hace falta parar la instancia:

ALTER SYSTEM SET UNDO_TABLESPACE=’UNDO_TEMP’ SID=’PRUEBA1′;

Si ahora reviso los parámetros que hacen referencia al undo en esta instancia, me encuentro con lo siguiente (show parameter undo):

 

NAME TYPE VALUE
——————— ———– ——————–
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_TEMP

En teoría, ya puedo borrar el tablespace de undo que me estaba dando la lata. Si se diera la condición de que todos los segmentos estuvieran en estado offline, no haría ni siquiera falta poner offline el tablespace; le podría hacer un drop sin más complicaciones. El problema viene cuando hay algún segmento que no está offline; teniendo claro que no quiero hacer rollback, ni recuperar ningún tipo de transacción, y que puedo prescindir totalmente de estos datos, lo que voy a hacer es forzar el estado offline de los segmentos que no lo estén para poder borrar el tablespace de forma limpia. ¿Cómo compruebo esto? Con la siguiente select:

SELECT SEGMENT_NAME,TABLESPACE_NAME,STATUS
FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME=’UNDOTBS1′;

Que me dará un resultado como el que sigue:

SEGMENT_NAME TABLESPACE_NAME STATUS
——————– ————————- —————-
_SYSSMU13$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU14$ UNDOTBS2 OFFLINE
_SYSSMU15$ UNDOTBS2 OFFLINE
_SYSSMU16$ UNDOTBS2 OFFLINE
_SYSSMU17$ UNDOTBS2 NEEDS RECOVERY
_SYSSMU18$ UNDOTBS2 OFFLINE
_SYSSMU19$ UNDOTBS2 OFFLINE
_SYSSMU20$ UNDOTBS2 OFFLINE

Cualquier status diferente de OFFLINE necesitará que le apliquemos el procedimiento que a continuación describo.

Lo siguiente es bajar la instancia; por suerte, estoy en un entorno RAC, por lo tanto no va a haber pérdida de servicio (no voy a explicar aquí como hace Oracle RAC el TAF, en todo caso en otra entrada). Como tengo que arrancar con un PFILE modificado a partir del SPFILE (con el que estoy trabajando), voy a crear un PFILE antes de bajar la instancia:

CREATE PFILE=’/tmp/pfile.ora’ from SPFILE;

Ahora bajo la instancia y edito el PFILE. Tengo que modificar el parámetro UNDO_MANAGEMENT al valor ‘MANUAL’ y añadir un parámetro oculto de ORACLE para poder forzar el OFFLINE de los segmentos UNDO que no lo estén. Para el caso que describo, y según la anterior SELECT, yo insertaría esta línea en mi PFILE:

*._offline_rollback_segments=”_SYSSMU13$,_SYSSMU17$”

Lo siguiente es levanter la instancia con el PFILE modificado:

STARTUP PFILE=’/tmp/pfile.ora’

Si se ejecuta la anterior select, el resultado va a ser el mismo. Pero ahora ya se puede actuar sobre esos segmentos. En primer lugar:

DROP ROLLBACK SEGMENT ‘_SYSSMU13$’;

Que dará un resultado como este:

ERROR en línea 1:
ORA-02175: nombre de segmento de rollback no válido

Obviamos el error y repetimos el DROP sobre el otro segmento. Después de eso, ya se puede borrar el segmento de undo:

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Una vez borrado este tablespace, vuelvo a parar la instancia y arranco con el SPFILE (hago un startup sin ningún parámetro). En este momento, ya puedo crear el nuevo tablespace de undo con los parámetros deseados:

CREATE UNDO TABLESPACE UNDOTBS1
DATAFILE SIZE 256M
AUTOEXTEND ON NEXT 128M MAXSIZE 1G;

Igual que he hecho antes, cambio el tablespace de undo por defecto, substituyendo UNDO_TEMP por UNDOTBS1.

ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS1’ SID=’PRUEBA1’;

Finalmente, solo queda repetir el proceso con la otra instancia. Cuando se concluya, se puede borrar el tablespace de UNDO que se ha creado para solucionar el problema.

15 Responses to Reducir tamaño en el tablespace UNDO

  1. roque dice:

    muy buen articulo , de donde salen esos parametros ocultos?

  2. jop89s dice:

    En mi centro de trabajo tenemos soporte de Oracle, de modo que esos parámetros ocultos salen de la documentación publicada en Metalink.

  3. Hquinteros dice:

    Muy valiosa informacion!!

  4. Raquel dice:

    Perfecta, explicación perfecta y útil.
    Gracias mil.

  5. Simón Fuentes dice:

    Estimado

    Me ha servido enormemente tu nota. La verdad tenia un problemon al pasar mas de 10 horas con el shutdown immediate y con este proceso del undo tablespace.

    Muchisimas Gracias!!!

  6. jop89s dice:

    Gracias a todos los que me habéis leído y os habéis molestado en escribir un comentario. Como las cosas evolucionan, y aunque ahora tengo cada vez menos tiempo para mantener este sitio actualizado, quisiera actualizar esta entrada con ‘recientes descubrimientos’ realizados en nuestros entornos Oracle.

  7. Cristian dice:

    Buenisimo manualillo.
    Acabo de realizar todos los pasos en una base de test que teníamos el tablespace de undo cargadiiisimo.

    Gracias!

  8. Oscar dice:

    Hola
    muy buen articulo, acabo de ejecutar todo el procedimiento solo que al realizar el alter con el siguiente comando:
    ALTER SYSTEM SET UNDO_TABLESPACE=’UNDO_TEMP’ SID=’PRUEBA1′;
    no se modificaba en mi BD por lo que tuve que modificarlo desde el Enterprise manager de Oracle y luego todo lo demas funciono ok.
    Gracias

  9. Joan dice:

    Perfecto. Muchas gracias me ha pasado varias veces
    y por fin, ahora veo como solucionarlo.

  10. Miguel Salfate dice:

    Muchas gracias por tu aporte. Clarisima tu explicacion.

    Salu2.

    Miguel

  11. Daavidd dice:

    Hola,

    Nosotros tenemos una aplicacion bajo Oracle, y el tablespace UNDO se está llenando considerablemente.

    ¿Se puede limpiar de datos?
    ¿Es aconsejable?
    ¿Que consecuencias podria tener?

    • jop89s dice:

      Hay mucha literatura sobre este asunto. El tablespace de UNDO es relativamente normal que se llene, dependiendo de las operaciones que se realicen en la base de datos, y no es nada anómalo; ahí la bd va registrando la información necesaria para ‘deshacer’ (traducción al español) los cambios que se van efectuando en las distintas tablas. ¿Se puede limpiar de datos? En tiempo de ejecución, no. La única forma de limpiarlo es parar la aplicación que lo está llenando y esperar que pase el tiempo marcado por el parámetro ‘undo_retention. ¿Es aconsejable limpiarlo de datos? No, porque la información ahí guardada puede ser necesaria para la consistencia de la base de datos.
      Si estás preocupado por esto, lo mejor que puedes hacer es investigar qué aplicación lo está llenando, porque igual la misma está efectuando algún tipo de operación no demasiado optimizada, como puede ser el efectuar muchas actualizaciones inserciones y/o actualizaciones de datos sin ejecutar ‘commit’ cada cierto tiempo.

  12. Pingback: Los números de 2010 « Blog Técnico

  13. Pingback: Los números de 2010 « Blog Técnico

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: