Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Urgent help - SOS !!!

RE: Urgent help - SOS !!!

From: Shreeni <>
Date: Fri, 6 Oct 2006 09:25:41 -0500
Message-ID: <007501c6e953$4d7db840$6400a8c0@shreenivasa>

Finally I was able to get things going. But the road to success was as bizzare as distasteful. DBMS_REGISTRY_SYS was a invalid and if I ran catproc.sql then every single DBMS_* package would become invalid. So I did a shutdown abort, stopped all listeners on the box and started the db in restricted session and ran the utlrp.sql. Then catproc.sql and utlrp.sql again.  

I created a test user with dba privs and tried to connect. While the connect was hanging I was looking at V$SESSION_WAIT to see if any new rows showed up. After almost 5 - 10 mins a row showed up with "resmgr:waiting.....". I killed the user session and noticed that all DBA_RESOURCE* views were invalid. I validated all the views did a alter system set resource_manager_plan=' ' and bounced the database. Every thing is "cool" for the present. However the foll questions remain:


        When in restricted session should'nt the database throw an error if I try to connect as any user other than SYS ? 2.

        Why did the RESOURCE* views go bad ? Is this a common occurrence in 10.2.0.x ?

        Why did catproc.sql invalidate packages ? 4.

        Should'nt a row apprear in V$SESSION_WAIT the moment someone tries to connect ?

        Was there a better and graceful way out of this situation ?

Thanks and have a good day.  

(630) 401-2887 (Mobile)
(630) 837-1730 (Home)

From: Stefan Knecht [] Sent: Friday, October 06, 2006 1:07 AM
Subject: Re: Urgent help - SOS !!!

How exactly did that message look that told you dbms_application_info was "inaccessible" ?

Did you mean you ran utlrp.sql or actually pupbld.sql ?

Utlrp will recompile invalid objects, while pupbld will build the sql*plus product user profile tables - should be run in SYSTEM schema.


On 10/6/06, Shreeni <> wrote:

There's no error at all...the prompt just sits there. However if I kill the process then I get a message saying DBMS_APPLICATION_INFO is inaccessible. I ran the pupbld.sql to recreate but the same continues. I queried the DBA_DDL_LOCKS table and cleared all the locks using the DBMSLOCK.sql script and bounced the instance. I also checked the login triggers if any and there arent any.

Nothing has helped so far.  

Thanks and have a good day.  

(630) 401-2887 (Mobile)
(630) 837-1730 (Home)

From: [] On Behalf Of Baumgartel, Paul
Sent: Thursday, October 05, 2006 5:41 PM To: ''; Subject: RE: Urgent help - SOS !!!

One possibility: Is there a database login trigger that's failing? If so, no one can log in except those who have ADMINISTER DATABASE TRIGGER system privilege.  

Paul Baumgartel
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
Phone 212.538.1143

-----Original Message-----
From: [mailto: <>]On Behalf Of Shreeni
Sent: Thursday, October 05, 2006 6:37 PM To:
Subject: Urgent help - SOS !!!

On a development database instance 10gR2 on Solaris 10, all of a sudden no one can login except SYS or SYSTEM. However if I grant SYSDBA to SCOTT he can login. If I revoke the grant and make SCOTT a normal user then the connect as Scott/Tiger just hangs.  

There is nothing in Alert log or Udump trace files. I checked if somehow the instance had gone into restricted mode but it is not. I shut the database down and restarted but no help. It is not in archivelog mode. I checked the space on the server and it is ok. There are 4 other instances on the same box and they are fine.  

Do you guys have seen anything like this and can u give me any directions as where to start looking ?  

The Database is on Solaris SunFire V240  

Thanks and have a good day.  

(630) 401-2887 (Mobile)
(630) 837-1730 (Home)

Please access the attached hyperlink for an important electronic communications disclaimer:


Received on Fri Oct 06 2006 - 09:25:41 CDT

Original text of this message