AWR Snapshots are not generated Automatically
Oracle Database 184.108.40.206. 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:
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;
SQL> alter system disable restricted session;
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.
Actually this is a bug in 220.127.116.11 and it is fixed by a patch 19565533.
Note id : 2023652.1 AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned