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
