DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, October 21, 2016

AWR Snapshots are not generated Automatically

Scenario:
------------

Oracle Database 11.2.0.4. My AWR Snapshots were not being generated even though it was full enabled.

Basic Checks done:
------------------------

1. Checked that I had enough free space on SYSAUX tablespace. I was using only 5GB of the total 20GB.
2. System Parameter statistics_level was TYPICAL.
3. DBMS_STATS.GET_STATS_HISTORY_RETENTION was to 30 days.
4. The snapshot Interval was 60 minutes and Retention was 8 days.

Query Check the AWR intervals:
=========================
select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from
dba_hist_wr_control;

5. Everything was OK,So finally checked the process responsible for AWR collection ie) MMON

Now MMON was not running.

Now it is a critical system and CRP testing is going, So i cannot bring down the system but i need to spawn the MMON process.

Here is the workaround :
-------------------------------

Just enable and disable the restricted sessions.

SQL> alter system enable restricted session;

System altered.

SQL> alter system disable restricted session;

System altered.

Now check the MMON.

[oradoyen@saravanadb trace]$ ps -ef|grep mmon
oradoy  9732     1  3 18:46 ?        00:00:00 ora_mmon_ORADOY
oradoy  9977 50953  0 18:47 pts/8    00:00:00 grep mmon
oradoy 28182     1  0 Oct10 ?        00:11:40 ora_mmon_DEV

Now AWR snapshots started generating.

Note :
Actually this is a bug in 11.2.0.4 and it is fixed by a patch 19565533.

Note id : 2023652.1 AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned

No comments: