Just an idea:
You raise an alarm when DBD::Oracle/OCI takes too long waiting for a
connection. If you just quit the connection attempt then, OCI may never
have a chance to clean up its connection attempt (especially the opened
UDP port).
Am Do, 15.01.2009, 04:01, schrieb Martin Gainty:
>
> Jaya-
>
> Port 1526 sounds odd as the default port for oracle is 1521
>
> Anyone see anything obvious?
> Martin
> ______________________________________________
> Disclaimer and confidentiality note
> Everything in this e-mail and any attachments relates to the official
> business of Sender. This transmission is of a confidential nature and
> Sender does not endorse distribution to any party other than intended
> recipient. Sender does not necessarily endorse content contained within
> this transmission.
>
>
>
>
>> Subject: DBI connect and ports issue
>> Date: Wed, 14 Jan 2009 16:31:12 -0500
>> From: jaya.megh...@vonage.com
>> To: dbi-users@perl.org
>>
>> Hi,
>>
>>
>>
>>
>>
>> My first post too the list!
>>
>>
>>
>> First some environment details:
>>
>> Red Hat Enterprise Linux WS release 4 (Nahant Update 4)
>>
>> Perl Version = 5.008007
>>
>> DBI Version = 1.48
>>
>> DBD::Oracle Version = 1.16
>>
>> Issue:
>>
>> My application connects to Oracle and some times the DB host is down. I
>> use sigaction to put a timeout on DBI->connect and retry again till
>> connection is established.
>>
>> The problem is: I see a lot of unused ports for the application process.
>>
>> 1). TCP ports in CLOSE_WAIT and ESTABLISHED states.
>>
>> 2). Ephemeral UDP ports.
>>
>> These ports accumulate over time as DB connection is lost and
>> re-established.
>>
>> I use lsof and netstat commands to check the stats on ports.
>>
>>
>>
>> To reproduce the issue I have this script (see below), I don't see any
>> TCP ports waiting in CLOSE_WAIT and ESTABLISHED states but each time the
>> script tries to connect (and is timed-out) a new ephemeral UDP port is
>> created.
>>
>> I use iptables to DROP any packets to DB host to simulate DB host down
>> scenario.
>>
>> ************************************************************************
>> ******
>>
>> Output:
>>
>> Here is the output of script and Port stats:
>>
>>
>>
>> $perl sigTry.perl
>>
>> I am in handler
>>
>> Trying to connect
>>
>> Error connecting to DB
>>
>> I out of handler
>>
>> <Hit Return key>
>>
>> I am in handler
>>
>> Trying to connect
>>
>> Error connecting to DB
>>
>> I out of handler
>>
>> <Hit Return key>
>>
>> I am in handler
>>
>> Trying to connect
>>
>> Error connecting to DB
>>
>> I out of handler
>>
>> #Command to see ports stats
>>
>> $lsof -p `pgrep perl` | egrep "TCP|UDP"
>>
>> perl 28572 jmeghani 4u IPv4 2132909 UDP
>> localhost:33544
>>
>> perl 28572 jmeghani 5u IPv4 2132913 TCP host1:33466->
>> someDBhost:1526 (SYN_SENT)
>>
>> perl 28572 jmeghani 7u IPv4 2132924 UDP
>> localhost:33545
>>
>> perl 28572 jmeghani 8u IPv4 2132926 TCP host1->
>> someDBhost:1526 (SYN_SENT)
>>
>> perl 28572 jmeghani 10u IPv4 2132927 UDP
>> localhost:33546
>>
>> perl 28572 jmeghani 11u IPv4 2132929 TCP
>> host1:33468->someDBhost:1526 (SYN_SENT)
>>
>> SYN_SENT ports go away with time but UDP ports (33544-33546) don't.
>>
>> ************************************************************************
>> ******
>>
>> Finally actual script (I have removed actual DB details)
>>
>> Script:
>>
>> ************************************************************************
>> ******
>>
>> $ENV{ORACLE_HOME} = '/usr/vendor/pkg/oracle/product/10.2.0';
>>
>> use strict;
>>
>> use warnings;
>>
>> use POSIX;
>>
>> use DBI;
>>
>> my $db_hostname = 'someDBhost';
>>
>> my $db_port = 1526;
>>
>> my $db_user = 'test';
>>
>> my $db_pass = 'test';
>>
>> my $db_sid = 'SID';
>>
>> my $dbh = undef;
>>
>> sub finished {
>>
>> print "I am outta here\n";
>>
>> exit(1);
>>
>> }
>>
>> sub handler {
>>
>> print "I am in handler\n";
>>
>> if (!defined($dbh)) {
>>
>> print "Trying to connect\n";
>>
>> my $old_action = POSIX::SigAction->new();
>>
>> my $action = POSIX::SigAction->new( sub{die 'TIMEOUT';
>> });
>>
>> sigaction(SIGALRM,$action, $old_action);
>>
>> my $old_t;
>>
>> eval {
>>
>> sigprocmask(SIG_UNBLOCK,
>> POSIX::SigSet->new(SIGALRM));
>>
>> $old_t = alarm(2);
>>
>> $dbh =
>> DBI->connect("dbi:Oracle:host=$db_hostname;sid=$db_sid;port=$db_port",
>>
>> $db_user, $db_pass,
>>
>> {PrintError => 0, RaiseError => 1,
>> AutoCommit => 0});
>>
>> alarm(0);
>>
>> print "I am connected\n";
>>
>> };
>>
>> alarm(0);
>>
>> sigaction(SIGALRM, $old_action);
>>
>> alarm($old_t);
>>
>> if ($@) {
>>
>> if(defined($dbh)){
>>
>> print "dbh was defined\n";
>>
>> $dbh=undef;
>>
>> }
>>
>> print("Error connecting to DB\n");
>>
>> }
>>
>> }
>>
>> else {
>>
>> print "Already connected\n";
>>
>> }
>>
>> print "I out of handler\n";
>>
>> }
>>
>> sub main {
>>
>> my $action_die = POSIX::SigAction->new(\&finished);
>>
>> sigaction(SIGINT,$action_die);
>>
>> my $old_action = POSIX::SigAction->new();
>>
>> my $action = POSIX::SigAction->new(\&handler);
>>
>> sigaction(SIGALRM,$action,$old_action);
>>
>> while(1)
>>
>> {
>>
>> alarm(3);
>>
>> my $foo = <STDIN>;
>>
>> }
>>
>> }
>>
>> exit(main());
>>
>> **********************************************************************
>>
>>
>>
>> If you are still reading ;) any help is greatly appreciated.
>>
>>
>>
>>
>>
>> Jaya
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
> _________________________________________________________________
> Windows Live*: Keep your life in sync.
> http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_012009
