resmgr: become active

突然发现用sqlplus连接不了数据库了，但也不报错，只是卡住在那：

 

查看数据库服务器的listener.log

显示已经连接上来了，

而且在用system用户在客户端和本地都是可以连接的，查看v$session：

 

有161多个resmgr:become active等待。

 

查看文档，发现2中可能性：

1、跟consumer group设置的active session limit有关

 

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

但是这个库一直是我在管，并没有设置这类限制。

2、实例处于quiesceing状态的时候也会出现这种情况。

"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. "

感觉第2中可能性较为吻合。

于是查询实例状态：

 

果然处于quiescing状态。

ACTIVE_STATE

• 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.

且alert日志中存在一条记录：

 

 

至于为什么数据库会被置为quiescing状态，原因未知，或许是认为的，也可能是orabf暴力破解密码造成的。

 

 

于是想恢复吧。

用如下sql找出造成quiescing的session：

select sid,serial#,event,program,sql_id,prev_sql_id,logon_time from v$session where event like 'wait%' ;

kill该session，实例恢复正常。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23132642/viewspace-718804/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23132642/viewspace-718804/