Describe 首頁/2023-03-24 here.
<<TableOfContents>>
= 问题现象 =
在某些Oracle场景下, 因开启audit审计而产生的大量文件, 从而占用了大量磁盘空间或inode, 导致数据库服务器相关目录空间耗尽而产生数据无法访问等问题.


= 原因分析 =
1. 数据库目录空间是有限的, 如果audit文件累积太多, 慢慢地就会占满整个目录. 可通过 df -h 命令查看.

2. inode数量也是有限的, 原因类似上一条. 可通过 df -i 命令查看.

以下为示例输出:
{{{
[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  393216  0   100% /u01
/dev/sdd1            22806528      71 22806457    1% /u01/oracle/product/11.2.0.3/dbhome_1/dbs/oracle
}}}
以上可以看出/u01目录inode已经耗尽

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

1. 从数据库中执行相关程序或创建触发器删除.

2. 从Linux操作系统层面执行命令删除.

从易于操作的角度出发, 本方案仅介绍"从Linux操作系统层面执行命令删除".

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

== Step 1: 确认audit目录 ==
使用oracle用户登录, 登录数据库中查询audit log所在的目录

su - oracle 

$ sqlplus / as sysdba

SQL> show parameter audit;


示例结果: 
{{{
[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 24 16:51:54 2023

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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/

1. 统计一下目录大小

du -hs 

2. 用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

## 可选确认项: 统计一共有多少个aud文件和将要删除的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: 执行删除操作 ==

1. 进入相应的目录
 * 确认是在audit目录之处
cd  /u01/oracle/product/11.2.0.3/admin/huafafin

pwd

2. 执行命令删除.

find adump -name "*.aud" -mtime +120 -type f | xargs rm -v

示例输出:
{{{
[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'
...

}}}

== Step 4: 确认目录可用空间或inode已释放 ==

df -h && df -i