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
