手动清理Oracle审计记录x
来源：软件设计师 发布时间：2020-09-08 点击：
手动清理 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 非常规方式来处理。
推荐访问:审计 清理 记录