How to modify the information collection frequency of AWR in Oracle

By default, AWR information collects awr information every one hours and remains for 7 days. This data is stored in the Sysaux tablespace and may result in insufficient sysaux tablespace

If the sysaux space is grossly inadequate, ORA-1683 and ORA-1688 errors will be reported in the Alter log, and it is recommended that the changes be collected every 4 hours and retain 3 days of data.

Description of the phenomenon:

Version: Oracle 11.0.7

The alter log was found to be the following error in the Oracle daily check:

Ora-1683:unable to extend index SYS. WRH$_LATCH_PK partition

wrh$_latch_4064376933_1546 by

128 in Tablespace Sysaux

Ora-1688:unable to extend table SYS. Wrh$_sysstat partition

wrh$_syssta_4064376933_1546 by

128 in Tablespace Sysaux

Mmon Flush encountered Sysaux out of the Space error (1688).

Alarm information:

Ora-1683:unable to extend index SYS. WRH$_LATCH_PK partition

wrh$_latch_4064376933_1546 by

128 in Tablespace Sysaux

Ora-1688:unable to extend table SYS. Wrh$_sysstat partition

wrh$_syssta_4064376933_1546 by

128 in Tablespace Sysaux

Mmon Flush encountered Sysaux out of the Space error (1688).

Reason Analysis:

By default, AWR information collects awr information every one hours and remains for 7 days. This data is stored in the Sysaux tablespace.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Processing process:

Currently, the current network is the default configuration, it is recommended to modify the collection every 4 hours and keep 3 days of data.

Modify the steps as follows:

1. Query the current settings:

Sql> select * from Dba_hist_wr_control;

Return results for example:

DBID Snap_interval RETENTION Topnsql

1 4068003495 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

The field Snap_interval represents the time interval, which is collected once every 1 hours awr

2. View the size of the moving_window_size:

Sql> SELECT baseline_name, Start_snap_time, End_snap_time,

Moving_window_size

From Dba_hist_baseline_details

3. Returns the result, if the value of the field moving_window_size is greater than the number of reserved days to be modified, you must perform

The following command is modified, and should be modified to 3:

Begin Dbms_workload_repository. Modify_baseline_window_size (window_size=>3);

End

4. Modify AWR settings:

BEGIN

Dbms_workload_repository.modify_snapshot_settings (INTERVAL =>240,retention => 3 * 24 * 60);

End;

Here, the parameter interval represents the collection of AWR information every few minutes. The parameter retention represents the 3 (day) *24 (hour/day) *60 (minutes/hour) minutes.

5. The system does not automatically delete information from the 4th day to the 7th day after modifying the awr retention days from 7 days to 3 days. You still need to delete the method manually, please refer to the following.

To delete some awr information, follow these steps:

Find dbid:

Sql> Select dbid from V$database

Return results for example 4068003495

Determine which snap_id to clear:

Query view Dba_hist_snapshot determine the snap_id range that needs to be cleared, for example 51 to 130

Clear AWR history data:

BEGIN

Dbms_workload_repository.drop_snapshot_range (

low_snap_id => 51,

high_snap_id => 130,

dbid => 4068003495);

End;

6. Query view Dba_hist_snapshot and Wrh$_latch, you should find that the snap_id range of 51 to 130 of the data has been cleared.

Suggestions and summaries:

After Oracle 10G new table Space Sysaux space, by default, AWR information collects awr information every one hours and retains 7 days. This data is stored in the Sysaux tablespace and is recommended to be modified to be collected every 4 hours and retain 3 days of data.

This article is from "Richard's notes-accumulate micro Cheng" blog, please be sure to keep this source http://zxf261.blog.51cto.com/701797/762046