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


Oracle Database 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.
4. The snapshot Interval was 60 minutes and Retention was 8 days.

Query Check the AWR intervals:
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"

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 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: