Differences between revisions 1 and 16 (spanning 15 versions)
⇤ ← Revision 1 as of 2023-03-24 08:37:51 →
Size: 4093
Editor: localhost
Comment:
← Revision 16 as of 2023-03-24 11:00:33 → ⇥
Size: 6265
Editor: localhost
Comment:
Deletions are marked like this. Additions are marked like this.
Line 2: Line 2:
<<TableOfContents>>
= 问题现象 =
在某些Oracle场景下, 因开启audit审计而产生的大量文件, 从而占用了大量磁盘空间或inode, 导致数据库服务器相关目录空间耗尽而产生数据无法访问等问题.
Line 3: Line 6:
== Step 1 ==
show parameter audit;
= 原因分析 =
1. 数据库目录空间是有限的, 如果audit文件累积太多, 慢慢地就会占满整个目录. 可通过 df -h 命令查看.

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

以下为示例输出:
Line 6: Line 14:
[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
Line 17: Line 73:
== Step 2 == == Step 2: 进入audit_file_dest目录之处 ==
Line 21: Line 77:
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

以下为示例输出.
Line 22: Line 89:
[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$find adump -name "*.aud" -mtime +120 -type f | xargs ls -lt | head [huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$ find adump -name "*.aud" -mtime +120 -type f | xargs ls -lt | head
Line 35: Line 102:
[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$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 | tail
Line 47: Line 114:
## 可选确认项: 统计一共有多少个aud文件和将要删除的aud文件有多少个.
Line 56: Line 123:
== 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
}}}		 == Step 3: 执行删除操作 ==
Line 71: Line 125:
1. 进入相应的目录
 * 确认是在audit目录之处
cd /u01/oracle/product/11.2.0.3/admin/huafafin

2. 执行命令删除.

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

示例输出:
Line 90: Line 153:
[huafafin02:oracle:/u01/oracle/product/11.2.0.3/admin/huafafin]$find adump -name "*.aud" -type f | wc -l
2437128
== Step 4: 确认目录可用空间或inode已释放 ==

df -h && df -i

Describe 首頁/2023-03-24 here.

Contents

  1. 问题现象
  2. 原因分析
  3. 解决方案
    1. Step 1: 确认audit目录
    2. Step 2: 进入audit_file_dest目录之处
    3. Step 3: 执行删除操作
    4. Step 4: 确认目录可用空间或inode已释放

问题现象

在某些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

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

首頁/2023-03-24 (last edited 2023-03-24 11:01:20 by localhost)