导航

  修改awr生成报告时间间隔和保存时间时报错,由默认的每小时生成,保存8天修改为每半个小时生成一次,保存5天:

SQL>  exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60); END;
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (432000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 1

  根据报错,系统的window baseline size大于了设定的窗口基线大小。

  查看系统基线的由来:

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

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

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

691200/60/60/24
---------------
              8
SQL> select 432000/60/24/60 from dual;

432000/60/24/60
---------------
              5

  可以看到保存8天的基线是691200,而保存5天的基线是432000,刚才的报错是说系统的基线大于了要设定的基线,因此这里需要将系统的基线改小:

SQL> exec dbms_workload_repository.modify_baseline_window_size(5);

PL/SQL procedure successfully completed.

  然后再修改awr快照配置,可以看到修改成功:

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

PL/SQL procedure successfully completed.

  进行检查:

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL             RETENTION                 TOPNSQL
---------- ------------------------- ------------------------- ----------
1466421350 +00000 00:30:00.0         +00005 00:00:00.0         DEFAULT

  检查当前的移动窗口基线大小:

SQL> col BASELINE_NAME for a25
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

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