Oracle-监控AUD$记录

1. 按照类型统计aud$记录数量

set echo on
col username for a20
break on username
select USERNAME, action, action_name, count(*) from dba_audit_trail
  group by  USERNAME, action, action_name order by USERNAME, action;
select count(*) TOTAL from sys.aud$;
set echo off
set serveroutput on
BEGIN
    IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
     (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
       THEN
         dbms_output.put_line('Audit trail STD not initialized');
   end  if;
end;
/

2. 统计AUD$段大小

SELECT owner
      ,table_name
      ,SUM(decode(seg_type, 'table', size_mb)) tab_size_mb
      ,SUM(decode(seg_type, 'index', size_mb)) idx_size_mb
      ,SUM(decode(seg_type, 'lob', size_mb)) lob_size_mb
      ,SUM(size_mb)
  FROM (SELECT /*+ rule */  t.owner
              ,t.table_name
              ,SUM(s.bytes) / 1024 / 1024 size_mb
              ,'table' seg_type
          FROM dba_segments s, dba_tables t
         WHERE s.owner = t.owner
           AND s.segment_name = t.table_name
         GROUP BY t.owner, t.table_name
        UNION ALL
        SELECT /*+ rule */ l.owner
              ,l.table_name
              ,SUM(s.bytes) / 1024 / 1024 size_mb
              ,'lob' seg_type
          FROM dba_segments s, dba_lobs l
         WHERE s.owner = l.owner
           AND s.segment_name = l.segment_name
         GROUP BY l.owner, l.table_name
        UNION ALL
        SELECT /*+ rule */ i.table_owner owner
              ,i.table_name
              ,SUM(s.bytes) / 1024 / 1024 size_mb
              ,'index' seg_type
          FROM dba_segments s, dba_indexes i
         WHERE s.owner = i.owner
           AND s.segment_name = i.index_name
         GROUP BY i.table_owner, i.table_name)
 WHERE owner = 'SYS'
   AND table_name = 'AUD$'
   group by owner, table_name;

3. 根据上面2得到值，分段逐次清理审计数据

TRUNCATE TABLE sys.aud$ reuse storage;
ALTER TABLE sys.aud$ deallocate unused keep 10240M;
ALTER TABLE sys.aud$ deallocate unused keep 8192M;
ALTER TABLE sys.aud$ deallocate unused keep 6144M;
ALTER TABLE sys.aud$ deallocate unused keep 4096M;
ALTER TABLE sys.aud$ deallocate unused keep 2048M;
ALTER TABLE sys.aud$ deallocate unused keep 0M;

4. 通过触发器捕捉sysdba登录客户端信息写入alert_<SID>.log

CREATE OR REPLACE TRIGGER sysdba_to_alert
  AFTER logon ON DATABASE
DECLARE
  message     VARCHAR2(256);
  ip          VARCHAR2(15);
  v_os_user   VARCHAR2(80);
  v_module    VARCHAR2(50);
  v_action    VARCHAR2(50);
  v_pid       VARCHAR2(10);
  v_sid       NUMBER;
  v_program   VARCHAR2(48);
  v_client_id VARCHAR2(64);
BEGIN
  IF USER = 'SYS'
  THEN
    -- get IP for remote connections:
    IF sys_context('userenv', 'network_protocol') = 'TCP'
    THEN
      ip := sys_context('userenv', 'ip_address');
    END IF;
    SELECT DISTINCT sid
      INTO v_sid
      FROM sys.v_$mystat;
    SELECT p.spid, v.program
      INTO v_pid, v_program
      FROM v$process p, v$session v
     WHERE p.addr = v.paddr
       AND v.sid = v_sid;
    v_os_user := sys_context('userenv', 'os_user');
    dbms_application_info.read_module(v_module, v_action);
    v_client_id := sys_context('userenv', 'client_identifier');
    message := to_char(SYSDATE, 'Dy Mon dd HH24:MI:SS YYYY') ||
               ' SYSDBA logon from ' || nvl(ip, 'localhost') || ' ' || v_pid || ' ' ||
               v_os_user || ' ' || v_client_id || ' with ' || v_program || ' ' ||
               v_module || ' ' || v_action;
    sys.dbms_system.ksdwrt(2, message);
  END IF;
END;
/
-- end trigger

5. 迁移AUD$表至单独表空间

-- 创建表空间
create tablespace AUD_TBS datafile size 16384M autoextend off;
 
-- 迁移AUD$表
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUD_FILE');
END;
/
 
-- 迁移FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUD_FILE');
END;
/
 
-- 检查确认
set lines 168 pages 999
col segment_name for a30
col table_name for a18
col tablespace_name for a18
SELECT table_name, tablespace_name
  FROM dba_tables
 WHERE table_name IN ('AUD$', 'FGA_LOG$')
 ORDER BY table_name;
 
col SEGMENT_NAME for a32
select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME
  from dba_lobs
 where table_name in ('AUD$', 'FGA_LOG$');