Semi-annual W005_PROCESS_LOG archiving

This task designed to be run semi-annually to prevent table W005_PROCESS_LOG from being overflowed and throwing of out of space error(OracleException (0x80004005): ORA-01652: unable to extend temp segment by 128 in tablespace TEMP)

The core of the process sits in the Stored Procedure CAMIS.SP_BACKUP_W005_PROCESS_LOG

The purpose of the procedure is to:

Rename (ALTER) the current W005_PROCESS_LOG table into the archived versions using patterns such as
Cascade changes such triggers replacements, indexes, synonyms accordingly
Send email upon completion using parameters from the table DA004_SETTING
1. Field TO: is taken from “wa005_process_log table half-yearly backup” => DLOTTAFCCCDBASUPPORT@tc.gc.ca;
2. Field FROM: “ERROR_MAIL_FROM“ => DLOTTAFCCCDBASUPPORT@tc.gc.ca;
3. Body of the email constructed accordingly:
  1. Success message with timestamp
  2. Error message with the body of the error and timestamp
The Stored procedure is using another store procedure - CAMIS_DUI.sp_email_notify(…) to send emails

To run CAMIS.SP_BACKUP_W005_PROCESS_LOG procedure Oracle is using Scheduler Program CAMIS.PROG_RUN_SP_BACKUP_W005

BEGIN
  DBMS_SCHEDULER.DROP_PROGRAM
    (program_name          => 'CAMIS.PROG_RUN_SP_BACKUP_W005');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'CAMIS.PROG_RUN_SP_BACKUP_W005'
     ,program_type         => 'STORED_PROCEDURE'
     ,program_action       => 'CAMIS.sp_backup_w005_process_log'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => 'WA005_PROCESS_LOG Half-yearly backup'
    );

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'CAMIS.PROG_RUN_SP_BACKUP_W005');
END;
/

The Scheduler Program is connected to Scheduler Job CAMIS.JOB_RUN_SP_BACKUP_W005

BEGIN
  SYS.DBMS_SCHEDULER.DROP_JOB
    (job_name  => 'CAMIS.JOB_RUN_SP_BACKUP_W005');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
      ,schedule_name   => 'CAMIS.SCHED_HALF_YEARLY'
      ,program_name    => 'CAMIS.PROG_RUN_SP_BACKUP_W005'
      ,comments        => 'Half yearly job to run program PROG_RUN_SP_BACKUP_W005'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'RESTARTABLE'
     ,value     => TRUE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'RESTART_ON_RECOVERY'
     ,value     => TRUE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'RESTART_ON_FAILURE'
     ,value     => TRUE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'CAMIS.JOB_RUN_SP_BACKUP_W005'
     ,attribute => 'STORE_OUTPUT'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'CAMIS.JOB_RUN_SP_BACKUP_W005');
END;
/

And the Scheduler Job is dependent on Scheduler Schedule CAMIS.SCHED_HALF_YEARLY

BEGIN
  SYS.DBMS_SCHEDULER.DROP_SCHEDULE
    (schedule_name  => 'CAMIS.SCHED_HALF_YEARLY');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
    (
      schedule_name    => 'CAMIS.SCHED_HALF_YEARLY'
     ,start_date       => TO_TIMESTAMP_TZ('2021/06/15 14:00:00.000000 -04:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
     ,repeat_interval  => 'FREQ=monthly; INTERVAL=6; BYDAY=1TUE;BYHOUR=00; BYMINUTE=0; BYSECOND=30'
     ,end_date         => NULL
     ,comments         => 'Running the first Tuesday, every six months at 0000:00:30hrs (30 sec after midnight)'
    );
END;
/

..TEST