Have you ever tried to run an AWR report only to find that your hourly snapshots did not get created? A colleague of mine ran into this and worked with Oracle to identify a solution:
select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > sysdate - 1 and instance_number = 1 order by 1; SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ------------------------------ ------------------------------ 239690 2017-06-28-01.17.23.246000 2017-06-28-02.00.34.302000 239691 2017-06-28-02.00.34.302000 2017-06-28-03.00.28.007000 239692 2017-06-28-03.00.28.007000 2017-06-28-04.00.39.460000 239693 2017-06-28-04.00.39.460000 2017-06-28-05.00.03.694000 239694 2017-06-28-05.00.03.694000 2017-06-28-06.00.13.452000 <--missing 239696 2017-06-28-07.00.10.342000 2017-06-28-08.00.20.270000 239697 2017-06-28-08.00.20.270000 2017-06-28-09.00.09.222000 239698 2017-06-28-09.00.09.222000 2017-06-28-10.00.39.143000 239699 2017-06-28-10.00.39.143000 2017-06-28-11.00.55.633000 239700 2017-06-28-11.00.55.633000 2017-06-28-12.00.02.801000 239701 2017-06-28-12.00.02.801000 2017-06-28-13.00.12.972000 239702 2017-06-28-13.00.12.972000 2017-06-28-19.21.20.515000 <--missing 12 rows selected.
The issue is caused by MMON being suspended – confirmed by this command:
oradebug unit_test kebm_dmp_slv_attrs kewrmafsa_ Status: 3 Flags: 0 Runtime limit: 900 CPU time limit: 300 Violations: 4 Suspended until: 1498817730 <-- non-zero indicated MMON is suspended
To clear the suspension issue this command:
oradebug unit_test kebm_set_slv_attrs kewrmafsa_ retain retain retain retain 0 0 Modified attributes of kewrmafsa_ (slave id 12)
And now it shows zero and snapshots are being generated again:
oradebug unit_test kebm_dmp_slv_attrs kewrmafsa_ Status: 3 Flags: 0 Runtime limit: 900 CPU time limit: 300 Violations: 0 Suspended until: 0 zero indicates MMON is running normally
The reason for the missing snapshots was SYSAUX cleanup work which locked up some of the tables that snapshot generation needs. But it can happen for a variety of reasons.
