Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.  The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier.

Follow below steps for puring aud$ table.

1. Make sure AUD$ table is not in SYSTEM table tablespace.

  If AUD$ table is present in SYSTEM tablespace , then move it to a a new dedicated tablespace using below script.

 

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER        SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME                BYTES/1024/1024
------------ ------------ ------------------ ------------------------------ ---------------
SYS          AUD$         TABLE              SYSTEM                                   176


SQL>
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TS_AUDIT');
END;
/

SQL>   2    3    4    5

PL/SQL procedure successfully completed.

SQL>
 select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER        SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME                BYTES/1024/1024
------------ ------------ ------------------ ------------------------------ ---------------
SYS          AUD$         TABLE              TS_AUDIT                                   176


 

2. Check the audit mgmt configuration parameters:
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SET LINES 2000
 
SQL>
SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    UNIFIED AUDIT TRAIL
DB AUDIT TABLESPACE            TS_AUDIT             STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               UNIFIED 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
AUDIT WRITE MODE               QUEUED WRITE MODE    UNIFIED AUDIT TRAIL
 
14 rows selected.

 

3. Initialize the clean job:

Note – If you haven’t moved the AUD$ table out of SYSTEM tablespace, then it the below script will move the AUD$ to SYSAUX tablespace by default.

SQL>
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;
/  
 
PL/SQL procedure successfully completed.

4. Validate whether initialization is success or not:

It should return YES

SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/ 

YES

PL/SQL procedure successfully completed.

 

5. Set LAST_ARCHIVE_TIMESTAMP:

             This value allows the audit record to be purged before a particular date. Suppose LAST_ARCHIVE_TIME is set to SYSTIMESTAMP -30, then it will keep only recent 30 days record and purge the all audit records dated before 30 days.

SQL>
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-30);
END;
/  2    3    4    5    6
 
PL/SQL procedure successfully completed.
 

SQL>  COLUMN audit_trail FORMAT A20
SQL> COLUMN last_archive_ts FORMAT A40
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS                          DATABASE_ID CONTAINER_GUID
-------------------- ------------ ---------------------------------------- ----------- ---------------------------------
STANDARD AUDIT TRAIL            0 28-MAY-16 11.29.39.000000 AM +00:00        552547857 183E654F83D9063AE0540010E0202A24


6. If you want to run the purge instantly( one time):
BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

 7. Schedule a job to purge AUD$ regularly.

Below job will run daily and delete all the aud$ records before systimestamp- 30 .

SQL[SYS@TCRMDB03]SQL>>]BEGIN
DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'PURGE_DB_AUDIT_TRAILS',
use_last_arch_timestamp => TRUE);
END;
/

PL/SQL procedure successfully completed.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'set_last_archive_aud_timestamp',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/

8. Updating audit properties.

Note:-   If the AUD$ table, lot of rows ( around 2 to 3 crores), then setting the DB AUDIT CLEAN BATCH SIZE to a bit higher values (around 2 to 3 lakh) will increase the performance.  Keeping a low value will increase the time taken for purge and a very high value will led to higher undo generation.

 

SQL>
  SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL

SQL> SQL>
SQL> begin
 dbms_audit_mgmt.set_audit_trail_property (
 audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
 audit_trail_property => dbms_audit_mgmt.db_delete_batch_size,
 audit_trail_property_value => 300000);
 end;
 /  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL>  SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE      300000               STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL