Oracle审计记录手动清理的方法

Oracle 数据库审计功能非常强大，通常包括标准审计(包括用户级审计和系统级审计)和细粒度审计。尽管如此，一不小心就容易造成性能问题。同时会把系统表空间给撑爆。

下面的内容描述的是如何将审计从系统表空间剥离以及清理Oracle审计记录，供大家参考。

一、审计的相关配置

--环境 
SQL> select * from v$version where rownum=1; 
 
BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 
 
SQL> show parameter audit 
 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
audit_file_dest                      string      /home/oraprod/app/product/11.2 
                                                 .0/dbhome_1/rdbms/audit 
audit_sys_operations                 boolean     FALSE 
audit_syslog_level                   string 
audit_trail                          string      DB  --此值为当前Oracle 11gR2缺省配置 
 
--从下面的查询中可以看出，当前的审计位于system表空间 
SQL> col segment_name FOR a10 
SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$'; 
 
OWNER                          SEGMENT_NA TABLESPACE_NAME 
------------------------------ ---------- ------------------------------ 
SYS                            AUD$       SYSTEM 

二、修改审计存储表空间 

新增一个表空间用于存储审计日志 
SQL> CREATE tablespace audit_data datafile '/home/oracle/app/oradata/orcl/audit01.dbf' 
  2  SIZE 100M autoextend ON NEXT 50M; 
 
SQL> @tbs_free.sql 
 
TABLESPACE_NAME                USED (MB FREE (MB TOTAL (M PER_FR 
------------------------------ -------- -------- -------- ------ 
AUDIT_DATA                            1    1,199    1,200  100 % 
SYSAUX                            1,133       77    1,210    6 % 
SYSTEM                            1,875       15    1,890    1 % 
 
-- 设定审计数据存放表空间   
SQL> BEGIN 
  2    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( 
  3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
  4      AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA' 
  5    ); 
  6  END; 
  7  / 
BEGIN 
* 
ERROR at line 1: 
ORA-46267: Insufficient space in 'AUDIT_DATA' tablespace, cannot complete 
operation 
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1576 
ORA-06512: at line 2 
-- 错误提示，尽管我们使用了自动扩展表空间，依旧提示空间不够 
 
-- 查看当前审计数据大小，如下为1152MB 
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; 
 
SEGMENT_NAME              BYTES/1024/1024 
------------------------- --------------- 
AUD$                                 1152 
 
-- 下面调整数据文件大小 
SQL> alter database datafile '/home/oracle/app/oradata/orcl/audit01.dbf' resize 1200m; 
 
Database altered. 
 
-- 再次设定审计数据存放表空间OK  
SQL> BEGIN 
  2    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( 
  3      AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
  4      AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA' 
  5    ); 
  6  END; 
  7  / 
 
PL/SQL procedure successfully completed. 
 
Elapsed: 00:02:23.10 
--整个过程花费了2m23s，主要是期间进行了数据搬迁 
 
SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$'; 
 
OWNER                          SEGMENT_NAME                   TABLESPACE_NAME 
------------------------------ ------------------------------ ------------------------------ 
SYS                            AUD$                           AUDIT_DATA 
 
SQL> @tbs_free.sql 
 
TABLESPACE_NAME                USED (MB FREE (MB TOTAL (M PER_FR 
------------------------------ -------- -------- -------- ------ 
AUDIT_DATA                        1,153       47    1,200    4 % 
SYSAUX                            1,143       67    1,210    6 % 
SYSTEM                              724    1,166    1,890   62 % 
 
-- 从上面的这个查询可以看出，原来位于system表空间的AUD$被迁移到了AUDIT_DATA 
-- 相应地AUDIT_DATA表空间已使用增加，而SYSTEM表空间使用率下降 
 
-- 查看审计数据字典配置信息 
SQL> col PARAMETER_NAME FOR a30 
SQL> col PARAMETER_VALUE FOR a15 
SQL> col AUDIT_TRAIL FOR a20 
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL 
  2  FROM DBA_AUDIT_MGMT_CONFIG_PARAMS 
  3  WHERE audit_trail = 'STANDARD AUDIT TRAIL'; 
 
PARAMETER_NAME                 PARAMETER_VALUE AUDIT_TRAIL 
------------------------------ --------------- -------------------- 
DB AUDIT TABLESPACE            AUDIT_DATA      STANDARD AUDIT TRAIL 
DB AUDIT CLEAN BATCH SIZE      10000           STANDARD AUDIT TRAIL 

三、清除审计记录

通过这个过程设定清除间隔 
SQL> BEGIN 
  2    DBMS_AUDIT_MGMT.init_cleanup( 
  3      audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 
  4      default_cleanup_interval => 120 /* hours */); 
  5  END; 
  6  / 
 
PL/SQL procedure successfully completed. 
 
-- 下面严验证审计日志清除是否已开启 
SQL> SET SERVEROUTPUT ON 
SQL> BEGIN 
  2    IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN 
  3      DBMS_OUTPUT.put_line('YES'); 
  4    ELSE 
  5      DBMS_OUTPUT.put_line('NO'); 
  6    END IF; 
  7  END; 
  8  / 
YES 
 
PL/SQL procedure successfully completed. 
 
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; 
 
SEGMENT_NAME        BYTES/1024/1024 
------------------- --------------- 
AUD$                           1152 
 
SQL> select 'Leshami' As author,'http://blog.csdn.net/leshami' as Blog from dual; 
 
AUTHOR  BLOG 
------- ---------------------------- 
Leshami http://blog.csdn.net/leshami 
 
SQL> select count(*) from AUD$; 
 
  COUNT(*) 
---------- 
   5908086 
 
SQL> select min(ntimestamp#) from aud$; 
 
MIN(NTIMESTAMP#) 
--------------------------------------------------------------------------- 
20-AUG-14 06.11.09.901253 AM 
 
-- 设定归档间隔 
SQL> BEGIN 
  2  DBMS_AUDIT_MGMT.set_last_archive_timestamp( 
  3  audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
  4  last_archive_time => SYSTIMESTAMP-10); 
  5  END; 
  6  / 
 
PL/SQL procedure successfully completed 
 
--查看设定的归档间隔 
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts; 
 
AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS 
-------------------- ------------ --------------------------------------------------------------------------- 
STANDARD AUDIT TRAIL            0 09-OCT-15 01.27.17.000000 PM +00:00 
 
--通过调用DBMS_AUDIT_MGMT.clean_audit_trail进行手动清理审计日志 
BEGIN 
  DBMS_AUDIT_MGMT.clean_audit_trail( 
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
   use_last_arch_timestamp => TRUE); 
END; 
/ 
 
DBMS_AUDIT_MGMT.clean_audit_trail 
This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the  
SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records. 
 
--也可以通过创建一个purge Job来进行清理已归档的历史审计记录 
SQL> BEGIN 
  2    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( 
  3      AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
  4      AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */, 
  5      AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job', 
  6      USE_LAST_ARCH_TIMESTAMP    => TRUE 
  7    ); 
  8  END; 
  9  / 
 
PL/SQL procedure successfully completed. 
 
-- 本次测试使用了job进行清理，注，上面的purge job 并非使用DBMS_SCHEDULER.CREATE_JOB创建 
-- 执行job用于清理归档，通过观察，由于redo log size为50MB，切换较为频繁，花费了19分钟 
-- 同时伴随有Checkpoint not complete等待事件，可见redo size过小 
SQL> exec DBMS_SCHEDULER.RUN_JOB(job_name => 'SYS.DAILY_AUDIT_PURGE_JOB'); 
 
PL/SQL procedure successfully completed. 
 
Elapsed: 00:19:26.38 
 
SQL> select count(*) from AUD$; 
 
  COUNT(*) 
---------- 
        12 
 
--经查看，清理后空间并没有释放         
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; 
 
SEGMENT_NAME                   BYTES/1024/1024 
------------------------------ --------------- 
AUD$                                      1152 
 
SQL> alter table sys.aud$ shrink space cascade; 
alter table sys.aud$ shrink space cascade 
* 
ERROR at line 1: 
ORA-10636: ROW MOVEMENT is not enabled 
 
SQL> alter table sys.aud$ enable row movement; 
 
Table altered. 
 
SQL> alter table sys.aud$ shrink space cascade; 
 
Table altered. 
 
SQL> alter table sys.aud$ disable row movement; 
 
Table altered. 
 
-- 下面的查询可以看到，空间已经被释放 
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; 
 
SEGMENT_NAME         BYTES/1024/1024 
-------------------- --------------- 
AUD$                           .0625 

四、小结

a、对于Oracle 11g，审计功能默认被开启，因此如果在必须启用的情况下应考虑性能影响； 
b、开启审计的情况下，建议将审计从system或sysaux表空间剥离，使用单独的表空间； 
c、对于历史审计日志的清除，应考虑清除期间所带来的性能影响； 
d、调用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION这个过程已经开始了搬迁过程，如果审计日志很庞大，应考虑IO影响； 
e、审计日志的清除需要先设定归档，已归档的审计日志会被清理; 
f、也可以通过trunate table aud$ reuse storage以及deallocate非常规方式来处理。