sysaux表空间清理，小心有坑

Sysaux】sysaux表空间清理，小心有坑

原创 Oracle 作者：yhdmy 时间：2017-03-13 11:50:02 9046 1 删除编辑
一、问题描述
        SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等，而如果SYSAUX表空间在默认条件下你如果不做任何配置，随着时间的推移，会膨胀的越来越大。经过几次的不断扩展增加SYSAUX表空间，目前已经24G以上了，所以是该考虑减肥的时候了。
 
二、sysaux表空间分析与处理
1.检查表空间使用情况，发现sysaux表空间使用空间已达24G

  1. SYS@orcl1 > set lines 200
  2. SYS@orcl1 > Select Tablespace_Name,
  3. Sum_m,
  4. Max_m,
  5. Count_Blocks Free_Blk_Cnt,
  6. Sum_Free_m,
  7. To_Char(100 * Sum_Free_m / Sum_m, '99.9999')|| '%' As Pct_Free,
  8. 100 - To_Char(100 * Sum_Free_m / Sum_m,'99.9999') || '%' As Pct_used
  9. From (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 As Sum_m
  10. From Dba_Data_Files
  11. Group By Tablespace_Name)
  12. Left Join
  13. (Select Tablespace_Name As Fs_Ts_Name,
  14. Max(Bytes) / 1024 / 1024 As Max_m,
  15. Count(Blocks) As Count_Blocks,
  16. Sum(Bytes / 1024 / 1024) As Sum_Free_m
  17. From Dba_Free_Space
  18. Group By Tablespace_Name)
  19. On Tablespace_Name = Fs_Ts_Name
  20. ORDER BY Sum_Free_m / Sum_m ;
TABLESPACE_NAME                     SUM_M      MAX_M FREE_BLK_CNT SUM_FREE_M PCT_FREE  PCT_USED
------------------------------ ---------- ---------- ------------ ---------- --------- -----------------------------------------
SYSTEM                              11450         53            2      53.75    .4694% 99.5306%
SYSAUX                              24280        628          330  1138.1875   4.6878% 95.3122%
USERS                                 230         46            2    46.8125  20.3533% 79.6467%
... ...

2.查看SYSAUX表空间内详细占存储空间的比重信息，AWR快照占用了近21G多空间，其他信息占空间都非常小
 

  1. col Item for a30
  2. col Schema for a20
  3. set lines 200
  4. SYS@orcl1 > SELECT occupant_name"Item",
  5.  round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
  6.  schema_name "Schema",
  7.  move_procedure "MoveProcedure"
  8.  FROM v$sysaux_occupants
  9.  ORDER BY 2 Desc;
Item                      Space Used (GB) Schema     MoveProcedure
------------------------- --------------- ---------- ----------------------------------------------------------------
SM/AWR                             21.377 SYS
SM/ADVISOR                           .526 SYS
SM/OPTSTAT                           .188 SYS
XDB                                  .124 XDB        XDB.DBMS_XDB.MOVEXDB_TABLESPACE
EM                                   .085 SYSMAN     emd_maintenance.move_em_tblspc
SDO                                  .073 MDSYS      MDSYS.MOVE_SDO
... ...

3.根据以上查询情况得出，只要处理AWR的快照信息就可以将存储空间清理出来，检查快照采样间隔为每30分钟一次，保留时间为8天，当然这个值从Oracle 11g 开始，默认值就为30分钟一次，如果没有特殊要求可以不做修改。

  1. SYS@orcl1 > col SNAP_INTERVAL for a40
  2. SYS@orcl1 > col RETENTION for a30
  3. SYS@orcl1 > select * from dba_hist_wr_control;
  5.       DBID SNAP_INTERVAL RETENTION TOPNSQL
  6. ---------- -------------------- --------------------------------------------------------------------------- ----------
  7. 1361686490 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT
4.由于快照采样时间过短，需要将快照采样间隔调整为1小时1次
 

  1. SYS@orcl1 > begin
  2.   2 dbms_workload_repository.modify_snapshot_settings(
  3.   3 interval => 60
  4.   4 );
  5.   5 end;
  6.   6 /
  8. PL/SQL procedure successfully completed.
  10. SYS@orcl1 > col SNAP_INTERVAL for a40
  11. SYS@orcl1 > col RETENTION for a30
  12. SYS@orcl1 > select * from dba_hist_wr_control;
  14.       DBID SNAP_INTERVAL RETENTION TOPNSQL
  15. ---------- -------------------------------------------------------------------------------------------- ----------
  16. 1361686490 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
5.检查最小和最大快照ID
 

  1. SYS@orcl1 > select min(snap_id),max(snap_id) from dba_hist_snapshot;
  3. MIN(SNAP_ID) MAX(SNAP_ID)
  4. ------------ ------------
  5.        37091 37680
6.按照官网介绍是可以用dbms_workload_repository包中drop_snapshot_range存储过程来删除快照，但是在此处，由于环境中存在着大量快照信息，会有个大坑，需要DBA们take care！！！你要删除的快照信息不多时，仍然是首选该过程处理。如下命令。

  1. Syntax:
  2. DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
  3.    low_snap_id IN NUMBER,
  4.    high_snap_id IN NUMBER
  5.    dbid IN NUMBER DEFAULT NULL);
  7. Examples:
  8. EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(37091, 37679);

7.大坑描述与分析
根据用DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE来删除快照耗时非常久，此时我也感觉到困惑，生产库这样子操作很危险，于是就继续跟踪查找问题，最后发现在执行该过程时后台实际运行的都是delete基表的动作，这下大家明白了吧，delete大表呀， undo表空间够不够大，归档日志切换频繁，导致归档目录空间不足。多么可怕的大坑啊！
 
8.另外一种处理方式，简单粗暴，但很简单实用，思路是按照上面操作存储过程的方法进行改量，采取手动备份基表部分数据，truncate基表，再将备份部分数据插入回基表。经验证该方法很高效而且成功，顺便提一句，truncate表同时索引空间也被清空了。
 
三、高效处理方法
1.检查最小和最大快照ID，根据快照ID来定们边界值，以便找到要删除和保留内容

  1. SYS@orcl1 > select min(snap_id),max(snap_id) from dba_hist_snapshot;
  3. MIN(SNAP_ID) MAX(SNAP_ID)
  4. ------------ ------------
  5.        37091 37680

2.查找到那些占用sysaux表空间的基表，按照大小进行排序
 

  1. select * from (select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc) where rownum<=10;

3.查表基本的组织结构发现WRH$表中都有snap_id字段，所以我们就用这个字段进行分界线处理。我们对占用空间第一的表进行处理，备份WRH$_ACTIVE_SESSION_HISTORY表保留数据到WRH$_ACTIVE_SESSION_HISTORY_B表。

  1. CREATE TABLE WRH$_ACTIVE_SESSION_HISTORY_B AS SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY WHERE SNAP_ID>37679 ;

4.验证WRH$_ACTIVE_SESSION_HISTORY_B表存储及包含数据
 

  1. SELECT COUNT(*) FROM WRH$_ACTIVE_SESSION_HISTORY_B;
5.清除源表WRH$_ACTIVE_SESSION_HISTORY数据
 

  1. TRUNCATE TABLE WRH$_ACTIVE_SESSION_HISTORY;
6.将备份数据恢复至源表

  1. INSERT INTO WRH$_ACTIVE_SESSION_HISTORY SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY_B;
  2. COMMIT;
7.验证基表数据
 

  1. SELECT COUNT(*) FROM WRH$_ACTIVE_SESSION_HISTORY;
8.删除备份临时表
 

  1. drop table WRH$_ACTIVE_SESSION_HISTORY_B purge;
9.按照上面的操作方式，将其余占空间的WRH$_开头的表继续清除数据，最终还给我们一个干净的Sysaux表空间。
 

四、总结
在这次处理sysaux辅助表空间时，我们掌握了两种方法: （1）DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE 存储过程方式  （2）查出哪些基表占用空间大，进行手工备份与删除  。 在这次清理过程中，让我们感觉到使用ORACLE提供的存储过程也会有大坑，还是要了解清楚，或者在测试环境测试过方可在生产上执行，否则还真会带来不少麻烦。学习的道路是坎坷快乐的，但是为了人生更大的目标，需要努力。Where there is a will there is a way.
posted @ 2020-01-10 14:08  耀阳居士  阅读(642)  评论(0)  编辑  收藏  举报