oracle audit log configuration

This post is about controlling the audit log on your system, at least the ones that goes into your database. The audit trails located on your fs is not covered here. Your vendor will normally set up an audit  configuration, but don't count on it. Worst case, they might not set up anything at all. A default oracle 11g install will set up standard audit trail to log at  least logon / logoff events, and even this will add up quite a bit over time. By default your audit logs will be added to your system tablespace, and really,  they should not be there.

So, if you have a running database, that has been there a while, this post might be worth while reading. If you have a brand new database, or your audit data is lets say, less than a GB, you can more or less  just follow the instructions.

If you have large amounts of audit data, many GB, you might need to do this in steps. Be aware that you can get extensive archive logging. As a rule of thumb, around the size of the portion you are cutting away. So use the queries below to do the math. Post me questions if you like :)

This example only keep audit logs online for 90 days. If you need to go back more than 90 days you must restore a copy of the database to cover you needs.


First of all, you need a good overview of your existing audit data.

select segment_name,round(bytes/1024/1024) from dba_segments where segment_name in ('AUD$', 'FGA_LOG$');

select table_name, tablespace_name from dba_tables where table_name in ('AUD$', 'FGA_LOG$') ORDER BY table_name;

NOTE! for test db's you might see entries in the audit log belonging to other dbid's. Purging will only affect rows belonging to the active dbid.

-- select dbid,count(*) from aud$ group by dbid;

// Find the oldest entries
-- select dbid,min(NTIMESTAMP#) from aud$ group by dbid;

// Check out historic number of rows per month and use this to calculate the space you need to allocate for the future.
-- select dbid,to_char(NTIMESTAMP#, 'yyyy-mm'), count(*) from aud$ group by dbid,to_char(NTIMESTAMP#, 'yyyy-mm') order by 1,2;

// Check out recent events trapped in the audit log
column terminal format a20
column action_name format a10
column userhost format a30
column os_username format a20
-- column extended_timestamp format a40
select action_name,returncode,username,count(*)
from dba_audit_session where timestamp>(sysdate-7) group by action_name,returncode,username;

NOTE! Use the size from the first query and create new tablespace with initial 2 x the size of AUD$

-- create tablespace audit_aux datafile '/u02/oradata/your-db/audit_aux01.dbf' size 200m autoextend on next 100m maxsize 4000m;

/* The following will physically move the AUD$ table from what you had ( default system ) to your new tablespace. */

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

/* The following will physically move the FGA_LOG$ table from what you had ( default system ) to your new tablespace. */

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

// Check if already initialized, if not do it.
set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

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;
/

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

/* This will take both types located in db, aud$ and fga_log$
NOTE! if you have not moved the the audit tables to the new database, this will trigger a move. The destination will be as specified by: SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS; */

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    default_cleanup_interval => 24 /* hours */);
END;
/

// Check to see if "last archived audit timestamp" is set
-- select * FROM dba_audit_mgmt_last_arch_ts;

// If not, set it, 90 will preserve audit logs online for three months
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-90);
END;
/

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    last_archive_time => SYSTIMESTAMP-90);
END;
/

// Create jobs to purge the audit logs

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
   audit_trail_purge_interval  => 24,
   audit_trail_purge_name      => 'std_audit_trail_purge_job',
   use_last_arch_timestamp     => TRUE
   );
end;
/

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_fga_std,
   audit_trail_purge_interval  => 24,
   audit_trail_purge_name      => 'fga_audit_trail_purge_job',
   use_last_arch_timestamp     => TRUE
   );
end;
/

// Create a job to alter the preserved date to 90 days
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'audit_last_archive_time',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE
      l_days NUMBER := 90;
      BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_aud_std, TRUNC(SYSTIMESTAMP)-l_days);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_fga_std, TRUNC(SYSTIMESTAMP)-l_days);
      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;
/

// if you ever need it, jobs can be kicked off manually
begin
   dbms_scheduler.run_job (job_name => 'audit_last_archive_time');
end;
/

// to list the audit jobs only
select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

// to list all jobs
select owner||'.'||job_name||'.'||start_date||'.'||next_run_date||'.'||state from dba_scheduler_jobs where enabled='TRUE';