[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删除.