结论：
1,共享服务器模式关闭，在关闭前一定要通过v$shared_server及v$dispatchers确认调度器进程及共享服务器进程空闲，方可快速关闭数据库2，MMNL absent for %u secs; Foregrounds taking over，在本文的原因可能就是由于在关闭数据库期间，数据库出于受限模式，导致MMNL收集ASH信息出现故障，所以关闭数据库HANG住
3，MMNL absent for %u secs; Foregrounds taking over有几种原因，也可能是BUG，在数据库多个版本皆可能出现，一定要全面在MOS进行匹配，进行对应处理
测试：
1，数据库在开启共享服务器模式，关闭数据库非常慢[ora10g@seconary admin]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 23 05:33:59 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
2,查看告警日志发现在等待调度器进程以及共享服务器进程关闭
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
3，然后过很久显示MMNL进程很久不活动，然后由前台进程接管
Wed Sep 23 05:54:06 EDT 2015
MMNL absent for 1204 secs; Foregrounds taking over
4，查看与MMNL相关的MOS文章
"MMNL absent for %u secs; Foregrounds taking over" Messages in Alert.log (文档 ID 465891.1)
Message In Alert Log: Mmnl Absent For XXXX Secs (文档 ID 462402.1)
ora10g@seconary ~]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 23-SEP-2015 06:15:49
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
5，杀死LOCAL想着的ORACLE进程，ORACLE仍然没有关闭
ora10g 13667 13666 95 05:33 ? 00:42:51 oracleora10g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[ora10g@seconary ~]$ kill -9 13667
[ora10g@seconary admin]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 23 06:19:28 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected.
SQL> exit
Disconnected
[ora10g@seconary admin]$ ps -ef|grep ora_
ora10g 4810 1 0 Sep22 ? 00:00:01 ora_pmon_ora10g
ora10g 4812 1 0 Sep22 ? 00:00:00 ora_psp0_ora10g
ora10g 4814 1 0 Sep22 ? 00:00:00 ora_mman_ora10g
ora10g 4816 1 0 Sep22 ? 00:00:01 ora_dbw0_ora10g
ora10g 4818 1 0 Sep22 ? 00:00:01 ora_lgwr_ora10g
ora10g 4820 1 0 Sep22 ? 00:00:02 ora_ckpt_ora10g
ora10g 4822 1 0 Sep22 ? 00:00:00 ora_smon_ora10g
ora10g 4824 1 0 Sep22 ? 00:00:00 ora_reco_ora10g
ora10g 4843 1 0 Sep22 ? 00:00:00 ora_arc0_ora10g
ora10g 4845 1 0 Sep22 ? 00:00:00 ora_arc1_ora10g
ora10g 15084 3748 0 06:20 pts/0 00:00:00 grep ora_
[ora10g@seconary admin]$
6，只能手工KILL 检查点进程，数据库关闭
[ora10g@seconary admin]$ kill -9 4820
[ora10g@seconary admin]$
Wed Sep 23 06:20:21 EDT 2015
Errors in file /home/ora10g/admin/ora10g/bdump/ora10g_pmon_4810.trc:
ORA-00469: CKPT process terminated with error
Wed Sep 23 06:20:21 EDT 2015
PMON: terminating instance due to error 469
Termination issued to instance processes. Waiting for the processes to exit
Wed Sep 23 06:20:31 EDT 2015
Instance termination failed to kill one or more processes
Instance terminated by PMON, pid = 4810
[ora10g@seconary admin]$ ps -ef|grep ora_
ora10g 15112 3748 0 06:20 pts/0 00:00:00 grep ora_
[ora10g@seconary admin]$
7,我们重新分析下为何关闭数据库这么慢
--当前数据共有2个调度器进程
[root@seconary ~]# su - ora10g
[ora10g@seconary ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 23 10:08:38 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter disp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (protocol=tcp)(service=ora10g)
(dispatchers=2)
max_dispatchers integer 10
SQL> host ps -ef|grep -i --color d00
ora10g 4042 1 0 09:59 ? 00:00:00 ora_d000_ora10g
ora10g 4044 1 0 09:59 ? 00:00:00 ora_d001_ora10g
8，确认当前的调度器进程信息
SQL> col network for a100
SQL> select name,network from v$dispatcher;
NAME NETWORK
---- ----------------------------------------------------------------------------------------------------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=64456))
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=35517))
9,关闭调度器进程
SQL> alter system shutdown immediate 'D000';
System altered.
SQL> alter system shutdown immediate 'D001';
System altered.
--告警日志显示2个调度器进程被中断
Wed Sep 23 10:12:35 EDT 2015
idle dispatcher 'D000' terminated, pid = (13, 1)
idle dispatcher 'D001' terminated, pid = (14, 1)
--关闭调度器有时有个延迟，因为调度器进程可能正在处理客户端会话连接
SQL> select name,network from v$dispatcher;
no rows selected
--查看当前的会话信息
SQL> select user,type,program,status,server from v$session;
USER TYPE PROGRAM STATUS SERVER
------------------------------ ---------- ------------------------------------------------ -------- ---------
SYS BACKGROUND oracle@seconary (q002) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (q001) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (QMNC) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (ARC1) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (ARC0) ACTIVE DEDICATED
SYS USER sqlplus@seconary (TNS V1-V3) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (MMNL) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (MMON) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (CJQ0) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (CKPT) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (RECO) ACTIVE DEDICATED
USER TYPE PROGRAM STATUS SERVER
------------------------------ ---------- ------------------------------------------------ -------- ---------
SYS BACKGROUND oracle@seconary (SMON) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (LGWR) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (DBW0) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (MMAN) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (PSP0) ACTIVE DEDICATED
SYS BACKGROUND oracle@seconary (PMON) ACTIVE DEDICATED
17 rows selected.
--查看数据库进程信息
SQL> select program,username from v$process;
PROGRAM USERNAME
------------------------------------------------ ---------------
PSEUDO
oracle@seconary (PMON) ora10g
oracle@seconary (PSP0) ora10g
oracle@seconary (MMAN) ora10g
oracle@seconary (DBW0) ora10g
oracle@seconary (LGWR) ora10g
oracle@seconary (CKPT) ora10g
oracle@seconary (SMON) ora10g
oracle@seconary (RECO) ora10g
oracle@seconary (CJQ0) ora10g
oracle@seconary (MMON) ora10g
PROGRAM USERNAME
------------------------------------------------ ---------------
oracle@seconary (MMNL) ora10g
oracle@seconary (S000) ora10g
oracle@seconary (S001) ora10g
oracle@seconary (TNS V1-V3) ora10g
oracle@seconary (ARC0) ora10g
oracle@seconary (ARC1) ora10g
oracle@seconary (QMNC) ora10g
oracle@seconary (q001) ora10g
oracle@seconary (q002) ora10g
20 rows selected.
10,数据库正常关闭
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Wed Sep 23 10:17:24 EDT 2015
Shutting down instance (immediate)
Wed Sep 23 10:17:24 EDT 2015
Shutting down instance: further logons disabled
Wed Sep 23 10:17:24 EDT 2015
Stopping background process CJQ0
Wed Sep 23 10:17:24 EDT 2015
Stopping background process QMNC
Wed Sep 23 10:17:25 EDT 2015
Stopping background process MMNL
Wed Sep 23 10:17:26 EDT 2015
Stopping background process MMON
License high water mark = 3
Wed Sep 23 10:17:27 EDT 2015
Job queue slave processes stopped
Waiting for shared server 'S000' to die
Waiting for shared server 'S001' to die
All dispatchers and shared servers shutdown
Wed Sep 23 10:17:28 EDT 2015
ALTER DATABASE CLOSE NORMAL
Wed Sep 23 10:17:28 EDT 2015
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed Sep 23 10:17:28 EDT 2015
Shutting down archive processes
Archiving is disabled
Wed Sep 23 10:17:33 EDT 2015
ARCH shutting down
ARC1: Archival stopped
Wed Sep 23 10:17:38 EDT 2015
ARCH shutting down
ARC0: Archival stopped
Wed Sep 23 10:17:39 EDT 2015
Thread 1 closed at log sequence 151
Successful close of redo thread 1
Wed Sep 23 10:17:39 EDT 2015
Completed: ALTER DATABASE CLOSE NORMAL
Wed Sep 23 10:17:39 EDT 2015
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
11，如果不杀死调度器进程，关闭数据库会不会很慢呢？
---重启库
--2个调度器进程
SQL> host ps -ef|grep -i --color d00
ora10g 7921 1 0 10:19 ? 00:00:00 ora_d000_ora10g
ora10g 7923 1 0 10:19 ? 00:00:00 ora_d001_ora10g
--2个共享服务器进程
SQL> host ps -ef|grep -i --color s00
ora10g 7925 1 0 10:19 ? 00:00:00 ora_s000_ora10g
ora10g 7927 1 0 10:19 ? 00:00:00 ora_s001_ora10g
--查询调度器进程的信息
SQL> col network for a80
SQL> select name,network,status,idle,busy from v$dispatcher;
NAME NETWORK STATUS IDLE BUSY
---- -------------------------------------------------------------------------------- ---------------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=51323)) WAIT 14960 0
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=secondary.redhat.com)(PORT=54891)) WAIT 14959 0
--查询共享服务器进程的信息
SQL> set linesize 300
SQL> select name,status,idle,busy from v$shared_server;
NAME STATUS IDLE BUSY
---- ---------------- ---------- ----------
S000 WAIT(COMMON) 26303 0
S001 WAIT(COMMON) 26302 0
SQL> select server,status,count(*) from v$session group by server,status;
SERVER STATUS COUNT(*)
--------- -------- ----------
DEDICATED ACTIVE 17
创建2个共享服务器会话
[ora10g@seconary ~]$ sqlplus scott/system@ora10g
SQL> select server,status,count(*) from v$session group by server,status;
SERVER STATUS COUNT(*)
--------- -------- ----------
NONE INACTIVE 2
DEDICATED ACTIVE 17
--这下共享服务器进程忙起来了
SQL> select name,paddr,status,idle,busy from v$shared_server;
NAME PADDR STATUS IDLE BUSY
---- ---------------- ---------------- ---------- ----------
S000 00000000A41B8388 WAIT(COMMON) 44393 40
S001 00000000A41B8B80 WAIT(COMMON) 44409 22
---通过PADDR获知共享服务器进程的信息，并且共享服务器进程不是会话，不存在于v$session中
SQL> set linesize 300
SQL> select program,username from v$process where addr in ('00000000A41B8388','00000000A41B8B80');
PROGRAM USERNAME
------------------------------------------------ ---------------
oracle@seconary (S000) ora10g
oracle@seconary (S001) ora10g
SQL> host ps -ef|grep -i --color s00
ora10g 7925 1 0 10:19 ? 00:00:00 ora_s000_ora10g
ora10g 7927 1 0 10:19 ? 00:00:00 ora_s001_ora10g
12,这样也可以正常关闭数据库，还是没有模拟出来最开始的现象
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Wed Sep 23 10:31:40 EDT 2015
Shutting down instance (immediate)
Wed Sep 23 10:31:40 EDT 2015
Shutting down instance: further logons disabled
Wed Sep 23 10:31:40 EDT 2015
Stopping background process CJQ0
Wed Sep 23 10:31:40 EDT 2015
Stopping background process QMNC
Wed Sep 23 10:31:42 EDT 2015
Stopping background process MMNL
Wed Sep 23 10:31:43 EDT 2015
Stopping background process MMON
License high water mark = 3
Wed Sep 23 10:31:44 EDT 2015
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
Waiting for dispatcher 'D001' to shutdown
All dispatchers and shared servers shutdown
Wed Sep 23 10:31:46 EDT 2015
ALTER DATABASE CLOSE NORMAL
Wed Sep 23 10:31:46 EDT 2015
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed Sep 23 10:31:46 EDT 2015
Shutting down archive processes
Archiving is disabled
Wed Sep 23 10:31:51 EDT 2015
ARCH shutting down
ARC1: Archival stopped
Wed Sep 23 10:31:56 EDT 2015
ARCH shutting down
ARC0: Archival stopped
Wed Sep 23 10:31:57 EDT 2015
Thread 1 closed at log sequence 151
Successful close of redo thread 1
Wed Sep 23 10:31:57 EDT 2015
Completed: ALTER DATABASE CLOSE NORMAL
Wed Sep 23 10:31:57 EDT 2015
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
13,换个思路分析，即使数据库从告警日志看最后关闭不了，是卡在如下的信息
Wed Sep 23 05:54:06 EDT 2015
MMNL absent for 1204 secs; Foregrounds taking over
14，了解下MMMNL进程的含义，它用于采集ASH信息以及度量信息的采集
MMNL
Manageability Monitor Lite Process
Performs tasks relating to manageability, including active session history sampling and metrics computation
MMNL performs many tasks relating to manageability, including session history capture and metrics computation.
Database and ASM instances
15，上MOS看看与MMNL相关有无相关的BUG或故障文章，依次分析，基本可以判断与文档 ID 567562.1有关，就是数据库关闭时，处理受限模式，MMNL这个进程呢要FLUSH ASH BUFFER到ASH，但
此时MMNL进程又已经关闭，所以关库就HANG在哪儿了
---数据库的数据文件MAXBYTES指定过小，导致，可以增加MAXBYTES或者关闭数据文件的自动扩展功能
Message In Alert Log: Mmnl Absent For XXXX Secs (文档 ID 462402.1)
--适用于10.2.0.3数据库版本后，数据库处于受限模式下，可能会产生上述的报错信息，只要数据库停止便不再出现此信息
Receive Messages MMNL Absent for 4159 Secs; Foregrounds Taking Over in Alert.log (文档 ID 567562.1)
Receive Messages MMNL Absent for 4159 Secs; Foregrounds Taking Over in Alert.log (文档 ID 567562.1)
--由于ASH缓冲过小或者MMNON进程出现异常，导致MMNL进程DUMP缓冲到ASH TARCE FILE，产生极大的文件
MMNL Background Process Creates Large Trace Files Containing ASH Information (文档 ID 1952274.1)
16,我们再把告警日志自报错的地方，向上看，确实在报错之前MMNL进程已经关闭了，并且你看关闭调度器及共享服务器进程是发生在Wed Sep 23 05:34:06 EDT 2015，
而报错MMNL absent for 1204 secs; Foregrounds taking over是发生在Wed Sep 23 05:54:06 EDT 2015，这个中间的时间消耗是20分钟，所以在关闭共享服务器情况下
一定要先确保共享服务器进程及调度器进程空闲
Wed Sep 23 05:34:02 EDT 2015
Shutting down instance (immediate)
Wed Sep 23 05:34:02 EDT 2015
Shutting down instance: further logons disabled
Wed Sep 23 05:34:02 EDT 2015
Stopping background process CJQ0
Wed Sep 23 05:34:02 EDT 2015
Stopping background process QMNC
Wed Sep 23 05:34:04 EDT 2015
Stopping background process MMNL
Wed Sep 23 05:34:05 EDT 2015
Stopping background process MMON
License high water mark = 9
Wed Sep 23 05:34:06 EDT 2015
Job queue slave processes stopped
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Wed Sep 23 05:54:06 EDT 2015
MMNL absent for 1204 secs; Foregrounds taking over
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通4G数据库性能分析与优化
中国联通4G数据库性能分析与优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1807686/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1807686/
3470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
