执行AWR报ORA-13541

背景：运行AWR收集数据库统计数据,需要修改AWR产生snapshot的频率和保留策略，在SQL*PLUS下时，
运行exec dbms_workload_repository.modify_snapshot_settings(interval=>10,retention=>2*24*60)

出错信息：
ORA-13541: system moving window baseline size (691200) greater than retention(172800)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 1

解决办法：
1.查看当前的移动窗口基线值(系统默认值)：
SQL> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

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

2.修改移动窗口的基线值：
SQL> exec dbms_workload_repository.modify_baseline_window_size(2);

PL/SQL procedure successfully completed.

3.重新执行修改的保留策略和频率：
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>10,retention=>2*24*60);

PL/SQL procedure successfully completed.

4.检验修改结果是否正确：
SQL> COL SNAP_INTERVAL FOR A20
SQL> COL RETENTION FOR A20
SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
1285342393 +00000 00:10:00.0 +00002 00:00:00.0 DEFAULT

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

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- -------------------- ------------- ------------------
1285342393 SYSTEM_MOVING_WINDOW MOVING_WINDOW 2

5.问题解决。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26121829/viewspace-1053307/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26121829/viewspace-1053307/