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

  • October 20, 2016 | 13 views
  • Comments