DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL is NOT purging all the records before LAST_ARCHIVE_TIMESTAMP in Unified Auditing when there is one DBID (Doc ID 2744602.1)

Last updated on AUGUST 11, 2023

Applies to:

Symptoms

When using LAST_ARCHIVE_TIMESTAMP ==> TRUE in DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL, all the records before the LAST_ARCHIVE_TIMESTAMP is not Purging from Unified_Audit_Trail

Even when it runs successfully it seems to not account for the timestamp to purge. Customers see the records that are older than LAST_ARCHIVE_TIMESTAMP

Steps Performed

Step 1

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => '10-JAN-2021 12:00:00');
END;
/

PL/SQL procedure successfully completed.

Step 2

SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

PL/SQL procedure successfully completed.

Step 3

SQL> select to_char(trunc(event_timestamp), 'YYYY-MM-DD') as "DAY" , count(*) from unified_audit_trail group by to_char(trunc(event_timestamp), 'YYYY-MM-DD') order by 1;

DAY COUNT(*)
---------- ----------
2020-12-14 6
2020-12-15 9
2021-01-09 3414
2021-01-10 8977
2021-01-11 12183
2021-01-12 8847
2021-01-13 2926

Changes

 Happens when there are huge number of records in Unified audit tables 

In this case there is no multiple DBIDs present

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.