[AWR] ORA-13541修改AWR保留策略



修改awr保存时间和频率报错

[oracle@node3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 22:00:20 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> begin 

   dbms_workload_repository.modify_snapshot_settings ( 

      interval => 30,       --30分钟收集一次

      retention => 7*24*60  --保存时间为7天

   );end;  

  8  /

begin

*

ERROR at line 1:

ORA-13541: system moving window baseline size (691200) greater than retention

(604800)

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222

ORA-06512: at line 3

报错了！！！

1.查看错误信息

[oracle@node3 ~]$ oerr ora 13541

13541, 00000, "system moving window baseline size (%s) greater than retention (%s)"

// *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.

2.查看当前基线大小

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

     DBID  BASELINE_NAME BASELINE_TYPE      MOVING_WINDOW_SIZE

---------- ---------------------------------------------------- -----------

2029768178 SYSTEM_MOVING_WINDOW  MOVING_WINDOW8

计算出现错误时两个数字（如下所示，2是打算修改的数值）

SQL>  select 691200/60/60/24 from dual;

691200/60/60/24

---------------

      8

SQL> select 604800/60/60/24 from dual;   --要修改的值

604800/60/60/24

---------------

      7

基数值必须小于或等于AWR快照所设置的保留值，所以需先设置AWR保留时长。 

3.调整基线大小

SQL>  exec dbms_workload_repository.modify_baseline_window_size(7);

PL/SQL procedure successfully completed.

4.再次执行一下语句

SQL> begin 

   dbms_workload_repository.modify_snapshot_settings ( 

      interval => 30, 

      retention => 10080

   );

  end;  

 /

PL/SQL procedure successfully completed.

5.查看快照保存时间

SQL>  select * from dba_hist_wr_control;

 

      DBID SNAP_INTERVAL       RETENTION           TOPNSQL

---------- ------------------- ------------------- ----------

2029768178 +00000 00:30:00.0   +00007 00:00:00.0   DEFAULT

可以看到30分钟收集一次，保存时间为7天。