How to clean up SYSAUX manually

ORACLE的辅助表空间SYSAUX暴增解决之道

一、问题描述

最近一个项目上，在快照保留期为 7 天的oracle数据库遇到 sysaux 增长到 30G 左右的问题，在检查后发现最大的表如下：

• WRH$SQL_BIND_METADAT、

• WRH$ACTIVE_SESSION_HISTORY

查询SYSAUX表空间大对象占用的语句：

SELECT *
      FROM (  SELECT bytes / 1024 / 1024 dim_Mb, segment_name, segment_type
                FROM dba_segments
               WHERE tablespace_name = 'SYSAUX'
            ORDER BY dim_Mb DESC)
     WHERE ROWNUM < 5;

原因分析

驻留在 SYSAUX 表空间中的服务器可管理性 (SM) 的两个组件可能会导致此问题。涉及的组件是自动工作负载存储库 (AWR) 和优化器统计历史记录 (OPTSTAT)。这两个组件都有与其数据关联的保留期，MMON 进程作为计划维护任务的一部分每晚运行，以清除超过这些保留期的数据。从 11G 版本开始，MMON 清除过程被限制在每个清除的时间窗口内；如果超出此窗口，则清除停止并将 ORA-12751 错误写入 m000 跟踪文件。

以 WRH$ 开头的表中的 AWR 数据，可能的原因是许多表已分区。作为 MMON 进程的一部分，为这些表创建新分区。不幸的是，分区拆分过程似乎是清除过程中的最后一项任务。由于后面的分区未拆分，因此它们最终包含更多数据。这会导致清除过程中的分区修剪，从而降低效率。

AWR 数据中的第二个组成部分是 WRM$ 表，它们实际上是元数据，在我的实践中，即使它们很大，也可以很容易地被 Oracle 直接修复……当然，这是当它们的子 WRH$ 表数据已修复的情况下。

对于以 WRI$ 开头的表中的 OPTSTAT 数据，问题也更可能与保存在表中的数据量有关。WRI$ 表保存数据库中所有段的历史统计数据，保存时间由统计历史保留期指定。因此，如果数据库包含大量具有较长保留期（比如 30 天）的表，那么在指定窗口内，清除过程在尝试清除所有旧统计信息时出现问题。

 

检查保留期的命令：

SELECT retention FROM dba_hist_wr_control;

如果我们检查在 7 天保留期内可用的当前 snap ID，则最小值为 49032，最大值为 49210，但 WRH$ACTIVE_SESSION_HISTORY 和 WRH$SQL_BIND_METADATA 表的 snap ID 低于当前最小 snap ID。

 

SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;
​
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
49032 49210
​
SQL> select min(snap_id),MAX(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;
​
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
45912 49210
​
SQL> select min(snap_id),MAX(snap_id) from WRH$_EVENT_HISTOGRAM;
​
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
20121 49210
​
SQL> select min(snap_id),MAX(snap_id) from WRH$_SQL_BIND_METADATA;
​
MIN(SNAP_ID) MAX(SNAP_ID)

二、解决方案

解决方案是检查现有分区，然后强制拆分分区，并手动删除旧快照 ID

1、检查现有分区

SQL> select table_name,partition_name from dba_tab_partitions where
table_name = 'WRH$_ACTIVE_SESSION_HISTORY'; （检查现有分区）
​
TABLE_NAME PARTITION_NAME
————————— ———————————
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2779876350_20121
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN

2、使用命令强制拆分分区操作

SQL> alter session set "_swrf_test_action" = 72;

 

3、分区检查

SQL> select table_name,partition_name from dba_tab_partitions where
table_name = ‘WRH$_ACTIVE_SESSION_HISTORY’; ( checking partitions again)
​
TABLE_NAME PARTITION_NAME
—————————— ——————————
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2779876350_20121
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2779876350_49142
WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN
SQL>

 

4、删除快照并检查

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 45912,46100,2779876350); 
//lower_snap_id，high_snap_id,db_id
​
​
SQL> select min(snap_id),MAX(snap_id) from WRH$_ACTIVE_SESSION_HISTORY;
​
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
49067 49235
SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;
​
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
49067 49235
​
SQL> select min(snap_id),MAX(snap_id) from WRH$_SQL_BIND_METADATA;
​
MIN(SNAP_ID) MAX(SNAP_ID)
———— ————
49067 49285
​
Share this: