Re: sqlplus scott/tiger@tnsname hangs
- From: Prasad <p4cldba@xxxxxxxxx>
- To: "Dan Norris" <dannorris@xxxxxxxxxxxxx>, krish.hariharan@xxxxxxxxxxxx
- Date: Fri, 18 Apr 2008 20:55:36 -0700
Thanks Dan/Krish .
I was not able to get any valuable info so far . For time being I have
suggested to enable the listener logging with trace_level_client=16 and wait
for the next occurrance. I heard that they have a similar issue before 2
months . so hopefully I will be able to catch it the next time by following
both of your suggestion..
Thanks
-Prasad
On Fri, Apr 18, 2008 at 4:26 PM, Dan Norris <dannorris@xxxxxxxxxxxxx> wrote:
> If restarting the listener "fixed" the problem, then I'd have to believe
> that your issue was either related to networking issues on the DB server,
> the listener process somehow became unresponsive, or your dispatcher(s)
> became unresponsive. The only way to investigate would be to reproduce the
> issue with SQL*Net tracing on (trace_level_client=16) and see what's in the
> trace.
>
> It might also be interesting to know how many connection requests the
> listener is servicing. I suppose it's possible that the tcp queuedepth is
> too small to handle all the requests if there some some sort of a login
> storm or something. Without listener logging enabled, you'd only be able to
> check v$sysstat (where name = 'logons cumulative') periodically to see how
> many logins are occurring. Statspack should also be gathering that
> information, so consult there for historical purposes.
>
> Dan
>
>
> Prasad wrote:
>
> On 17/04/2008, *Prasad* <p4cldba@xxxxxxxxx> wrote:
> >
> > Greetings.
> >
> > This was something that happened today in one of the database and I am
> > looking for list's
> > response on this. This is a Oracle 9.2.0.7 database running solaris 9.
> >
> > Around 10am the Unix group called me and told me that one of the
> > osprocess is taking more cputime
> > then what it normally takes. and upon investigation I found that this
> > database was running on
> > shared server mode with the osprocess being the only dispatcher at that
> > time . So I created another
> > 3 dispatcher and stopped the existing dispatcher d000.
> >
> > and everything was going fine until 2.30pm when the user called me and
> > told me that he is not able to
> > make connection using servicename. so I did a test
> >
> > test1- sqlplus '/as sysdba'
> > test2- sqlplus scott/tiger
> > test3- sqlplus scott/tiger@tnsname
> >
> >
> > I was able to logon to database successfully with test1 and test2 .
> > However when I did test3
> > it just hangs and it didnt allow me to interrupt it also.
> >
> > I checked if the dispatchers were busy or not and didnt found anything
> > significant.
> > The listener logging was not enabled so the initial reaction was to
> > shutdown and start the
> > listener which I did and at this time the test3 worked fine . However
> > the user was still not able to
> > access its application and I observed a large number of session in
> > dba_blocker and dba_waiter
> > and also few deadlock error in alert log file . So I killed the session
> > on the beginning of the queue
> > and it immediately releases all the sessions in dba_blockers and
> > dba_waiters and the user was
> > able to access the application.
> >
> > Here comes the hard part what is the root cause of this issue?
> >
> > Please advise.
> >
> > Thanks
> > -Prasad
> >
>
>
Other related posts:
