WHEN AWR SNAPSHOTS ARE NOT GENERATED AUTOMATICALLY, HOW To DIAGNOSE. (文档 ID

郑全

In this Document

Symptoms

Cause

Solution

References

---

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.4 to 10.2.0.4 [Release 10.1 to 10.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.1.0.4 to 10.2.0.4

Symptoms

In RAC or Single instance databases, the AWR snapshots are not collected automatically sometimes.

MMON process is not taking the AWR snapshots automatically.
By default MMON process should gather statistics every hour, but it is seen that there are no snapshot in DBA_HIST_SNAPSHOT.

There are some cases in RAC where for some instances the snapshots are generated automatically without any issues but for some instances it is not.

When tried generating manual snapshots, it takes long which usually used to finish up within a minute.

WORKAROUND
----------------------
Only recycling the box which is not feasible in prod.

Cause

Unpublished Bug 4699610 encountered for the problem which is fixed in 11.1

Another unpublished Bug 4226260 encountered for the problem which is fixed in 10.2

Solution

There could be many reason behind the issue.

To troubleshoot, check whether the MMON trace ,i.e m00x has been generated in bdump.

If the trace is generated then check the contents.

Case 1:

========

If the MMON trace shows the following then the SYSAUX tablespace could be growing and that could stop the snapshot collection.

KEWRAFC: Flush slave failed, AWR Enqueue Timeout
The issue appears when deleting from WRI$_OPTSTAT_HISTHEAD_HISTORY.

This issue can effect other workload repository tables such as SYS.WRH$_SQL_PLAN.

Solution

=======

1. Take backup of table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY , Do create table as select of the table.
2. Start the database in restricted mode 
alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move;
3. Monitor the trace files

Once the SYSAUX space is released MMON would resume collecting snapshots automatically.

Case 2

=======

If the MMON trace shows the following then it could be due to the Bug 4699610.

***KEWRAPC: Auto-Purge slave failed, return Code: 5
*** 2007-06-27 17:47:13.544
***KEWRAPC: Auto-Purge slave failed, return Code: 5
*** 2007-06-27 18:17:14.632
***KEWRAPC: Auto-Purge slave failed, return Code: 5

It is an unpublished Bug 4699610 which is fixed in 11g. The patch could be requested.

Case 3

=======

If the snapshots are not collected automatically and the MMON trace is not generated then try generating the snapshots manually.

If the manual generation of snapshots end up with the following error then do the following:-

ERROR at line 1:
ORA-13509: error encountered during updates to a SWRF table
ORA-02291: integrity constraint (ORA-02291: integrity constraint (SYS.WRM$_SNAPSHOT_FK) violated - parent key not found .) violated - parent key not found
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 8
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 31
ORA-06512: at line 1


To take the snapshots we need to populate WRM$_SNAPSHOT table successfully and for that instance metadata should be present in WRM$_DATABASE_INSTANCE.


If the workload related tables are not populated with the data then there could be some dependency issues which could stop the snapshot collection automatically.

Solution

========

To recreate the AWR objects by running catnoawr.sql and catawr.sql and then bounce the database. On restart of the database instance the WRM$_DATABASE_INSTANCE will be populated with the required data. Then MMON process would start collecting the snapshots.

Case 4

=======

Unpublished Bug 4226260.

In memory AWR hash information may not be cleared properly leading to incorrect MMON behaviour.
The most likely symptoms of this issue are that MMON may spin and not capture further AWR snapshots and/or ORA-600 [ktte_verify_tsth] may be reported.

The bug is fixed in 10.2 and 10.1.0.5 patchset also.

Case 5

=======

If the above cases are not relevant and there is no MMON trace generated then restart of box would help.

Otherwise you could recreate the AWR objects by running catnoawr.sql and catawr.sql and then bounce the database. This would anyways resolve the problem.