[20230303]学习UNIFIED audit--定期清理AUDSYS.AUD$UNIFIED.txt
[20230303]学习UNIFIED audit--定期清理AUDSYS.AUD$UNIFIED.txt
--//前面的学习包括手工操作整理AUDSYS.AUD$UNIFIED以及移动AUDSYS.AUD$UNIFIED到别的表空间.
--//下面学习定期清理AUDSYS.AUD$UNIFIED
--//参考:
--//https://smarttechways.com/2020/06/19/purge-the-audit-records-with-truncate-or-dbms_audit_mgmt-package/
--//https://www.cnblogs.com/cqdba/p/13258891.html
--// Create the scheduler job which purge the AUDSYS.AUD$UNIFIED table daily
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.
2.步骤:
--//定时调度设置时间戳.执行DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP。
--//取月尾日期.
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-04-01 00:00:00 2023-01-01 00:00:00
--//仔细看了链接https://www.cnblogs.com/cqdba/p/13258891.html,感觉有点繁琐。
--//使用了DBMS_AUDIT_MGMT.CREATE_PURGE_JOB.
--//根据我前面测试，操作最好直接删除整个分区，这样更加快捷。
--//索性我设置时间戳后马上执行清理操作，修改如下：
BEGIN
DBMS_SCHEDULER.create_job
(
job_name => 'JOB_PURGE_AUDIT_RECORDS'
,job_type => 'PLSQL_BLOCK'
,job_action => q'[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;]'
,-- start_date => add_months(trunc(sysdate,'mm'),1),
start_date => SYSDATE
,-- repeat_interval => 'freq=monthly; byhour=7; byminute=50; bysecond=0;',
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
,enabled => TRUE
,comments => 'Update last_archive_timestamp and clean unified audit trail'
);
END;
/
--//根据需要设定保留的时间段,我设置保留最少2个月. 如果保留3个月就是-3.
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 00:00:00.000000 +00:00 4090373436 B60D258AC2D9EF54E0532A63A8C09F1F
SYS@192.168.100.141:1521/dyhis> select start_date,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name= 'JOB_PURGE_AUDIT_RECORDS'
@pr
==============================
START_DATE : 2023-03-03 09:07:24.000000 +08:00
LAST_START_DATE :
LAST_RUN_DURATION :
NEXT_RUN_DATE : 2023-04-01 08:50:00.000000 +08:00
PL/SQL procedure successfully completed.
--//不知道是否时区会有影响.前面的+0时区,后面的+08时区.
SYS@192.168.100.141:1521/dyhis> select max(event_timestamp) from AUDSYS.AUD$UNIFIED;
MAX(EVENT_TIMESTAMP)
---------------------------------------------------------------------------
2023-03-03 01:12:40.778111
SYS@192.168.100.141:1521/dyhis> select max(event_timestamp) from AUDSYS.AUD$UNIFIED;
MAX(EVENT_TIMESTAMP)
---------------------------------------------------------------------------
2023-03-03 01:12:46.815100
SYS@192.168.100.141:1521/dyhis> select SYSDATE from dual ;
SYSDATE
-------------------
2023-03-03 09:13:10
--//很明显EVENT_TIMESTAMP改用+0时区.
SYS@192.168.100.141:1521/dyhis> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;
no rows selected
--//没有采用DBMS_AUDIT_MGMT.CREATE_PURGE_JOB方式.
--//相关视图：内容来自https://www.cnblogs.com/cqdba/p/13258891.html
--//DBA_AUDIT_MGMT_CLEAN_EVENTS 显示传统(即非统一)审计跟踪的清除事件的历史记录
--//统计审计的清除记录 select * from UNIFIED_AUDIT_TRAIL
--//where OBJECT_NAME='DBMS_AUDIT_MGMT' and OBJECT_SCHEMA='SYS' AND SQL_TEXT LIKE '%DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL%';
--//DBA_AUDIT_MGMT_CLEANUP_JOBS 显示当前配置的审核跟踪清除作业
--//DBA_AUDIT_MGMT_CONFIG_PARAMS 显示DBMS_AUDIT_MGMTPL / SQL程序包 使用的当前配置的审核跟踪属性
--//DBA_AUDIT_MGMT_LAST_ARCH_TS 显示为审计跟踪清除设置的最后一个归档时间戳记
--//前面的学习包括手工操作整理AUDSYS.AUD$UNIFIED以及移动AUDSYS.AUD$UNIFIED到别的表空间.
--//下面学习定期清理AUDSYS.AUD$UNIFIED
--//参考:
--//https://smarttechways.com/2020/06/19/purge-the-audit-records-with-truncate-or-dbms_audit_mgmt-package/
--//https://www.cnblogs.com/cqdba/p/13258891.html
--// Create the scheduler job which purge the AUDSYS.AUD$UNIFIED table daily
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.
2.步骤:
--//定时调度设置时间戳.执行DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP。
--//取月尾日期.
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-04-01 00:00:00 2023-01-01 00:00:00
--//仔细看了链接https://www.cnblogs.com/cqdba/p/13258891.html,感觉有点繁琐。
--//使用了DBMS_AUDIT_MGMT.CREATE_PURGE_JOB.
--//根据我前面测试，操作最好直接删除整个分区，这样更加快捷。
--//索性我设置时间戳后马上执行清理操作，修改如下：
BEGIN
DBMS_SCHEDULER.create_job
(
job_name => 'JOB_PURGE_AUDIT_RECORDS'
,job_type => 'PLSQL_BLOCK'
,job_action => q'[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;]'
,-- start_date => add_months(trunc(sysdate,'mm'),1),
start_date => SYSDATE
,-- repeat_interval => 'freq=monthly; byhour=7; byminute=50; bysecond=0;',
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
,enabled => TRUE
,comments => 'Update last_archive_timestamp and clean unified audit trail'
);
END;
/
--//根据需要设定保留的时间段,我设置保留最少2个月. 如果保留3个月就是-3.
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 00:00:00.000000 +00:00 4090373436 B60D258AC2D9EF54E0532A63A8C09F1F
SYS@192.168.100.141:1521/dyhis> select start_date,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name= 'JOB_PURGE_AUDIT_RECORDS'
@pr
==============================
START_DATE : 2023-03-03 09:07:24.000000 +08:00
LAST_START_DATE :
LAST_RUN_DURATION :
NEXT_RUN_DATE : 2023-04-01 08:50:00.000000 +08:00
PL/SQL procedure successfully completed.
--//不知道是否时区会有影响.前面的+0时区,后面的+08时区.
SYS@192.168.100.141:1521/dyhis> select max(event_timestamp) from AUDSYS.AUD$UNIFIED;
MAX(EVENT_TIMESTAMP)
---------------------------------------------------------------------------
2023-03-03 01:12:40.778111
SYS@192.168.100.141:1521/dyhis> select max(event_timestamp) from AUDSYS.AUD$UNIFIED;
MAX(EVENT_TIMESTAMP)
---------------------------------------------------------------------------
2023-03-03 01:12:46.815100
SYS@192.168.100.141:1521/dyhis> select SYSDATE from dual ;
SYSDATE
-------------------
2023-03-03 09:13:10
--//很明显EVENT_TIMESTAMP改用+0时区.
SYS@192.168.100.141:1521/dyhis> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;
no rows selected
--//没有采用DBMS_AUDIT_MGMT.CREATE_PURGE_JOB方式.
--//相关视图：内容来自https://www.cnblogs.com/cqdba/p/13258891.html
--//DBA_AUDIT_MGMT_CLEAN_EVENTS 显示传统(即非统一)审计跟踪的清除事件的历史记录
--//统计审计的清除记录 select * from UNIFIED_AUDIT_TRAIL
--//where OBJECT_NAME='DBMS_AUDIT_MGMT' and OBJECT_SCHEMA='SYS' AND SQL_TEXT LIKE '%DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL%';
--//DBA_AUDIT_MGMT_CLEANUP_JOBS 显示当前配置的审核跟踪清除作业
--//DBA_AUDIT_MGMT_CONFIG_PARAMS 显示DBMS_AUDIT_MGMTPL / SQL程序包 使用的当前配置的审核跟踪属性
--//DBA_AUDIT_MGMT_LAST_ARCH_TS 显示为审计跟踪清除设置的最后一个归档时间戳记
