Problem with TNS_ADMIN in .profile

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hi!

In my .profile I have a line that says

export TNS_ADMIN=$HOME

I received the .profile file from a colleague who uses it to access SQL*Plus from a UNIX server. When I try the same method he uses I get

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

which makes me think that this TNS_ADMIN line is causing the error since my copy of tnsnames.ora is located at

C:\Oracle\product\10.1.0\Client_1\network\ADMIN

. My attempts at pointing to that location has failed. Does anyone have a suggestion?

Thanks!

2

How's the permissions on tnsnames.ora ?

3

I have read/write on the tnsnames.ora file (I've done minor edits as needed). I'm not sure though if .profile should be pointing from the UNIX environment to somewhere within the UNIX environment or to a location on the Windows box I'm operating from ...

4

A tnsnames.ora file is read by the Oracle listener when it starts on the computer where the database resides.
Another tnsnames file is read by the client computer to initialise the client-server session.
i.e. Each computer has its own copy.
The client and server copies should be consistent for the section relevant to that connection. They don't have to be identical because a server could offer multiple listeners for different purposes.

I can't see why tnsnames.ora would be mentioned in a unix .profile - even for account "oracle". The unix .profile is used to initalise a "telnet" session. It would be more ususal to mention tnsnames.ora in a script which starts the listener in background.

Tnsnames.ora - Oracle FAQ

5

As I recall, it is configured via environment, so .profile is fair game to support all oracle products launched in the session, like SQL*PLUS or PRO-C compiled applications.
Configuring SQL*Plus

You can connect without tnsnames.ora, by putting the right section on the command line or wherever appropriate, but why? Get a copy of the file.
Ask Tom "How to connect SQLPlus without tnsnames..."

6

In your case the TNS_ADMIN is used to locate the OracleNet configuration. It should point to where your OralceNet services are defined: the tnsnames.ora (and eventually sqlnet.ora, ldap.ora etc.) files on your client.

If you unset TNS_ADMIN and set ORACLE_HOME correctly, your client will search for tnsnames.ora
in the $ORACLE_HOME/network/admin directory on your client machine (and in other places, but I don't think you need these details now).

7

That's what the first link above says. Since you probably need ORACLE_HOME even worse, why not use the default location? It might not be right, the owner might be on vacation, or you like config bits elsewhere from installed code!

Configuring SQL*Plus

8

In summary, the O/P may not have realised that both client and server would normally have a tnsnames.ora file.

The client won't consult tnsnames.ora on the server .... because it doesn't know the way to find the server until it has read its own tnsnames.ora .

Similarly the server knows nothing about the clients until they connect and cannot know where to find a copy of tnsnmes.ora on a client.

9

Except clients can connect without if they know the magic words other ways.