Deconfigure or Remove Audit Cleanup Job

It may be required at times to deconfigure auditing from database, once the audit parameters are unset it is important to remove audit data maintenance jobs to ensure they don't unnecessarily keep running and contribute to impacting database performance.

Please follow below steps to deconfigure audit purge / cleanup job:

You can verify that Cleanup has been initialized by running the following:

set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME            PARAMETER_VALUE AUDIT_TRAIL
------------------------- --------------- --------------------
DB AUDIT TABLESPACE       SYSAUX          STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE       SYSAUX          FGA AUDIT TRAIL
AUDIT FILE MAX SIZE       10000           OS AUDIT TRAIL
AUDIT FILE MAX SIZE       10000           XML AUDIT TRAIL
AUDIT FILE MAX AGE        5               OS AUDIT TRAIL
AUDIT FILE MAX AGE        5               XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000           STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000           FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000            OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000            XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24              XML AUDIT TRAIL

If there are "DEFAULT CLEAN UP INTERVAL" parameters listed in the output then it is initialized. In above output 24 hours is set for cleanup interval for all four audit types viz Standard, FGA, OS and XML.

You can see which audit job is responsible for clean-up tasks, this is explicitly set by DBA, so if there is no output then most probably there can be some other job working for cleanup.

SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

JOB_NAME               JOB_STAT AUDIT_TRAIL      JOB_FREQUENCY
---------------------- -------- ---------------- -----------------------
PURGE_ALL_AUDIT_TRAILS ENABLED  ALL AUDIT TRAILS FREQ=HOURLY;INTERVAL=24

In case you do not see any output from above query:

SELECT JOB_NAME, ENABLED, JOB_ACTION FROM DBA_SCHEDULER_JOBS 
WHERE JOB_NAME LIKE '%PURGE%' OR JOB_NAME LIKE '%CLEAN%';

JOB_NAME                  STATE           ENABLED
------------------------- --------------- ------
PURGE_ALL_AUDIT_TRAILS    SCHEDULED       TRUE

JOB_ACTION
--------------------------------------------------------
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);  END;


If even this does not helps, then please monitor other jobs in database that are cleaning up your AUD$ table.

Once you have identified the job you can drop it using below procedures:

EXEC DBMS_AUDIT_MGMT.DROP_PURGE_JOB('PURGE_ALL_AUDIT_TRAILS');

OR

EXEC DBMS_SCHEDULER.DROP_JOB('PURGE_ALL_AUDIT_TRAILS');





After dropping the job, you can de-initialize cleanup by running procedure:


BEGIN
  DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
    AUDIT_TRAIL_TYPE   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/



If you have only one or two types of auditing enabled then modify accordingly. Below audit trail types are valid:
AUDIT_TRAIL_AUD_STD
AUDIT_TRAIL_FGA_STD
AUDIT_TRAIL_OS
AUDIT_TRAIL_XML

That's all!

Cheers,
Anurag