在通过运行脚本来安装产品数据库的时候，发现用新创建的用户在连接数据库的时候hang住了，卡了半天没有任何变化。于是手动用sqlplus来尝试下连接下，结果一样是登陆不了，session被block住了。换成sys用户登录，结果很顺利连接上数据库。那么问题出在什么地方呢？
首先去查看了下alert文件，没有发现什么异常的信息。 好吧，查询下视图v$session看看有啥等待事件没有，结果发现了有几个session出于等待状态，等待事件显示为"resmgr:become active", 从文档上了解到这个等待时间的含义，如下....
resmgr: become active
The session is waiting for a resource manager active session slot. This event occurs when the resource manager is enabled and the number of active sessions in the session's current consumer group exceeds the current resource plan's active session limit for the consumer group. To reduce the occurrence of this wait event, increase the active session limit for the session's current consumer group.
Wait Time: The time the session waited to be allocated an active session slot
| Parameter | Description |
|---|---|
| location | location of the wait |
看起来这个等待时间跟database resource manager相关。但是从视图v$session中的resource_consumer_group发现那些session的consumer_group都显示为NULL, 参数resource_manager_plan的值也为NULL. 那按道理那些session应该不会受到resource方面的限制，尤其是登陆session的个数相关限制才对。
Quiescing a Database
偶然在concepts上发现如下一段话...
"The Database Resource Manager blocks all actions that were initiated by a user other than SYS or SYSTEM while the system is quiesced.
Oracle instructs the Database Resource Manager in all instances to prevent all inactive sessions (other than SYS and SYSTEM) from becoming active. No user other than SYS and SYSTEM can start a new transaction, a new query, a new fetch, or a new PL/SQL operation.
During the quiesced state, you cannot change the Resource Manager plan in any instance. "
这个很吻合我现在遇到的情况，从等待事件中得知问题跟resource manager有关系，而且现在确实是非sys/system用户连接不上数据库。那么很有可能是因为处于了quiesce状态了。
可以通过查询视图v$instance得知当前数据库是不是处于quiesce状态....
Viewing the Quiesce State of an Instance
You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:
-
NORMAL: Normal unquiesced state. -
QUIESCING: Being quiesced, but some non-DBA sessions are still active. -
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.
那么该如何把数据库至于正常模式呢,可以通过如下命令....
ALTER SYSTEM UNQUIESCE;
反过来，如果想把数据库至于quiesced状态，可以执行如下命令：
ALTER SYSTEM QUIESCE RESTRICTED;
但是上面这条命令不会立即执行成功，它会等待其他非sys/system的事务结束。
“Oracle waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or terminate).
Oracle also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish.
If a query is carried out by multiple successive OCI fetches, Oracle does not wait for all fetches to finish. It waits for the current fetch to finish and then blocks the next fetch.
Oracle also waits for all sessions (other than those of SYS or SYSTEM) that hold any shared resources (such as enqueues) to release those resources. ”
The ALTER SYSTEM QUIESCE RESTRICTED statement may wait a long time for active sessions to become inactive. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view. This view returns only a single column: SID (Session ID). You can join it with V$SESSION to get more information about the session, as shown in the following example:
select bl.sid, user, osuser, type, program from v$blocking_quiesce bl, v$session se where bl.sid = se.sid;
Sum-Up
以前没有太注意到数据库还有这个quiesced状态模式，以后遇到类似问题的时候，需要多想一下。
---------
Configure EM may cause this issue as well....
So also: http://amardeepsidhu.com/blog/2011/03/04/waiting-for-resmgr-become-active-cant-login/
