一．SYSAUX表空间使用率高问题处理
一般来讲除开业务数据存放的表空间，DBA要着重关注SYSTEM,SYSAUX,UNDO,TEMP表空间，SYSTEM表空间的大小一般是衡定的，UNDO和TEMP表空间的大小由数据库的业务情况决定，而SYSAUX表空间在默认条件下你如果不做任何配置，随着时间的推移，会膨胀的越来越大！SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等，个人认为，如果你的SYSAUX表空间大小超过2G，那么该考虑让他减肥了！
(一)：使用下列语句查询表空间使用率
SELECT * FROM (
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1)
WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');
(二)：查询SYSAUX表空间内各个分类项目占存储空间的比重，很明显可以看出来AWR快照占用了2G左右的空间，统计信息为149M左右，同时数据库关闭了审计audit_trail，所以审计表aud$不占空间
SELECT occupant_name "Item",
space_usage_kbytes / 1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
(三)：修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> exec dbms_stats.alter_stats_history_retention(7);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
7
(四)：修改AWR快照的保存时间为7天(7*24*60),每小时收集一次，也可以通过EM界面查看和修改
SQL> begin
dbms_workload_repository.modify_snapshot_settings (
interval => 60,
retention => 10080,
topnsql => 100
);
end;
(五)：删除AWR快照，再次查看SYSAUX表空间使用率,最后表空间使用率降低为38.42%
select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID
begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 10758,
high_snap_id => 10900,
dbid => 387090299);
end;
Reference http://ylw6006.blog.51cto.com/470441/1135593/
二．SYSTEM表空间使用率高问题处理
1.类型占用空间大小
select owner, segment_name, segment_type, sum(bytes)/1024/1024 space_m
from dba_segments
where tablespace_name = 'SYSTEM'
group by owner, segment_name, segment_type
having sum(bytes)/1024/1024 >= 20
order by space_m desc;
2.查看哪个用户下记录的比较多
select userid, userhost, count(1) from sys.aud$
where ntimestamp# >=CAST(to_date('2016-01-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)
group by userid, userhost
having count(1) > 500
order by count(1) desc;
3.继续找哪天的比较多----按userid和userhost找
select to_char(ntimestamp#, 'YYYY-MM-DD') audit_date, count(1)
from sys.aud$
where ntimestamp# >=CAST(to_date('2014-03-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)
and userid = 'DBSNMP' and userhost = 'test11g'
group by to_char(ntimestamp#, 'YYYY-MM-DD')
order by count(1) desc;
----》
select spare1, count(1) from sys.aud$
where ntimestamp# between CAST(to_date('2014-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)
and CAST(to_date('2014-03-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)
and userid = 'xxxx' and userhost = 'xxxx'
group by spare1;
select action#, count(1) from sys.aud$
where ntimestamp# between CAST(to_date('2014-03-10 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)
and CAST(to_date('2014-03-11 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AS TIMESTAMP)
and userid = 'xxxx' and userhost = 'xxxx'
and spare1 = 'xxxx'
group by action#
order by count(1) desc;
SYS AUD$存放的是审计信息
可以TRUNCATE掉回收空间 如果用不到审计的话 建议关掉它TEM,SYSAUX表空间过大，如何减小啊
一般是先关闭审计不让其继续写，然后再清空。11g会默认开启审计功能的。
noaudit session;
alter system set audit_trail = none scope=spfile;
重启实例
清空：
truncate table sys.aud$;
一．关闭审计
|
|
1、查看审计功能是否开启 sqlplus "/as sysdba" SQL> show parameter audit; NAME TYPE VALUE -------------------- ------- -------------------------------- audit_file_dest string /u01/app/oracle/admin/ORCL/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB 说明：表明审计功能为开启的状态
2、关闭oracle的审计功能 SQL> alter system set audit_sys_operations=FALSE scope=spfile; System altered. SQL> alter system set audit_trail=NONE scope=spfile; System altered.
3、重启数据库 SQL> shutdown immediate; SQL> startup;
4、验证审计是否已经被关闭 SQL> show parameter audit; NAME TYPE VALUE -------------------- ------- -------------------------------- audit_file_dest string /u01/app/oracle/admin/ORCL/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string NONE 说明：表明审计功能为关闭的状态
5、清空审计表数据 SQL> truncate table SYS.AUD$; |
reference 关于收缩空间 http://blog.csdn.net/edwzhang/
关于审计 http://blog.itpub.net/15747463/
二．关于审计
初始化参数AUDIT_TRAIL用于控制数据库审计，取值说明:
none 禁用数据库审计
os 启用数据库审计，并将数据库审计记录定向到操作系统审计记录
db 启用数据库审计，并将数据库所有审计记录定向到数据库的SYS.AUD$表
db,extended 启用数据库审计，并将数据库所有审计记录定向到数据库的SYS.AUD$表。另外，填充SYS.AUD$表的SQLBIND 列和SQLTEXT CLOB 列。
xml 启用数据库审计，并将所有记录写到XML格式的操作系统文件中。
xml,extended 启用数据库审计，输出审计记录的所有列，包括SqlText和SqlBind的值。
Oracle公司还推荐使用基于OS文件的审计日志记录方式(OS audit trail files)。
三．审计表
select * from dba_audit_object;
select * from dba_audit_session;
select * from dba_audit_trail;
select * from aud$ order by logoff$time desc;
select action_name,count(*) from dba_audit_trail group by action_name;
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1204 as bytes_m from dba_segments where lower(segment_name) in ('aud$');
相关表
SYS.AUD$ 是唯一保留审计结果的表。其它的都是视图。
STMT_AUDIT_OPTION_MAP 包含有关审计选项类型代码的信息由SQL.BSQ 脚本在CREATEDATABASE 的时候创建
AUDIT_ACTIONS 包含对审计跟踪动作类型代码的说明
ALL_DEF_AUDIT_OPTS 包含默认对象审计选项。当创建对象时将应用这些选项
DBA_STMT_AUDIT_OPTS 描述由用户设置的跨系统的当前系统审计选项
DBA_PRIV_AUDIT_OPTS 描述由用户正在审计的跨系统的当前系统权限
DBA_OBJ_AUDIT_OPTS 描述在所有对象上的审计选项
USER_OBJ_AUDIT_OPTS USER 视图描述当前用户拥有的所有对象上的审计选项
以下是审计记录
DBA_AUDIT_TRAIL 列出所有审计跟踪条目
USER_AUDIT_TRAIL USER视图显示与当前用户有关的审计跟踪条目
DBA_AUDIT_OBJECT 包含系统中所有对象的审计跟踪记录
USER_AUDIT_OBJECT USER 视图列出一些审计跟踪记录而这些记录涉及当前用户可以访问的对象的语句
DBA_AUDIT_SESSION 列出涉及CONNECT 和DISCONNECT 的所有审计跟踪记录
USER_AUDIT_SESSION USER视图列出涉及当前用户的CONNECT 和DISCONNECT 的所有审计跟踪记录
DBA_AUDIT_STATEMENT 列出涉及数据库全部的GRANT REVOKE AUDIT NOAUDIT 和ALTER SYSTEM 语句的审计跟踪记录
USER_AUDIT_STATEMENT 对于USER 视图来说这些语句应是用户发布的
DBA_AUDIT_EXISTS 列出BY AUDIT NOT EXISTS 产生的审计跟踪条目
下面的视图用于细粒度审计
DBA_AUDIT_POLICIES 显示系统上的所有审计策略
DBA_FGA_AUDIT_TRAIL 列出基于值的审计的审计跟踪记录
四．手动清理审计及迁移审计存储表空间
1.查看审计相关配置
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
--从下面的查询中可以看出，当前的审计位于system表空间
SQL> col segment_name FOR a10
SQL> SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$';
OWNER SEGMENT_NA TABLESPACE_NAME
------------------------------ ---------- ------------------------------
SYS AUD$ SYSTEM
select owner,
segment_name,
segment_type,
tablespace_name,
bytes / 1024 / 1024 bytes_m
from dba_segments
where segment_name = 'AUD$';
----迁移之前，也可以先查询下表空间占用情况：
WITH df AS
(SELECT tablespace_name,
SUM(bytes) bytes,
COUNT(*) cnt,
DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
FROM dba_data_files
GROUP BY tablespace_name),
tf AS
(SELECT tablespace_name,
SUM(bytes) bytes,
COUNT(*) cnt,
DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
FROM dba_temp_files
GROUP BY tablespace_name)
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0) as total_m,
round(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024, 2) as used_m,
round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) used_percent,
a.autoext,
round(NVL(f.bytes, 0) / 1024 / 1024, 2) free_m,
d.status,
a.cnt --数据文件
FROM dba_tablespaces d,
df a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT d.contents = 'UNDO'
AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
round(NVL(t.ub * d.block_size, 0) / 1024 / 1024, 2),
round(NVL((t.ub * d.block_size) / a.bytes * 100, 0), 2),
a.autoext,
(round((NVL(a.bytes, 0) / 1024 / 1024 -
NVL((t.ub * d.block_size), 0) / 1024 / 1024),
2)),
d.status,
a.cnt
FROM dba_tablespaces d,
tf a,
(SELECT ss.tablespace_name, sum(ss.used_blocks) ub
FROM gv$sort_segment ss
GROUP BY ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND
d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
round(NVL(u.bytes, 0) / 1024 / 1024,2),
round(NVL(u.bytes / a.bytes * 100, 0),2),
a.autoext,
round(NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024,2),
d.status,
a.cnt
FROM dba_tablespaces d,
df a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_undo_extents
where status in ('ACTIVE', 'UNEXPIRED')
GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = u.tablespace_name(+) AND d.contents = 'UNDO'
ORDER BY 1
2.修改审计存储表空间
SQL> select name from v$datafile;
SQL> create tablespace audit_data datafile '/u01/app/oracle/oradata/orcl/audit01.dbf' size 100m autoextend on next 50m;
----迁移审计数据存放表空间
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA'
);
END;
/
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX'
);
END;
/
-----这里注意，前一之前，一定要确定aud$审计文件大小是否能放得下到新表空间，如果放不下会报错。这时可以调整下新表空间数据文件大小
SQL> alter database datafile '/home/oracle/app/oradata/orcl/audit01.dbf' resize 1200m;
----迁移完成后，再次查询：已经在新表空间中
select owner,
segment_name,
segment_type,
tablespace_name,
bytes / 1024 / 1024 bytes_m
from dba_segments
where segment_name = 'AUD$';
----可以再次查看表空间使用情况，可以发现system表空间size有所下降。
----查看审计数据字典配置信息
SQL> col PARAMETER_NAME FOR a30
SQL> col PARAMETER_VALUE FOR a15
SQL> col AUDIT_TRAIL FOR a20
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
2 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
3 WHERE audit_trail = 'STANDARD AUDIT TRAIL';
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- --------------------
DB AUDIT TABLESPACE AUDIT_DATA STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
3.清除审计记录
----设定清除间隔
BEGIN
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 120 /* hours */);
END;
/
----下面验证审计日志清除是否开启
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
---àYES
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';
SEGMENT_NA BYTES/1024/1024
---------- ---------------
AUD$ 2
SQL> select count(*) from AUD$;
COUNT(*)
----------
9682
SQL> select to_char(min(ntimestamp#),'yyyy-mm-dd hh24:mi:ss') from aud$;
TO_CHAR(MIN(NTIMEST
-------------------
2016-07-17 15:55:43
----设置归档间隔
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-10);
END;
/
----查看设定的归档间隔
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-08-04 02:59:40
SQL> SELECT audit_trail,rac_instance,to_char(last_archive_ts,'yyyy-mm-dd hh24:mi:ss') FROM dba_audit_mgmt_last_arch_ts;
AUDIT_TRAIL RAC_INSTANCE TO_CHAR(LAST_ARCHIV
-------------------- ------------ -------------------
STANDARD AUDIT TRAIL 0 2016-07-25 02:57:45
----通过调用DBMS_AUDIT_MGMT.clean_audit_trail进行手动清理审计日志
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
DBMS_AUDIT_MGMT.clean_audit_trail
This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the
SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.
--也可以通过创建一个purge Job来进行清理已归档的历史审计记录
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
-- 本次测试使用了job进行清理，注，上面的purge job 并非使用DBMS_SCHEDULER.CREATE_JOB创建
exec DBMS_SCHEDULER.RUN_JOB(job_name => 'SYS.DAILY_AUDIT_PURGE_JOB');
----清理后行数减少咯，但是空间并没有释放
SQL> select count(*) from AUD$;
COUNT(*)
----------
9419
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';
SEGMENT_NA BYTES/1024/1024
---------- ---------------
AUD$ 2
SQL> alter table sys.aud$ enable row movement;
Table altered.
SQL> alter table sys.aud$ shrink space cascade;
Table altered.
SQL> alter table sys.aud$ disable row movement;
Table altered.
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';
SEGMENT_NA BYTES/1024/1024
---------- ---------------
AUD$ 1.8125
4.小结
a、对于Oracle 11g，审计功能默认被开启，因此如果在必须启用的情况下应考虑性能影响；
b、开启审计的情况下，建议将审计从system或sysaux表空间剥离，使用单独的表空间；
c、对于历史审计日志的清除，应考虑清除期间所带来的性能影响；
d、调用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION这个过程已经开始了搬迁过程，如果审计日志很庞大，应考虑IO影响；
e、审计日志的清除需要先设定归档，已归档的审计日志会被清理;
f、也可以通过trunate table aud$ reuse storage以及deallocate非常规方式来处理。
Reference http://www.2cto.com/database/
Dave http://blog.csdn.net/tianlesoftware/