My Oracle Support Banner

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 JANUARY 19, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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