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

[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表空间,另外如何定期清理还给看一些相关文档!!