From sqlplus prompt Connect username/password is hanging

Hi ,
Iam working as junior DBA , Iam facing an issue with two users intermittently i.e
there are two  database users app_prd and tst_prd . app_prd is used by the application which is developed in java to connect to oracle database and to perform db operations. tst_prd is used by developers for connecting to DB and performing DB operations.

Very frequently , when ever I login using the above two users from SQLPLUS , it hangs .

I am able to login into the DB as sysdba as below

#> SQLPLUS / as sysdba

prd01> -- After login i connect as app_prd it hangs
prd01> connect app_prd/prd ;

It hangs  as shown above . The database is in no archive log mode. Similarly the same thing happens with tst_prd

#> SQLPLUS / as sysdba
prd01> -- after that i connect as tst_prd it hangs
prd01> connect tst_prd/prd ;

Any help is much appreciated. Iam stuck with these two users . Kindly guide me what could be the issue and solution.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Are you sure you have one database with those two users or do you have two users in different databases?

I assume prd01 is the main database.

Any reason you connect 'as sysdba' first?

What happens if you just try to connect directly:

sqlplus  tst_prd/prd
sqlplus app_prd/prd

I'm thinking that you are connecting 'as sysdba' to a different database.

Verify your ORACLE_SID.  If the database is a remote one, you might need a tnsnames alias.

>>Very frequently

Define very frequently.  Every time, some times, etc...

By 'hang' do you mean it just never comes back or does it eventually generate an error?

I've really never seen a connect just hang and never return some error message eventually.
sam_2012Author Commented:
ORACLE_SID is same as prd01. Iam not connecting to an remote DB. I just mentioned that i can connect as sysdba.
Even when I type from the linux prompt
sqlplus app_prd/prd
it hangs
sam_2012Author Commented:
it happens very  frequently  and it does not return any error message.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Alexander Eßer [Alex140181]Software DeveloperCommented:
This thread seems to be related to this one

You might want to merge these two?!
sam_2012Author Commented:
No Alex , they are two different issues .
Alexander Eßer [Alex140181]Software DeveloperCommented:
Just a thought because you stated in both threads that the DB hangs/lags, so maybe the core problem is the same or similar (connected)...
The issues/symptoms might be different, but the reason could be the same ;-)
Geert GOracle dbaCommented:
do you have database logon triggers ?

check output of this statement
select * from dba_triggers
where trigger_type = 'AFTER EVENT'
  and substr(triggering_event, 1, 5) = 'LOGON'
  and substr(base_object_type, 1, 8) = 'DATABASE';
sam_2012Author Commented:
I do not have any database logon triggers ..
Geert GOracle dbaCommented:
since this is a connection problem ...
you'll only be able to find a problem for the newly connecting user with a session already connected

on the server hosting the db start an sqlplus session (probably best with a dba or sys user) and leave it running
>> i am assuming this succeeds sooner or later ... :)

when the connection problem happens again ...
check the server with this query (we call this Q the session waiters):
select s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(sql.sql_text, 1, 80)
  from v$sql     	 sql,
       v$session  s,
       v$session_wait sw
 where sql.hash_value(+) = s.sql_hash_value
   and sw.sid = s.sid
   and sw.event <> 'SQL*Net message from client'    
   and sw.event <> 'rdbms ipc message'              
   and sw.event <> 'pmon timer'                     
   and sw.event <> 'smon timer'                     
   and sw.event <> 'pipe get'                       
   and sw.event <> 'PL/SQL lock timer'
   and sw.event <> 'jobq slave wait'
   and sw.event not like 'Streams AQ%'
 group by s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(sql.sql_text, 1, 80)
 order by sid

Open in new window

the query should give the reason why your login is not returning
based on the output, further investigation will be needed

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Check the resources on the database server.  You might be maxing something out:  memory, CPU, disk, etc...

Also check the alert log to see if there is something in there.
johnsoneSenior Oracle DBACommented:
It sounds like you are waiting for the shared pool to flush.  Connecting as SYS you are bypassing certain things.  When connecting as a regular user you need to load packages and do some other housekeeping items.  SYS could also bypass logon triggers (although you said you don't have any).

I would be really curious what sessions are waiting on when you have the problem.
sam_2012Author Commented:
Hi  Geert_Gruwez,

Can you tell some details about the below events . like what are these events , why they are excluded etc.

and sw.event <> 'SQL*Net message from client'    
   and sw.event <> 'rdbms ipc message'              
   and sw.event <> 'pmon timer'                    
   and sw.event <> 'smon timer'                    
   and sw.event <> 'pipe get'                      
   and sw.event <> 'PL/SQL lock timer'
   and sw.event <> 'jobq slave wait'
   and sw.event not like 'Streams AQ%'

@  johnsone,

Can you tell what are things that are by passed when I login as SYS and what are the things are not bypasses as normal user.

This would help me in next time when I get this error
johnsoneSenior Oracle DBACommented:
The events that are being filtered out are background processes and inactive sessions.  You really don't care about those, you care about the waits for the user processes.

There are some internal packages that are loaded during normal user login that are bypassed when SYS logs in.  For SYS they are loaded on demand because SYS can be connecting to a database that isn't running and it wouldn't be possible to load the packages.  There is also some session accounting information that is not gathered for SYS (like application and those types of things).  Additionally, logon triggers could be bypassed in some situations.
sam_2012Author Commented:
Hi  Geert_Gruwez,

below is the output I got after firing the query you have shared , can you explain me the output of it.

8      0      class slave wait      42577      444286      
43      621496260      SQL*Net message to client      1297      0      select s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(sql.sq
80      0      ASM background timer      65031      1275960      
157      0      VKTM Logical Idle Wait      1      1275962      
235      0      DIAG idle wait      47013      1275960      
236      0      wait for unread message on broadcast channel      29507      1275960      
313      0      PING      39876      1275960      
391      0      DIAG idle wait      58155      1275960      
469      0      ges remote message      10252      1275960      
508      0      gcs remote message      48285      1275960      
547      0      gcs remote message      47453      1275960      
625      0      GCR sleep      58651      1275960      
704      0      Space Manager: slave idle wait      74      362      
860      0      wait for unread message on broadcast channel      49559      1275942
sam_2012Author Commented:
Answered my problem, I fired the query and found that there was an row cache lock  and resolved it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.