oracle定时清理aud$

原创已于 2022-09-24 11:01:23 修改 · 2.4k 阅读
5 ·
本内容遵循CC 4.0 BY-SA版权协议
标签
#oracle
于 2022-09-24 10:47:46 首次发布
Oracle 专栏收录该内容
6 篇文章
订阅专栏
该博客详细介绍了如何在Oracle数据库中迁移审计表空间（AUD$和FGA_LOG$）到新的表空间（TBS_FAUDIT），并设置了默认表空间。接着，配置了审计清理周期、保留策略和手动清理操作。最后，创建了自动清理作业，并通过调度器定期更新审计的最后归档时间戳。
原文： https://www.yuque.com/wei01/rlggh6/vmysyy

-- 确认默认表空间
col owner for a10
col SEGMENT_NAME for a10
set line 200
col SEGMENT_TYPE for a10
col TABLESPACE_NAME for a10
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER      SEGMENT_NA SEGMENT_TY TABLESPACE BYTES/1024/1024
---------- ---------- ---------- ---------- ---------------
SYS        AUD$       TABLE      SYSAUX               3

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='FGA_LOG$';


-- 迁移aud$ 到其他表空间，并设置默认表空间
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TBS_FAUDIT');
END;
/

-- 如果是fga的表FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'TBS_FAUDIT');
END;
/


COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SET LINES 2000
SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT TABLESPACE            TBS_FAUDIT           STANDARD AUDIT TRAIL  --表空间路径
DB AUDIT TABLESPACE            TBS_FAUDIT           FGA 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


-- 设置清理周期
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;
/  

-- 确认是否完成
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/

YES

-- 设置保留策略
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-60);
END;
/ 

-- 查看保留策略（LAST_ARCHIVE_TS是固定时间）
select * from dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL            0 25-JUN-22 01.39.11.000000 PM +00:00


-- 手动执行清理
BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/


-- 确认aud$的保留时间
SQL> select min(a.ntimestamp#) from sys.aud$ a;

MIN(A.NTIMESTAMP#)
---------------------------------------------------------------------------
27-JUN-22 02.51.17.900888 AM


-- 设置作业单次清理行数
begin
 dbms_audit_mgmt.set_audit_trail_property (
 audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
 audit_trail_property => dbms_audit_mgmt.db_delete_batch_size,
 audit_trail_property_value => 300000);
 end;
/

-- 确认策略
SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


-- 创建自动清理job
BEGIN
DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'PURGE_DB_AUDIT_TRAILS',
use_last_arch_timestamp => TRUE);
END;
/

PL/SQL procedure successfully completed.

-- 由于LAST_ARCHIVE_TS是固定时间，所以需要设置scheduler job定期修改LAST_ARCHIVE_TS
-- 创建自动修改LAST_ARCHIVE_TS的scheduler job
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'set_last_archive_aud_timestamp',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-60);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/

-- 查看audit的信息
set linesize 200 pages 500
col parameter_name for a30
col parameter_value for a30
col AUDIT_TRAIL for a30
col JOB_NAME for a30
col JOB_FREQUENCY for a30
select * from dba_audit_mgmt_config_params;

PARAMETER_NAME                 PARAMETER_VALUE                AUDIT_TRAIL
------------------------------ ------------------------------ ------------------------------
DB AUDIT TABLESPACE            TBS_FAUDIT                     STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            TBS_FAUDIT                     FGA 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
DEFAULT CLEAN UP INTERVAL      12                             STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      12                             FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      12                             OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      12                             XML AUDIT TRAIL

-- 时区是0时区
select * from dba_audit_mgmt_last_arch_ts; 

AUDIT_TRAIL                    RAC_INSTANCE LAST_ARCHIVE_TS
------------------------------ ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL                      0 25-JUL-22 02.21.14.000000 PM +00:00

-- 执行的时间，时区是0时区
select * from dba_audit_mgmt_clean_events;


AUDIT_TRAIL                    RAC_INSTANCE CLEANUP_TIME                                                                DELETE_COUNT WAS_FORCE
------------------------------ ------------ --------------------------------------------------------------------------- ------------ ---------
STANDARD AUDIT TRAIL                      0 23-SEP-22 05.41.05.469553 AM +00:00                                                 6920 NO
STANDARD AUDIT TRAIL                      0 23-SEP-22 06.21.19.083130 AM +00:00                                                 2917 NO

select * from dba_audit_mgmt_cleanup_jobs;

JOB_NAME                       JOB_STATUS               AUDIT_TRAIL                    JOB_FREQUENCY
------------------------------ ------------------------ ------------------------------ ------------------------------
PURGE_DB_AUDIT_TRAILS          ENABLED                  ALL AUDIT TRAILS               FREQ=HOURLY;INTERVAL=24