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

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

Session Auditing in Oracle Database - 10g/11g/12c

Oracle session auditing has its importance derived from requirements of keeping track of users logging on to the database and logging off.

At times it becomes important to know which users were available on the system as a part of auditing to determine:

1. Accountability of users
2. Intruders Detection
3. Problem Detection
4. Visibility on system

Since auditing is a vast topic and its study and implementation varies according to requirements, I am only taking up session auditing in this blog as it is most basic and crucial to a database environment.

Steps to Enable Session Auditing

1. Set auditing parameters to enable audit feature of Oracle RDBMS

Ø  audit_trail

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:

·         none or false - Auditing is disabled.

·         db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).

·         db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.

·         xml- Auditing is enabled, with all audit records stored as XML format OS files.

·         xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.

·         os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

Syntax:
ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE;

 

Ø  audit_file_dest

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

Since I am using AUDIT_TRAIL=DB, I am not concerned about this parameter.

Ø  audit_sys_operations

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

Its your choice to log sys operations in audit log or not.

2.  Enable Auditing of Sessions

Run below command to enable Oracle instance to log each session logon and logoff in audit tables

AUDIT SESSION;

This would starting creating records for each logon/logoff in AUD$ table. This has been validated to work on 10gR2, 11gR1/11gR2 and 12c.

3. View session audit details

To view details Oracle provides below views that gives most appropriate details: DBA_AUDIT_TRAIL and DBA_AUDIT_SESSION

A query to use:

COL OSUSER FOR A20
COL DBUSER FOR A20
COL TERMINAL FOR A15
SET LINESIZE 150
SET PAGESIZE 1000
SELECT os_username "OSUSER",
     username "DBUSER",
     terminal,
     returncode,
     TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
     TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session;

4. Maintenance

It is important to keep check on usage of AUD$ table, it starts flooding with records and in no time can grow into gigabytes. It is strongly advised to decide a retention period of auditing records and purge what is not needed.

Refer to my other post about AUD$ maintenance for detailed steps.

Cheers!

Anurag