Describe 首頁/2023-03-24 here.

Contents

  1. 问题现象
  2. 解决方案
    1. Step 1 确认audit目录
    2. Step 2: 进入audit_file_dest目录
    3. Step 3

问题现象

在某些Oracle场景下, 因开启audit审计而产生的大量文件, 从而占用了大量磁盘空间或inode, 导致数据库服务器相关目录空间耗尽而产生数据无法访问等问题.

解决方案

经过与客户沟通和确认, 审计日志是可以删除的. 其保留周期暂定为120天之内.

以下为在Linux操作系统中所执行删除步骤

* 注意: 请确保每一步输出是预期的结果之后再执行下一步.

Step 1 确认audit目录

使用oracle用户登录, 登录数据中查询audit log所在的目录

su - oracle

sqlplus / as sysdba

SQL> show parameter audit;

示例结果: 

SQL> show parameter audit;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/oracle/product/11.2.0.3/a
                                                 dmin/huafafin/adump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      OS

Step 2: 进入audit_file_dest目录

cd /u01/oracle/product/11.2.0.3/admin/huafafin/

用find结合head和tail命令, 确认120天之前所产生的文件日期是否与预期相符.

$ find adump -name "*.aud" -mtime +120 -type f | xargs ls -lt | head

$ find adump -name "*.aud" -mtime +120 -type f | xargs ls -lt | tail

以下为示例输出. 

[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$ find adump -name "*.aud" -mtime +120 -type f | xargs ls -lt | head
-rw-rw---- 1 oracle oinstall 1257 Nov 23 16:31 adump/huafafin_ora_24574_1.aud
-rw-rw---- 1 oracle oinstall 1257 Nov 23 16:31 adump/huafafin_ora_24576_d.aud
-rw-rw---- 1 oracle oinstall 1277 Nov 23 16:30 adump/huafafin_ora_24572_3.aud
-rw-rw---- 1 oracle oinstall 1278 Nov 23 16:30 adump/huafafin_ora_24564_1.aud
-rw-rw---- 1 oracle oinstall 1278 Nov 23 16:30 adump/huafafin_ora_24562_c.aud
-rw-rw---- 1 oracle oinstall 1277 Nov 23 16:30 adump/huafafin_ora_24560_e.aud
-rw-rw---- 1 oracle oinstall 1283 Nov 23 16:30 adump/huafafin_ora_24558_7.aud
-rw-rw---- 1 oracle oinstall 1278 Nov 23 16:30 adump/huafafin_ora_24556_6.aud
-rw-rw---- 1 oracle oinstall 1276 Nov 23 16:30 adump/huafafin_ora_24554_4.aud
-rw-rw---- 1 oracle oinstall 1276 Nov 23 16:30 adump/huafafin_ora_24552_5.aud
xargs: ls: terminated by signal 13

[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$ find adump -name "*.aud" -mtime +120 -type f | xargs ls -lt | tail
-rw-rw---- 1 oracle oinstall 1164 Apr 18  2019 adump/huafafin_ora_15672_1.aud
-rw-rw---- 1 oracle oinstall 1283 Apr 17  2019 adump/huafafin_ora_13291_2.aud
-rw-rw---- 1 oracle oinstall 1281 Apr 17  2019 adump/huafafin_ora_11888_1.aud
-rw-rw---- 1 oracle oinstall 1278 Apr 16  2019 adump/huafafin_ora_5286_2.aud
-rw-rw---- 1 oracle oinstall 1281 Apr 15  2019 adump/huafafin_ora_31201_1.aud
-rw-rw---- 1 oracle oinstall 1281 Apr 12  2019 adump/huafafin_ora_13835_1.aud
-rw-rw---- 1 oracle oinstall 1283 Apr 12  2019 adump/huafafin_ora_10580_1.aud
-rw-rw---- 1 oracle oinstall 1164 Apr 10  2019 adump/huafafin_ora_31053_1.aud
-rw-rw---- 1 oracle oinstall 1300 Apr  8  2019 adump/huafafin_ora_22403_1.aud
-rw-rw---- 1 oracle oinstall 1283 Apr  8  2019 adump/huafafin_ora_22377_1.aud


[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$find adump -name "*.aud" -type f | wc -l
2437128

[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$find adump -name "*.aud" -mtime +120 -type f | wc -l
1800879

Step 3

[root@huafafin02 huafafin]# df -i
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
/dev/mapper/vg_root-lv_root
                     2154496  240423 1914073   12% /
tmpfs                3868294       3 3868291    1% /dev/shm
/dev/sda1              32768      39   32729    1% /boot
/dev/mapper/vg_oradata-lv_oradata
                     32768000    9533 32758467    1% /oradata
/dev/mapper/vg_u01-lv_u01
                     3932160 2501065 1431095   64% /u01
/dev/sdd1            22806528      71 22806457    1% /u01/oracle/product/11.2.0.3/dbhome_1/dbs/oracle

[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$find adump -name "*.aud" -mtime +120 -type f | xargs rm -v
removed `adump/huafafin_ora_4495_a.aud'
removed `adump/huafafin_ora_28887_59.aud'
removed `adump/huafafin_ora_19862_1.aud'
removed `adump/huafafin_ora_4197_58.aud'
removed `adump/huafafin_ora_11359_25.aud'
removed `adump/huafafin_ora_14298_68.aud'
removed `adump/huafafin_ora_2638_5a.aud'
removed `adump/huafafin_ora_16117_20.aud'
removed `adump/huafafin_ora_24592_6a.aud'
removed `adump/huafafin_ora_30782_3f.aud'
removed `adump/huafafin_ora_23499_73.aud'
removed `adump/huafafin_ora_27277_1d.aud'
removed `adump/huafafin_ora_5154_23.aud'
removed `adump/huafafin_ora_30577_3c.aud'
...

[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$find adump -name "*.aud" -type f | wc -l 2437128

[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$find adump -name "*.aud" -mtime +120 -type f | wc -l 1261206

[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$du -hs 7.5G .