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.

Advertisements