Aug 11

AWR snapshots not being generated even though it's enabled

This post is also available in: Português

Those days, I was facing an issue in my Oracle Database 11.2.0.4. My AWR Snapshots were not being generated even though it was full enabled.

I did some checks to be sure I had no issues:

  • Checked that I had enough free space on SYSAUX tablespace. I was using only 5GB of the total 15GB.
  • My control_management_pack_access was "DIAGNOSTIC+TUNING". That's fine.
  • My parameter statistics_level was "TYPICAL". That's fine.
  • My DBMS_STATS.GET_STATS_HISTORY_RETENTION was to 31 days. That's fine.
  • The snapshot Interval was 60 minutes and Retention was 8 days. Also OK.

Everything was OK, so I had no clue what was going on, until I decided to check which process is responsible for AWR collection.

Answer: MMON

So I started searching for any MMON related issues on alert log. What was causing that problem finally appeared when I checked if MMON was running:

[oracle@myhost ~]$ ps -ef | grep mmon
grid      7667     1  0  2015 ?        00:00:16 asm_mmon_+ASM
oracle   15937  2814  0 16:11 pts/0    00:00:00 grep mmon
[oracle@myhost ~]$

So, my MMON was NOT RUNNING! Only for the ASM, not the DB. Unbelievable..

As this was a critical and very important Production system, how could I respawn that daemon without bouncing the whole DB?

Well, at least there is a work around!

You can simply enable/disable restricted session on your database:

SQL> alter system enable restricted session;
System altered.
SQL> alter system disable restricted session;
System altered.

Checking again if MMON was running:

[oracle@myhost ~]$ ps -ef | grep mmon
grid      7667     1  0  2015 ?        00:00:16 asm_mmon_+ASM
oracle    8680  1     0 16:15 pts/0    00:00:00 ora_mmon_orcl
oracle   15937  2814  0 16:11 pts/0    00:00:00 grep mmon
[oracle@myhost ~]$

Finally my AWR snapshots started to be taken again!

Update:

Later, I've found that all this issue was caused for YAB (Yet Another Bug). It seems it only affects 11.2.0.4.

More information at MOS: 2023652.1 (AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned)

Have you enjoyed? Please leave a comment or give a 👍!

7 comments

Skip to comment form

    • saud on January 11, 2017 at 17:26
    • #
    • Reply

    SQL> alter system enable restricted session;
    System altered.
    SQL> alter system disable restricted session;
    System altered.
    Checking again if MMON was running:

    -- Thanks this started MMOM for me also.

    • Santhosh on December 17, 2018 at 05:10
    • #
    • Reply

    I encountered with same issue in dev environment and did the same thing you said. It solved my problem. Thanks for your information.

    • Anirudh on February 4, 2019 at 14:55
    • #
    • Reply

    It solved my problem too on Exadata compute node.

    • Anirudh on February 4, 2019 at 14:59
    • #
    • Reply

    I am wondering why Enabling and disabling restricted session is starting the MMON process.

      • satish D Nawle on March 21, 2019 at 03:39
      • #
      • Reply

      It will force MMON to restart which is died thus snapshot stopped working

        • Swayam on September 4, 2019 at 13:56
        • #
        • Reply

        in my case the mmon process is also running but still no snaps are getting generated . Apart from this all parameters are intact and space requirements are fulfilled. What may be the reason for the same?

        1. Hard to tell.. maybe try to restart the ASH process:
          alter system set "_ash_enable"=false;
          alter system set "_ash_enable"=true;

Leave a Reply

Your email address will not be published.