11gR1修改awr快照参数遭遇ORA-13541错误

晚上在做11gR1的一个awrrpt报告时,顺便想调整一下AWR snapshot的保留时间,结果遇到了ORA-13541这样的错误.下面是这个问题的发生和解决过程.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE	11.1.0.6.0	Production

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> 

检查当前系统的保留时间为8天,1小时采样一次.

SQL> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL         RETENTION              TOPNSQL
---------- --------------------- ---------------------- ----------
4096851118 +00000 01:00:00.0     +00008 00:00:00.0      DEFAULT

我想调整为7天，每隔30分钟采样一次.执行如下脚本

SQL> begin
  2        dbms_workload_repository.modify_snapshot_settings (
  3          interval => 30,
  4          retention => 10080,
  5          topnsql => 100
  6        );
  7  end;
  8  /
begin
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: 在 line 2

结果遇到了这个错误.查询ORA-13541的错误信息如下

ORA-13541: system moving window baseline size (string) greater than retention (string)  
Cause:     The system moving window baseline size must be less than the retention setting. The specified window size 
           or retention violate this.  
Action: Check the moving window baseline size or retention. 

在看看上面的个数字基线大小 (691200) 和保留时间 (604800)

基线大小691200
SQL> select 691200/60/60/24 from dual;

691200/60/60/24
---------------
              8
是8天的时间

保留时间604800           
SQL> select 604800/60/24/60 from dual;

604800/60/24/60
---------------
              7

是7天,正是我要设置的时间

检查当前的移动窗口基线大小
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

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

正是上面的8天对应的数字(8*60*60*24=691200).

知道了问题的原因,修改起来就容易了.调用如下过程修改移动窗口baseline size为7天
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);

PL/SQL 过程已成功完成。

然后继续上面的修改操作

SQL> begin
  2        dbms_workload_repository.modify_snapshot_settings (
  3          interval => 30,
  4          retention => 7*24*60,
  5          topnsql => 100
  6        );
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL>
OK,修改成功了,再检查一下看看结果是不是正确

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL         RETENTION             TOPNSQL
---------- --------------------- --------------------- ----------
4096851118 +00000 00:30:00.0     +00007 00:00:00.0            100

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

      DBID BASELINE_NAME           BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ----------------------- ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW    MOVING_WINDOW                  7

SQL> 

好了,到这里问题就解决了!