[20230301]學習UNIFIED audit-移動AUDSYS.AUD$UNIFIED到別的表空間.txt

[20230301]學習UNIFIED audit-移動AUDSYS.AUD$UNIFIED到別的表空間.txt

--//12c開始已經採用Unified Audit,不再使用sys.aud$記錄審計資訊.而採用AUDSYS.AUD$UNIFIED表記錄相關資訊.
--//預設表空間sysaux表空間,而且採用每個月1個分割槽模式,這樣做刪除歷史記錄操作更加快捷.
--//dbms_audit_mgmt包也單獨採用建立在AUDSYS模式下.

1.環境:
SYS@192.168.100.141:1521/dyhis> @ ver1
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 AUDIT_UNIFIED_ENABLED_POLICIES;
POLICY_NAME                    ENABLED_OPTION  ENTITY_NAME                    ENTITY_ SUC FAI
------------------------------ --------------- ------------------------------ ------- --- ---
ORA_SECURECONFIG               BY USER         ALL USERS                      USER    YES YES
ORA_LOGON_FAILURES             BY USER         ALL USERS                      USER    NO  YES

--//這樣記錄的資訊相對少一些.雖然預設放在表空間sysaux表空間,如果審計內容很多的情況下,最好還是建立單獨的表空間維護管理更
--//加方便.

2.先整理AUDSYS.AUD$UNIFIED:
--//移動前做一些清理,實際上這步多餘,因為改動僅僅是以後建立的分割槽在別的表空間.看後面測試.
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.
--//Unified審計的清除oracle設定特別繁瑣,首先要執行DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP,設定一個時間點.

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 2022-12-31 11:15:01.000000 +00:00  4090373436 B60D258AC2D9EF54E0532A63A8C09F1F

SYS@192.168.100.141:1521/dyhis> select sysdate-60 from dual;
SYSDATE-60
-------------------
2022-12-31 11:15:25

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,不過這樣應該是全部清除!!

3.移動到別的表空間:
--//建立新的表空間,我偷懶直接建立在users上.
SYS@192.168.100.141:1521/dyhis> ALTER USER AUDSYS QUOTA UNLIMITED ON users;
User altered.

--//AUDIT_TRAIL_UNIFIED           CONSTANT NUMBER := 51;
--//可以檢視包DBMS_AUDIT_MGMT定義確定.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value => 'users');
END;
/

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-03-01 11:17:08
AUDSYS AUD$UNIFIED TABLE PARTITION      VALID         176513     176513 2023-03-01 08:00:05 2023-03-01 08:00:05
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AUDSYS AUD$UNIFIED TABLE PARTITION      VALID         171804     171804 2023-01-01 08:00:04 2023-01-01 08:00:04

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
------ ------ ------------ ------------- --------------- ------------------- ---------- ---------- ----------
     1 AUDSYS AUD$UNIFIED  SYS_P20923    TABLE PARTITION SYSAUX                     104          3     145690
   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
--//舊的記錄並沒有移動,應該是建立的新分割槽會建立在users表空間,給等下一個月觀察.

SYS@192.168.100.141:1521/dyhis> select * from DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- ----------------------------
DB AUDIT TABLESPACE            SYSAUX               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            USERS                UNIFIED AUDIT TRAIL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
AUDIT WRITE MODE               QUEUED WRITE MODE    UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE             5                    UNIFIED AUDIT TRAIL
14 rows selected.


3.我做了一個跟蹤:
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-59);
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-01-01 11:21:27.000000 +00:00  4090373436 B60D258AC2D9EF54E0532A63A8C09F1F
--//LAST_ARCHIVE_TS='2023-01-01 11:21:27.000000 +00:00',注意時區是0,這樣能刪除1個分割槽.

SYS@192.168.100.141:1521/dyhis> @ 10046on 12
Session altered.

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.

SYS@192.168.100.141:1521/dyhis> @ 10046off
Session altered.

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-03-01 11:21:46
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         176513     176513 2023-03-01 08:00:05 2023-03-01 08:00:05
AUDSYS AUD$UNIFIED TABLE PARTITION      VALID         171804     171804 2023-01-01 08:00:04 2023-01-01 08:00:04
4 rows selected.

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
------ ------ ------------ ------------- --------------- ------------------- ---------- ---------- ----------
     1 AUDSYS AUD$UNIFIED  SYS_P20923    TABLE PARTITION SYSAUX                     120          3     145690
   144 AUDSYS AUD$UNIFIED  SYS_P19622    TABLE PARTITION SYSAUX                   18432          3     656234
    88 AUDSYS AUD$UNIFIED  SYS_P20283    TABLE PARTITION SYSAUX                   11264          3     550906
3 rows selected.


--//後面有刪除分割槽執行如下
$ egrep -i 'drop |delete' aa.trc | grep -i 'AUDSYS.AUD\$UNIFIED'
CALL DBMS_PDB_EXEC_SQL('ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P18918')
ALTER TABLE AUDSYS.AUD$UNIFIED DROP PARTITION SYS_P18918
delete from audsys.aud$unified where event_timestamp < :1 and  (dbid = :2 or dbid = 0)

--//前面沒有刪除分割槽執行如下
$ egrep -i 'drop |delete' ab.trc | grep -i 'AUDSYS.AUD\$UNIFIED'
delete from audsys.aud$unified where event_timestamp < :1 and  (dbid = :2 or dbid = 0)

--//可以大致猜測執行步驟,根據設定的時間點,如果可以刪除分割槽直接drop分割槽.然後剩下的選擇直接從表audsys.aud$unified刪除.