ELK收集Oracle审计日志

最新推荐文章于 2026-06-04 14:48:56 发布

原创

最新推荐文章于 2026-06-04 14:48:56 发布 · 1.9k 阅读

标签

#filebeat #logstash配置 #oracle审计 #elk收集oracle日志

本文档详细介绍了如何在Oracle数据库中开启审计，迁移审计表空间，设计审计规则以收集DDL语句，并利用ELK（Elasticsearch、Logstash、Kibana）收集审计日志。通过创建视图、Python脚本定期读取审计日志，并配置Filebeat和Logstash进行日志传输和处理，最终在Kibana中查看审计状态。

一、开启审计，记录sql

--Oracle审计
https://max.book118.com/html/2016/1214/72170219.shtm

--开启审计
alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=db,extended scope=spfile;
--重启实例
shutdown immediate
startup
--查看审计状态
SYS@orcl>show parameter audit;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest 		     string	 /u01/app/oracle/admin/orcl/adump
audit_sys_operations		 boolean	 TRUE
audit_syslog_level		     string
audit_trail			         string	 DB, EXTENDED

二、迁移审计表空间

--迁移审计表到ogg的表空间
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'OGG');
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'OGG');
END;
/

--验证表空间
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$');
select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME from dba_lobs where table_name in ('AUD$', 'FGA_LOG$');

--审计段大小统计
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.tabl

最低0.47元/天解锁文章