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

--昨天下午看数据库的awr报表发现无法查看,检查对比发现MMON进程死掉.
--http://docs.oracle.com/cd/E11882_01/server.112/e40540/process.htm#CNCPT9841

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