Oracle hangs at connect

DrManhattan has asked for the wisdom of the Perl Monks concerning the following question:

I'm having difficulty connecting to an Oracle database with perl. Sqlplus connects immediately, but DBI takes over 3 minutes to establish a connection. Here's some test code (auth info removed):

#!/usr/bin/perl

use strict;
use DBI;

$ENV{ORACLE_HOME} = "/usr/local/oracle9";

my $dbhost   = '';
my $database = '';
my $username = '';
my $password = '';

# Build a dsn
my $dsn = "DBI:Oracle:host=$dbhost;sid=$database";

# Create a database handle
print scalar localtime(), " connecting ...\n";
my $dbh = DBI->connect($dsn, $username, $password, {
        RaiseError => 0,
        PrintError => 1,
        AutoCommit => 0
}) or die $DBI::errstr;
print scalar localtime(), " done.\n";
$dbh->disconnect();
[download]

Here's the script output:

Mon Aug  9 10:30:00 2004 connecting ...
Mon Aug  9 10:33:09 2004 done.

The same code works fine on another Oracle database I'm using, so there appears to be something different about this server. Since Sqlplus connects immediately, though, I have to think there's something I need to do differently on the client side.

-Matt

Comment on Oracle hangs at connect Download Code

Replies are listed 'Best First'.
Re: Oracle hangs at connect by etcshadow (Priest) on Aug 09, 2004 at 15:01 UTC
Can you provide the connect description you give to sqlplus (minus the auth info, of course)? I'm taking a wild guess here, but I don't think that you're using both the host name and the SID... probably just using a TNS name (which maps, via either your $ORACLE_HOME/tnsnames.ora, or via an oranames server listed in your $ORACLE_HOME/sqlnet.ora, to a host, SID, and port number). Anyway, my best guess for what to try is: drop the `host=$host` and the `sid=$sid`, and instead make your DSN just be: `DBI:Oracle:$tnsname`. That's what I always do. Oh, and as to why this will work with one database and not with another... well, the different databases maybe differently configured on either your oranames server or on the sqlnet client's tnsnames.ora file. update: that should be $ORACLE_HOME/network/admin/tnsnames.ora (and sqlnet.ora). Sorry, I typed too fast :-) `------------ :Wq Not an editor command: Wq` [download]	[reply] [d/l] [select]
Re^2: Oracle hangs at connect by DrManhattan (Chaplain) on Aug 09, 2004 at 16:59 UTC
++etcshadow and clscott. Using tnsnames fixed the problem. -Matt	[reply]
Re: Oracle hangs at connect by clscott (Friar) on Aug 09, 2004 at 15:20 UTC
I find that the form specifying host is prone to taking long times to connect. Set up your TNS names file approriately ($ORACLE_HOME/network/admin/tnsnames.ora) and use the form: `$dsn = "dbi:Oracle:$database";` [download] -- Clayton	[reply] [d/l]
Re^2: Oracle hangs at connect by clscott (Friar) on Aug 17, 2004 at 20:25 UTC
I'm following up to myself because I think someone has discovered the cause. Unless you specify which port Oracle is listening on it will try port 1526 first, which would explain a long delay in waiting for a response before trying the more standard port of 1521. `my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=$port");` [download] Source: http://use.perl.org/~jdavidb/journal/20440 -- Clayton	[reply] [d/l]
Re^3: Oracle hangs at connect by Anonymous Monk on Dec 24, 2008 at 04:29 UTC
this is realy good answer. its worksfine Thanks	[reply]
Re: Oracle hangs at connect by hmerrill (Friar) on Aug 09, 2004 at 15:23 UTC
Unfortunately I don't know the answer to your problem, but I can suggest that you ask your question on the Perl DBI mailing list - with the expert people on that list I'm sure you will get some good helpful information there.	[reply]