oracle 11.2.0.4 归档满后 用户限制连接， awr快照停止生成（实例）
awr snapshots are not being created because mmon is not being respawned (doc id 2023652.1) to bottomto bottom
applies to:
oracle database - enterprise edition - version 11.2.0.4 to 11.2.0.4 [release 11.2]
information in this document applies to any platform.
symptoms
due to other reasons, the mmon process died and subsequent awr reports are therefore not getting generated.
the mmon process should automatically be respawned but it is not.
cause
mmon/mmnl not being respawned or recreated is due to:
unpublished bug: bug 19565533 - mmon/mmnl can not be automatically restarted after killed
the issue only occurs on 11.2.0.4 and is a regression of the fix for bug 14213856
solution
to solve the issue:
apply interim patch 19565533 for 11.2.0.4, if available for your platform.
to check for conflicting patches, please use the mos patch planner tool
please refer to
note 1317012.1 - how to use mos patch planner to check and request the conflict patches?
if no patch exists for your version, please contact oracle support for a backport request.
- or -
as a workaround, you can respawn the mmon process by next methods:
restart the database instance
- or -
set the instance to "restricted session" mode and then bring it back to normal mode using following commands as sysdba:
alter system enable restricted session;
alter system disable restricted session;
这个如果不是dba的用户会限制登录，dba的账户则可以
[oracle@host01 ~]$ sqlplus clspuser/clsp
sql*plus: release 11.2.0.3.0 production on fri jan 19 23:29:09 2018
copyright (c) 1982, 2011, oracle. all rights reserved.
error:
ora-01035: oracle only available to users with restricted session privilege
enter user-name: clspuser
[oracle@host01 ~]$
sql> create user eviswang identified by evis;
user created.
sql> gant dba to eviswang;
sp2-0734: unknown command beginning "gant dba t..." - rest of line ignored.
sql> grant dba to eviswang;
grant succeeded.
sql> exit
disconnected from oracle database 11g enterprise edition release 11.2.0.3.0 - production
with the partitioning, real application clusters, automatic storage management, olap,
data mining and real application testing options
[oracle@host01 ~]$ sqlplus eviswang
sql*plus: release 11.2.0.3.0 production on fri jan 19 23:30:31 2018
copyright (c) 1982, 2011, oracle. all rights reserved.
enter password:
connected to:
oracle database 11g enterprise edition release 11.2.0.3.0 - production
with the partitioning, real application clusters, automatic storage management, olap,
data mining and real application testing options
sql>
alter system disable restricted session;后都可以登录
[oracle@host01 ~]$ sqlplus clspuser/clsp
sql*plus: release 11.2.0.3.0 production on fri jan 19 23:31:15 2018
copyright (c) 1982, 2011, oracle. all rights reserved.
connected to:
oracle database 11g enterprise edition release 11.2.0.3.0 - production
with the partitioning, real application clusters, automatic storage management, olap,
data mining and real application testing options
sql>
alter跟踪如下：
fri jan 19 23:31:41 2018
stopping background process mmnl
stopping background process mmon
starting background process mmon
fri jan 19 23:31:43 2018
mmon started with pid=216, os id=54742
starting background process mmnl
fri jan 19 23:31:43 2018
mmnl started with pid=219, os id=54744
alter system enable restricted session;
fri jan 19 23:32:02 2018
alter system disable restricted session;
[oracle@adminm trace]$