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

ORA-8180 when purging Unified Audit Trail

With Oracle Database 12c a new audit facility got introduced called Unified Audit Trail. It combines all the audit data that is gathered throughout the whole database. See the Database Security Guide for more information about this feature.

To manage audit records in the Unified Audit Trail the package DBMS_AUDIT_MGMT got enhanced. So in order to get rid of outdated records you need to specify a timestamp, all records older than that are eligible for deletion. To keep one week of audit data for the current container is set up like this:

SQL> exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( -
2 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
3 LAST_ARCHIVE_TIME => SYSTIMESTAMP -7, -
4 CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT -
5 );

You can verify this setting using the following query:

SQL> SELECT last_archive_ts FROM dba_audit_mgmt_last_arch_ts;

Now you can remove the outdated records:

SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL ( -
2  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
3  USE_LAST_ARCH_TIMESTAMP => TRUE -
4 );

In order to do the audit purging automatically you need to create a database job:

SQL> exec DBMS_AUDIT_MGMT.CREATE_PURGE_JOB ( -
2 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
3 AUDIT_TRAIL_PURGE_INTERVAL => 1, -
4 AUDIT_TRAIL_PURGE_NAME => 'All_Audit_Trail_PJ', -
5 USE_LAST_ARCH_TIMESTAMP => TRUE, -
6 CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

Unfortunately there is a bug 18743542 which causes an error instead of deleting old audit records. This happens in interactive mode as well as with the purge job. For the first case you see the error immediately, in the latter case you will see it in the alert.log.

Interactively:

SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL ( -
2 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
3 ,USE_LAST_ARCH_TIMESTAMP => TRUE -
4 );

ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 5134
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1187
ORA-06512: at line 2

Using purge job:

Thu Jan 28 08:31:12 2016
Errors in file /u01/app/oracle/diag/rdbms/cdbxxx/CDBXXX_2/trace/CDBXXX02_2_j000_13620.trc:
ORA-12012: error on auto execute of job "SYS"."ALL_AUDIT_TRAIL_PJ"
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 5134
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1187
ORA-06512: at line 1

The bug is fixed in PSU 5 (October 2015) onwards. So be sure to apply the patch before starting to use the Unified Audit Trail.