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等