Tareas Programadas con Oracle Schedule Jobs

Hace tiempo, en mi anterior trabajo necesitábamos una forma de ejecutar tareas programadas que no dependiesen completamente de los Jobs de Oracle, primero por que teníamos que solicitarle al DBA los creara, no podíamos monitorearlo y los logs en caso de errores los teníamos que llevar por nuestra cuenta y el tiempo programado de ejecución no era preciso.

Al final se creo una aplicación que guardaba todas las tareas por ejecutar, con las fechas de inicio y su programación y se mandaba a llamar desde un Job Oracle un procedimiento que leía todos los procedimientos programados para esa hora y los iba ejecutando uno a uno, generaba la siguiente fecha de ejecución y en caso de errores guardaba el log en una tabla, para con ello el trabajo no se desactivara en caso de error.

Cuando llego a mi nueva empresa, tengo la misma problemática con los Jobs en la versión 9i, había momentos en que los Jobs se empalmaban por mas que les asignara una fecha inicial diferente (los tiempos de ejecución intervenían en el cálculo de las nuevas fechas) y no tenía modo de monitorear si se había ejecutado correctamente o no, por lo que el error era detectado posteriormente por el desarrollador que recibía el reclamo de que no había llegado o no se había generado el reporte.

Ya con la migración de la Base de Datos, me puse a Investigar un poco sobre una característica existente desde la versión 10g, los Scheduled Jobs.

Los Scheduled Jobs, no es más que la programación de ejecución de algún programa PL/SQL, o comando externo desde la Base de Datos a una fecha y hora determinada.

¿Cual es la diferencia con los Jobs normales? Primero, que no le afecta el tiempo de ejecución del job, segundo, guardan un log de ejecuciones (programable para solo cuando sucedan errores, sea satisfactorio o ambos) y tercero, no le afecta la configuración de Database Vault al querer modificarlos/ejecutarlos con el usuario SYS si el dueño es otro usuario.

Lo que he observado, es que finalmente, un Scheduled Job internamente cuando llega la hora de ejecutarse, crea un Job, se ejecuta y lo elimina, por lo que se tiene que tener especial cuidado en la programación de tiempo, para evitar empalmes de ejecución del mismo procedimiento (posibles bloqueos masivos o sesiones colgadas con procesos a medio terminar).

Para implementar un Scheduled Job se ejecuta un script como este ejemplo:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2012/03/08 13:35:00.000000 -06:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'SERVICIOS_BD.P_DesasignaPrivilegios'
      ,end_date        => NULL
      ,comments        => 'Desasignación de Privilegios asignados a Desarrolladores por el procedimiento de Autoasignación de Privilegios de Base de Datos'
    );
    
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_FULL);
    
    DBMS_SCHEDULER.ENABLE(name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB');
    
END;
/

La primer parte crea el Scheduled Job y la segunda lo activa, ya que por default estos no son activados al crearse.

Una observación es con la especificación de la fecha inicio de ejecución y el horario de verano, en el caso de mi país (México) y por mi uso horario (Ciudad de México) el uso horario que me corresponde es el GMT -6:00, sin embargo, cuando entra en rigor el horario de verano pasa a ser GMT -5:00 por lo que el Scheduled Job comienza a ejecutarse con una hora de retraso por su programación inicial.

En los países que utilicen el horario de Verano, cuando se especifique la fecha de inicio deberán utilizar el formato con el código de zona de tiempo (TIME ZONE) que le corresponda.

En mi caso, quedaría de la siguiente manera:

BEGIN

   DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'

      ,start_date      => TO_TIMESTAMP_TZ('2012/03/08 13:35:00 America/Mexico_City','yyyy/mm/dd hh24:mi:ss tzr')

      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'

      ,job_type        => 'STORED_PROCEDURE'

      ,job_action      => 'SERVICIOS_BD.P_DesasignaPrivilegios'

      ,end_date        => NULL

      ,comments        => 'Desasignación de Privilegios asignados a Desarrolladores por el procedimiento de Autoasignación de Privilegios de Base de Datos'

    );

    

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'

     ,attribute => 'LOGGING_LEVEL'

     ,value     => SYS.DBMS_SCHEDULER.LOGGING_FULL);

    

    DBMS_SCHEDULER.ENABLE(name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB');

    

END;

/

Noten los cambios en la especificación de la hora, en la cual se agregó mi TIME ZONE y la máscara de formato, que se le agrega el TZR, con ello Oracle toma en cuenta que esa zona de Tiempo le es aplicado el horario de verano, y así considera en los cálculos de la próxima fecha de ejecución el cambio de horario.

Si necesitas consultar los códigos de Time Zone, puedes hacer un Select a la vista V$TIMEZONE_NAMES.

Lo del horario de verano lo encontré en esta página http://deryaoktay.wordpress.com/2010/01/21/oracle-job-scheduler-runs-my-jobs-one-hour-earlier/

Los scheduled jobs permiten muchas más configuraciones, pero para las necesidades que tengo ahora, con esto fué suficiente.

Me queda como tarea el habilitar las notificaciones automáticas vía correo electrónico de las ejecuciones de estas tareas.