Audit Table (AUD$) Purging and Cleanup

Often you may observe after enabling auditing, the AUD$ or FGA_LOG$ table starts growing. This may grow larger and larger causing its tablespace to run out of space (usually default is SYSAUX). Thus maintenance of audit data becomes important and with a retention period in mind and daily purge job, this is an easy task to do.

I have written another blog on enabling session audit in database, follow this link to see it.

First things first
You may be curious to know or might already know the magnitude of your audit table. You may get the basic details using below four queries:

> Number of rows
SELECT COUNT(*) FROM AUD$;

>  Size of table
SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_NAME='AUD$';

> Oldest record
SELECT MIN(NTIMESTAMP#) FROM AUD$;

> Tablespaces holding audit data
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
---------- ---------------
AUD$       SYSAUX
FGA_LOG$   SYSAUX

It would be good to keep note of these to validate things after purge has been executed.

In case you need to change default tablespace for AUD$ or FGA_LOG$ data to be stored, run below procedure with appropriate modifications:

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   audit_trail_location_value  => '<tablespace_name>') ;
END;
/

Below audit trail types are valid:
AUDIT_TRAIL_AUD_STD
AUDIT_TRAIL_FGA_STD
AUDIT_TRAIL_OS
AUDIT_TRAIL_XML

For all of them: AUDIT_TRAIL_ALL

Before we start, you can verify that Cleanup has not been initialized by running the following.
Cleanup has not yet been initialized if there are no "DEFAULT CLEAN UP INTERVAL" parameters listed in the output.

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_ 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

In case you see DEFAULT CLEAN UP INTERVAL parameters configured, I would recommend to follow this link to blog where I have explained how to deconfigure audit cleanup. It would be good to start afresh then from below section.

Initialize Audit Cleanup

You can run below procedure to initialize cleanup of audit data, the procedure accepts cleanup interval in hours (1 to 999).

I am initializing for 14 days as below:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    DEFAULT_CLEANUP_INTERVAL => 24*14 /*hours x days*/
    );
END;
/

After procedure has been completed you can re-check the config parameters:

SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME            PARAMETER_ 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 336        STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 336        OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 336        FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 336        XML AUDIT TRAIL

Once this is configured, we have two options to purge audit data:
1. Complete Purge
2. Partial Purge using LAST ARCHIVE TIMESTAMP

As their name suggests, complete purge will clean-up whole tables retaining nothing, whereas partial purge can retain data that has timestamp greater than last archive timestamp.

After initializing, it is time to implement a procedure that needs to be called when executing purge. I like this way because you can customize procedure to write in alertlog when the job has been run or use it to notify via email etc.

Procedure to Purge Audit Data

Below procedure would delete data from all audit trails, you can update the for specific ones by using specific constants as mentioned earlier in this blog.

**FOR COMPLETE PURGE**
The timestamps for each audit trail can be cleared to allow a complete purge using the CLEAR_LAST_ARCHIVE_TIMESTAMP procedure. This can be scheduled using DBMS scheduler for desired time intervals to perform a full cleanup.

/*  SP_PURGE_AUDIT_TRAIL
Complete Purge
Script by: Anurag Tripathi - v1.0*/

CREATE OR REPLACE PROCEDURE SP_PURGE_AUDIT_TRAIL
AS
BEGIN

  DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
    AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);

   SYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/

**FOR PARTIAL PURGE**
This will first set last archive timestamp according to specified retention variable and then schedule purge using next steps.

/*  SP_PURGE_AUDIT_TRAIL
Partial Purge
Script by: Anurag Tripathi - v1.0*/

CREATE OR REPLACE PROCEDURE SP_SET_LAST_ARCH_TS
AS
  retention NUMBER;
BEGIN
  retention := 14 /* days */;
 

   SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   LAST_ARCHIVE_TIME => SYSTIMESTAMP - retention
   );
END;
/

After the procedures have been created, it is time to schedule a job to run either one of these procedure using dbms scheduler.

Create Job Schedule using DBMS Scheduler for Updating Last Archive Timestamp

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB (
        job_name => 'JOB_SET_LAST_ARCH_TS',
        schedule_name => 'SYS.MAINTENANCE_WINDOW_GROUP',
        job_class => 'DEFAULT_JOB_CLASS',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN SP_SET_LAST_ARCH_TS(); END;',
        comments => 'Job to purge all audit trails'
    );
  
    SYS.DBMS_SCHEDULER.ENABLE(name => 'JOB_SET_LAST_ARCH_TS');
  
END;
/

Create Job for Purging Audit Data

Using below PL block you may schedule the job to run every 24 hours. Thus cleanup will be performed every day.

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
        audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        audit_trail_purge_interval => 24,

        audit_trail_purge_name => 'JOB_PURGE_AUDIT_TRAIL',

        use_last_arch_timestamp => TRUE

    );  
END;
/

After creating the purge job, enable the job using SET_PURGE_JOB_STATUS procedure

BEGIN
DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
  audit_trail_purge_name      => 'JOB_PURGE_AUDIT_TRAIL',
  audit_trail_status_value    => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

After the job has been enabled and running. You may check status of clean events using query:

SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;

Thats all!

Cheers,
Anurag