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