oracle出现resmgr:become active等待事件分析

用户无法登录,用sys或system用户可以登录
第一反映查看数据库日志,日志正常
第二查看数据库等待事件分析
SQL> select event,count(*) from v$session_wait group by event;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: waiting for messages in the queue 1
SQL*Net message to client 1
gcs remote message 6
SQL*Net message from client 127
smon timer 1
pmon timer 1
DIAG idle wait 1
wait for possible quiesce finish 1
rdbms ipc message 16
ges remote message 1
Streams AQ: qmn slave idle wait 1
EVENT COUNT(*)
---------------------------------------------------------------- ----------
ASM background timer 1
resmgr:become active 197
Streams AQ: qmn coordinator idle wait 1
Streams AQ: waiting for time management or cleanup tasks 1
class slave wait

resmgr:become active 等待事件非常严重,查看metalink id340463.1
建议检查resouce_manger_plan设置和v$rsrc_plan
resouce_manger_plan和v$rsrc_plan的值为INTERNAL_QUIESCE,
起用了INTERNAL_QUIESCE后只允许SYS_GROUP中的user登陆 SQL> show parameter plan

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string INTERNAL_QUIESCE

那解决方法就比较简单了
取消resouce_manger_plan的值即可
alter system SET resource_manager_plan = '';
resmgr:become active等待事件消失,数据库其他会话登录正常。

总结下解决问题的思路:
1.查数据库日志
2.查数据库等待事件，看是否有异常
3.看性能收集报告比如statspack,awr,addm等