Bug #41249 for DBD-Oracle: possible DBD::Oracle Red Hat 5 Enterprise Linux 5.1 bug

Brief Description: Try to connect to multiple databases, in series, and execute a simple SQL statement (select ...). If there is a problem connecting to one database my script is unable to connect to any database after this problem database. Test Run 1: Connect to three databases, fcstdbi, fctsdbi, osdbi. The fctsdbi database server is down. The problem is that the osdbi db is running and I should connect successfully but I don't. oracle@orabckprd> date Wed Nov 26 15:19:56 EST 2008 oracle@orabckprd> tnsping fcstdbi TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 26-NOV-2008 15:20:01 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /oracle/product/10.2/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = osint.iso-ne.com) (PORT = 1521))) (CONNECT_DATA= (SERVICE_NAME=fcstdbi.world))) OK (0 msec) oracle@orabckprd> tnsping fctsdbi TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 26-NOV-2008 15:20:06 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /oracle/product/10.2/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP) (HOST = mislop0.iso-ne.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME=fctsdbi.world))) TNS-12541: TNS:no listener oracle@orabckprd> tnsping osdbi TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 26-NOV-2008 15:20:10 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /oracle/product/10.2/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = osint.iso-ne.com) (PORT = 1521))) (CONNECT_DATA= (SERVICE_NAME=osdbi.world))) OK (0 msec) oracle@orabckprd> oracle@orabckprd> date; strace -o run1_strace.log ./test_connect_multi.pl Wed Nov 26 15:20:37 EST 2008 Connect to fcstdbi *** instance_name: fcstdbi time: 11/26/2008 15:20:37 instance running on host: osinta Connect to fctsdbi ERROR: DBI connect('fctsdbi','backup',...) failed: ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach) at ./test_connect_multi.pl line 27 Connect to osdbi ERROR: OCIHandleAlloc(OCI_HTYPE_ERROR) failed at /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/Oracle.pm line 232. oracle@orabckprd> Test Run 2: Stop trying to connect to the fctsdbi database. Connect to two databases, fcstdbi and osdbi. I have no problems connecting to any database. oracle@orabckprd> date; strace -o run2_strace.log ./test_connect_multi.pl Wed Nov 26 15:22:19 EST 2008 Connect to fcstdbi *** instance_name: fcstdbi time: 11/26/2008 15:22:19 instance running on host: osinta Connect to osdbi *** instance_name: osdbi time: 11/26/2008 15:22:20 instance running on host: osinta oracle@orabckprd> Test 3: Connect to three databases, fcstdbi, foobar, osdbi. The foobar database doesn't exist. The problem is that the osdbi db is running and I should connect successfully but I don't. oracle@orabckprd> date Wed Nov 26 15:24:10 EST 2008 oracle@orabckprd> tnsping fcstdbi TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 26-NOV-2008 15:24:13 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /oracle/product/10.2/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = osint.iso-ne.com) (PORT = 1521))) (CONNECT_DATA= (SERVICE_NAME=fcstdbi.world))) OK (10 msec) oracle@orabckprd> tnsping foobar TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 26-NOV-2008 15:24:15 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /oracle/product/10.2/network/admin/sqlnet.ora TNS-03505: Failed to resolve name oracle@orabckprd> tnsping osdbi TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 26-NOV-2008 15:24:18 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: /oracle/product/10.2/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = osint.iso-ne.com) (PORT = 1521))) (CONNECT_DATA= (SERVICE_NAME=osdbi.world))) OK (10 msec) oracle@orabckprd> oracle@orabckprd> date; strace -o run3_strace.log ./test_connect_multi.pl Wed Nov 26 15:24:23 EST 2008 Connect to fcstdbi *** instance_name: fcstdbi time: 11/26/2008 15:24:23 instance running on host: osinta Connect to foobar ERROR: DBI connect('foobar','backup',...) failed: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at ./test_connect_multi.pl line 27 Connect to osdbi ERROR: OCIHandleAlloc(OCI_HTYPE_ERROR) failed at /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/Oracle.pm line 232. oracle@orabckprd> Perl Module versions: oracle@orabckprd> perl -MDBD::Oracle -e 'print "$DBD::Oracle::VERSION\n"' 1.22 oracle@orabckprd> perl -MDBI -e 'print "$DBI::VERSION\n"' 1.607 oracle@orabckprd> Test script: #!/usr/bin/perl # # script: test_connect.pl # # a quick'n'dirty Perl script to test whether a Perl script is able to connect to an Oracle database # # # load the required modules # use DBI; # variables used by this script my $current_db_time; my $host_name; my $instance_name; @db_to_process = (fcstdbi, fctsdbi, osdbi); $username = "backup"; $password = "sp1der-55"; foreach my $db (@db_to_process){ print " Connect to $db\n"; eval{ $dbh = DBI->connect("dbi:Oracle:$db", $username, $password, { RaiseError => 1, PrintError => 0, AutoCommit => 0 }); # uncomment the following section if you want to run a SQL statement once connected to the database. my $sql = qq/ select to_char(sysdate, 'mm\/dd\/yyyy hh24:mi:ss'), instance_name, host_name from v\$instance /; my $stmt = $dbh->prepare($sql); $stmt->execute(); $stmt->bind_columns( undef, \$current_db_time, \$instance_name, \$host_name); $stmt->fetch(); $stmt->finish(); print "\n"; print " *** instance_name: $instance_name\n"; print " time: $current_db_time\n"; print " instance running on host: $host_name\n"; print "\n"; $dbh->disconnect(); }; # handle any exception if( $@ ) { print "ERROR: $@\n"; } } oracle@orabckprd> OS: oracle@orabckprd> uname -a Linux orabckprd 2.6.18-53.el5PAE #1 SMP Wed Oct 10 16:48:18 EDT 2007 i686 oracle@orabckprd> Perl version: (I believe that this is the perl that comes with this Red Hat OS.) oracle@orabckprd> perl -V Summary of my perl5 (revision 5 version 8 subversion 8) configuration: Platform: osname=linux, osvers=2.6.9-34.elsmp, archname=i386-linux-thread-multi uname='linux hs20-bc2-2.build.redhat.com 2.6.9-34.elsmp #1 smp fri feb 24 16:56:28 est 2006 i686 i686 i386 gnulinux ' config_args='-des -Doptimize=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -Dversion=5.8.8 -Dmyhostname=localhost -Dperladmin=root@localhost -Dcc=gcc -Dcf_by=Red Hat, Inc. -Dinstallprefix=/usr -Dprefix=/usr -Darchname=i386-linux -Dvendorprefix=/usr -Dsiteprefix=/usr -Duseshrplib -Dusethreads -Duseithreads -Duselargefiles -Dd_dosuid -Dd_semctl_semun -Di_db -Ui_ndbm -Di_gdbm -Di_shadow -Di_syslog -Dman3ext=3pm -Duseperlio -Dinstallusrbinperl=n -Ubincompat5005 -Uversiononly -Dpager=/usr/bin/less -isr -Dd_gethostent_r_proto -Ud_endhostent_r_proto -Ud_sethostent_r_proto -Ud_endprotoent_r_proto -Ud_setprotoent_r_proto -Ud_endservent_r_proto -Ud_setservent_r_proto -Dinc_version_list=5.8.7 5.8.6 5.8.5 -Dscriptdir=/usr/bin' hint=recommended, useposix=true, d_sigaction=define usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='gcc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm', optimize='-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables', cppflags='-D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -I/usr/include/gdbm' ccversion='', gccversion='4.1.1 20060928 (Red Hat 4.1.1-28)', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=4, prototype=define Linker and Libraries: ld='gcc', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib libs=-lresolv -lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lpthread -lc perllibs=-lresolv -lnsl -ldl -lm -lcrypt -lutil -lpthread -lc libc=/lib/libc-2.5.so, so=so, useshrplib=true, libperl=libperl.so gnulibc_version='2.5' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E -Wl,-rpath,/usr/lib/perl5/5.8.8/i386-linux-thread-multi/CORE' cccdlflags='-fPIC', lddlflags='-shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP USE_ITHREADS USE_LARGE_FILES USE_PERLIO USE_REENTRANT_API Built under linux Compiled at Oct 3 2006 11:01:57 @INC: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.7/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 . oracle@orabckprd>