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>

 

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


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值