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

Hi Alan,

Both versions of Oracle point back to the same tnsnames.ora file.

/opt/app/oracle/product/10.2/network>$ ls -l admin
lrwxrwxrwx 1 oracle dba 35 Feb 8 2007 admin -> /opt/app/oracle/admin/network/admin

/opt/app/oracle/product/9.2/network>$ ls -l admin
lrwxrwxrwx 1 oracle dba 35 Apr 23 2003 admin -> /opt/app/oracle/admin/network/admin

And the tnsnames.ora file contains both entries for the two Oracle servers.

Peter

-----Original Message-----
From: Kong, Alan [mailto:KONGA@xxxxxxxxx]
Sent: Wednesday, December 12, 2007 1:44 PM
To: Loo, Peter # PHX; Martin Evans; dbi-users@xxxxxxxx
Subject: RE: ORA-12154: TNS:could not resolve the connect identifier specified

Peter,

Why don't you use one oracle home and set up two tns entries, one for each instance, in the tnsnames.ora file?

For example: you can set 10.2 as oracle home, in it's corresponding tnsnames.ora file, create two tns entries each pointing to one instance. Then the connection calls can pass in different connection strings to connect to different instances.

Alan

-----Original Message-----
From: Loo, Peter # PHX [mailto:Peter.Loo@xxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Wednesday, December 12, 2007 3:29 PM
To: Martin Evans; dbi-users@xxxxxxxx
Subject: RE: ORA-12154: TNS:could not resolve the connect identifier specified


Hi Martin,

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;

Peter

-----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)

Thanks.

Peter


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.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com


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.


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

  • RE: ADO.Net Connection Pooling Problem with Oracle
    ... the CLR is releasing the connection to your database. ... the number of database sessions in Oracle seem to ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: SOS! ORA-03114: not connected to ORACLE && MSs Bug??
    ... not connected to ORACLE && MS's Bug?? ... > but sometimes returning the following exception "ORA-03114: ... > when loosing the connection with the Oracle database server the connections ... > even after the connection with the database server has been restore. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: SOS! ORA-03114: not connected to ORACLE && MSs Bug??
    ... not connected to ORACLE && MS's Bug?? ... > but sometimes returning the following exception "ORA-03114: ... > when loosing the connection with the Oracle database server the connections ... > even after the connection with the database server has been restore. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connecting to ODBC DB at startup
    ... Creating an Oracle connection in an independent object ... Dim cnConn As ADODB.Connection ... Dim strDB As String ...
    (comp.databases.ms-access)
  • RE: SOS! ORA-03114: not connected to ORACLE && MSs Bug??
    ... not connected to ORACLE && MS's Bug?? ... > but sometimes returning the following exception "ORA-03114: ... > when loosing the connection with the Oracle database server the connections ... > even after the connection with the database server has been restore. ...
    (microsoft.public.dotnet.languages.csharp)