想透過 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS 來修改AWR snapshot 保留的時間，但是調整的時候遇到ORA-13541錯誤

過程如下：

C:\Users\aaaa>sqlplus yang/yang as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 8月 4 20:51:28 2011

連線到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@rac1>col SNAP_INTERVAL for a20

sys@rac1>col RETENTION for a20

檢查當前系統的保留時間為8天,1小時取樣一次.

sys@rac1>select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

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

1284003062 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

sys@rac1>exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 24*60 );

BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 24*60 ); END;

*

第 1 行出現錯誤:

ORA-13541: 系統移動視窗基線大小 (691200) 大於保留時間 (86400)

ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 174

ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 222

ORA-06512: 在 line 1

這裡 691200 ，86400都是以秒為單位的。

SQL> !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.

sys@rac1>select 691200/60/60/24 from dual;

691200/60/60/24

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

8

sys@rac1>select 86400/(24*24*60) from dual;

86400/(24*24*60)

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

2.5

檢視系統的當前的MOVING_WINDOW_SIZE

sys@rac1> select dbid,baseline_name,baseline_type,moving_window_size from dba_hist_baseline;

DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE

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

1284003062 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8

修改其大小為2 ，即兩天。

sys@rac1> exec dbms_workload_repository.modify_baseline_window_size(2);

PL/SQL 過程已成功完成。

sys@rac1>exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 5*24*60 );

PL/SQL 過程已成功完成。

sys@rac1>select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

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

1284003062 +00000 00:30:00.0 +00005 00:00:00.0 DEFAULT

sys@rac1>exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>30,retention => 2*24*60 );

PL/SQL 過程已成功完成。

sys@rac1>select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL

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

1284003062 +00000 00:30:00.0 +00002 00:00:00.0 DEFAULT

sys@rac1>

ORA-13541: 系統移動視窗基線大小 (691200) 大於保留時間 (86400)

相關文章