[转帖]审计日志太多导致Oracle系统表空间占满的处理_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
2
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2245 | 回复: 1   主题: [转帖]审计日志太多导致Oracle系统表空间占满的处理        上一篇   下一篇 
HaukeKn
注册用户
等级:新兵
经验:66
发帖:1
精华:0
注册:2018-10-8
状态:离线
发送短消息息给HaukeKn 加好友    发送短消息息给HaukeKn 发消息
发表于: IP:您无权察看 2019-9-18 9:47:08 | [全部帖] [楼主帖] 楼主

1.查看所有表空间信息(包括总大小、已使用、空闲、使用百分比):

 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;


查询结果:

 TABLESPACE_NAME    Total(M)    Free(M)    Used(M)    Used(%)
---------- ---------- ---------- ----------
MY_TABLESPACES   30720 30718.8125     1.1875          0
SYSAUX           880    70.0625   809.9375      92.04
UNDOTBS1         8720    8695.25      24.75        .28
TABLESPACE_NAME  Total(M)    Free(M)    Used(M)    Used(%)
---------- ---------- ---------- ----------
USER_DATA2      50         49          1          2
USERS           187.5    183.375      4.125        2.2
SYSTEM          10960      15.25   10944.75      99.86
TABLESPACE_NAME   Total(M)    Free(M)    Used(M)    Used(%)
---------- ---------- ---------- ----------
EXAMPLE          100      21.25      78.75      78.75
USER_DATA        15600  5081.5625 10518.4375      67.43


2.查看物理表空间的使用信息:

 SELECT A.TABLESPACE_NAME,B.TOTAL/1024/1024||'M',(B.TOTAL-A.USE)/1024/1024||'M' FREE 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  B.TOTAL/1024/1024||'M'  FREE
--------------------------------------------------------------------------------
MY_TABLESPACES  32767.984375M  32767.796875M
EXAMPLE  32767.984375M  32690.234375M
TABLESPACE_NAME  B.TOTAL/1024/1024||'M'  FREE
--------------------------------------------------------------------------------
USER_DATA  30720M  20201.3125M


这个命令看不到系统表空间SYSTEM的信息,SYSTEM可以直接查看文件.dbf大小:

 ls -lh $ORACLE_HOME
-rw-r----- 1 oracle dba  11G May  3 12:25 system01.dbf


如果你的系统表空间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




赞(0)    操作        顶端 
联动大白
注册用户
等级:列兵
经验:91
发帖:0
精华:0
注册:2015-5-27
状态:离线
发送短消息息给联动大白 加好友    发送短消息息给联动大白 发消息
发表于: IP:您无权察看 2019-10-31 0:30:00 | [全部帖] [楼主帖] 2  楼

为了方便大家阅读,我对文章中错误号来解释一下吧!

Error Id: ORA-09925

Title: Unable to create audit trail file

Description:

Unable to create audit trail file

Action:

Check the UNIX error number for a possible operating system error. If there is no error, contact ORACLE customer support.

Cause:

ORACLE was not able to create the file being used to hold audit trail records.


也许你已明白,但对一个人有用也是我存在的理由!^_^ By:持之以恒的大白

-- 来自: 北京联动北方科技有限公司



赞(0)    操作        顶端 
总帖数
2
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论