审计日志太多导致Oracle系统表空间占满 ORA

审计日志太多导致Oracle系统表空间占满 ORA-09925: Unable to create audit trail file_数据库审计日志满了,怎么处理_sunny05296的博客-CSDN博客Oracle磁盘写满导致的 Oracle sqlplus 连接报错 ORA-09925: Unable to create audit trail file_ora09925_sunny05296的博客-CSDN博客

--查看表空间的物理文件使用信息（物理表空间文件的占用）： 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不了数据库）。

如果要重新打开审计日志则执行： SQL> alter system set audit_trail=db,extended scope=spfile; 重启数据库

audit_trail 配置参数： db            启用审计，信息记录在 SYS.AUD$中 os            启用审计，审计信息保存在操作系统 db,extended   启用审计，并且记录对应的执行语句 xml           启用审计，审计信息以xml形式保存在操作系统上 xml,extended  启用审计，并且包括所有的执行语句

手动清除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;