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$');