0

I am trying to figure out how I can create/schedule a DBMS_AUDIT_MGMT.CREATE_PURGE_JOB to execute at a specific time of day, rather than at the interval, which appears to start at the time you execute the CREATE_PURGE_JOB ?

The only thing I can think of is to change the SYSTEM time temorarily, run this procedure, then fix the system time ??? (dumb)

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_PURGE_INTERVAL => 24 * 30 /* hours */,
    AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;

Is there another way I am missing ?

It does not appear to have a start_date parameter such as DBMS_SCHEDULER.CREATE_JOB where you can specify a time.

bbaley
  • 199
  • 6
  • 22

2 Answers2

1

The purge job functionality is just a wrapper over the DBMS_SCHEDULER package, so if you want to schedule this task to be executed at another time, you should use DBMS_SCHEDULER functionality.

In your case after executing your code, DBA_AUDIT_MGMT_CLEANUP_JOBS has the following information:

SQL> SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS ;

JOB_NAME                  JOB_STAT AUDIT_TRAIL                  JOB_FREQUENCY
------------------------- -------- ---------------------------- ------------------------------
DAILY_AUDIT_PURGE_JOB     ENABLED  STANDARD AUDIT TRAIL         FREQ=HOURLY;INTERVAL=720

And DBA_SCHEDULER_JOBS has the following: 

SQL> SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%' ;

JOB_NAME                  NEXT_RUN_DATE                                                STATE           ENABL
------------------------- ------------------------------------------------------------ --------------- -----
DAILY_AUDIT_PURGE_JOB     15-AUG-15 01.11.40.411757 AM AMERICA/ARGENTINA/RIO_GALLEGOS  SCHEDULED       TRUE

Schedule a new "manual" job as usual:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'MANUAL_AUDIT_PURGE_JOB',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(1, TRUE, 1);  END;',
   enabled              =>  TRUE,
   comments             => 'Manual Audit Purge Job');
END;
/

And then you can verify its execution by querying dba_scheduler_job_log as follows:

select log_date
, job_name
, status
from dba_scheduler_job_log
order by log_date asc;

You will get something like this:

LOG_DATE                                                                    JOB_NAME                  STATUS
--------------------------------------------------------------------------- ------------------------- ------------------------------
16-JUL-15 01.50.03.081815 AM -03:00                                         ORACLE_APEX_MAIL_QUEUE    SUCCEEDED
16-JUL-15 01.52.29.935133 AM -03:00                                         MANUAL_AUDIT_PURGE_JOB    SUCCEEDED

Note: This POC was performed using 12.1.0.2.0 release.

Daniel Vukasovich
  • 1,692
  • 1
  • 18
  • 26
0
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'DAILY_AUDIT_PURGE_JOB'
     ,attribute => 'START_DATE'
     ,value     => trunc(sysdate) + interval '5' hour + interval '10' minute
     );
END;
/
Павел Рогачев
  • 1
  • 1