Describe 首頁/2023-03-24 here.
Contents
问题现象
在某些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