Home » RDBMS Server » Networking and Gateways » ghost tnsnames.ora (oracle9, instant client/php, windows)
ghost tnsnames.ora [message #312522] Wed, 09 April 2008 02:31 Go to next message
Permidion
Messages: 6
Registered: April 2008
Junior Member
Hello,

I have some very weird problem, I have a ghost tnsnames.ora.

My setup is as follow:

1 server with oracle9,
1 client pc in the same network as the server, that client connect to the server with PHP via oracle's Instant Client 10.2.0.1.

Everything was working fine until we upgraded PHP from version 5.2.0 to version 5.2.5 (this is a needed upgrade for us and downgrading is not an option)

Now we get the following error message:

Quote:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


Since our application is quite complicated, I made some test with a clean and simple script (found on oracle instant client instructions page http://www.oracle.com/technology/pub/notes/technote_php_instant.html)

<?php

//$conn = oci_connect("mylogin", "mypass", '//myserver:1521/oracle9.c2c');
$conn = oci_connect("mylogin", "mypass", 'ORACLE9');

$query = 'select * from mydb.mytable';

$stid = OCIParse($conn, $query);

OCIExecute($stid, OCI_DEFAULT);
 while ($succ = OCIFetchInto($stid, $row)) {
    foreach ($row as $item) {
      echo $item." ";
    }
    echo "<br>\n";
 }

 OCILogoff($conn);

 ?>


my existing tnsnames.ora file, located in the instant client folder:
ORACLE9 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle9.c2c)
    )
  )


First of all I checked that the database was running and the listener was ok, no problem on the server side.

I checked that I DID NOT have any other tnsnames.ora files on the client pc. I searched several time and the only tnsnames.ora file is the one in my instant client folder.

I checked that the TNS_ADMIN environment variable was correctly set and available (I rebooted several time and doing "echo %TNS_ADMIN% effectively output the correct path to my tnsnames.ora file).

So, I tried to rename/remove the tnsnames.ora file, BUT I STILL GET THE SAME ERROR MESSAGE!

Then I tried to replace the connection syntax with a Easy Connect syntax
$conn = oci_connect("mylogin", "mypass", '//myserver:1521/oracle9.c2c');

IT WORKS!!!!
I get a sql result just fine. So everything is ok beside the tnsnames.ora which is bypassed in this case.
But I can not use the Easy Connect syntax for my application as we use PEAR DB and it only accept the old syntax using tnsnames.ora, otherwise my problem would have been already solved.

Then I tried to use sqlplus on the client pc.
C:\instant_client>sqlplus mylogin@oracle9

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 9 08:32:55 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>
SQL> select * from mydb.mytable;

...

169 rows selected.

SQL>

IT WORKS!!!
I can connect just fine and query result is ok too.

If I remove or rename my tnsnames.ora file, sqlplus doesnt like it:
C:\instant_client>sqlplus mylogin@oracle9

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 9 08:50:45 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

So sqlplus effectively use my tnsnames.ora file.

I then tried to use some none existent listener name in my test php script:
$conn = oci_connect("mylogin", "mypass", 'ORACLE9TEST');

now the error message CHANGE! to :
ORA-12154: TNS:could not resolve the connect identifier specified


!!!!!!!!!!!!!!!!!!!!!!

In other words, when trying to connect with PHP using the tnsnames syntax, some listener definition is used but it's not read from my tnsnames.ora, it's read from outerspace!

I really dont understand what's going on.

Any help very welcome.
Re: ghost tnsnames.ora [message #312782 is a reply to message #312522] Wed, 09 April 2008 14:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
did you already try
phpinfo();

to check the environment?
Re: ghost tnsnames.ora [message #313668 is a reply to message #312782] Mon, 14 April 2008 01:57 Go to previous messageGo to next message
Permidion
Messages: 6
Registered: April 2008
Junior Member
yes, the oci8 driver is displayed in the list of loaded extensions.

beside, I can connect fine when using the oracle Easy Connect syntax.

the problem only occure when the connection info are defined in the tnsnames.ora file.
Re: ghost tnsnames.ora [message #313836 is a reply to message #312522] Mon, 14 April 2008 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If you are desperate you could try changing
(SERVICE_NAME = oracle9.c2c)
to be
(SID = oracle9.c2c)
or
(SID = oracle9) # whatever the actual SID is on database server.
Re: ghost tnsnames.ora [message #313886 is a reply to message #313836] Tue, 15 April 2008 00:59 Go to previous messageGo to next message
Permidion
Messages: 6
Registered: April 2008
Junior Member
Hi

I get the same error as before:

Warning: oci_connect() [function.oci-connect]: 
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor in
 C:\ms4w\Apache\htdocs\testoracle.php on line 4


In fact, whatever I write in the tnsnames.ora file, I get the same error. If I rename/move my tnsnames.ora file, I still get the same error.

Thats why I was talking about a ghost tnsnames.ora file, I have absolutly no idea where it is looking for the tnsnames.ora file but it is NOT looking where im telling it to look (ie. TNS_ADMIN)

My thought is something is fucked with the oci8 driver in php 5.2.5.

The instant client itself works fine, otherwise sqlplus would not work.
Re: ghost tnsnames.ora [message #317510 is a reply to message #312522] Thu, 01 May 2008 13:58 Go to previous messageGo to next message
aliyesami
Messages: 28
Registered: October 2005
Location: Florida
Junior Member
did you get this solved ? I am having exact same issue with php5.2.5
Re: ghost tnsnames.ora [message #321762 is a reply to message #317510] Wed, 21 May 2008 04:43 Go to previous messageGo to next message
Permidion
Messages: 6
Registered: April 2008
Junior Member
I have a bit more infos about this problem.

The php I was using was included with a MS4W (GIS) package.

If I do the same tests in a WAMP package (same php version as the ms4w package), everything work fine! The tnsnames.ora file is found and parsed correctly.

So something is wrong with some dll in the ms4w package (maybe php_oci8.dll ?)

Im reporting this to the ms4w people,

expect more news soon.
Re: ghost tnsnames.ora [message #321827 is a reply to message #321762] Wed, 21 May 2008 07:29 Go to previous messageGo to next message
Permidion
Messages: 6
Registered: April 2008
Junior Member
even more info:

The problem occure only when php is running in cgi mode.

If php is configured to run as an Apache module (in httpd.conf), everything work fine! The tnsnames.ora is parsed correctly and the connection happens normaly.

(so the problem has nothing to do with ms4w)

[Updated on: Wed, 21 May 2008 08:15]

Report message to a moderator

Re: ghost tnsnames.ora [message #321996 is a reply to message #312522] Thu, 22 May 2008 01:53 Go to previous message
Permidion
Messages: 6
Registered: April 2008
Junior Member
the solution is:

if your php is running in cgi mode
AND you need to use the tnsnames.ora for the Oracle connection details,
you MUST place the tnsnames.ora file in the SAME directory as the php-cgi.exe file

OR

define the environment variable in the httpd.conf :

SetEnv ORACLE_HOME "c:/path/to/InstantClient"
SetEnv TNS_ADMIN "c:/path/to/InstantClient"
SetEnv PATH "c:/path/to/InstantClient"

thats all

(pff, so many day lost for something that silly... Im suprised I couldnt find any info about this anywhere)

[Updated on: Thu, 22 May 2008 02:27]

Report message to a moderator

Previous Topic: How to upgrade NET80 of forms6i
Next Topic: oracle cluster
Goto Forum:
  


Current Time: Thu Mar 28 06:50:51 CDT 2024