在Oracle Database 11g中配置AWR快照保留时间的时候,你可能会遇到如题所示的问题。
具体如下:
当前数据库版本:
Oracle PL/SQL
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>
1
2
3
4
5
6
7
8
9
10
11
SQL>select*fromv$version;
BANNER
--------------------------------------------------------------------------------
OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction
PL/SQLRelease11.2.0.4.0-Production
CORE11.2.0.4.0Production
TNSforLinux:Version11.2.0.4.0-Production
NLSRTLVersion11.2.0.4.0-Production
SQL>
查看当前快照保留时间:
Oracle PL/SQL
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
1200000000 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
SQL>
1
2
3
4
5
6
7
SQL>select*fromdba_hist_wr_control;
DBIDSNAP_INTERVALRETENTIONTOPNSQL
---------- -------------------- -------------------- ----------
1200000000+0000001:00:00.0+0000800:00:00.0DEFAULT
SQL>
可以看到当前快照的保留时间是【8】天
修改AWR快照保留时间,有两种方式:
1. exec dbms_stats.alter_stats_history_retention
2. dbms_workload_repository.modify_snapshot_settings
可以执行下【dbms_workload_repository.modify_snapshot_settings】修改保留时间为7天:
Oracle PL/SQL
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>7*24*60);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>7*24*60); END;
*
第 1 行出现错误:
ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800) ORA-06512: 在
"SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: 在 line 1
SQL>
1
2
3
4
5
6
7
8
9
10
11
12
SQL>execdbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>7*24*60);
BEGINdbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>7*24*60);END;
*
第1行出现错误:
ORA-13541:系统移动窗口基线大小(691200)大于保留时间(604800)ORA-06512:在
"SYS.DBMS_WORKLOAD_REPOSITORY",line174
ORA-06512:在"SYS.DBMS_WORKLOAD_REPOSITORY",line222
ORA-06512:在line1
SQL>
可以看到,报错了。
查看下报错中提到的:
1. 移动窗口基线大小
2. 保留时间
Oracle PL/SQL
SQL> select 691200/60/60/24 from dual;
691200/60/60/24
---------------
8
SQL> select 604800/60/60/24 from dual;
604800/60/60/24
---------------
7
SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL>select691200/60/60/24fromdual;
691200/60/60/24
---------------
8
SQL>select604800/60/60/24fromdual;
604800/60/60/24
---------------
7
SQL>
也就是说,我们期望的保留时间7天,小于,移动窗口基线大小8天。
因此,上面的修改操作失败。
所以:
Oracle PL/SQL
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
1200000000 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
SQL>
1
2
3
4
5
6
7
SQL>select*fromdba_hist_wr_control;
DBIDSNAP_INTERVALRETENTIONTOPNSQL
---------- -------------------- -------------------- ----------
1200000000+0000001:00:00.0+0000800:00:00.0DEFAULT
SQL>
【retention】的8天,还是没有被改掉。
看看我们系统的移动窗口时间:
Oracle PL/SQL
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------------------------------------------------------- ------------- ------------------
1200000000 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
SQL>
1
2
3
4
5
6
7
SQL>SELECTdbid,baseline_name,baseline_type,moving_window_sizefromdba_hist_baseline;
DBIDBASELINE_NAMEBASELINE_TYPEMOVING_WINDOW_SIZE
---------- ---------------------------------------------------------------- ------------- ------------------
1200000000SYSTEM_MOVING_WINDOWMOVING_WINDOW8
SQL>
移动窗口时间是八天。
修改移动窗口的基线时间
Oracle PL/SQL
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------------------------------------------------------- ------------- ------------------
1200000000 SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
SQL>
SQL> exec dbms_workload_repository.modify_baseline_window_size(7);
PL/SQL 过程已成功完成。
SQL> SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;
DBID BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ---------------------------------------------------------------- ------------- ------------------
1200000000 SYSTEM_MOVING_WINDOW MOVING_WINDOW 7
SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL>SELECTdbid,baseline_name,baseline_type,moving_window_sizefromdba_hist_baseline;
DBIDBASELINE_NAMEBASELINE_TYPEMOVING_WINDOW_SIZE
---------- ---------------------------------------------------------------- ------------- ------------------
1200000000SYSTEM_MOVING_WINDOWMOVING_WINDOW8
SQL>
SQL>execdbms_workload_repository.modify_baseline_window_size(7);
PL/SQL过程已成功完成。
SQL>SELECTdbid,baseline_name,baseline_type,moving_window_sizefromdba_hist_baseline;
DBIDBASELINE_NAMEBASELINE_TYPEMOVING_WINDOW_SIZE
---------- ---------------------------------------------------------------- ------------- ------------------
1200000000SYSTEM_MOVING_WINDOWMOVING_WINDOW7
SQL>
再来改AWR的保留时间:
Oracle PL/SQL
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
1200000000 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
SQL>
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>7*24*60);
PL/SQL 过程已成功完成。
SQL>
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ----------
1200000000 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL>select*fromdba_hist_wr_control;
DBIDSNAP_INTERVALRETENTIONTOPNSQL
---------- -------------------- -------------------- ----------
1200000000+0000001:00:00.0+0000800:00:00.0DEFAULT
SQL>
SQL>execdbms_workload_repository.modify_snapshot_settings(interval=>60,retention=>7*24*60);
PL/SQL过程已成功完成。
SQL>
SQL>select*fromdba_hist_wr_control;
DBIDSNAP_INTERVALRETENTIONTOPNSQL
---------- -------------------- -------------------- ----------
1200000000+0000001:00:00.0+0000700:00:00.0DEFAULT
SQL>
就没问题了。
————————————
Done。
Post Views:
273
感谢支持
赏