tnsnames resolve failing

Anon

Hi all,

I've been writing a web based app that queries an oracle db.

My problem is that every n times i call the page I seem to get an oracle tnsnames error..
Warning: _oci_open_server: ORA-12154: TNS:could not resolve service name

Now this happens randomly it seems and I wondered if there was any caveat or tricks I should know about while querying oracle ( I ususally use mysql).

Loading on the box is low and it's local so it's not a network issue.

I'm not really that au fait with the name>host>db resolution process for tns so wondered if anyone had any ideas?

Cheers,
Matt

Anon

Do you acheive to connect with SQLPlus or something like that ?

If you can, the problem come from your code, if you can't the problem come from the Oracle configuration..

to configure Oracle (in web or local ) you must have on the server this file :
/dir/where/oracleis/network/admin/listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NameorIP)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DataBase.Domain)
(ORACLE_HOME = /dir/where/oracleis)
(SID_NAME = GeneralyjusttheDataBaseName)
)

------------------must have on the client(or server if it's both) this file :
/dir/where/oracleis/network/admin/tnsname.ora

DATABASE.DOMAIN(or anotherAlias) =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NameorIP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DataBase.Domain)
)

you must have the same number of alias in the tnsname.ora that you have DB on the listener.ora.

If it's from your code...let's post it, we'll take a look...

Anon

Check the file : tnsnames.ora

under Linux : [ORA_HOME]/network/admin

for an entry like this :

your_sid.your_domain=

(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = your_host.your_domain)(PORT = 1521))
)(CONNECT_DATA =

(SERVICE_NAME = your_sid)

))

Replace :

your_host : hostname of the DB Computer

your_domain : domainname of your Network

your_sid : database SID (name)

Simple is to have the same hostname as the sid name !

Bounty

Anon

I dont think I explained the fault preoperly sorry 😮)

The connection fails intermitently, it will display the page for say 4-5 queries, but if you refresh the pagfe repeeatedly it can randomly fail, then work again etc..

This is why i dont think it's a problem with the connection code..

All of my tnsnames.ora settings are correct as I can connect without a problem from sqlplus.

This seems to happen with local databases and remote databases.

Hope that clarifies my problem somewhat!

Cheers,
Matt