Unified Audit Log Cannot Be Purged Based On Last Archive Timestamp When Existing PART_n Partition Under CLI_SWP$xxxx
(Doc ID 2524766.1)
Last updated on FEBRUARY 20, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
- Unified audit log cannot be purged with USE_LAST_ARCH_TIMESTAMP=>TRUE.
-
SQL> select count(*)
2 from UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
1733336
SQL> BEGIN
2 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
4 last_archive_time => TO_TIMESTAMP('01-12-1805:00:00.0','DD-MM-RRHH24:MI:SS.FF')
5 );
6 END;
7 /
The PL/SQL procedure completed successfully.
SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
AUDIT_TRAIL RAC_INSTANCE
-------------------- ------------
LAST_ARCHIVE_TS
---------------------------------------------------------------------------
DATABASE_ID CONTAINER_GUID
----------- ---------------------------------
UNIFIED AUDIT TRAIL 0
18-12-01 05:00:00.000000 +00:00
414287026 6095281B648929FAE053032210AC65A3
SQL> BEGIN
2 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
4 use_last_arch_timestamp => TRUE);
5 END;
6 /
The PL/SQL procedure completed successfully.
SQL> select count(*)
2 from UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
1733336
- From the 10046 events trace, there are multiple partitions PART_3, PART_4 and HIGH_PART in CLI_SWP$xxxx table, and no partition drop statement against PART_3 and PART_4, and the delete from HIGH_PART did not delete any rows.
-
SQL ID: 8wfpkmg39czws Plan Hash: 4245966381
select max_scn, max_time
from
"CLI_SWP$61d34298$2$1" partition("PART_3") where max_scn = (select
max(max_scn) from "CLI_SWP$61d34298$2$1" partition("PART_3")) order by
max_scn, max_time desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.42 0.74 55228 110488 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.42 0.74 55228 110488 0 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=110488 pr=55228 pw=0 time=746562 us cost=29325 size=16 card=1)
1 1 1 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=110488 pr=55228 pw=0 time=746545 us cost=14662 size=16 card=1)
1 1 1 TABLE ACCESS FULL CLI_SWP$61d34298$2$1 PARTITION: 1 1 (cr=110488 pr=55228 pw=0 time=746543 us cost=14662 size=16 card=1)
1 1 1 SORT AGGREGATE (cr=55244 pr=55223 pw=0 time=649083 us)
298751 298751 298751 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=55244 pr=55223 pw=0 time=318771 us cost=14662 size=2316408 card=289551)
298751 298751 298751 TABLE ACCESS FULL CLI_SWP$61d34298$2$1 PARTITION: 1 1 (cr=55244 pr=55223 pw=0 time=298210 us cost=14662 size=2316408 card=289551)
SQL ID: 6ygmhq49ypxf0 Plan Hash: 4245966381
select max_scn, max_time
from
"CLI_SWP$61d34298$2$1" partition("PART_4") where max_scn = (select
max(max_scn) from "CLI_SWP$61d34298$2$1" partition("PART_4")) order by
max_scn, max_time desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.24 0.40 32025 64076 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.24 0.40 32025 64076 0 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=64076 pr=32025 pw=0 time=403928 us cost=16261 size=16 card=1)
1 1 1 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=64076 pr=32025 pw=0 time=403912 us cost=8130 size=16 card=1)
1 1 1 TABLE ACCESS FULL CLI_SWP$61d34298$2$1 PARTITION: 2 2 (cr=64076 pr=32025 pw=0 time=403907 us cost=8130 size=16 card=1)
1 1 1 SORT AGGREGATE (cr=32038 pr=32020 pw=0 time=346897 us)
174509 174509 174509 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=32038 pr=32020 pw=0 time=46381 us cost=8130 size=1302440 card=162805)
174509 174509 174509 TABLE ACCESS FULL CLI_SWP$61d34298$2$1 PARTITION: 2 2 (cr=32038 pr=32020 pw=0 time=39809 us cost=8130 size=1302440 card=162805)
SQL ID: 2xh2ckgj62gtd Plan Hash: 300013943
delete from "CLI_SWP$61d34298$2$1" partition("HIGH_PART")
where
max_time < :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.18 0.31 31312 33569 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.18 0.31 31312 33569 1 0
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE CLI_SWP$61d34298$2$1 (cr=33569 pr=31312 pw=0 time=314892 us)
0 0 0 PARTITION RANGE SINGLE PARTITION: 3 3 (cr=33569 pr=31312 pw=0 time=314889 us cost=7336 size=39 card=1)
0 0 0 TABLE ACCESS FULL CLI_SWP$61d34298$2$1 PARTITION: 3 3 (cr=33569 pr=31312 pw=0 time=314886 us cost=7336 size=39 card=1)
- Query the max_time range of Both PART_3 and PART_4, and it is found that the max value of max_time of both partiton is newer than last_archive_timestamp setting.
-
SQL> select min(FLUSH_TIME),min(max_scn),min(max_time) ,
2 max(FLUSH_TIME),max(max_scn),max(max_time)
3 from audsys."CLI_SWP$61d34298$2$1" partition("PART_3");
MIN(FLUSH_ MIN(MAX_SCN) MIN(MAX_TI MAX(FLUSH_ MAX(MAX_SCN) MAX(MAX_TI
---------- ------------ ---------- ---------- ------------ ----------
2018-11-15 3.7249E+10 2018-11-15 2018-12-06 3.7438E+10 2018-12-06
SQL> select min(FLUSH_TIME),min(max_scn),min(max_time) ,
2 max(FLUSH_TIME),max(max_scn),max(max_time)
3 from audsys."CLI_SWP$61d34298$2$1" partition("PART_4");
MIN(FLUSH_ MIN(MAX_SCN) MIN(MAX_TI MAX(FLUSH_ MAX(MAX_SCN) MAX(MAX_TI
---------- ------------ ---------- ---------- ------------ ----------
2018-12-06 3.7435E+10 2018-12-06 2018-12-18 3.7544E+10 2018-12-18
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 |