Purging AUDIT TRAIL RECORDS (AUD$ Table)

If in your production database configured audit, DBA should maintain audit tables.
Because audit records may grows up to undesired size.

One of the most significant aspects of database security involves setting up auditing to record user activities.

When auditing is enabled, the audit output is recorded in an audit trail, which is usually stored in the database in a table under the SYS schema called AUD$. It can also reside as files in the file system, and the files can optionally be stored in XML format. For more-precise control, the Fine Grained Auditing feature of Oracle Database 11g provides granular control of what to audit, based on a more detailed set of policies. Fine Grained Auditing audits are usually stored in another table, FGA_LOG$, under the SYS schema.

These various audit trails can quickly grow out of control when database activity increases. As audit trails grow, two main challenges must be addressed: 

1. Trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis.
2. Because database-resident trails are typically stored in the SYSTEM tablespace, they can potentially fill it up—bringing the database to a halt. 

Fortunately, the new auditing features in Oracle Database 11g Release 2 can help address these challenges. These capabilities, implemented in a package called DBMS_AUDIT_MGMT, enable you to move audit trails from the SYSTEM tablespace to one of your choice.

The new auditing features also let you set up one-time and automated purge processes for each of your audit trail types. Historically, to purge an audit trail, you were generally forced to stop auditing (which may have required bouncing the database), truncate, and then restart auditing (and bouncing the database again).

In this article, you will learn how to use the new features in Oracle Database 11g Release 2 to manage your audit trails. 

Oracle 11g Release 1 turned on auditng by default for the first time. Oracle 11g Release 2 now allows better management of the audit trail using theDBMS_AUDIT_MGMT package.

The AUDIT_TRAIL_TYPE parameter is specified using one of three constants.

• DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
• DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
• DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.

If you want the purge job to maintain an audit trail of a specific number of days, the easiest way to accomplish this is to define a job to set the last archive time automatically. The following job resets the last archive time on a daily basis, keeping the last archive time 90 days in the past.

Automated Purge with retention of 90 days.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'audit_last_archive_time',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN 
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90);
                          DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
END;
/

 Manual Purging only aud$ table with retention of 120 days.

col owner for a20
select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$';

LAST_ANALYZED   OWNER                TABLE_NAME                       NUM_ROWS
--------------- -------------------- ------------------------- ---------------
14-OCT-07       SYS                  AUD$                                    0

20:03:01 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5);

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.65
20:03:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$';

LAST_ANALYZED   OWNER      TABLE_NAME                       NUM_ROWS
--------------- ---------- ------------------------- ---------------
24-MAY-13       SYS        AUD$                              1982500

00:36:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
28-AUG-10 11.58.44.054379 PM


02:45:16 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
10-JAN-12 11.56.07.081514 PM

Elapsed: 00:00:01.51


02:46:13 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -250);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
16-SEP-12 11.58.46.296859 PM

Elapsed: 00:00:01.55


02:59:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -120);

MAX(NTIMESTAMP#)
---------------------------------------------------------------------------
24-JAN-13 11.59.17.319628 PM


03:04:53 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000);

795713 rows deleted.

Elapsed: 00:01:02.47


03:06:08 [SYS]>> commit;

Commit complete.

Elapsed: 00:00:00.02


03:06:38 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500);

660309 rows deleted.

Elapsed: 00:00:56.64


03:07:58 [SYS]>> commit;

Commit complete.

Elapsed: 00:00:00.01


03:08:08 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -120);

396701 rows deleted.

Elapsed: 00:00:46.57


03:09:07 [SYS]>> commit;

Commit complete.



Rebuilding Indexes
----------------------
03:25:23 [SYS]>> select owner,index_name,index_type,LAST_ANALYZED from dba_indexes where table_name='AUD$' and owner='SYS';

OWNER        INDEX_NAME                               INDEX_TYPE   LAST_ANALYZED
------------ ---------------------------------------- ------------ ---------------
SYS          SYS_IL0000000375C00040$$                 LOB
SYS          SYS_IL0000000375C00041$$                 LOB
SYS          I_AUD1                                   NORMAL       25-MAY-13


-->If any Indexes listed above needs to rebuild indexes.

03:27:50 [SYS]>> alter index SYS.I_AUD1 rebuild online;

Index altered.

Elapsed: 00:00:05.39


03:35:59 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5);

PL/SQL procedure successfully completed.


03:37:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$';

LAST_ANALYZED   OWNER        TABLE_NAME                  NUM_ROWS
--------------- ------------ -------------------- ---------------
25-MAY-13       SYS          AUD$                          123510