My Oracle Support Banner

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

Last updated on APRIL 27, 2020

Applies to:

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

Symptoms

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