DBI and Oracle ORA-12505 error

[jmb]

Hi,

I am seeking for your wisdom because I have a strange connectivity problem with DBI and an Oracle 11.2.0.3 server.

First of all, a disclosure: I am not experienced in Oracle. So, here is my problem:

When I try to connect using the usual dsn/user/password format, thus (according to my understanding) basing the connection on the tnsnames.ora conf file, I obtain the following connection error:

ORA-12505:
    TNS:listener does not currently know of SID 
    given in connect descriptor.

But when I connect using an alternative parameter format and reproduce the exact entry of the tnsnames.ora file in my code, as a string, then I am able to connect.

By the way, the tnsnames.ora file seems to be ok, as the Oracle SQL Developper client can use it successfully to connect.

Does any person with Oracle experience have any ideas about why this is happening ?

Thank you in advance for your feedback.

Here is the code I am using:

use v5.12;
use strict;
use warnings;
use diagnostics;

use DBI;

my $dbh;

# This code produces an ORA-12505 error
$dbh = DBI->connect(
    "DBI:Oracle:sid=JUPITER;host=jupitercluster",
    "user",
    "pass",
);
    
# Alternatively, this code, 
# reproducting the content of tnsnames.ora, works !
$dbh = DBI->connect(
    "DBI:Oracle:",
    "user/pass@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jupite
+rcluster)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_N
+AME = jupiter)))",
);

__END__

# Content of the entry in tnsnames.ora
JUPITER =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = jupitercluster)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME=jupiter)
  )
)

[Tux]

Does tnsping help?

Have you tried connecting using the TWO_TASK variable? Is it set or unset?

What is the result of

# Note that the SID is case sensitive in Ora 11!
my $dbh = DBI->connect ("dbi:Oracle:sid=jupiter;host=jupitercluster",
    "user", "pass", {
    RaiseError         => 1,
    PrintError         => 1,
    ChopBlanks         => 1,
    ShowErrorStatement => 1,
    FetchHashKeyName   => "NAME_lc",
    }) or die $DBI::errstr;

[jmb]

Thank you for your reply ! I apologize in advance for the French in the error messages below (all this is happening on a French Windows XP box).

First of all, the TWO_TASK environment variable is not set in this Windows system. I have not tried to use it. Here is the output of the snippet that you suggested:

C:\test>perl oracle_test.pl
DBI connect('sid=JUPITER;host=jupitercluster','user',...) failed: ORA-
+12505: TNS : le module d'écoute (listener) n'a pas pu résoudre le SID
+ donné dans le descripteur de connexion (DBD ERROR: OCIServerAttach) 
+at oracle_test.pl line 11

As for tnsping, I guess that it gives a strong indication. Using it produces a TNS-03505 error for every single alias in the tnsnames.ora file ! This is strange because I can successfully use all the other tnsnames.ora aliases from within Perl/DBI, as well as using the Oracle SQL Developer client. I mean, except the one I am having a problem with, which does not work from within Perl/DBI, but *does* work using the Oracle SQL Developer client.

c:\test> tnsping JUPITER
   or 
c:\test> tnsping jupiter
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on
+ 13-MAI-2013 17:17:40

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Fichiers de paramètres utilisés :
C:\Oracle\network\admin\sqlnet.ora

TNS-03505: Echec de la résolution du nom

Here is the (short) content of sqlnet.ora:

# Generated by our DBA

NAMES.DEFAULT_DOMAIN = mycompany.com

NAMES.DIRECTORY_PATH= (TNSNAMES)

Any further suggestions ? Again, thanks a lot for your answer. And yes, learning about Oracle and Perl/Oracle is fun ;-)

[Tux]

In your first code snippet, you are still using the uppercase SID. Try it lowercased!.

tnsping works with the identifier, which most often is not the same as the SID.

$ head -4 $ORACLE_HOME/network/admin/tnsnames.ora
oradb_btest = ( DESCRIPTION = ( ADDRESS_LIST =
  ( ADDRESS      = ( HOST = oradb )( PORT = 1521 )( PROTOCOL = TCP )))
  ( CONNECT_DATA = ( SERVICE_NAME = basetest ))
  )
$ tnsping oradb_btest

TNS Ping Utility for HPUX: Version 10.2.0.4.0 - Production on 13-MAY-2
+013 18:24:26

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
/prod/oracle/v102/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact ( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( P
+ROTOCOL = TCP)( PORT = 1521) ( HOST = oradb))) ( CONNECT_DATA = ( SER
+VICE_NAME = basetest)))
OK (90 msec)
$

/code