[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