MMNL absent for 1201 secs; Foregrounds taking over
                                                 Last update (2012-07-24 10:45:03)
                                                                                                                   Date added (2011-05-13 09:48:13)

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
Reviews
Filters
Search