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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution 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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED]
RE: Oracle connect hangs forever
-Original Message- From: Scott T. Hildreth [mailto:[EMAIL PROTECTED] 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. [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 to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution 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 [EMAIL PROTECTED] To: [EMAIL PROTECTED], 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:[EMAIL PROTECTED] 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