資料庫的審計資訊一般較重，尤其在開啟登入審計後，所有 session 的登入和退出都會在審計表中留有記錄，由於表空間或者儲存的限制，如果不能寫入審計記錄，資料庫執行需要有審計的操作將被掛起，比如開啟了 CREATE SESSION 審計，由於空間限制普通使用者將不能登入資料庫。需要有關辦法來靈活的管理資料庫的審計記錄空間。

# 環境檢查

### 檢視保留時間

```sql
select min(NTIMESTAMP#) from aud$;
```

### 檢視歸檔時間戳

```sql
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
```

### 檢視空間使用情況

```sql
col owner for a20
col segment_name for a50
SELECT OWNER, SEGMENT_NAME, segment_type, TABLESPACE_NAME, BYTES/1024/1024 "SIZE(MB)"
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME IN ('AUD$','FGA_LOG$');
```

### 檢視是否有索引

```sql
SELECT I.INDEX_NAME,
       I.INDEX_TYPE,
       I.TABLE_OWNER,
       I.TABLE_NAME,
       I.UNIQUENESS,
       I.TABLESPACE_NAME,
       C.COLUMN_NAME,
       C.COLUMN_POSITION,
       C.COLUMN_LENGTH
  FROM DBA_INDEXES I, DBA_IND_COLUMNS C
 WHERE I.INDEX_NAME = C.INDEX_NAME
   AND I.TABLE_NAME in ('AUD$','FGA_LOG$');
```

### 檢視審計表高水位

```sql
SELECT TABLE_NAME,
       NUM_ROWS,
       AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 NEED,
       BLOCKS * 8 / 1024 TRUE,
       (BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) RECOVER_MB
  FROM DBA_TABLES
 WHERE TABLE_NAME in ('AUD$','FGA_LOG$')
   AND BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 > 100;
```

### 檢視回收作業

```sql
select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

col owner format a6 
col job_name format a20 
col comments format a20
col program_owner format a6
col program_name format a15
col job_type format  a12
col state format a9
col job_action format a100
col schedule_name format a20
col schedule_type format a14
col repeat_interval format a20
col last_start_date format a25
col last_run_duration format a25
col next_run_date format a25
SELECT OWNER,
       JOB_NAME,
       COMMENTS,
       PROGRAM_OWNER,
       PROGRAM_NAME,
       JOB_TYPE,
       STATE,
       JOB_ACTION,
       SCHEDULE_NAME,
       SCHEDULE_TYPE,
       REPEAT_INTERVAL,
       LAST_START_DATE,
       LAST_RUN_DURATION,
       NEXT_RUN_DATE
  FROM DBA_SCHEDULER_JOBS
 WHERE JOB_NAME=(SELECT JOB_NAME FROM DBA_AUDIT_MGMT_CLEANUP_JOBS)
    OR JOB_ACTION like '%SET_LAST_ARCHIVE_TIMESTAMP%';
```

# 清理

針對已經分析過的老資料做自動清理，首先要初始化清理策略：

```plsql
 begin
   dbms_audit_mgmt.init_cleanup(
       audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
       default_cleanup_interval  => 24 
   );
 end;
 /
 ```

- audit_trail_type：指要設定的清理型別，這裡是 audit_trail_db_std，標準的資料庫審計跟蹤，即 aud$ 表。其他清理型別詳情請見下表：
  - audit_trail_aud_std：The standard AUD$ audit trail in the database
  - audit_trail_fga_std：The FGA_LOG$ table, for Fine Grained Auditing
  - audit_trail_db_std：Both standard and FGA audit trails
  - audit_trail_os：The OS audit trail
  - audit_trail_xml：The XML audit trail
  - audit_trail_files：Both OS and XML audit trails
  - audit_trail_all：All of the above

- default_cleanup_interval：這個值表示每隔多少時間執行清理任務，24 即 24 小時。

### 半自動清理（手動設定清理的時間）

我們需要設定一個過程引數 SET_LAST_ARCHIVE_TIMESTAMP，它的作用是告訴清理程式一個審計歸檔時間戳。

```plsql
begin
  dbms_audit_mgmt.set_last_archive_timestamp(
      audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
      last_archive_time => to_timestamp('2021-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),   
      rac_instance_number => null  );
end;
/
```

- audit_trail_type：要清理的審計型別，如 audit_trail_aud_std
- last_archive_time：最後一次歸檔時間，可以手工設定
- rac_instance_number：RAC 的話可以指定例項號

有了歸檔時間後，就可以執行真正的清理程式了。

```plsql
begin
  dbms_audit_mgmt.clean_audit_trail(
      audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
      use_last_arch_timestamp => TRUE );
end;
/
```

- audit_trail_type 表示清理的審計型別
- use_last_arch_timestamp：TRUE，表示用最後的歸檔時間，如果為 false，將會清理掉 audit_trail_aud_std 型別的所有審計資訊。

### 自動清理

第一個任務：自動產生清理時間點

```plsql
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
      job_name  => 'AUDPURGE',
      job_type  => 'PLSQL_BLOCK',
      job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
                         AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
                         LAST_ARCHIVE_TIME => sysdate-180); END;',
      start_date => sysdate,
      repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
      enabled  => TRUE,
      comments  => 'Create an archive timestamp' );
END;
/
```

第二個任務：根據時間點自動清理

```plsql
BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,  
      AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
      AUDIT_TRAIL_PURGE_NAME => 'DAILY_AUD_PURGE',
      USE_LAST_ARCH_TIMESTAMP  => TRUE );
END;
/
```

### 清理任務最佳化

到此資料庫審計自動清理就可以實現了，但是從資料庫的效能考慮，如果您的資料庫很大，我們就得考慮資料的審計清理的效能，oracle11g  也提供了一個很好的解決方法，設定清理批處理時 commit 行數。如下：

```plsql
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 => 100000);
end;
/
```

- audit_trail_type 清理型別
- audit_trail_property 設定刪除批處理
- audit_trail_property_value 表示每次 commit 行數 100000。

### 空間回收

由於清理操作為 delete 操作，而非 truncate，所以要定時收縮一下表。

```sql
ALTER TABLE SYS.AUD$ ENABLE ROW MOVEMENT;
ALTER TABLE SYS.AUD$ SHRINK SPACE CASCADE;
ALTER TABLE SYS.AUD$ DISABLE ROW MOVEMENT;
EXEC dbms_stats.gather_table_stats('SYS','AUD$');

ALTER TABLE SYS.FGA_LOG$ ENABLE ROW MOVEMENT;
ALTER TABLE SYS.FGA_LOG$ SHRINK SPACE CASCADE;
ALTER TABLE SYS.FGA_LOG$ DISABLE ROW MOVEMENT;
EXEC dbms_stats.gather_table_stats('SYS','FGA_LOG$');
```

Oracle：審計清理

相關文章