RE: ORA-12154: TNS:could not resolve the connect identifier specified


Hi David,

I am not familiar with the tnsnames.ora file. Here is what they are set to. I did not find anything out of the ordinary. To answer your question about why I am changing the ORACLE_HOME, it is because I spawn a "sqlldr" using system() within the program. If I don't set the ORACLE_HOME to the relevant version of Oracle, I get the SQL*Loader-951 and ORA-00942 errors when I spawn system($sqlldrCmd).

INST1.domain.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname.domain.com)(PORT = 1521))
)
(SDU = 32767)
(CONNECT_DATA =
(SID = inst1)
)
(TDU = 32767)
)

INST2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname.domain.com)(PORT = 1521)
)
)
(CONNECT_DATA =
(SERVICE_NAME = inst2)
)
)
)

Thanks for all your help.

Peter

-----Original Message-----
From: David Dooling [mailto:banjo@xxxxxxxxxxxxxxxxxxxxx]
Sent: Wednesday, December 12, 2007 2:01 PM
To: dbi-users@xxxxxxxx
Subject: Re: ORA-12154: TNS:could not resolve the connect identifier specified

On Wed, Dec 12, 2007 at 01:28:37PM -0700, Loo, Peter # PHX wrote:
I tried setting both variables at the very beginning before any
connection is made and the result is still the same.

$ENV{ora_envhp} = 0;
$ENV{ORA_ENVHP} = 0;

Why are you switching the ORACLE_HOME at all? The 9i client should be able to connect to the 10g database just fine (and vice versa). All you need to do is ensure you have the appropriate entries for the 9i and 10g instances in the network/admin/tnsnames.ora of whatever client you are using.

Switching as you are is quite problematic since the Oracle shared libraries are probably only loaded once, the first time DBD::Oracle is loaded by Perl. Switching ORACLE_HOME and PATH (it seems you would also have to change LD_LIBRARY_PATH) after loading one set of shared libraries is going to lead to unpredictable behavior.

-----Original Message-----
From: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Sent: Wednesday, December 12, 2007 1:47 AM
To: dbi-users@xxxxxxxx
Subject: Re: ORA-12154: TNS:could not resolve the connect identifier
specified

Loo, Peter # PHX wrote:
Hello All,

I am confused as to why I am getting this error message. I am
trying to connect to two different Oracle instances within one program run.
The two Oracle instances are different in versions. inst1 is 9.2
while
inst2 is 10.2. I am able to connect to the first instance without
problems, but the instance yields "ORA-12154" error. However, when
I reverse the connection order by connecting the inst2 with 10.2
version first, then connecting to the inst1 with 9.2 version, there is no error.
I hope someone can shed some lite. Here is the code:

#!/usr/bin/perl

use DBI;

$ENV{'ORACLE_HOME'} = "/opt/app/oracle/product/9.2"; $ENV{'PATH'} =
"/opt/app/oracle/product/9.2/bin:/usr/ccs/bin:/opt/studio9/SUNWspro/bin:
/opt/app/oracle/product/10.2/bin:/opt/CA/Shar
edComponents/dmscript/bin:/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/us
r/ lo cal/bin:/opt/app/oracle/product/8.1.7.4/bin:/usr/local/bin:/
usr/local/apps/common/devl/bin:/usr/lbin:/usr/ccs/bin:/usr/xpg4/bin:
/o pt /pkware:/opt/RICHPse/bin:/opt/RICHPse/examples:/opt/sas82:/o
pt/sas82/sasexe:/opt/Navisphere/bin:/opt/fileport/bin:/opt/syncsort/bin:
/usr/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/opt/mfcobol/cob
ol/bin:/opt/starsql/bin:/opt/uni/bin:/opt/uni/scripts:/etc:/opt/emc/
EM Cp ower/bin/sparcv9:/etc/emc/bin:/usr/local/nz/bin/:.";

my $dbh_inst1 = DBI->connect("dbi:Oracle:inst1", "schema",
"password", { RaiseError => 1, PrintError => 1});

$ENV{'ORACLE_HOME'} = "/opt/app/oracle/product/10.2"; $ENV{'PATH'} =
"/opt/app/oracle/product/10.2/bin:/opt/app/oracle/product/9.2/bin:/u
sr /c cs/bin:/opt/studio9/SUNWspro/bin:/opt/CA/Shar
edComponents/dmscript/bin:/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/us
r/ lo cal/bin:/opt/app/oracle/product/8.1.7.4/bin:/usr/local/bin:/
usr/local/apps/common/devl/bin:/usr/lbin:/usr/ccs/bin:/usr/xpg4/bin:
/o pt /pkware:/opt/RICHPse/bin:/opt/RICHPse/examples:/opt/sas82:/o
pt/sas82/sasexe:/opt/Navisphere/bin:/opt/fileport/bin:/opt/syncsort/bin:
/usr/bin:/usr/openwin/bin:/opt/SUNWspro/bin:/opt/mfcobol/cob
ol/bin:/opt/starsql/bin:/opt/uni/bin:/opt/uni/scripts:/etc:/opt/emc/
EM Cp ower/bin/sparcv9:/etc/emc/bin:/usr/local/nz/bin/:.";

my $dbh_inst2 = DBI->connect("dbi:Oracle:inst2", "schema",
"password", { RaiseError => 1, PrintError => 1});

$dbh_inst1->disconnect();
$dbh_inst2->disconnect();

exit;

Here is the error message:

DBI connect('inst2','schema',...) failed: ORA-12154: TNS:could not
resolve the connect identifier specified (DBD ERROR:
OCIServerAttach)

Sounds as though you have hit an issue sharing an env.
I'm not sure as I've not done this myself but you may need to look at the ora_envhp attribute:

The first time a connection is made a new OCI ’environment’ is created
by DBD::Oracle and stored in the driver handle. Subsequent connects
reuse (share) that same OCI environment by default.

The ora_envhp attribute can be used to disable the reuse of the OCI
environment from a previous connect. If the value is 0 then a new OCI
environment is allocated and used for this connection.

The OCI environment is what holds information about the client side
con‐ text, such as the local NLS environment. So by altering %ENV and
setting ora_envhp to 0 you can create connections with different NLS
settings. This is most useful for testing.

--
David Dooling


This E-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply E-mail, and destroy all copies of the original message.
.

Relevant Pages

  • 10.1 - resolving hostnames slow for the first time
    ... I have encountered a very strange problem with 10.1 on my laptop. ... 30s or something like this when estabilishing SSH connection etc. ... Sometimes it even takes so long to resolve something, ... the hostname, then the result gets cached and the hostname is resolved ...
    (alt.os.linux.suse)
  • Re: web site not resolving to IP
    ... both connection are on the same pc. ... In a command prompt type nslookup and hit enter. ... If nslookup is able to resolve the name then it isn't DNS and I would ... You could also check the MTU, I've seen MTU ...
    (microsoft.public.win2000.dns)
  • Re: Dns.GetHostEntry throws SocketException in WM2005, OK in PPC2003
    ... serious unable to resolve address exception. ... as I'm able to use PocketPing to verify that the connection is ... pointer address in attempting to use a pointer argument in a call ... an invalid pointer value, or if the length of the buffer is too small. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: pppd not working (no reply)
    ... Modifying ipcp-max-configure and ipcp-restart does not resolve. ... Connection terminated. ... 'Compression Control Protocol' received", ...
    (comp.protocols.ppp)
  • Re: PGP msg stuck in Routing Queue
    ... have the same settings/status. ... All names resolve in AD. ... I also looked at the POP/IMAP/SMTP Virtual server settings, ... Authentication, NDR, Routing, and Connection. ...
    (microsoft.public.exchange.admin)