ORA-13541修改AWR保留策略

转载至http://blog.itpub.net/29487349/viewspace-1093820/

在检查数据库时发现SYSAUX表空间使用率过高，80G+的空间还剩余1G多，想重新调整AWR快照的保留时间，来自动清理一些数据。下面是调整过程：

SQL> select * from v$version;   BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE    11.2.0.4.0      Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 – Production SQL> select * from dba_hist_wr_control;         DBID SNAP_INTERVAL        RETENTION            TOPNSQL ---------- -------------------- -------------------- ---------- 2649397625 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

 

设置AWR快照保留时长为两天

 

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>2880) BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>2880); END;   * ERROR at line 1: 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     SQL>

查看错误信息

[oracle@oradb1 ~]$ 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.

 

查看当前基线大小

SQL> 1   1* SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline SQL> /         DBID BASELINE_NAME        BASELINE_TYPE MOVING_WINDOW_SIZE ---------- -------------------- ------------- ------------------ 2649397625 SYSTEM_MOVING_WINDOW MOVING_WINDOW                  8   SQL>

 

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

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

 

 

调整基线大小：

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(2)   PL/SQL procedure successfully completed.   SQL>

 

再次执行

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>2880);   PL/SQL procedure successfully completed.   SQL> col SNAP_INTERVAL for a20 SQL> col RETENTION for a20 SQL> select * from dba_hist_wr_control;         DBID SNAP_INTERVAL        RETENTION            TOPNSQL ---------- -------------------- -------------------- ---------- 2649397625 +00000 01:00:00.0    +00002 00:00:00.0    DEFAULT   SQL>

 

附：2天时间感觉太短，打算再改为8天时，先调整基数时报错了。如下

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(8) BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(8); END;   * ERROR at line 1: ORA-13541: system moving window baseline size (691200) greater than retention (172800) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 686 ORA-06512: at line 1

 

提示基数大于172800（2*24*60*60）这个值，查看官网关于这个参数设置，得知，基数值必须小于或等于AWR快照所设置的保留值，所以需先设置AWR保留时长。

The window size must be less than or equal to the AWR retention setting. If the window size needs to be greater than the retention setting, theMODIFY_SNAPSHOT_SETTINGS Procedures can be used to adjust the retention setting. A moving window can be set to a maximum of 13 weeks.

 

基数最大值13周，也就是13*7=91天，下面我们做一下测试（将保留时间设置为14周，修改移动窗口基线大小为92天（13*7+1），报错：

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>211680);   PL/SQL procedure successfully completed.   SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(92); BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(92); END;   * ERROR at line 1: ORA-13539: invalid input for modify baseline window size (window_size, 92) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 686 ORA-06512: at line 1     SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(91);   PL/SQL procedure successfully completed.   SQL>

 

在做数据库的改动之前，尤其生产数据库，最好查看相关参数设置的作用、危险系数及限制，这样可以让我们更好的管理数据库。