[20170518]11G審計日誌清除3.txt

[20170518]11G審計日誌清除3.txt

--//以前寫的連結:http://blog.itpub.net/267265/viewspace-2133145/
--//我當時寫存在許多問題,僅僅清除sys.aud$內容,引數audit_file_dest目錄的檔案不清除,而這裡引數大量的aud檔案,重新測試看看.

1.環境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.清理工作:

======================================================
-- Constants  注意重疊問題.
-- Audit Trail types
--
AUDIT_TRAIL_AUD_STD           CONSTANT NUMBER := 1;
AUDIT_TRAIL_FGA_STD           CONSTANT NUMBER := 2;
--
-- Both AUDIT_TRAIL_AUD_STD and AUDIT_TRAIL_FGA_STD
AUDIT_TRAIL_DB_STD            CONSTANT NUMBER := 3;
--
AUDIT_TRAIL_OS                CONSTANT NUMBER := 4;
AUDIT_TRAIL_XML               CONSTANT NUMBER := 8;
--
-- Both AUDIT_TRAIL_OS and AUDIT_TRAIL_XML
AUDIT_TRAIL_FILES             CONSTANT NUMBER := 12;
--
-- All above audit trail types
AUDIT_TRAIL_ALL               CONSTANT NUMBER := 15;
======================================================

--//注:我當時選擇AUDIT_TRAIL_AUD_STD,現在選擇全部(AUDIT_TRAIL_ALL).直接使用數字15應該也可以.
--//首先清除前面的設定

exec DBMS_AUDIT_MGMT.DEINIT_CLEANUP(AUDIT_TRAIL_TYPE=>SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD);
exec dbms_scheduler.drop_job( job_name =>  'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP' , force => TRUE);
exec SYS.DBMS_AUDIT_MGMT.DROP_PURGE_JOB( AUDIT_TRAIL_PURGE_NAME =>   'Daily_Audit_Purge_Job');

--//這些參考連結http://blog.itpub.net/267265/viewspace-2133145/,感嘆一下看文件不認真.
--//這些步驟都是前面沒有配置好,大家可以不看這些內容.我僅僅做一個記錄.

3.初始化工作:

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24 /* hours */);
END;
/

SET SERVEROUTPUT ON
BEGIN
  IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/
YES
PL/SQL procedure successfully completed.

--//返回YES,說明已經做清理的初始化工作.
--//這樣設計對全部audit才有效.

SYS@xxxxx> column PARAMETER_VALUE format a30
SYS@xxxxx> column PARAMETER_name format a30
SYS@xxxxx> SELECT *  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME                 PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- ----------------------------
DB AUDIT TABLESPACE            TSP_AUDIT       STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            TSP_AUDIT       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      24              FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24              OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24              STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24              XML AUDIT TRAIL
14 rows selected.

--//看最後4行DEFAULT CLEAN UP INTERVAL,DEFAULT CLEAN UP INTERVAL,DEFAULT CLEAN UP INTERVAL,DEFAULT CLEAN UP INTERVAL
--//已經定義了清理間隔時間.

4.建立schedule,清理日誌:
--//oracle處理這些問題的機制有點繁瑣,首先是標記那些需要clean,執行SET_LAST_ARCHIVE_TIMESTAMP然後交由清除程式處理.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP',
    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-30);
                       DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,LAST_ARCHIVE_TIME => SYSDATE-30);
                   END;',
    start_date => sysdate,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
    enabled    =>  TRUE,
    comments   => 'Create an archive timestamp'
  );
END;
/

--//注:這裡AUDIT_TRAIL_TYPE 不能使用 DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,必須1個1個寫.
SYS@xxxxx> BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,LAST_ARCHIVE_TIME => SYSDATE-1); END;
  2  /
BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,LAST_ARCHIVE_TIME => SYSDATE-1); END;
*
ERROR at line 1:
ORA-46250: Invalid value for argument 'AUDIT_TRAIL_TYPE'
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2233
ORA-06512: at line 1

--//每個小時檢查1次,標記30天前的日誌.一些引數可以根據需要自己調整.我自己的測試保留30天.建議生產系統保留100天或者半年.
--//建立清理程式:

BEGIN
  SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
    AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;
/

4.相關檢視查詢:
SYS@xxxxx> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;
JOB_NAME                       JOB_STAT AUDIT_TRAIL                  JOB_FREQUENCY
------------------------------ -------- ---------------------------- -----------------------
DAILY_AUDIT_PURGE_JOB          ENABLED  ALL AUDIT TRAILS             FREQ=HOURLY;INTERVAL=24

SYS@xxxxx> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL            0 2017-04-18 15:25:37.000000 +00:00
OS AUDIT TRAIL                  1 2017-04-18 15:25:37.000000 +08:00

SYS@xxxxx> select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;
AUDIT_TRAIL          RAC_INSTANCE CLEANUP_TIME                      DELETE_COUNT WAS
-------------------- ------------ --------------------------------- ------------ ---
STANDARD AUDIT TRAIL            0 2017-05-17 00:52:47.741345 +00:00           87 NO
STANDARD AUDIT TRAIL            0 2017-05-18 00:52:47.701398 +00:00            1 NO
OS AUDIT TRAIL                  1 2017-05-18 07:25:13.354238 +00:00          285 NO

SYS@xxxxx> @ &r/pt2 'SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE ''%AUDIT%'''
   ROW_NUM    COL_NUM COL_NAME         COL_VALUE
---------- ---------- ---------------- ----------------------------------------------------------------------------------------------------
         1          1 OWNER            SYS
                    2 JOB_NAME         DAILY_AUDIT_PURGE_JOB
                    3 JOB_STYLE        REGULAR
                    4 JOB_CREATOR      SYS
                    5 JOB_TYPE         PLSQL_BLOCK
                    6 JOB_ACTION       BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);  END;
                    7 START_DATE       2017-05-18 12:03:39.838620 +08:00
                    8 REPEAT_INTERVAL  FREQ=HOURLY;INTERVAL=24
                    9 ENABLED          TRUE
                   10 STATE            SCHEDULED
                   11 SYSTEM           TRUE
                   12 NLS_ENV          NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
                   13 COMMENTS         Audit clean job = 'Daily_Audit_Purge_Job'
         2          1 OWNER            SYS
                    2 JOB_NAME         DAILY_AUDIT_ARCHIVE_TIMESTAMP
                    3 JOB_STYLE        REGULAR
                    4 JOB_CREATOR      SYS
                    5 JOB_TYPE         PLSQL_BLOCK
                    6 JOB_ACTION       BEGIN
                                                              DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUD

                    7 START_DATE       2017-05-18 15:21:40.000000 +08:00
                    8 REPEAT_INTERVAL  FREQ=HOURLY;INTERVAL=24
                    9 ENABLED          TRUE
                   10 STATE            SCHEDULED
                   11 SYSTEM           TRUE
                   12 NLS_ENV          NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUME
                   13 COMMENTS         Create an archive timestamp
26 rows selected.