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.
Issues in upgrading from 9i to 10g
Last week I had a chance to upgrade a 9.2.0.7 database to 10.2.0.5. The size of the database was around 800 GB. The major applications connecting to the database were developed in Pro*C and Oracle Forms. The upgrade itself pretty smooth but there were few glitches around that needed to be handled. Just thought about documenting all the issues:
-
Few users in the database were assigned the CREATE SESSION privilege through a password protected role (That role was the default role for that user). 10.2.0.5 onwards, password protected roles can’t be set as default roles. The alternate is to either disable the password for the role or assign CREATE SESSION directly to the user, not through a role.
-
After the upgrade, few procedures became invalid and while compiling started giving ORA-00918: COLUMN AMBIGUOUSLY DEFINED. The issue was bug 2846640 which is fixed in 10.2. Actually, in few of the queries using ANSI syntax, the developer didn’t qualify the column names with table names. It worked fine in 9i but due to the bug getting fixed in 10g, it started giving ORA-00918. The simple solution is to prefix the column name with the table name.
-
Few of the application schema owner users complained that they were not able to modify the procedures/packages in their own schemas. The schemas were not assigned CREATE PROCEDURE privilege but as per documentation, they should be able to modify the existing procedures/packages owned by them. This again is a documentation bug. It worked fine in 9i but in 10g onwards you need to have either a CREATE PROCEDURE or ALTER ANY PROCEDURE privilege (a risky one) to be able to edit the PL/SQL units in your own schema.
These were few of the issues encountered, rest of the upgrade was super smooth !
Happy upgrading !
ORA-39083: Object type INDEX failed to create with error
Another let-us-help-Google post 
.
While running impdp import in 11g, you hit:
ORA-39083: Object type INDEX failed to create with error: ORA-14102: only one LOGGING or NOLOGGING clause may be specified
It is related to bug 9015411 where DBMS_METADATA.GET_DDL creates incorrect create index statement by dumping both LOGGING and NO LOGGING clauses. Due to this the CREATE INDEX statement, while running impdp fails with the above error. It applies to 11.2.0.1 (Metalink doc id 1066635.1)
Fix is to install the patch, if it is available for your platform. Another workaround is given in this OTN thread i.e. strip the create index statement of storage related information by using TRANSFORM=SEGMENT_ATTRIBUTES:N:INDEX & TRANSFORM=SEGMENT_ATTRIBUTES:N:CONSTRAINT
EXP-00056: ORACLE error 28002 encountered
Yesterday, a friend of mine asked me about an error he was getting while running a schema level export in Oracle 8i:
exp system/manager@DB owner=ABC file=ABC.dmp log =ABC.log Export: Release 8.1.7.1.0 - Production on Fri Nov 12 04:21:05 2010 (c) Copyright 2000 Oracle Corporation. All rights reserved. EXP-00056: ORACLE error 28002 encountered ORA-28002: the password will expire within 11 days EXP-00056: ORACLE error 24309 encountered ORA-24309: already connected to a server EXP-00000: Export terminated unsuccessfully
I googled and metalink’ed (oops…is there anything like that ? ) a bit and found that it was bug 1654141 where user accounts in grace period cannot perform export. It is fixed in Oracle 9i (version 9.0.0.0 as per metalink). The obvious work around is to change the password and then try again. Thought about posting it here so that Google can give little better results if someone in trouble comes searching for it .
Sangam 10
It has been almost an year since i posted something (useful) here. The last post was also a crappy one 
. Well, it all boils down to sheer laziness . Now, i think the time has come to be regular again. Here i am getting a good start talking about Sangam10, i attended last week. It was a great opportunity to meet so many fellow Oracle professionals and most awesomely to meet & see Jonathan Lewis talk about Performance & Tuning. As expected the whole experience was amazing. It was a 2 day event where Jonathan was delivering 2 half day seminars on SQL Tuning and there were other break out sessions as well. We had planned to go a day in advance so me, Aman, Ankit & Neeraj reached Hyderabad on 2nd Sep.
Jonathan’s presentations were simply amazing. His knowledge about how things work (and why they work this way not that) is simply awesome. He is an inspiration for newbies like us and there was so much to learn from him. Few of the quick tips that i picked up from him:
- Don’t believe what you read or hear. Make small test cases to test and confirm how things work & how they don’t. He said that he has around 2000 test cases on his laptop. Some of them ready to be fired on Oracle database 12g .
- Always document your findings. At a later date you only won’t be able to remember that something that you already faced and solved something you are stuck in. If you document things properly, you would always remember a bit of it and you can search it in a minute.
Also i got to meet & attend presentation of good friend Francisco Munoz Alvarez. I have been in touch with him since more than 2 years but this was for the first time i was meeting him in person. Also his presentation on how to become a good DBA was really awesome. Enjoyed every bit of it.
Two of my colleagues Vivek Sharma and Rahul Dutta were also presenting, so got a chance to see their presentations too. Vivek talked about developing scalable applications and Rahul’s presentation was about developing a EBS reporting solution using Oracle streams.
I attended some part of Mark Rittman‘s session also. I am not much into data warehousing but Mark is such a respected name so wanted to be present in his session .
I also met and attended one of the presentation of Iggy Fernandez. He talked about 52 weeks in the life of a database. I couldn’t attend his other presentation on reading execution plans as Vivek was presnting in the same time slot.
Overall, it was an amazing experience and i am already looking forward to attending Sangam (or whatever it would be called ) 11 !
Read Aman’s post about Sangam 10.