[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表空間,另外如何定期清理還給看一些相關檔案!!