在檢查資料庫時發現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>

在做資料庫的改動之前，尤其生產資料庫，最好檢視相關引數設定的作用、危險係數及限制，這樣可以讓我們更好的管理資料庫。

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

相關文章