[20230403]學習UNIFIED audit--驗證清理AUDSYS.AUD$UNIFIED.txt

[20230403]學習UNIFIED audit--驗證清理AUDSYS.AUD$UNIFIED.txt

--//前一陣子寫了定期清理AUDSYS.AUD$UNIFIED的schedule.連結
--//http://blog.itpub.net/267265/viewspace-2937975/=>[20230303]學習UNIFIED audit--定期清理AUDSYS.AUD$UNIFIED.txt

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.JOB_PURGE_AUDIT_RECORDS'
      ,start_date      => TO_TIMESTAMP_TZ('2023/03/03 09:07:24.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'freq=monthly;bydate=0101,0201,0301,0401,0501,0601,0701,0801,0901,1001,1101,1201;byhour=8; byminute=50; bysecond=0;'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,add_months(trunc(sysdate,''mm''),-2));
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,USE_LAST_ARCH_TIMESTAMP=>TRUE);
END;'
      ,comments        => 'Update last_archive_timestamp and clean unified audit trail'
    );
END;
/

--//今天看看效果:

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.

SYS@192.168.100.141:1521/dyhis> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS                                                             DATABASE_ID CONTAINER_GUID
-------------------- ------------ --------------------------------------------------------------------------- ----------- ---------------------------------
UNIFIED AUDIT TRAIL             0 2023-02-01 00:00:00.000000 +00:00                                            4090373436 B60D258AC2D9EF54E0532A63A8C09F1F

SYS@192.168.100.141:1521/dyhis> select add_months(trunc(sysdate,'mm'),1),add_months(trunc(sysdate,'mm'),-2) from dual ;
ADD_MONTHS(TRUNC(SY ADD_MONTHS(TRUNC(SY
------------------- -------------------
2023-05-01 00:00:00 2023-02-01 00:00:00

--//設定LAST_ARCHIVE_TS已經是2023-02-01 00:00:00.
SYS@192.168.100.141:1521/dyhis> select * from DBA_SCHEDULER_JOB_log where JOB_NAME='JOB_PURGE_AUDIT_RECORDS'
  2  @ pr
==============================
LOG_ID                        : 249070
LOG_DATE                      : 2023-04-01 08:50:07.083490 +08:00
OWNER                         : SYS
JOB_NAME                      : JOB_PURGE_AUDIT_RECORDS
JOB_SUBNAME                   :
JOB_CLASS                     : DEFAULT_JOB_CLASS
OPERATION                     : RUN
STATUS                        : SUCCEEDED
USER_NAME                     :
CLIENT_ID                     :
GLOBAL_UID                    :
CREDENTIAL_OWNER              :
CREDENTIAL_NAME               :
DESTINATION_OWNER             :
DESTINATION                   :
ADDITIONAL_INFO               :
PL/SQL procedure successfully completed.

SYS@192.168.100.141:1521/dyhis> @ o2 AUDSYS.AUD$UNIFIED
owner  object_name object_type          SEG_PART_NAME        status           OID      D_OID CREATED             LAST_DDL_TIME
------ ----------- -------------------- -------------------- --------- ---------- ---------- ------------------- -------------------
AUDSYS AUD$UNIFIED TABLE                                     VALID          18580            2020-10-20 10:28:13 2023-03-01 11:21:46
AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P19622           VALID         171804     171804 2023-01-01 08:00:04 2023-01-01 08:00:04
AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P20283           VALID         174234     174234 2023-02-01 08:00:00 2023-02-01 08:00:00
AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P20923           VALID         176513     176513 2023-03-01 08:00:05 2023-03-01 08:00:05
AUDSYS AUD$UNIFIED TABLE PARTITION      SYS_P21663           VALID         179544     179544 2023-04-01 08:00:01 2023-04-01 08:00:01

SYS@192.168.100.141:1521/dyhis> @ seg2 AUDSYS.AUD$UNIFIED
SEG_MB OWNER  SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE         SEG_TABLESPACE_NAME     BLOCKS     HDRFIL     HDRBLK
------ ------ ------------ ------------- -------------------- ------------------- ---------- ---------- ----------
   175 AUDSYS AUD$UNIFIED  SYS_P20923    TABLE PARTITION      SYSAUX                   22400          3     145690
    16 AUDSYS AUD$UNIFIED  SYS_P21663    TABLE PARTITION      USERS                     2048          2      48657
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    
   144 AUDSYS AUD$UNIFIED  SYS_P19622    TABLE PARTITION      SYSAUX                   18432          3     656234
    88 AUDSYS AUD$UNIFIED  SYS_P20283    TABLE PARTITION      SYSAUX                   11264          3     550906
--//新建立的分割槽已經在users表空間.

SYS@192.168.100.141:1521/dyhis> select trunc(EVENT_TIMESTAMP,'mm'),count(*) from audsys.AUD$UNIFIED group by trunc(EVENT_TIMESTAMP,'mm');
TRUNC(EVENT_TIMESTA   COUNT(*)
------------------- ----------
2023-04-01 00:00:00      29666
2023-03-01 00:00:00     473349
2023-02-01 00:00:00     261572

--//可以發現2023-02-01 00:00:00日期之前的記錄已經刪除.
--//驗證了前面寫的SCHEDULER JOB沒有問題.