Old Unified Audting Log Cannot Be Purged By use_last_arch_timestamp => TRUE (Doc ID 2655999.1)

Last updated on JULY 20, 2024

Applies to:

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

Symptoms

Customer uses tablespace AUDIT_DATA as the location of Unified Auditing log, and the usage of AUDIT_DATA reached 95.18%.
Customer execute following command to purge the Unified Auditing log before 2020/01/01, and the usage of AUDIT_DATA decreased to 48.52%, but many audit records before 2020/01/01 are still remaining.

[Tue Feb 25 16:34:22.298 2020] 16:34:05 SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;
...
[Tue Feb 25 16:34:37.408 2020] 16:34:20 SQL> select count(*) from unified_audit_trail where EVENT_TIMESTAMP < '20191231 00:00:00';
[Tue Feb 25 16:35:07.411 2020]
[Tue Feb 25 16:35:07.411 2020]   COUNT(*)
[Tue Feb 25 16:35:07.411 2020] ----------
[Tue Feb 25 16:35:07.411 2020]       1271
...
[Tue Feb 25 16:36:14.696 2020] 16:35:57 SQL> BEGIN
[Tue Feb 25 16:36:36.072 2020] 16:36:19   2 dbms_audit_mgmt.set_last_archive_timestamp(
[Tue Feb 25 16:36:36.072 2020] 16:36:19   3 audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
[Tue Feb 25 16:36:36.087 2020] 16:36:19   4 last_archive_time => to_date('2020/01/01 09:00:00','YYYY/mm/dd HH24:MI:SS'));
[Tue Feb 25 16:36:36.103 2020] 16:36:19   5 END;
[Tue Feb 25 16:36:36.119 2020] 16:36:19   6 /
...
[Tue Feb 25 16:36:36.306 2020] 16:36:19 SQL> BEGIN
[Tue Feb 25 16:37:02.949 2020] 16:36:46   2 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
[Tue Feb 25 16:37:02.965 2020] 16:36:46   3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
[Tue Feb 25 16:37:02.980 2020] 16:36:46   4 use_last_arch_timestamp => TRUE);
[Tue Feb 25 16:37:02.996 2020] 16:36:46   5 END;
[Tue Feb 25 16:37:03.011 2020] 16:36:46   6 /
...
[Tue Feb 25 16:39:00.942 2020] 16:38:44 SQL>
[Tue Feb 25 16:39:02.348 2020] 16:38:45 SQL> select count(*) from unified_audit_trail where EVENT_TIMESTAMP < '20191231 00:00:00';
[Tue Feb 25 16:39:14.536 2020]
[Tue Feb 25 16:39:27.989 2020]
[Tue Feb 25 16:39:27.989 2020]   COUNT(*)
[Tue Feb 25 16:39:27.989 2020] ----------
[Tue Feb 25 16:39:27.989 2020]        382

Many .bin files under $ORACLE_BASE/audit/$ORACLE_SID are still remaining.
10046 event trace of DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL shows DELETE against "CLI_SWP$xxxx" besides AUDSYS.AUD$UNIFIED partition drop and DELETE against audsys.aud$unified.

--------------------------------
PARSING IN CURSOR #140059085014528 len=77 dep=1 uid=8 oct=7 lid=8 tim=8286086755148 hv=3479003038 ad='fe932380' sqlid='ftghcug7purwy'
delete from "CLI_SWP$83eba4e8$1$1" partition("HIGH_PART") where max_time < :1
END OF STMT
PARSE #140059085014528:c=96,e=96,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=8286086755148
BINDS #140059085014528:

Bind#0
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0000 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7ffe73f9c5f8 bln=07 avl=07 flg=09
value="1/1/2020 9:0:0"
--------------------------------

Changes

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

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

Old Unified Audting Log Cannot Be Purged By use_last_arch_timestamp => TRUE (Doc ID 2655999.1)

Applies to:

Symptoms

Changes

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!