[20170207]11G mmon程式死掉.txt

[20170207]11G mmon程式死掉.txt

--昨天下午看資料庫的awr報表發現無法檢視,檢查對比發現MMON程式死掉.
--

Manageability Monitor Processes (MMON and MMNL)

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For
example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for
recently modified SQL objects.

The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA
to disk. MMNL writes to disk when the ASH buffer is full.

--我當時沒有任何辦法,因為是rac系統,我僅僅選擇重啟例項.
--今天我google發現透過如下操作可以啟動MMON程式(要 翻 牆).

SQL> alter system enable restricted session;
System altered.
SQL> alter system disable restricted session;
System altered.
--連結:http://allappsdba.blogspot.com/2016/10/awr-snapshots-are-not-generated.html

--我在測試環境下測試看看.

1:環境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

$ ps -ef | grep ora_mmon_boo[k]
oracle   36734     1  0 08:25 ?        00:00:00 ora_mmon_book

$ kill -9 36734
$ ps -ef | grep ora_mmon_boo[k]

SCOTT@book> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

SCOTT@book> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

SCOTT@book> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

select * from
(SELECT   snap_id, snap_level, instance_number,
         to_char(begin_interval_time, 'yyyy/mm/dd hh24:mi:ss') begin_interval_time,
         to_char(end_interval_time, 'yyyy/mm/dd hh24:mi:ss') end_interval_time,
         to_char(flush_elapsed) flush_elapsed,
         to_char(startup_time, 'yyyy/mm/dd hh24:mi:ss') startup_time,
         error_count
FROM     DBA_HIST_SNAPSHOT
order by 1  desc) where rownum<=3;

   SNAP_ID SNAP_LEVEL INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME   FLUSH_ELAPSED      STARTUP_TIME        ERROR_COUNT
---------- ---------- --------------- ------------------- ------------------- ------------------ ------------------- -----------
       988          1               1 2017/02/07 10:39:24 2017/02/07 10:41:37 +00000 00:00:00.1  2017/02/07 08:25:38           0
       987          1               1 2017/02/07 10:39:22 2017/02/07 10:39:24 +00000 00:00:00.2  2017/02/07 08:25:38           0
       986          1               1 2017/02/07 10:00:42 2017/02/07 10:39:22 +00000 00:00:00.5  2017/02/07 08:25:38           0

--//奇怪能生成awr報表啊..連結還提到是bug
--Note :
--Actually this is a bug in 11.2.0.4 and it is fixed by a patch 19565533.我生產系統11.2.0.3.
--Note id : 2023652.1 AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned

2.測試連結的方法好不好用:

SCOTT@book> host  ps -ef | grep ora_mmon_boo[k]
--//不存在

SCOTT@book> alter system enable restricted session;
System altered.

SCOTT@book> host  ps -ef | grep ora_mmon_boo[k]
oracle   37716     1  0 10:46 ?        00:00:00 ora_mmon_book

--//ok,啟動了.

SCOTT@book> alter system disable restricted session;
System altered.

SCOTT@book> host  ps -ef | grep ora_mmon_boo[k]
oracle   37716     1  0 10:46 ?        00:00:00 ora_mmon_book

3.我感覺奇怪的是,我並沒有再現生產系統問題.
SCOTT@book> host kill -9 37716
SCOTT@book> host  ps -ef | grep ora_mmon_boo[k]

--參看alert檔案,發現實際上會自動啟動的:
Tue Feb 07 10:46:21 2017
Stopping background process MMNL
Starting background process MMON
Tue Feb 07 10:46:22 2017
MMON started with pid=18, OS id=37716
Starting background process MMNL
Tue Feb 07 10:46:22 2017
MMNL started with pid=21, OS id=37718
ALTER SYSTEM enable restricted session;
Tue Feb 07 10:46:32 2017
ALTER SYSTEM disable restricted session;
Tue Feb 07 10:48:38 2017
Restarting dead background process MMON
Tue Feb 07 10:48:38 2017
MMON started with pid=18, OS id=37750
Tue Feb 07 10:51:47 2017
Restarting dead background process MMON
Tue Feb 07 10:51:47 2017
MMON started with pid=17, OS id=37795
Tue Feb 07 10:55:48 2017

$ ps -ef | grep ora_mmon_boo[k]
oracle   37795     1  0 10:51 ?        00:00:00 ora_mmon_book