My Oracle Support Banner

Cleanup unified audit trail records based on Last_archive_timestamp does not works as expected. (Doc ID 2904294.1)

Last updated on JULY 20, 2024

Applies to:

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

Symptoms

The goal is to clean up unified audit trail records based on Last_archive_timestamp but it does not works as expected.

- The Last_archive_timestamp was set on "18-OCT-22" (Today is 20-OCT-2022)

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => SYSTIMESTAMP-2);
END;
/

PL/SQL procedure successfully completed.

 

- Current output of minimum EVENT_TIMESTAMP value of unified Audit records is "16-OCT-22"

select max(EVENT_TIMESTAMP), min(EVENT_TIMESTAMP) from unified_audit_trail;

MAX(EVENT_TIMESTAMP) MIN(EVENT_TIMESTAMP)
---------------------------------------- ----------------------------------------
20-OCT-22 06.01.25.498386 PM 16-OCT-22 01.46.45.957198 AM

 

- Verified Last_archive_timestamp is set at "18-OCT-22"

AUDIT_TRAIL RID LAST_ARCHIVE_TS DBID CONTAINER_GUID
-------------------- --- -------------------- ------------- ---------------
UNIFIED AUDIT TRAIL 0 18-OCT-22 09.57.08.000000 PM +00:00 3453913676 86B637B62FDF7A6

 

- Executed below procedure to clean up unified audit trail records based on Last_archive_timestamp.

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.

SQL>

 

- The expectation is that aging audit records should have been deleted from "18-OCT-22" and there are still aging records in Unified_audit_trail view.

select max(EVENT_TIMESTAMP), min(EVENT_TIMESTAMP) from unified_audit_trail;

MAX(EVENT_TIMESTAMP) MIN(EVENT_TIMESTAMP)
---------------------------------------- ----------------------------------------
20-OCT-22 06.20.02.539013 PM 16-OCT-22 01.46.45.957198 AM <---- Aging records still listed. 

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
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.