oracle moving window size与 AWR retention period关系
来源:互联网 发布:使命召唤审判枪数据 编辑:程序博客网 时间:2024/06/10 08:33
转自: http://tomszrp.itpub.net/post/11835/494147
晚上在做11gR1的一个awrrpt报告时,顺便想调整一下AWR snapshot的保留时间,结果遇到了ORA-13541这样的错误.下面是这个问题的发生和解决过程.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE11.1.0.6.0Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL>
检查当前系统的保留时间为8天,1小时采样一次.
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------- ---------------------- ----------
4096851118 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
我想调整为7天,每隔30分钟采样一次.执行如下脚本
SQL> begin
2 dbms_workload_repository.modify_snapshot_settings (
3 interval => 30,
4 retention => 10080,
5 topnsql => 100
6 );
7 end;
8 /
begin
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: 在 line 2
结果遇到了这个错误.查询ORA-13541的错误信息如下
ORA-13541: system moving window baseline size (string) greater than retention (string)
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.
在看看上面的个数字基线大小 (691200) 和保留时间 (604800)
基线大小691200
SQL> select 691200/60/60/24 from dual;
691200/60/60/24
---------------
8
是8天的时间
保留时间604800
SQL> select 604800/60/24/60 from dual;
604800/60/24/60
---------------
7
是7天,正是我要设置的时间
检查当前的移动窗口基线大小
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------ ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
正是上面的8天对应的数字(8*60*60*24=691200).
知道了问题的原因,修改起来就容易了.调用如下过程修改移动窗口baseline size为7天
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);
PL/SQL 过程已成功完成。
然后继续上面的修改操作
SQL> begin
2 dbms_workload_repository.modify_snapshot_settings (
3 interval => 30,
4 retention => 7*24*60,
5 topnsql => 100
6 );
7 end;
8 /
PL/SQL 过程已成功完成。
SQL>
OK,修改成功了,再检查一下看看结果是不是正确
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------- --------------------- ----------
4096851118 +00000 00:30:00.0 +00007 00:00:00.0 100
SQL>
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ----------------------- ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7
SQL>
好了,到这里问题就解决了!
The window size must be set to a value that is equal to or less than the value of the AWR retention setting.
晚上在做11gR1的一个awrrpt报告时,顺便想调整一下AWR snapshot的保留时间,结果遇到了ORA-13541这样的错误.下面是这个问题的发生和解决过程.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE11.1.0.6.0Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL>
检查当前系统的保留时间为8天,1小时采样一次.
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------- ---------------------- ----------
4096851118 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
我想调整为7天,每隔30分钟采样一次.执行如下脚本
SQL> begin
2 dbms_workload_repository.modify_snapshot_settings (
3 interval => 30,
4 retention => 10080,
5 topnsql => 100
6 );
7 end;
8 /
begin
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 89
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: 在 line 2
结果遇到了这个错误.查询ORA-13541的错误信息如下
ORA-13541: system moving window baseline size (string) greater than retention (string)
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.
在看看上面的个数字基线大小 (691200) 和保留时间 (604800)
基线大小691200
SQL> select 691200/60/60/24 from dual;
691200/60/60/24
---------------
8
是8天的时间
保留时间604800
SQL> select 604800/60/24/60 from dual;
604800/60/24/60
---------------
7
是7天,正是我要设置的时间
检查当前的移动窗口基线大小
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------ ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
正是上面的8天对应的数字(8*60*60*24=691200).
知道了问题的原因,修改起来就容易了.调用如下过程修改移动窗口baseline size为7天
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);
PL/SQL 过程已成功完成。
然后继续上面的修改操作
SQL> begin
2 dbms_workload_repository.modify_snapshot_settings (
3 interval => 30,
4 retention => 7*24*60,
5 topnsql => 100
6 );
7 end;
8 /
PL/SQL 过程已成功完成。
SQL>
OK,修改成功了,再检查一下看看结果是不是正确
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------- --------------------- ----------
4096851118 +00000 00:30:00.0 +00007 00:00:00.0 100
SQL>
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ----------------------- ------------- ------------------
4096851118 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7
SQL>
好了,到这里问题就解决了!
The window size must be set to a value that is equal to or less than the value of the AWR retention setting.
0 0
- oracle moving window size与 AWR retention period关系
- oracle awr设置与生成
- Oracle AWR管理与维护
- oracle 之 Undo Retention
- Oracle AWR报告生成与查看
- oracle awr报告生成与分析
- 禁用与卸载Oracle AWR特性
- 禁用与卸载Oracle AWR特性
- Oracle AWR报告生成与查看
- Oracle AWR
- Oracle AWR
- Oracle AWR
- Oracle Awr
- ORACLE AWR
- oracle awr
- oracle awr
- RMAN Recovery Window retention policy tips
- @Retention
- Android:网络:多线程断点下载
- oracle 11g新特性Flashback data archive
- Python-OpenCV 处理图像(八):图像二值化处理
- Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX
- MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' 解决方法
- oracle moving window size与 AWR retention period关系
- Spring定时任务的几种实现
- oracle Health Monitor
- Linux系统手动安装rzsz 软件包
- 我来学习excel------sumifs函数
- oracle 行链接与行迁移
- pl/sql集合类型
- windows下MongoDB的安装
- 批量执行 bulk collect与forall用法