手动清理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非常规方式来处理。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

276100
来自专栏乐沙弥的世界

启用 Oracle 10046 调试事件

    Oracle 10046是一个Oracle内部事件。最常用的是在Session级别设置sql_trace(alter session set sql_t...

6220
来自专栏.NET技术

经典SQL语句大全之数据开发

1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Str...

19250
来自专栏杨建荣的学习笔记

MySQL在RR隔离级别下的unique失效和死锁模拟

今天在测试MySQL事务隔离级别的时候,发现了一个有趣的问题,也参考了杨一之前总结的一篇。http://blog.itpub.net/22664653/view...

37860
来自专栏杨建荣的学习笔记

基于时间点的不完全恢复的例子(r6笔记第9天)

说到不完全恢复,一般有三种场景,基于时间点的不完全恢复,基于scn的不完全恢复,基于cancel的不完全恢复。 三种情况都是不完全恢复采用的方式,而不完全恢复都...

27150
来自专栏杨建荣的学习笔记

MySQL中如何得到权限信息

最近在做一次MySQL数据迁移的时候,突然发现自己遗漏了一个地方,那就是权限信息没有导出,如果我们使用mysqldump --all-databases...

31950
来自专栏敏捷开发&项目管理

Database first with EntityFramework (Migration)安装和升级

最近看了国外几个项目,发现用EntityFramework做Code First的项目现在很流行。 最让我有兴趣的一个功能则是,EntityFramework对...

36380
来自专栏乐沙弥的世界

MySQL 慢查询日志(Slow Query Log)

    同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日...

19520
来自专栏cloudskyme

oracle10g分区的几种类型

为了简化数据库大表的管理,例如在数据仓库中一般都是TB级的数量级.ORACLE8以后推出了分区选项.分区将表分离在若于不同的表空间上,用分而治之的方法来支撑元限...

363110
来自专栏杨建荣的学习笔记

浅谈exp/imp(下) (r5笔记第84天)

相关链接:浅谈exp/imp(上) (r5笔记第81天) 你可能 不了解的dump文件 在工作中,dump文件对于dba而言是再平常不过的文件了。不过因为dum...

33390

扫码关注云+社区

领取腾讯云代金券