[20230301]学习UNIFIED audit-整理AUDSYS.AUD$UNIFIED.txt

--//以前Failed Logon Delay等待事件,导致AUDSYS.AUD$UNIFIED记录了大量的垃圾日志.整理一下.

1.环境:
SYS@192.168.100.141:1521/dyhis> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SELECT MAX (event_timestamp)
  FROM unified_AUDIT_trail
 WHERE     UNIFIED_AUDIT_POLICIES = 'ORA_LOGON_FAILURES'
       AND os_username = 'capaa';

MAX(EVENT_TIMESTAMP)
--------------------------
2023-03-01 11:48:33.096124
--//问题还是没有解决!!

2.执行:
SYS@192.168.100.141:1521/dyhis> exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,LAST_ARCHIVE_TIME => SYSDATE-60);
PL/SQL procedure successfully completed.
--//审计的清除oracle设置特别繁琐,首先要执行DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP,设置一个时间点.

SYS@192.168.100.141:1521/dyhis> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,USE_LAST_ARCH_TIMESTAMP => TRUE);
PL/SQL procedure successfully completed.
--//如果你不想执行前面的步骤,可以设置USE_LAST_ARCH_TIMESTAMP =>FALSE,不过这样应该是全部清除!!

SYS@192.168.100.141:1521/dyhis> @ o2 audsys.AUD$UNIFIED
owner  object_name object_type          status           OID      D_OID CREATED             LAST_DDL_TIME
------ ----------- -------------------- --------- ---------- ---------- ------------------- -------------------
AUDSYS AUD$UNIFIED TABLE                VALID          18580            2020-10-20 10:28:13 2023-02-24 11:57:30
AUDSYS AUD$UNIFIED TABLE PARTITION      VALID         171804     171804 2023-01-01 08:00:04 2023-01-01 08:00:04
AUDSYS AUD$UNIFIED TABLE PARTITION      VALID         174234     174234 2023-02-01 08:00:00 2023-02-01 08:00:00
AUDSYS AUD$UNIFIED TABLE PARTITION      VALID         169121     169121 2022-12-01 08:00:04 2022-12-01 08:00:04

SYS@192.168.100.141:1521/dyhis> @ seg audsys.AUD$UNIFIED
SEG_MB OWNER  SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE    SEG_TABLESPACE_NAME     BLOCKS     HDRFIL     HDRBLK
------ ------ ------------ ------------- --------------- ------------------- ---------- ---------- ----------
   144 AUDSYS AUD$UNIFIED  SYS_P19622    TABLE PARTITION SYSAUX                   18432          3     656234
   148 AUDSYS AUD$UNIFIED  SYS_P18918    TABLE PARTITION SYSAUX                   18944          3     682490
    88 AUDSYS AUD$UNIFIED  SYS_P20283    TABLE PARTITION SYSAUX                   11264          3     550906
--//可以看出每个月基本在14XM.
--//从各种情况考虑,还是不要放在sysaux表空间,另外如何定期清理还给看一些相关文档!!