waiting for resmgr:become active – can’t login

Some time back, I was at a client where the customer complained that no one was able to log in to the database. It was Oracle 10.2.0.4 running on HP-Ux. I logged in to the database and checked the wait events:

SQL> @wait

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
wait for possible quiesce finish                                          1
Streams AQ: qmn coordinator idle wait                                     1
Streams AQ: qmn slave idle wait                                           1
Streams AQ: waiting for time management or cleanup tasks                  1
SQL*Net message to client                                                 1
smon timer                                                                1
pmon timer                                                                1
jobq slave wait                                                           4
rdbms ipc message                                                        11
SQL*Net message from client                                              27
resmgr:become active                                                    322

11 rows selected.

SQL>

Tanel’s snapper showed something like:

SQL> @snapper ash 5 1 all
Sampling with interval 5 seconds, 1 times...

-- Session Snapper v3.11 by Tanel Poder @ E2SN ( http://tech.e2sn.com )

-----------------------------------------------------------------------
Active% | SQL_ID          | EVENT                     | WAIT_CLASS
-----------------------------------------------------------------------
 26322% | 4ffu7nb93c2c9   | resmgr:become active      | Scheduler
  1900% | 2wn958z7gzh57   | resmgr:become active      | Scheduler
  1400% | 9d9bg2r538nd2   | resmgr:become active      | Scheduler
   600% | 4d3k70q6y344k   | resmgr:become active      | Scheduler
   500% | d6vwqbw6r2ffk   | resmgr:become active      | Scheduler
   500% | 4tsrz92mmshbw   | resmgr:become active      | Scheduler
   200% | 37td1bbvc1a69   | resmgr:become active      | Scheduler
   100% | ftdjfxws0s8q9   | resmgr:become active      | Scheduler
   100% | 41apc1bjqrfbv   | resmgr:become active      | Scheduler
   100% | af9d8aqtkvn02   | resmgr:become active      | Scheduler

--  End of ASH snap 1, end=2011-02-10 11:06:40, seconds=5, samples_taken=23

PL/SQL procedure successfully completed.

SQL>

If we check the description of the wait event, it says:

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.

But if we check the resource_limit settings:

SQL> show parameter resource

NAME_COL_PLUS_SHOW_PARAM   TYPE		VALUE_COL_PLUS_SHOW_PARAM
----------------------- -----------     --------------------------
resource_limit            boolean		FALSE
resource_manager_plan     string

SQL>

What ? Resource manager is not enabled. But why all the sessions are waiting for resmgr:become active and nobody is able to login ?

A bit of googling lead me to this page from where I got the clue.

Generally, this wait situation occurs when you execute certain EMCA operations such as the operation for creating the EM repository. As a result of these operations, the systems implicity switches to QUIESCE mode. Therefore, all database connections (except users SYS and SYSTEM) must wait for “resmgr:become active”. In this case, refer to Note 1044758 and execute the following command if necessary:

ALTER SYSTEM UNQUIESCE;

I asked around in the DBA team and one of the guys was trying to configure EM for the database due to which system switched tto QUIESCE mode and all the sessions were waiting on resmgr:become active.

After canceling the operation, the wait event was gone and everything was working normally.

Leave a Reply

Your email address will not be published. Required fields are marked *