tnsnames.ora file - What's the relevance??

[Sanjay Verma]

Hi Friends,

I need to put the entry of the services / servers that I need to connect, in this file (tnsnames.ora).

Would you be able to explain the working of this file and how does it connect to the respective database using the parameters entered.

Sanjay

[Jeff Schussele · Accepted solution]

Hi Sanjay,

The tnsnames.ora file is like a roadmap to all the DBs you may wish to connect to.
It contains all the info a client would need to find & connect to a DB - such as:

1) SID name - the common name the DB is known as. It's the value NOT in parens.

2) PROTOCOL - the type of transport (TCP, etc) to use to communicate with the DB

3) HOST - Could be a name OR IP

4) PORT = The port number to use to communicate

This file is referrenced when you do something like an sql connect call such as

sqlplus username/password@DBNUM01

It looks up the DBNUM01 value in that file & uses the host/port info to setup the connection.

HTH,
Jeff

[A. Clay Stephenson]

This is a far from trivial question because it involves setting up a listener, defining ports, and hostname resolution.

The best place to start is in ${ORACLE_HOME}/network/admin/samples. That directory will contain a tnsnames.ora sample as well as a listeners.ora sample. If you are only setting up a client then you can probably copy a tnsnames.ora file from another client. You must still be able to resolve hostnames using /etc/hosts, NIS, and/or DNS.

[Sanjay Verma]

Jeff & Clay, What services must be running on my pc (Win 2000) for the connection to be established using this file - tnsnames.ora. For e.g., Before running Toad, what services must be running (tnslistener etc.)?

[Sanjay Verma]

Verified and found that 3rd party appln. e.g., TOAD, refers to this file but doesn't require any Oracle services to be running.

Thanks Guys.

[Dimitry Snezhkov]

Sanjay,

I think you do need Oracle client
installed: Toad is looking for
client libraries: oci.dll,
which is an API to connect to oracle. After that, Setup
ORACLE_HOME , a place where
Toad will start searching for cupported binaries to connect to Oracle. When you specify which DB you need to connect, that's where you need entry in local tnsnames.ora where you'll point
to Oracle_SID and all its characteristics: which host it's
on , etc ...

[Kimathi Njeru]

Sanjay,
Install Oracle client for whatever version of database you are trying to connect to.

Run the Net Manager or SQL*NET Easy Configurator application that will be installed after the client.

Follow the wizard which should be intuitive. You should add an alias, database SID and host IP address that the database resides in. Your configuration will be saved on completion which builds a tnsnames.ora in ORACLE_HOME.

You can then use the alias that you created to connect to the database with any tool including TOAD.

Hope this helps.
Kim