审计日志太多导致Oracle系统表空间占满 ORA-09925: Unable to create audit trail file

「爱情、让人受尽委屈。」 2022-05-26 13:29 242阅读 0赞

[https://blog.csdn.net/sunny05296/article/details/80178470][https_blog.csdn.net_sunny05296_article_details_80178470]  
[https://blog.csdn.net/sunny05296/article/details/112707420][https_blog.csdn.net_sunny05296_article_details_112707420]

\--查看表空间的物理文件使用信息（物理表空间文件的占用）：  
select A.tablespace\_name,B.TOTAL/1024/1024||'M' TOTAL\_M ,(B.TOTAL-A.USE)/1024/1024||'M' FREE\_M FROM  
(  
select tablespace\_name,sum(bytes) as USE from dba\_segments  
where tablespace\_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')  
GROUP BY tablespace\_name  
) A ,  
(  
WITH TABLESPACE\_TOTAL AS  
(  
select tablespace\_name,sum(MAXBYTES) TOTAL FROM DBA\_DATA\_FILES T  
where T.tablespace\_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')  
and T.AUTOEXTENSIBLE='YES' group by tablespace\_name  
UNION ALL  
select tablespace\_name,sum(bytes) TOTAL FROM DBA\_DATA\_FILES T  
where T.tablespace\_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')  
and T.AUTOEXTENSIBLE='NO' group by tablespace\_name  
)  
select tablespace\_name,SUM(TOTAL) TOTAL FROM TABLESPACE\_TOTAL GROUP BY tablespace\_name  
) B  
where A.tablespace\_name=B.tablespace\_name;

结果：

TABLESPACE\_NAME                        TOTAL\_M                   FREE\_M  
\-----------------------------------------------------------------------------------  
SDE\_TBS                                400M                      365.5625M  
TBS\_PT01                               2048M                     2047.9375M  
MY\_TABLESPACES                32767.984375M             32767.734375M  
USER\_DATA2                             10240M                    10239.875M  
EXAMPLE                                32767.984375M             32690.234375M  
USER\_DATA                              30720M                    17080.875M  
TBS\_PT02                               2048M                     2047.9375M  
TBS\_PT03                               2048M                     2047.9375M

8 rows selected.

SQL>

注意：这个命令看不到系统表空间SYSTEM的信息，SYSTEM可以直接查看文件.dbf大小，或者使用后面介绍的查看数据表空间的方法  
ls -lh $ORACLE\_HOME  
\-rw-r----- 1 oracle dba  11G May  3 12:25 system01.dbf

\--查看表空间（包含临时表空间）的使用信息（包括总大小、已使用、空闲、使用百分比）：  
select a.tablespace\_name,TOTAL "TOTAL(M)",free "Free(M)",TOTAL-free "Used(M)",round(((TOTAL-free)/TOTAL)\*100,2) "Used(%)" from  
( select tablespace\_name,sum(bytes)/1024/1024 TOTAL from dba\_data\_files group by tablespace\_name) a,  
( select tablespace\_name,sum(bytes)/1024/1024 free from dba\_free\_space group by tablespace\_name) b  
where a.tablespace\_name=b.tablespace\_name  
union all -- and TEMP TABLESPACE  
select c.tablespace\_name,TOTAL "TOTAL(M)",free "Free(M)",TOTAL-free "Used(M)",round(((TOTAL-free)/TOTAL)\*100,2) "Used(%)" from  
( select tablespace\_name,sum(bytes)/1024/1024 TOTAL from dba\_temp\_files group by tablespace\_name ) c,  
( select tablespace\_name,sum(bytes\_cached)/1024/1024 free from v$temp\_extent\_pool group by tablespace\_name ) d  
where c.tablespace\_name = d.tablespace\_name  
order by tablespace\_name;

查询结果：

TABLESPACE\_NAME                              TOTAL(M)    Free(M)    Used(M)    Used(%)  
\------------------------------------------ ---------- ---------- ---------- ----------  
EXAMPLE                                           100      21.25      78.75      78.75  
SDE\_TBS                                           400   364.5625    35.4375       8.86  
SYSAUX                                            890    68.5625   821.4375       92.3  
SYSTEM                                          10960   10243.25     716.75       6.54  
MY\_TABLESPACES                         30720   30718.75       1.25          0  
TBS\_PT01                                           50    48.9375     1.0625       2.13  
TBS\_PT02                                           50    48.9375     1.0625       2.13  
TBS\_PT03                                           50    48.9375     1.0625       2.13  
TBS\_SDE                                           400        399          1        .25  
TEMP                                               29         28          1       3.45  
TSP\_SDE                                           400        399          1        .25

TABLESPACE\_NAME                              TOTAL(M)    Free(M)    Used(M)    Used(%)  
\------------------------------------------ ---------- ---------- ---------- ----------  
UNDOTBS1                                         8720  8399.8125   320.1875       3.67  
USERS                                           187.5    183.375      4.125        2.2  
USER\_DATA                                       15650  2010.1875 13639.8125      87.16  
USER\_DATA2                                         50     48.875      1.125       2.25  
USER\_SDE\_DATA                                     400        399          1        .25  
USER\_TEMP                                          50          5         45         90

17 rows selected.

SQL>

\-- 查看临时表空间（dba\_temp\_files 和 v$tempfile视图，包含：表空间名称、物理文件路径、文件大小、AUTOEXTENSIBLE状态等信息）：  
select a.tablespace\_name,a.file\_name,(a.bytes)/1024/1024 file\_size,a.autoextensible,b.status,b.enabled from dba\_temp\_files a,v$tempfile b where a.file\_name=b.name;

TABLESPACE\_NAME     FILE\_NAME                                  FILE\_SIZE   AUTOEXTEN   STATUS    ENABLED  
TEMP                /opt/oracle/oradata/orcl/temp01.dbf        29          YES         ONLINE    READ WRITE  
USER\_TEMP           /opt/oracle/oradata/my\_temp01.dbf          50          YES         ONLINE    READ WRITE  
USER\_TEMP2          /opt/oracle/user\_temp\_002.dbf              50          YES         ONLINE    READ WRITE

\-- 查看数据表空间（dba\_data\_files，包含：表空间名称、物理文件路径、文件大小、AUTOEXTENSIBLE状态等信息）：  
select a.tablespace\_name,a.file\_name,(a.bytes)/1024/1024 file\_size,a.autoextensible from dba\_data\_files a;

TABLESPACE\_NAME     FILE\_NAME                              FILE\_SIZE        AUTOEXTEN  
USERS                /opt/oracle/oradata/orcl/users01.dbf       187.50              YES  
UNDOTBS1            /opt/oracle/oradata/orcl/undotbs01.dbf     8720                YES  
SYSAUX              /opt/oracle/oradata/orcl/sysaux01.dbf       890                YES  
SYSTEM              /opt/oracle/oradata/orcl/system01.dbf       10960              YES  
EXAMPLE              /opt/oracle/oradata/orcl/example01.dbf     100                YES  
USER\_DATA            /opt/oracle/oradata/my\_data01.dbf        15650              YES

如果你的系统表空间SYSTEM占用比较多的空间，可能有以下几方面的原因：  
(1)没有为用户明确指定默认表空间，导致system系统表空间作为用户默认表空间  
(2)开启了审计，请检查此表的大小AUD$  
你可以运行以下查询来检查一下系统表空间哪些表比较大：  
select \* from (select SEGMENT\_NAME,sum(bytes)/1024/1024 sx from dba\_segments where tablespace\_name='SYSTEM' group by segment\_name) where sx>100 order by sx desc;

查看该表纪录数  
select count(\*) total from AUD$;

3.查看数据表空间对应的文件名  
select file\_name from dba\_data\_files where tablespace\_name='SYSTEM';  
/opt/oracle/oradata/orcl/system01.dbf

解决方法大概有下面几种:  
1.重新调整SYSTEM表空间的物理数据文件的大小：  
alter database datafile '/opt/oracle/oradata/orcl/system01.dbf' autoextend on;  
alter database datafile '/opt/oracle/oradata/orcl/system01.dbf' resize 10240M;  
注意：单文件最大支持32G。如果表空间超过32G，需要新增数据文件。

2.为SYSTEM表空间另外新增一个数据文件：  
alter tablespace system add datafile '/opt/oracle/oradata/orcl/system02.dbf' size 10240M;  
  
3.truncate掉AUD$表、并关闭审计功能：

truncate table AUD$;  
alter system set audit\_trail=none scope=spfile;

重启数据库：  
showdown immediate;  
startup;

注意：使用这种方法，效果立竿见影。如果不关闭此功能，需要定期清理此表。

4.将AUD$默认表空间由system移出。

创建迁移AUD$的普通表空间：  
create tablespace records   
datafile '/opt/oracle/oradata/orcl/auds01.dbf' size 10G autoextend on;   
   
迁移到目标表空间：  
BEGIN  
  DBMS\_AUDIT\_MGMT.SET\_AUDIT\_TRAIL\_LOCATION( AUDIT\_TRAIL\_TYPE => DBMS\_AUDIT\_MGMT.AUDIT\_TRAIL\_AUD\_STD, AUDIT\_TRAIL\_LOCATION\_VALUE => 'AUDS');  
END;  
/

查询所属表空间  
select OWNER, TABLE\_NAME, TABLESPACE\_NAME from DBA\_TABLES where TABLE\_NAME = 'AUD$' and OWNER = 'SYS';

\----------------

**Oracle审计日志清理**

数据库安装后数据库审计开关默认设置为DB（audit\_trail=DB，如果为其他值则表示被认为设置过）：  
SQL> show parameter audit\_trail;   
Name 　　　　　　TYPE 　　　　VALUE   
\-----------------------------------   
audit\_trail 　　string 　　　　DB

audit\_trail=DB时Oracle会把每次审计跟踪记录在数据库的的AUD$的表中。

默认情况下，AUD$这张表所在的表空间，是Oracle最重要的SYSTEM表空间：  
SQL> select owner,table\_name,tablespace\_name from dba\_tables a where a.table\_name ='AUD$';   
OWNER        TABLE\_NAME        TABLESPACE\_NAME  
\--------------------------------------------------------------  
SYS                AUD$                    SYSTEM

通常情况下，SYSTEM表空间都会设置为自动扩展（AUT=YES）：  
SQL> select a.tablespace\_name,a.bytes,a.autoextensible from dba\_data\_files a where a.tablespace\_name='SYSTEM';

TABLESPACE\_NAME        BYTES              AUT  
\--------------------------------------------------------  
SYSTEM                          1.1492E+10    YES

因此，时间长了以后，SYSTEM表空间最终会把磁盘撑爆。

管理审计日志

进入审计日志目录：  
cd $ORACLE\_BASE/实例名/$ORACLE\_SID/adump  
例如：ls $ORACLE\_BASE/admin/orcl/adump

仅保留30天的文件，其他的都删除  
find ./ -type f -name "\*.aud" -mtime +31 |xargs rm -f

如果不保留、也可以删除所有审计文件  
find ./ -type f -name "\*.aud" |xargs rm -f  
find ./ -type f -name "\*.aud" –delete

设置审计参数关闭：  
SQL> alter system set audit\_trail=none scope=spfile;  
SQL> shutdown immediate;  
SQL> startup;

注意：虽然关闭了审计，但Oracle还有一个叫强制审计的功能。如果操作者用SYSDBA/SYSOPER权限登录数据库，进行startup、shutdown等操作，Oracle依然会给你在$ORACLE\_BASE/admin/$ORACLE\_SID/adump目录中记.aud的文件。  
因此：开了DB功能，会同时将审计日志记在AUD$表中和操作系统aud文件中。时间长了以后，除了存SYSTEM表空间吧磁盘撑爆的风险外，还存在审计文件太多导致系统inodes耗尽的风险。  
如果设置为NONE，仍然会而且毫无其他办法的将记录在操作系统aud文件中。

如果系统inodes被耗尽，数据库会报错：  
ORA-09925: Unable to create audit trail file  
Linux-x86\_64 Error: 28: No space left on device  
Additional information: 9925

查看审计日志记录的表名和审计文件的系统目录：  
SQL> show parameter audit;  
默认的数据库的表为：sys.aud$，操作系统目录为：$ORACLE\_BASE/admin/实例名/adump/

最后：  
1.如果为DB，请定期清空aud$表：  
SQL> truncate table sys.aud$;

2.如果不用DB功能，可以设置关闭：  
SQL> alter system set audit\_trail=none scope=spfile;  
SQL> shutdown immediate;  
SQL> startup;

3.如果为NONE，请定期清空aud文件或者配置crontab定时任务定时清空aud文件（清空时不要直接删除adump目录，会导致sqlplus不了数据库）。

手动清除aud文件：

\--进入审计日志目录  
cd $ORACLE\_BASE/admin/$ORACLE\_SID/adump

\--删除1个月前的审计文件  
find ./ -type f -name "\*.aud" -mtime +31 |xargs rm -rf

\--一次清空所有审计文件  
find ./ -type f -name "\*.aud" |xargs rm -rf   
find ./ -type f -name "\*.aud" -mtime +7 -delete

配置定时任务自动定时清理1周以前的aud文件：  
\#su - oracle  
$crontab -e  
0 23 1 \* \* find $ORACLE\_BASE/admin/$ORACLE\_SID/adump/ -type f -name "\*.aud" -mtime +7 -delete

==========================

如果不是审计日志而是数据表的表空间占用较大的处理相关命令参考如下

set line 180  
col tablespace\_name for a30;  
col file\_name for a50;

\-- 查看临时表空间（包括物理文件路径）  
select a.tablespace\_name,a.file\_name,(a.bytes)/1024/1024 file\_size,a.autoextensible,b.status,b.enabled from dba\_temp\_files a,v$tempfile b where a.file\_name=b.name;

\-- 查看数据表空间（包括物理文件路径）  
select a.tablespace\_name,a.file\_name,(a.bytes)/1024/1024 file\_size,a.autoextensible from dba\_data\_files a;

\-- 查询用户的默认表空间和默认临时表空间（TEST用户为例）  
select username,default\_tablespace,temporary\_tablespace from dba\_users where username='TEST';

\-- 查询用户下所有表使用的表空间（TEST用户为例）  
select owner,table\_name,tablespace\_name from dba\_tables where owner='TEST';

\-- 修改表空间（调整大小）  
alter database datafile '/opt/oracle/oradata/orcl/my\_data01.dbf' resize 30720M;

\-- 为表空间增加单独的数据文件  
alter tablespace USER\_DATA add datafile '/opt/oracle/oradata/orcl/my\_data02.dbf' size 30720M;

\-- 查询某个表空间下面的所有表（USER\_DATA 表空间为例）  
select owner,table\_name,tablespace\_name from all\_tables where tablespace\_name='USER\_DATA';  
select \* from all\_tables where tablespace\_name='USER\_DATA';

alter table tableName move  
alter table tableName shrink space  
可以用来进行段收缩，降低高水位HWM，也都可以用来消除行链接（Row Chaining）和行迁移（Row Migration），效果不一定明显

purge 命令的使用  
使用 purge 从回收站删除一个表或索引，并释放与该对象关联的所有空间，或者删除整个回收站，或者删除回收站中丢弃的表空间的一部分。  
官方的说明：Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.

语法：

\-- Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.  
PURGE TABLE table;  
PURGE INDEX index;

\-- Use this clause to purge the current user's recycle bin. Oracle Database will remove all objects from the user's recycle bin and release all space associated with objects in the recycle bin.  
PURGE RECYCLEBIN;

\-- This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.  
PURGE DBA\_RECYCLEBIN;

\-- Use this clause to purge all the objects residing in the specified tablespace from the recycle bin.  
PURGE TABLESPACE tablespace;

\-- Use this clause to reclaim space in a tablespace for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace.  
PURGE TABLESPACE tablespace USER user;

\-- 查看回收站的内容  
select \* from recyclebin;  
或者  
select \* from user\_recyclebin;

\-- 重复DROP操作在RECYCLEBIN中会存在多条记录   
select object\_name, original\_name, operation, droptime from recyclebin;

\-- 根据表名PURGE清除表  
PURGE TABLE table;  
select object\_name, original\_name, operation, droptime from recyclebin;

\-- 根据回收站OBJECT\_NAME清除表  
PURGE TABLE  "BIN$s5Mx/gpFfOvgUwEAAH/RYg==$0";

\-- drop表后，清空回收站  
DROP TABLE table;  
PURGE RECYCLEBIN;  
select object\_name, original\_name, operation, droptime from recyclebin;

\-- drop表时待PURGE参数、不走回收站  
DROP TABLE table PURGE;  
select object\_name, original\_name, operation, droptime from recyclebin;

[https_blog.csdn.net_sunny05296_article_details_80178470]: https://blog.csdn.net/sunny05296/article/details/80178470
[https_blog.csdn.net_sunny05296_article_details_112707420]: https://blog.csdn.net/sunny05296/article/details/112707420