【AWR】ORA-13541修改AWR保留策略

最新推荐文章于 2023-03-02 17:03:14 发布

crygaj7140

最新推荐文章于 2023-03-02 17:03:14 发布

阅读量137

点赞数

在检查数据库时发现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

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

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

172800/60/60/24

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

调整基线大小：

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>