Data … as usual

Due to American Security laws, Oracle has changed its audit management rules in Oracle 11g.

Now, AUDIT_TRAIL is configured bydefault to ‘DB’. Many operations are now audited by default and stored in the AUD$ table (which is stored by default in the SYSTEM tablespace).

Audited operations are available in the DBA_PRIV_AUDIT_OPTS view, and if you have a look in it, you will see that “CREATE SESSION” operations are audited.

So, if you create your database with ‘dbca’, if you update your database with ‘dbua’, you leave the default configuration, and your SYSTEM Tablespace datafile is in autoextend mode … you will probably have a huge system datafile soon.

To manage the audit trail, you have different solutions:

– The first one is to disable audit trail: set AUDIT_TRAIL to NONE into your spfile file, shutdown and restart your database.

– The second one is to move your AUD$ to another tablespace :

SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            SYSTEM
SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUD_TS');

PL/SQL procedure successfully completed.
SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            AUD_TS

– The third one is to manage the properties of the audit trail with the DBMS_AUDIT_MGMT PL/SQL package

If you want to change it, you have to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure to configure properties of the audit trail:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                           audit_trail_property=>DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  4                                           audit_trail_property_value=>1000);
  5  end;
  6  /

Then, you have to initialize the cleanup, and to define the oldest record to leave in the table (Here 30 days):

SQL> begin
  2  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                               default_cleanup_interval=>720);
  4
  5  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  6                                             last_archive_time => SYSTIMESTAMP-30);
  7  end;
  8  /

Next, we create a job. This job will be executed every 168 hours (7 days).

SQL> BEGIN
  2  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                   audit_trail_purge_interval => 168,
  4                                   audit_trail_purge_name=> 'PURGE_AUD_JOB',
  5                                   use_last_arch_timestamp => TRUE);
  6  END;
  7  /
Note : if USE_LAST_ARCH_TIMESTAMP is set to TRUE, the job will purge only the oldest entries that match to the corresponding retention parameter. You can have a look to the DBA_SCHEDULER_JOBS to have more details about this new job.

Here I gave you an example to purge the DB audit structure (AUD$ and FGA_AUD$ tables), if you have a look to the Oracle documentation, you can use the same procedure to purge OS Audit File, XML File.