Managing the Audit Trail

Preface

The Oracle Database can audit quite a lot of things and keeps this information in several places. There is the standard audit trail, a trail for fine grained auditing, an OS audit trail and since 12c an unified audit trail. Since there are a couple of operations like logongs with SYSDBA privileges or use of ANY privileges that are audited by default, the trails keep growing. There is no default rule for purging since that depends on your business requirements. But Oracle provides a PL/SQL package to manage audit trail records called DBMS_AUDIT_MGMT. This post will outline the usage of this package to keep audit trails clean. I will handle the standard audit trail (AUD$) and OS audit trail keeping the last 7 days, but the procedures can be easily changed to handle other audit trails.

Initialization

Before starting to purge records, it is required to initialize each audit trail that should be handled. This procedure moves the audit tables into the SYSAUX tablespace which may take a while depending on the size of those tables. It also defines a default interval for purging.

SQL> exec DBMS_AUDIT_MGMT.INIT_CLEANUP( -
2    audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_STD, - 
3    default_cleanup_interval  => 24 -
4 );   
SQL> exec DBMS_AUDIT_MGMT.INIT_CLEANUP( -
2    audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, - 
3    default_cleanup_interval  => 24 -
4 );   

That’s it.

Define what to purge

Next step is to define the oldest timestamp until audit records may be purged. This timestamp is called the “last archive timestamp” since Oracle assumes that audit records are archived elsewhere.

For the standard audit trail:

SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( -
2  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, -
3  LAST_ARCHIVE_TIME => SYSTIMESTAMP -7 -
4  );

And similar for the OS audit trail:

SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( -
2  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, -
3  LAST_ARCHIVE_TIME => SYSTIMESTAMP -7 -
4  );

This is the moment where it gets special. If you are running RAC databases, you need to do that for all the instances:

SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( -
2  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, -
3  LAST_ARCHIVE_TIME => SYSTIMESTAMP -7, -
4  RAC_INSTANCE_NUMBER => 1 -
5  );
SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( -
2  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, -
3  LAST_ARCHIVE_TIME => SYSTIMESTAMP -7, -
4  RAC_INSTANCE_NUMBER => 2 -
5  );

Note that the “LAST_ARCHIVE_TIME” is a value, not an expression. I’ll come back to that later. Now let’s check the settings:

SQL> select AUDIT_TRAIL, RAC_INSTANCE, LAST_ARCHIVE_TS
  2  from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------
OS AUDIT TRAIL                  1 15-FEB-16 01.22.04.000000 PM +01:00
STANDARD AUDIT TRAIL            0 15-FEB-16 01.21.25.000000 PM +00:00
OS AUDIT TRAIL                  2 15-FEB-16 01.22.09.000000 PM +01:00

Purging

Now it is finally time to get rid of all those outdated audit records. Let’s start purging the standard audit trail:

SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
2   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, -
3   use_last_arch_timestamp => TRUE -
4 );

Followed by the OS audit trail:

SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
2   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, -
3   use_last_arch_timestamp => TRUE -
4 );

This time we do not care about RAC or single instance, it is done for all instances. The operation keeps the amount of deleted records in DBA_AUDIT_MGMT_CLEAN_EVENTS:

SQL> select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

AUDIT_TRAIL          RAC_INSTANCE CLEANUP_TIME                         DELETE_COUNT WAS
-------------------- ------------ ------------------------------------ ------------ ---
OS AUDIT TRAIL                  1 22-FEB-16 12.25.45.422483 PM +00:00           139 NO
OS AUDIT TRAIL                  2 22-FEB-16 12.25.45.443832 PM +00:00            99 NO

Automating

Of cause nobody wants to do manual tasks over and over again. That’s why the package allows us to create jobs that do the purging on a regular basis. This is also pretty staight forward:

SQL> EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( -
2   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, -
3   audit_trail_purge_interval => 24, -
4   audit_trail_purge_name => 'CLEANUP_AUDIT_AUD_STD', - 
5   use_last_arch_timestamp => TRUE -
6 );
SQL> EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( -
2   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, -
3   audit_trail_purge_interval => 24, -
4   audit_trail_purge_name => 'CLEANUP_AUDIT_TRAIL_OS', - 
5   use_last_arch_timestamp => TRUE -
6 );

We can of cause query the existing jobs:

SQL> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

JOB_NAME                  JOB_STAT AUDIT_TRAIL                  JOB_FREQUENCY             USE JOB_CON
------------------------- -------- ---------------------------- ------------------------- --- -------
CLEANUP_AUDIT_DB_STD      ENABLED  STANDARD AUDIT TRAIL         FREQ=HOURLY;INTERVAL=24   YES CURRENT
CLEANUP_AUDIT_TRAIL_OS    ENABLED  OS AUDIT TRAIL               FREQ=HOURLY;INTERVAL=24   YES CURRENT

As you can see we are using “use_last_arch_timestamp” equals “true”, that means the value defined by the SET_LAST_ARCHIVE_TS procedure is used. Now remember, this value is not an expression, it is a value. And it is not updated automatically. So the purge jobs are only half the truth. We need another job that updates that timestamp. The job may look like this, it takes care of RAC instances, for single instances you may need to remove the for loop:

begin
  dbms_scheduler.create_job(
    job_name   => 'CLEANUP_AUDIT_SET_TS',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( 
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
 LAST_ARCHIVE_TIME => SYSTIMESTAMP -7 
);	
for rec in (select inst_id from gv$instance) loop
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( 
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, 
 LAST_ARCHIVE_TIME => SYSTIMESTAMP -7, 
 RAC_INSTANCE_NUMBER => rec.inst_id
);
end loop;
end;',
    start_date      => trunc(systimestamp+1) + 1/24,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24'
  );
end;
/

That’s it.

Further reading

You may find further information here:
Oracle DOC: Administering the Audit Trail
Oracle DOC: DBMS_AUDIT_MGMT
My Blog: ORA-8180 when purging Unified Audit Trail

Advertisements