Oracle connect hangs forever

I plan on using Sys::SigAction to wrap an alarm around the connect, but
I thought I would post to see if anyone else has seen this problem. I have
a process that runs in cron every 2 minutes, it processes files that are ftp'd
into our site. I set a lock file so that if the previous run is still running
it will not start up and send an email indicating that a previous process is still
running. Well we moved to this processing was moved to a new server which is running
Oracle version 10.2.0.1.0. I've been having a problem where this process is hanging
for serveral hours (occurs over night) until it is killed. I run an strace on the pid
and it is hanging on a read of fd 6, which is the Oracle tns listener. I don't believe
any thing has processed, because there are no raised errors. I think that it has to be
hanging on the connect, since I believe the tns listener accepts a connection and then
gives the connect process a direct socket to Oracle. As I stated earlier, I was
wondering if anyone else on the list has problems with 10g tns listener?

Perl : 5.008008 (i686-linux)
OS : linux (2.6.14.6)
DBI : 1.50
DBD::mysql : 3.0002
DBD::Sponge : 11.10
DBD::SQLite : 1.11
DBD::Proxy : 0.2004
DBD::Oracle : 1.16
DBD::Multiplex : 1.98
DBD::File : 0.33
DBD::ExampleP : 11.12
DBD::DBM : 0.03


LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-APR-2006 15:27:25


Thanks,
STH

--
Scott T. Hildreth

RE: Oracle connect hangs forever

Get a 10046 trace of the process. This will tell you exactly what the
DB is waiting for.

I have had no issues with the TNS listener to date.l

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Scott T. Hildreth [mailto:shildreth@allantgroup.com]=20
Sent: Tuesday, April 25, 2006 2:28 PM
To: dbi-users@perl.org
Subject: Oracle connect hangs forever

I plan on using Sys::SigAction to wrap an alarm around the connect, but=20
I thought I would post to see if anyone else has seen this problem. I
have
a process that runs in cron every 2 minutes, it processes files that are
ftp'd
into our site. I set a lock file so that if the previous run is still
running
it will not start up and send an email indicating that a previous
process is still
running. Well we moved to this processing was moved to a new server
which is running
Oracle version 10.2.0.1.0. I've been having a problem where this
process is hanging
for serveral hours (occurs over night) until it is killed. I run an
strace on the pid
and it is hanging on a read of fd 6, which is the Oracle tns listener.
I don't believe
any thing has processed, because there are no raised errors. I think
that it has to be
hanging on the connect, since I believe the tns listener accepts a
connection and then
gives the connect process a direct socket to Oracle. As I stated
earlier, I was=20
wondering if anyone else on the list has problems with 10g tns listener?

Perl : 5.008008 (i686-linux)
OS : linux (2.6.14.6)
DBI : 1.50
DBD::mysql : 3.0002
DBD::Sponge : 11.10
DBD::SQLite : 1.11
DBD::Proxy : 0.2004
DBD::Oracle : 1.16
DBD::Multiplex : 1.98
DBD::File : 0.33
DBD::ExampleP : 11.12
DBD::DBM : 0.03


LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-APR-2006
15:27:25


Thanks,
STH

--=20
Scott T. Hildreth

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: Oracle connect hangs forever

On Tue, 2006-04-25 at 15:19 -0600, Reidy, Ron wrote:
> Get a 10046 trace of the process. This will tell you exactly what the
> DB is waiting for.

What makes it worse or harder to track down is it seems to be momentary
problem. While this process hangs, others are connecting
no problem. It's almost as the Listener drops a packet or something to
that effect and the DBI connect is still waiting for it to return. This
is a high traffic DB so it may be easier to work around, unless the
problem worsens. Trace files would be huge.

>
> I have had no issues with the TNS listener to date.l
>
> --
> Ron Reidy
> Lead DBA
> Array BioPharma, Inc.
>
> -----Original Message-----
> From: Scott T. Hildreth [mailto:shildreth@allantgroup.com]
> Sent: Tuesday, April 25, 2006 2:28 PM
> To: dbi-users@perl.org
> Subject: Oracle connect hangs forever
>
> I plan on using Sys::SigAction to wrap an alarm around the connect, but
> I thought I would post to see if anyone else has seen this problem. I
> have
> a process that runs in cron every 2 minutes, it processes files that are
> ftp'd
> into our site. I set a lock file so that if the previous run is still
> running
> it will not start up and send an email indicating that a previous
> process is still
> running. Well we moved to this processing was moved to a new server
> which is running
> Oracle version 10.2.0.1.0. I've been having a problem where this
> process is hanging
> for serveral hours (occurs over night) until it is killed. I run an
> strace on the pid
> and it is hanging on a read of fd 6, which is the Oracle tns listener.
> I don't believe
> any thing has processed, because there are no raised errors. I think
> that it has to be
> hanging on the connect, since I believe the tns listener accepts a
> connection and then
> gives the connect process a direct socket to Oracle. As I stated
> earlier, I was
> wondering if anyone else on the list has problems with 10g tns listener?
>
> Perl : 5.008008 (i686-linux)
> OS : linux (2.6.14.6)
> DBI : 1.50
> DBD::mysql : 3.0002
> DBD::Sponge : 11.10
> DBD::SQLite : 1.11
> DBD::Proxy : 0.2004
> DBD::Oracle : 1.16
> DBD::Multiplex : 1.98
> DBD::File : 0.33
> DBD::ExampleP : 11.12
> DBD::DBM : 0.03
>
>
> LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-APR-2006
> 15:27:25
>
>
> Thanks,
> STH
>
--
Scott T. Hildreth

RE: Oracle connect hangs forever

-----Original Message-----
From: Scott T. Hildreth [mailto:shildreth@allantgroup.com]=20
Sent: Tuesday, April 25, 2006 3:43 PM
To: Reidy, Ron
Cc: dbi-users@perl.org
Subject: RE: Oracle connect hangs forever

On Tue, 2006-04-25 at 15:19 -0600, Reidy, Ron wrote:
> Get a 10046 trace of the process. This will tell you exactly what the
> DB is waiting for.

What makes it worse or harder to track down is it seems to be momentary
problem. While this process hangs, others are connecting
no problem. It's almost as the Listener drops a packet or something to
that effect and the DBI connect is still waiting for it to return. This
is a high traffic DB so it may be easier to work around, unless the
problem worsens. Trace files would be huge. =20

[rr] Do other processes experience this issue? I suggest this course
of action because it only happens at a certain period of the day/night,
and because you do not seem sure where the issue is. If the program
cannot contact the DB (i.e. the dedicated server process is not
created), then a trace file will show this (actually, it will show
nothing). You could then move on to setting listener trace parameters.
Of course, this runs the risk of creating many large files and slowing
down your listener connections, but, at least you will be closer to
discovering the problem.


This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: Oracle connect hangs forever

Hi,

Note:340091.1 may be of interest to you concerning listener hanging on
10g.

Regards,

Michael


> Date: Tue, 25 Apr 2006 15:19:27 -0600
> From: "Reidy, Ron"
> To: shildreth@allantgroup.com, dbi-users@perl.org
> Subject: RE: Oracle connect hangs forever
>
> Get a 10046 trace of the process. This will tell you exactly what the
> DB is waiting for.
>
> I have had no issues with the TNS listener to date.l
>
> --
> Ron Reidy
> Lead DBA
> Array BioPharma, Inc.
>
> -----Original Message-----
> From: Scott T. Hildreth [mailto:shildreth@allantgroup.com]
> Sent: Tuesday, April 25, 2006 2:28 PM
> To: dbi-users@perl.org
> Subject: Oracle connect hangs forever
>
> I plan on using Sys::SigAction to wrap an alarm around the connect, but
> I thought I would post to see if anyone else has seen this problem. I
> have
> a process that runs in cron every 2 minutes, it processes files that are
> ftp'd
> into our site. I set a lock file so that if the previous run is still
> running
> it will not start up and send an email indicating that a previous
> process is still
> running. Well we moved to this processing was moved to a new server
> which is running
> Oracle version 10.2.0.1.0. I've been having a problem where this
> process is hanging
> for serveral hours (occurs over night) until it is killed. I run an
> strace on the pid
> and it is hanging on a read of fd 6, which is the Oracle tns listener.
> I don't believe
> any thing has processed, because there are no raised errors. I think
> that it has to be
> hanging on the connect, since I believe the tns listener accepts a
> connection and then
> gives the connect process a direct socket to Oracle. As I stated
> earlier, I was
> wondering if anyone else on the list has problems with 10g tns listener?
>
> Perl : 5.008008 (i686-linux)
> OS : linux (2.6.14.6)
> DBI : 1.50
> DBD::mysql : 3.0002
> DBD::Sponge : 11.10
> DBD::SQLite : 1.11
> DBD::Proxy : 0.2004
> DBD::Oracle : 1.16
> DBD::Multiplex : 1.98
> DBD::File : 0.33
> DBD::ExampleP : 11.12
> DBD::DBM : 0.03
>
>
> LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-APR-2006
> 15:27:25
>
>
> Thanks,
> STH
>
>

---//---
"He who learns must suffer. And even in our sleep,
pain that cannot forget, falls drop by drop upon the heart;
and, in our own despair, against our will,
comes wisdom to us by the awful grace of God."
--- Aeschylus