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:/usr/
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:/usr
/c cs/bin:/opt/studio9/SUNWspro/bin:/opt/CA/Shar
edComponents/dmscript/bin:/usr/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/
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
.

Relevant Pages

  • [PATCH 0/5] [RFC] AF_RXRPC socket family implementation [try #3]
    ... These patches together supply secure client-side RxRPC connectivity as a Linux ... kernel socket family. ... presentation side is left to the client. ... Each connection goes to a particular "service". ...
    (Linux-Kernel)
  • [PATCH 0/5] [RFC] AF_RXRPC socket family implementation
    ... These patches together supply secure client-side RxRPC connectivity as a Linux ... Make it possible for the client socket to be used to go to more than one ... Each connection goes to a particular "service". ...
    (Linux-Kernel)
  • [PATCH 0/5] [RFC] AF_RXRPC socket family implementation [try #2]
    ... These patches together supply secure client-side RxRPC connectivity as a Linux ... Make it possible for the client socket to be used to go to more than one ... Each connection goes to a particular "service". ...
    (Linux-Kernel)
  • Re: .Net Scalability problem
    ... LoadRunner will peak out a server with a few virtual users. ... To get an idea of load, ... Fire off the test client and watch the number of ... > So I think that the MTC generate concurrent connection and per ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Remote Client Configuration
    ... the client computer to the SBS domain via connect computer wizard remotely. ... local network or via dial up VPN connection, you will use an local copy on ... connection is established, Group Policy is not applied during logon. ...
    (microsoft.public.windows.server.sbs)