結論：

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資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/

oracle 10g 10.2.0.5關閉共享伺服器模式資料庫報MMNL absent for %u secs

結論：

測試：

個人簡介

相關文章