MMNL absent ,数据库无法连接

最新推荐文章于 2023-02-22 17:02:33 发布

转载最新推荐文章于 2023-02-22 17:02:33 发布 · 192 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/kevinsun/archive/2008/08/11/1264886.html

文章标签：

#数据库

本文记录了一次处理Oracle数据库MMNL长时间未响应导致的问题，详细解释了MMNL的作用及此错误的原因，并给出了针对性的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

周六晚上 11 点，正在看奥运新闻，同事打电话说数据库大量 lock ，无法使用，查看日志，发现如下错误，用 SQLPLUS 无法进入数据库，只好重新启动 Service 。

MMNL absent for 1201 secs; Foregrounds taking over

首先我们从MMNL可以知道，这是一个Oracle10g的数据库。
MMNL是Oracle10g引入的一个新的后台进程，其全拼名字为Memory Monitor Light ，是AWR(Automatic Workload Repository )的组件之一。

这个错误的含义是，MMNL过长时间未激活，前台接替了它的工作。

通常这是一个可以忽略的错误，不会对数据库产生什么影响；但是在某些情况下，该错误会导致数据库无法登陆或访问，需要重新启动数据库才能解决。
这类错误只在10gR1中存在，在10gR2中已经修正。
你可能看到类似的信息还有:

Wed Nov 9 11:53:47 2005

ALTER SYSTEM SET fast_start_mttr_target=3600 SCOPE=MEMORY;

Wed Nov 9 11:54:03 2005

MTTR advisory is disabled because either FAST_START_IO_TARGET or

LOG_CHECKPOINT_INTERVAL is set

Wed Nov 9 11:54:07 2005

ALTER SYSTEM SET fast_start_mttr_target=1200 SCOPE=MEMORY;

Wed Nov 9 12:23:44 2005

KTSMG_UPDATE_MQL(): MMNL absent for 1201 secs; Foregrounds taking over

以上是在盖国强网站上查到的原文，Metalink上也有类似的文章。可是，我的库已经是R2版本，应该不会有类似问题。

在Metalink上发现如下文章

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
This problem can occur on any platform.

Symptoms

Alert log shows the following messages :

Wed Oct 10 10:59:06 2007
MMNL absent for 293161 secs; Foregrounds taking over
Wed Oct 10 11:00:06 2007
MMNL absent for 293223 secs; Foregrounds taking over
Wed Oct 10 11:01:07 2007
MMNL absent for 293283 secs; Foregrounds taking over
Wed Oct 10 11:02:07 2007
MMNL absent for 293343 secs; Foregrounds taking over
Wed Oct 10 11:02:07 2007
MMNL absent for 293343 secs; Foregrounds taking over
MMNL absent for 293343 secs; Foregrounds taking over
MMNL absent for 293343 secs; Foregrounds taking over

Cause

The maxbytes of the datafiles was reduced lower than the bytes value.

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024 TABLESPACE_NAME AUT MAXBYTES/1024/1024
--------------- ------------------------------ --- ------------------
/db/oradata/pcis/SYSSPACE001/system.dbf
700 SYSTEM YES .0078125

/db/oradata/pcis/UNDOTSPACE001/undotbs.dbf
5000 UNDOTBS YES .0078125

/db/oradata/pcis/SYSSPACE001/sysaux.dbf
500 SYSAUX YES .0078125

Maxbytes should not be allowed to be lower than bytes. This is infact a bug which has already been logged.
Bug 5470031 has been raised for this issue on how the maxsize is allowed to be smaller than the actual filesize using the command ALTER DATABASE DATAFILE ...AUTOEXTEND MAXSIZE

As of now, at the time of writing this note ( Oct'07 ) , this bug is still under development/resolution phase.