一佳互联
虚拟化容器,大数据,DBA,中间件,监控。

oracle sysaux表空间文件过大的处理方法

31 12月
作者:admin|分类:DBA运维

一般来讲除开业务数据存放的表空间，DBA要着重关注SYSTEM,SYSAUX,UNDO,TEMP表空间，SYSTEM表空间的大小一般是衡定的，UNDO和TEMP表空间的大小由数据库的业务情况决定，而SYSAUX表空间在默认条件下你如果不做任何配置，随着时间的推移，会膨胀的越来越大！SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等，个人认为，如果你的SYSAUX表空间大小超过2G，那么该考虑让他减肥了！ 

一：使用下列语句查询表空间使用率 

  1. SELECT * FROM ( 

  2. SELECT D.TABLESPACE_NAME, 

  3.         SPACE || 'M' "SUM_SPACE(M)", 

  4.         BLOCKS "SUM_BLOCKS", 

  5.         SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 

  6.         ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 

  7.            "USED_RATE(%)", 

  8.         FREE_SPACE || 'M' "FREE_SPACE(M)" 

  9.    FROM (  SELECT TABLESPACE_NAME, 

  10.                   ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 

  11.                   SUM (BLOCKS) BLOCKS 

  12.              FROM DBA_DATA_FILES 

  13.          GROUP BY TABLESPACE_NAME) D, 

  14.         (  SELECT TABLESPACE_NAME, 

  15.                   ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 

  16.              FROM DBA_FREE_SPACE 

  17.          GROUP BY TABLESPACE_NAME) F 

  18.   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 

  19.  UNION ALL                                                           

  20.  SELECT D.TABLESPACE_NAME, 

  21.         SPACE || 'M' "SUM_SPACE(M)", 

  22.         BLOCKS SUM_BLOCKS, 

  23.         USED_SPACE || 'M' "USED_SPACE(M)", 

  24.         ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 

  25.         NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 

  26.    FROM (  SELECT TABLESPACE_NAME, 

  27.                   ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 

  28.                   SUM (BLOCKS) BLOCKS 

  29.              FROM DBA_TEMP_FILES 

  30.          GROUP BY TABLESPACE_NAME) D, 

  31.         (  SELECT TABLESPACE_NAME, 

  32.                   ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 

  33.                   ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 

  34.              FROM V$TEMP_SPACE_HEADER 

  35.          GROUP BY TABLESPACE_NAME) F 

  36.   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 

  37.  ORDER BY 1)  

  38.  WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP'); 

image.png


二：查询SYSAUX表空间内各个分类项目占存储空间的比重，很明显可以看出来AWR快照占用了11G左右的空间，统计信息为200M左右，同时数据库关闭了审计audit_trail，所以审计表aud$不占空间 

  1. SELECT occupant_name "Item", 

  2.        space_usage_kbytes / 1048576 "Space Used (GB)", 

  3.        schema_name "Schema", 

  4.        move_procedure "Move Procedure" 

  5.   FROM v$sysaux_occupants 

  6. ORDER BY 1 


image.png


、三：只要处理AWR的快照信息就可以将存储空间清理出来，修改统计信息的保持时间，

          默认为31天，这里修改为7天，过期的统计信息会自动被删除

  1. SQL> select dbms_stats.get_stats_history_retention from dual; 

  2.  

  3. GET_STATS_HISTORY_RETENTION 

  4. --------------------------- 

  5.                          31 


  1. SQL> exec dbms_stats.alter_stats_history_retention(7);       

  2. PL/SQL procedure successfully completed. 


  1. SQL> select dbms_stats.get_stats_history_retention from dual; 

  2.  

  3. GET_STATS_HISTORY_RETENTION 

  4. --------------------------- 

  5.                           7 



四：检查快照采样间隔为每30分钟一次，保留时间为8天，当然这个值从Oracle 11g 开始，默认值就为30分钟一次，如果没有特殊要求可以不做修改。

修改AWR快照的保存时间为7天(7*24*60),每小时收集一次，也可以通过EM界面查看和修改.


  1. SQL> begin 

  2.          dbms_workload_repository.modify_snapshot_settings ( 

  3.             interval => 60, 

  4.             retention => 10080, 

  5.             topnsql => 100 

  6.           ); 

  7. end; 

五：删除AWR快照，再次查看SYSAUX表空间使用率,最后表空间使用率降低为38.42%。

       注意: 这是个大坑此过程通过删除表数据

  1. select min(snap_id),max(snap_id) from dba_hist_snapshot;//查询最最小和最大快照ID 


        注意: 这是个大坑此过程通过删除表数据，数据量比较大，会将undo与归档空间给占满，导致oracle挂掉。

          你要删除的快照信息不多时，仍然是首选该过程处理。如下命令。


  1. begin 

  2.      dbms_workload_repository.drop_snapshot_range( 

  3.        low_snap_id => 10758, 

  4.       high_snap_id => 10900, 

  5.       dbid => 387090299); 

  6. end; 

官网说明如下:

  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);

六.大坑描述与分析


根据用DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE来删除快照耗时非常久，此时我也感觉到困惑，生产库这样子操作很危险，于是就继续跟踪查找问题，最后发现在执行该过程时后台实际运行的都是delete基表的动作，这下大家明白了吧，delete大表呀， undo表空间够不够大，归档日志切换频繁，导致归档目录空间不足。多么可怕的大坑啊！


七.另外一种处理方式，简单粗暴，但很简单实用，思路是按照上面操作存储过程的方法进行改量，采取手动备份基表部分数据，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提供的存储过程也会有大坑，还是要了解清楚，或者在测试环境测试过方可在生产上执行，否则还真会带来不少麻烦


更多文章推荐

历史上的今天
12月
31
浏览864 评论0
返回
目录 返回
首页
MySql5.7 表字段插入与更新中有特殊字符的处理方法 oracle 分区表删除分区或是删除分区表数据时,导致索引失效
↑