teamsun 告oracle,Oracle Database：修改AWR保留时间遇到的问题：ORA-13541: 系统移动窗口基线大小 (691200) 大于保留时间 (604800)...

在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

感谢支持

赏