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