Summary
According to Oracle the
Memory Monitor Light (MMNL) process is a new process in Oracle 10g which works with the Automatic Workload Repository (AWR) to write out full statistics buffers to disk as needed.
You can check if the process is running with the command:
$>ps -ef|grep ora_m|grep -v grep
oradwh 10840 1 0 19:05:22 ? 3:16 ora_mmnl_DWH01
If you monitor to the alert log messages like this:
MMNL absent for 1201 secs; Foregrounds taking over
Thu May 12 17:29:46 EETDST 2011
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=86
Thu May 12 17:29:46 EETDST 2011
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=91
Thu May 12 17:29:46 EETDST 2011
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=96
>>>Thu May 12 18:23:55 EETDST 2011
PMON failed to acquire latch, see PMON dump
Thu May 12 18:25:22 EETDST 2011
PMON failed to acquire latch, see PMON dump
Thu May 12 18:32:22 EETDST 2011
PMON failed to acquire latch, see PMON dump
Thu May 12 18:34:00 EETDST 2011
PMON failed to acquire latch, see PMON dump
Thu May 12 18:35:43 EETDST 2011
PMON failed to acquire latch, see PMON dump
Thu May 12 18:36:45 EETDST 2011
PMON failed to acquire latch, see PMON dump
Thu May 12 18:37:47 EETDST 2011
and you
cannot connect to the Database then the problem of the
MMNL absent for 1201 secs; Foregrounds taking over is related to the MMNL process or MMON errors.
You can find out what those 2 oracle background processes (MMON and MMNL) are doing with the query:
SQL> SELECT * FROM x$messages
2 WHERE dest IN ('MMNL', 'MMON')
3 ORDER BY DEST;
ADDR INDX INST_ID DESCRIPTION DEST
---------------- ---------- ---------- -------------------------------------------------------------
4000000001AD4250 195 1 ASH Sampler (KEWA) MMNL
4000000001AD4410 209 1 AWR Raw Metrics Capture MMNL
4000000001AD43F0 208 1 MMNL DB close MMNL
4000000001AD37D0 111 1 MMNL Periodic MQL Selector MMNL
4000000001AD37B0 110 1 tune undo retention MMNL
4000000001AD3590 93 1 recovery area alert action MMON
4000000001AD3610 97 1 Flashback Marker MMON
4000000001AD36F0 104 1 tablespace alert monitor MMON
4000000001AD3770 108 1 undo usage MMON
4000000001AD3790 109 1 UNDO MMON ACTION MMON
4000000001AD38F0 120 1 OLS Cleanup MMON
4000000001AD39D0 127 1 Compute cache stats in background MMON
4000000001AD3A70 132 1 PGA Max Size (QESMM) MMON
4000000001AD3AB0 134 1 MMON TSM Cleanup MMON
4000000001AD4190 189 1 alert message cleanup MMON
4000000001AD41B0 190 1 alert message purge MMON
4000000001AD41D0 191 1 AWR Auto Flush Task MMON
4000000001AD41F0 192 1 AWR Auto Purge Task MMON
4000000001AD4210 193 1 AWR Auto DBFUS Task MMON
4000000001AD4230 194 1 AWR Auto CPU USAGE Task MMON
4000000001AD4270 196 1 ASH Emergency Flusher (KEWA) MMON
4000000001AD42B0 198 1 Advisor delete expired tasks MMON
4000000001AD42D0 199 1 MMON Remote action Listener MMON
4000000001AD42F0 200 1 MMON Local action Listener MMON
4000000001AD4310 201 1 MMON Completion Callback Dispatcher MMON
4000000001AD4330 202 1 MMON ping action MMON
4000000001AD4350 203 1 MMON testing slave MMON
4000000001AD4370 204 1 run-once action driver MMON
4000000001AD4390 205 1 shutdown MMON MMON
4000000001AD43B0 206 1 MMON DB open MMON
4000000001AD43D0 207 1 MMON DB close MMON
4000000001AD4430 210 1 metrics monitoring MMON
4000000001AD4450 211 1 threshold reconciliation MMON
4000000001AD4470 212 1 ADDM (KEH) MMON
4000000001AD4490 213 1 WCR: Record Action Switcher MMON
4000000001AD44B0 214 1 WCR: Record State Check MMON
4000000001AD44D0 215 1 WCR: Record SharedDir Check MMON
4000000001AD44F0 216 1 WCR: Capture Stats Update MMON
4000000001AD4510 217 1 WCR: Abort capture on IO error MMON
4000000001AD4550 219 1 Check for sync messages from other instances MMON
4000000001AD4590 221 1 Check for autostart messages from other instances MMON
4000000001AD45B0 222 1 Coordinator autostart timeout MMON
4000000001AD2E30 34 1 SGA memory tuning MMON
4000000001AD2DB0 30 1 SGA memory tuning parameter update MMON
4000000001AD2CB0 22 1 reconfiguration MMON action MMON
4000000001AD4610 225 1 Job Autostart action force MMON
4000000001AD2C50 19 1 MMON request for RLB metrics MMON
4000000001AD2C90 21 1 reload failed KSPD callbacks MMON
48 rows selected.
As you can see for example the MMON process calls the
ASH Emergency Flusher (KEWA) job which is responsible for flushing the contents of ASH tables to the disk when the buffer gets full.
If the background process
MMNL is absent then foregrounds processes take over to do the job that should be doing by itself. For example to determine the longest query duration, to tune undo retention appropriately, etc.
There are cases where because of incomplete database shutdown process after the database startup the MMNL is not started!
The message
MMNL absent for x secs; Foregrounds taking over
is a
generic notification message and you can be for sure what caused that error.
In some cases something else caused the database hang and of course the MMNL messages appear after the hang!
If you want to investigate about MMNL and MMON problems, first check if a trace file was generated under bdump at the "failure" time.
Search for SID_
m000_pid.trc, SID_
mmon_pid.trc, SID_
mmnl_pid.trc trace files! (the process
M000 is the MMON background
slave process)
An example of a trace file:
/oracle/DwhProd/orabin/oradb/product/10.2.0/admin/DWH01/bdump/dwh01_m000_7127.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/DwhProd/orabin/oradb/product/10.2.0
System name: HP-UX
Node name: dwhprod1
Release: B.11.31
Version: U
Machine: ia64
Instance name: DWH01
Redo thread mounted by this instance: 1
Oracle process number: 221
Unix process pid: 7127, image: oracle@dwhprod1 (m000)
*** 2012-06-22 03:03:43.762
*** ACTION NAME:(Auto-Flush Slave Action) 2012-06-22 03:03:43.761
*** MODULE NAME:(MMON_SLAVE) 2012-06-22 03:03:43.761
*** SERVICE NAME:(SYS$BACKGROUND) 2012-06-22 03:03:43.761
*** SESSION ID:(1517.38513) 2012-06-22 03:03:43.761
*** KEWROCISTMTEXEC - encountered error: (ORA-00001: unique constraint (SYS.WRH$_TEMPSTATXS_PK) violated)
*** SQLSTR: total-len=650, dump-len=240,
STR={insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_change#, phyrds,
phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phyblkrd,
phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_num}
*** KEWRAFM1: Error=13509 encountered by kewrfteh
Searching Metalink you can take a closer look at:
Bug 10159846 - ORA-1 on SYS.WRH$_TEMPSTATXS_PK when collecting AWR snapshot - superceded [ID 10159846.8]
Trace Files Reporting ORA-1476 For m000 Background Process [ID 359950.1]
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged [ID 387914.1]
Bug 4359124 - AWR errors with ORA-1 (on SYS.WRH$_SQLTEXT_PK) kewrafm1 error=13509 [ID 4359124.8]
MMON generating Trace File '_m000_' Frequently Ora-12899 [ID 330552.1]
AWR Snapshots Not Generated, ORA-1: unique constraint (SYS.WRH$_IC_DEVICE_STATS_PK) violated [ID 960003.1]
Trace Files Generated Every Hour With ORA-26026 or ORA-01502. [ID 887579.1]
((Sys.Wrh$_undostat_pk) Violated) Ora-00001 [ID 397284.1]
Message In Alert Log: Mmnl Absent For XXXX Secs [ID 462402.1]
SQL*Plus Connection Fails With Ktsmg_update_mql(): Mmnl Absent For 1201 Secs; Foregrounds Taking Over [ID 340663.1]
Receive Messages MMNL Absent for 4159 Secs; Foregrounds Taking Over in Alert.log [ID 567562.1]
"MMNL absent for %u secs; Foregrounds taking over" Messages in Alert.log [ID 465891.1]
DATABASE CRASH WITH SGA_TARGET [ID 747812.1]
Reminder: Also look for the hidden parameter :
_ash_size and ASH data buffer size
Active Session History (ASH) Performed An Emergency Flush Messages In The Alert Log [ID 1385872.1]
MMON Consuming a Huge Amount of Memory Raising ORA-4030 and Causing an Instance Hang
