Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 32190 invoked from network); 17 Apr 2008 05:47:31 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.server4you.com with SMTP; 17 Apr 2008 05:47:30 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 737048420EB; Thu, 17 Apr 2008 06:47:30 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 10988-05; Thu, 17 Apr 2008 06:47:30 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D0CE58420CB; Thu, 17 Apr 2008 06:47:29 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 17 Apr 2008 06:09:11 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 25BE9841E76 for ; Thu, 17 Apr 2008 06:09:11 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07667-04 for ; Thu, 17 Apr 2008 06:09:11 -0400 (EDT) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.178]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B129E8412A2 for ; Thu, 17 Apr 2008 06:09:10 -0400 (EDT) Received: by wa-out-1112.google.com with SMTP id k22so2930waf.18 for ; Thu, 17 Apr 2008 03:09:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=BKa18RCS6JHByoC+jaOxne6szM9/LpSysEHbVqpRHaE=; b=mc9aMYwVG0iIdni0knsFCEB8vWPyjd8i8K14e7YJqEpFWXDG96fNVb/GD0R/EikGdAiOcB0FKy3KXbzT9nDRWNWgbSZUBug3hCAyC7v42pOWK5GK/+ni4ND4jDv6xXCb9OCfXS9gc1fv8vZvn7joJFl9QByh4TxDs6ASvc6X9KQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=uuyS5aQ9lBd66xfm78MELAds/T6HVi3u8UXKKmeDhE8IoBhzqEHWThe9ipFjJCsB21RVsRIxJHcAfqymKF5ieDdGU/tpAonr85sCXgo/hGdjYA3tFgQWvsGs01xNLgjXtqEeVjjGkB5jMvKUCG2/X/EAuVnF+oCl0ZfS4HIqglU= Received: by 10.114.168.1 with SMTP id q1mr1189603wae.96.1208426950033; Thu, 17 Apr 2008 03:09:10 -0700 (PDT) Received: by 10.114.25.16 with HTTP; Thu, 17 Apr 2008 03:09:09 -0700 (PDT) Message-ID: <13efe57b0804170309we256dfdga9f68ebdf06585ff@mail.gmail.com> Date: Thu, 17 Apr 2008 11:09:10 +0100 From: "David cheyne" To: p4cldba@gmail.com Subject: Re: sqlplus scott/tiger@tnsname hangs Cc: ORACLE-L In-Reply-To: <77a615a70804162254l25e186c7ue947554189f4def0@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_24867_14213404.1208426950025" References: <666b99c70804162130rbbe674eg325aae2bf35acae5@mail.gmail.com> <77a615a70804162254l25e186c7ue947554189f4def0@mail.gmail.com> X-archive-position: 7272 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: david.cheyne@gmail.com Precedence: normal Reply-to: david.cheyne@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_24867_14213404.1208426950025 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Prasad, You say that the listener log was not enabled. Have you switched off the logging or was it not updating? You don't say which O/S your on, but you can easily automate its management. Without bringing the listener down on Unix/Linux: cp listener.log listener.OLD cat /dev/null > listener.log This copies the log to another name and blanks the current log. You just may need to watch disk space if your keeping multiple copies (tar/zip?) David Cheyne On 17/04/2008, Jack van Zanen wrote: > > maybe check the size of your listener.log file > > I have seen occasions where a large log file saw this behaviour > > Jack > > > On 17/04/2008, Prasad 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 > > > > > > -- > J.A. van Zanen -- ~~~~~~~~~~~~~~~~~~~~~~~ David Cheyne BA(hons.) Oracle Database Administrator -- Outside of a dog, a book is a man's best friend. Inside of a dog it's too dark to read. Groucho Marx ------=_Part_24867_14213404.1208426950025 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Prasad,
 
You say that the listener log was not enabled.  Have you switched off the logging or was it not updating?  You don't say which O/S your on, but you can easily automate its management.
 
Without bringing the listener down on Unix/Linux:
 
cp listener.log listener.OLD
cat /dev/null  > listener.log 
 
This copies the log to another name and blanks the current log.  You just may need to watch disk space if your keeping multiple copies (tar/zip?)
 
 
 
David Cheyne
 
 
On 17/04/2008, Jack van Zanen <jack@vanzanen.com> wrote:
maybe check the size of your listener.log file
 
I have seen occasions where a large log file saw this behaviour
 
Jack

 
On 17/04/2008, Prasad <p4cldba@gmail.com> 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



--
J.A. van Zanen



--
~~~~~~~~~~~~~~~~~~~~~~~
David Cheyne
BA(hons.)
Oracle Database Administrator

-- Outside of a dog, a book is a man's best friend. Inside of a dog it's too dark to read.
Groucho Marx ------=_Part_24867_14213404.1208426950025-- -- http://www.freelists.org/webpage/oracle-l