11G设置审计日志保留策略
环境:
Os:Centos 7
DB:11.2.0.4
1.初始化清理Audit的功能,该命令只有在第一次执行时需要运行,default_cleanup_interval =>72 代表清理周期为72小时(3天).
BEGIN
sys.DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 72);
END;
/
2.查看清除Audit功能是否开启,yes为开启
SET SERVEROUTPUT ON
BEGIN
IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
3.设置需要清理的天数,最后一个数字3代表清理3天前的数据(归档时间大于等于清除时间)
BEGIN
sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-3);
END;
/
4.执行清除,时间长短受数据量大小影响
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
执行改命令看到后台是使用delete的方式删除的
DELETE FROM SYS.AUD$ WHERE DBID = 1510119012 AND NTIMESTAMP# < to_timestamp('2023-10-06 14:08:13', 'YYYY-MM-DD HH24:MI:SS.FF') AND ROWNUM <= 10000;
5.删除一天前的审计日志
BEGIN
sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-1);
END;
/
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
说明:
相关参数配置
SQL> set linesize 1000;
SQL> column PARAMETER_NAME format a32;
SQL> column PARAMETER_VALUE format a16;
SQL> column AUDIT_TRAIL format a32;
SQL> select PARAMETER_NAME,PARAMETER_VALUE,AUDIT_TRAIL from dba_audit_mgmt_config_params;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------- ---------------- --------------------------------
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 72 STANDARD AUDIT TRAIL
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------- ---------------- --------------------------------
DEFAULT CLEAN UP INTERVAL 72 FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 72 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 72 XML AUDIT TRAIL
14 rows selected.
aud$所在的表空间
SQL> select owner,table_name,tablespace_name from dba_tables
2 where table_name = 'AUD$';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYSTEM AUD$ SYSAUX
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
2021-10-09 logminer(基于redo log来存储数据字典)-归档日志+安装部署
2020-10-09 python3安装沙盒环境
2019-10-09 ogg从库进程监控