Problema con smon consumiendo 100% de CPU, luego de matar una sesión de base de datos

Les comento una situación que me acontenció con respecto al uso desmesurado en CPU de parte del SMON luego de matar una sesión de base de datos que causaba  un bloqueo. Me paso en un cliente que luego de bajar unas sesiones que estaban ocasionando un bloqueo el SMON quedó colgado consumiendo el 100% de 1 core y ojo que por lo menos aquello duró más de 24 horas, hasta que apliqué lo que a continuación mencionaré.


Lo anterior me llamó mucho la atención pues no es lo normal. Buscando encontré que esto es un bug, a causa de una transacción que el SMON no puede aplicar rollback. Lo siguiente muestra la la transacción con status en DEAD:


Esta base de datos ocupaba undo_management en MANUAL, o sea segmentos de rollback. Como se puede apreciar la transacción que el SMON intenta deshacer se encuentra alojada en el segmento RBS_4. Luego de una bajada/subida que se realizó en el motor de base de datos esta sigue mostrando que el SMON se encuentra deshaciendo dicha transacción y también manteniendo la CPU al 100%.

Esto es producto de un bug que afecta a esta versión de base de datos
Bug 18132629 : UNDO SEGMENT IN PARTLY AVAILABLE STAGE, DEAD TRANSACTION EXIST.

Lo normal es ver que en una versión 11g se ocupe undo_management en AUTO y no con MANUAL. Ahora, fuera de eso que no es lo correcto para una versión 11g, la única alternativa que quedó fue ocupar un parámetro oculto llamado _OFFLINE_ROLLBACK_SEGMENTS el cual debe ser ocupado con mucha precaución, si no se sabe usar puede dejar inconsistencias  o corrupción lógica en la base de datos. Antes de aplicar lo siguiente es mejor asegurarse de tener un respaldo de la base de datos. A dicho parámetro hay que indicarle el segmento a dejar offline (esta es la única manera de dejarlo offline, puesto que con la manera tradicional "alter rollback segment RBS4 offline" el motor no lo permite puesto que aún hay una trasacción activa dentro de él, aunque esté con status en DEAD).

alter system set "_OFFLINE_ROLLBACK_SEGMENTS"='RBS_4' scope=spfile ;

Luego de reiniciar el la base de datos y subirla en modo normal, se intenta eliminar el segmento mencionado sin problemas:

SQL> drop rollback segment RBS_4;

Rollback segment dropped.

Luego de eso se comprueba que finalmente la transacción ya no se encuentra haciendo rollback:

SQL> select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS,
a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn
xid_seq,a.ktuxecfl
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxeusn = b.us#  2    3    4    5 
  6  /

no rows selected

Ojo que esto debe ser ocupado como última opción. Sugiero quitar ese parámetro del spfile luego de aplicado este workaround, ojo que en caso de problemas y como todo uso no autorizado de los parámetros Ocultos, el soporte de Oracle se lava las manos muchas veces he visto. No olvidar.

alter system reset "_OFFLINE_ROLLBACK_SEGMENTS" scope=spfile;

saludos.