修改AWR快照出现ORA-13541

在11gR2中AWR默认的配置是保留期为8天，快照每小时生成一次。

SQL> col SNAP_INTERVAL for a20
SQL> col RETENTION for a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
2197530720 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

现在想要改变AWR配置满足系统的需求

SQL> BEGIN
  2    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 7200, 
  3                   interval => 30, topnsql => 100);
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (345600) ORA-06512: 在
"SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: 在 line 2

出现ORA-13541: system moving window baseline size (691200) greater than retention (518400)错误，根据错误提示，系统移动窗口基线（Moving Window Baseline）是691200，即是8天。也就是说修改AWR保留期的值，跟此数值有关系，必须要大于此数值。

查看当前Moving Window Baseline的值

SQL> col BASELINE_NAME for a30
SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
2197530720 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  8

既然知道了原因，解决就很方便了，Modifying the Window Size of the Default Moving Window Baseline

BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
                   window_size => 5, 
                   dbid => 3310949047);
END;
/

其中要注意：window size必须设置为一个小于或等于AWR设置的保留值。

In this example, the optional dbid parameter specifies the database identifier is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value.

检查当前Moving Window Baseline的值，看是否修改的如你所愿

SQL> select dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

      DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
2197530720 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  5

继续之前的修改

SQL> BEGIN
  2    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 7200, 
  3                   interval => 30, topnsql => 100);
  4  END;
  5  /

PL/SQL 过程已成功完成。

修改完后查看当前AWR配置

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
2197530720 +00000 00:30:00.0    +00005 00:00:00.0           100

到此问题已解决。