Oracle运行中常常会遇到system表空间或者sysaux表空间不足的情况,下面看看如何处理。
一、审计aud$表导致system用满
1.进行相关常用检查的SQL
# 堆代码 duidaima.com
--查看Oracle表空间大小
SELECT a.tablespace_name "tablespace",total / (1024 * 1024 * 1024) "total(G)",
free / (1024 * 1024 * 1024) "shengyu (G)",
(total - free) / (1024 * 1024 * 1024) "used (G)",
round((total - free) / total, 4) * 100 "rote %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
--查看system表空间哪个对像占用空间最大
SELECT * FROM
( SELECT
bytes,
segment_name,
segment_type,
owner
FROM
dba_segments
WHERE
tablespace_name = 'SYSTEM'
ORDER BY
bytes DESC
)
WHERE
ROWNUM < 10;
--查看审计日志是否开启的SQL语句
select name,type,value from v$parameter where name like '%aud%';
show parameter audit查看数据库审计功能是否打开;
audit_trail值为DB,说明数据库审计功能打开,审计策略也是打开;
audit_sys_operations为FALSE说明可以审计非sys/system用户的所有操作,想要审计sys/system的操作,需要手动打开audit_sys_operations参数,即:
alter system set audit_sys_operations=TRUE scope=spfile;
然后再重启数据库。
--查看aud$表大小SQL
select bytes/1024/1024 MB,owner,segment_name,tablespace_name
from dba_segments
where segment_type='TABLE' and segment_name = 'AUD$';
--sys用户查看aud$表,如果sys用户登录提示权限不足可以查看v$pwfile_users或者更改密码(谨慎)
select count(*) from aud$;
2.查看那种审计占的最多
select action_name,count(*) from dba_audit_trail group by action_name;
一般是LOGON和LOGOFF类型的审计最多。看要求取消此类审计:
3.如果不想清理表,或者说就是要开审计,为了避免日志过多,可以选择性的取消审计内容
noaudit session whenever successful;一般来说,如果空间不是占的特别多,此类审计还是保留为好。
可以取消对一些登录特别频繁的用户的审计,比如DBSNMP用户
noaudit session by dbsnmp;
4.如果确实是没有用,推荐的操作是截断表,虽然有也官方的存储过程来清理,但效率较低,会生成大量undo及归档。
需要注意的是,如果AUD$表过大,那么直接TRUNCATE AUD$表,系统要立即释放大量的EXTENTS,会严重影响系统性能。可以通过如下2个步骤逐步释放EXTENTS:
1). 清空数据并保留原来的extents
TRUNCATE TABLE SYS.AUD$ REUSE STORAGE;
在这里,REUSE STORAGE是TRUNCATE的一个参数,表示保持原来的存储不变。一般情况下,SQL命令“TRUNCATE TABLE TABLE_NAME;”其实就是“TRUNCATE TABLE TABLE_NAME DROP STORAGE;”。DROP STORAGE是TRUNCATE TABLE的默认参数。
2). 逐步回缩extents(执行的时候根据实际情况调整每次回缩空间大小)
ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 5000M;
2ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 2000M;
....
ALTER TABLE SYS.AUD$ DEALLOCATE UNUSED KEEP 10M;
5.如果说要保留,但不想放到SYSTEM里,可以新建审计表空间,把aud$表迁移过去
--aud$表移动到新tablespace,&AUD_TBS_NAME表示新的表空间名
--迁移AUD$表
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘&AUD_TBS_NAME’);
END;
/
6.或者说是有额外的数据库审计产品,不再需要数据库开启审计,那么可以执行如下关闭审计
alter system set audit_trail=none scope=spfile;
之后需要重启生效,如果是RAC,可以滚动重启两个节点。
二、AWR导致SYSAUX表空间用满
SYSAUX表空间做为SYSTEM表空间的辅助表空间,主要存放EM相关的内容以及表统计信息,AWR快照,审计信息等,而假设SYSAUX表空间在默认条件下你假设不做不论什么配置,随着时间的推移。会膨胀的越来越大。
1.查看表空间使用情况
SELECT a.tablespace_name "tablespace",total / (1024 * 1024 * 1024) "total(G)",
free / (1024 * 1024 * 1024) "shengyu (G)",
(total - free) / (1024 * 1024 * 1024) "used (G)",
round((total - free) / total, 4) * 100 "rote %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
2.查看sysaux表空间占比
col Item For a30
col "Space Used(GB)" For a10
col Schema For a20
col "MoveProcedure" For a200
SELECT occupant_name"Item",
round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
schema_name "Schema",
move_procedure "MoveProcedure"
FROM v$sysaux_occupants
ORDER BY 2 Desc;
3.修改统计信息的保持时间
SQL> select dbms_stats.get_stats_history_retention from dual;
31
--修改统计信息保持时间
SQL> exec dbms_stats.alter_stats_history_retention(15);
SQL> select dbms_stats.get_stats_history_retention from dual;
15
修改AWR快照的保存时间
改动AWR快照的保存时间为7天(7 24 60),每小时收集一次,也能够通过EM界面查看和改动
--检查当前系统的保留时间为8天,1小时採样一次
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
------------------------------------------------- -------------------------------------------------
1494575446 +0000001:00:00.0 +0000800:00:00.0 DEFAULT
--修改awr快照保存时长
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 60,
retention => 10080,--分钟
topnsql => 100
);
end;
ORA-13541: 系统移动窗体基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: 在"SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: 在 line 2
删除AWR快照
删除AWR快照,再次查看SYSAUX表空间使用率
--查询最最小和最大快照ID
SQL> select min(snap_id),max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
26705 27066
--注意,该方法有很大的坑,包底层是通过delete删除的会产生大量的redo,undo以及归档日志,会把空间撑满,可以使用其他手动删除方法
--删除最早的24个AWR快照,也就是最早的24小时的快照。(因为从八天变为7天减少24小时,所以手动删除第八天的awr快照,应该也可以等到时)
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id =>26705,high_snap_id => 26705+24);
(全文完)