oracle11g SAP测试机归档日志暴增排查（二）

       接上面一的内容，通过logminer可以知道是因为oracle11g设置awr快照引起的插入数据，所以要看这个插入是否正常。

      之前也发现SYSAUX表空间也没有多少了，应该这个原因引起产生大量的日志

6、查找SYSAUX表空间满的原因

     对于SYSAUX表空间而言，如果占用过大，那么一般情况下是由于AWR信息或对象统计信息没有及时清理引起的，具体原因可以通过如下的SQL语句查询：

     SELECT OCCUPANT_NAME "Item",
       SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
       SCHEMA_NAME "Schema",
       MOVE_PROCEDURE "Move Procedure"
  FROM V$SYSAUX_OCCUPANTS
 WHERE SPACE_USAGE_KBYTES > 1048576
 ORDER BY "Space Used (GB)" DESC;

    我这里查询出来的主要原因还是SM/AWR占用了大部分空间

    通过查询占用空间较大的表，即可得到占用空间较大的原因。

SQL> select min(snap_id),max(snap_id) from wrh$_active_session_history;

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>19267,high_snap_id => 19300);

执行完成后

SQL> select min(snap_id),max(snap_id) from wrh$_active_session_history;

       通过这种方式清理的AWR信息，再次查看SYSAUX表空间的空间，发现空间并没有被回收，使用率还和之前一样，这是因为清理AWR操作是通过DELETE操作实现的，表的水位线并没有下降导致的。但是通过再次查询可发现 WRH$_LATCH表记录已经少了。但是表大小还是没有变化。

查出大于100M的表分区

select distinct 'truncate table '||segment_name||';',s.bytes

from dba_segments s

where s.segment_name like 'WRH$%'

and segment_type in ('TABLE PARTITION', 'TABLE')

and s.bytes/1024/1024>100

order by s.bytes desc

其中

truncate table WRH$_EVENT_HISTOGRAM;

218103808

这个有218M

首先查看表的分区情况以及大小

SQL> select segment_name,partition_name,bytes/1024/1024 mb from dba_segments where segment_name='WRH$_EVENT_HISTOGRAM';

SEGMENT_NAME

PARTITION_NAME MB

WRH$_EVENT_HISTOGRAM

WRH$_EVENT_HISTO_MXDB_MXSN .0625

WRH$_EVENT_HISTOGRAM

WRH$_EVENT__766916574_0 208

7、对分区表进行MOVE操作，回收空间

      7.1 alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__766916574_0;

SQL> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__766916574_0;

alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__766916574_0

*

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace SYSAUX

       因为sysaux的空间不够，可以考虑增加一个文件1G的SYSAUX表空间，等空间有后可以考虑删除这个新增加的文件。

         7.2 alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN ;

这样类似可以删除其它一些表

如truncate table WRH$_SQLSTAT;

truncate table WRH$_LATCH;

等空间有了后，可以考虑调整awr参数。

        执行完MOVE操作后，需要对索引进行重建。同理，对于分区索引，只能对分区的单个索引进行重建，而不能总体重建：

ALTER INDEX WRH$_ACTIVE_SESSION_HISTORY_PK REBUILD PARTITION 分区名称;

需要注意的是，可以在以上SQL后加上“UPDATE GLOBAL INDEXES”子句让全局索引不失效。

8、调整AWR相关参数

        如果AWR信息占用过大，那么可以通过设置AWR的保留时间来减小AWR信息的存储空间。通过如下的SQL语句可以获取AWR的保留时间：

SELECT * FROM DBA_HIST_WR_CONTROL;

        通过如下的SQL语句可以设置AWR信息的保留时间为8天（8*24*60），每隔1小时收集一次AWR信息：

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>60, RETENTION=>8*24*60);

        需要注意的是，在Oracle 11g中，AWR默认保留8天，但原来设置成15天30分钟。

  9、统计信息的调整

      如果统计信息占用空间过大，那么可以修改统计信息的保留时间。统计信息默认保留31天，过期的统计信息会自动被删除。

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;  --查询统计信息的保留时间

EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);  --设置统计信息的保留时间

若发现统计信息占用了SYSAUX上的大量空间，则可以考虑使用DBMS_STATS.PURGE_STATS过程实施清理。

  10、最后表空间也下来了，也没有出现大量日志了

附一些有用的SQL语句

以下的SQL语句对于诊断SYSAUX表空间的占用情况非常有用：

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_IND_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_AUX_HISTORY;

SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_OPR;

以下SQL可以查询到无效的ASH信息：

SELECT COUNT(*)

  FROM SYS.WRH$_ACTIVE_SESSION_HISTORY A

 WHERE NOT EXISTS (SELECT 1

          FROM SYS.WRM$_SNAPSHOT B

         WHERE A.SNAP_ID = B.SNAP_ID

           AND A.DBID = B.DBID

           AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER);