SQLNet/ Documentos / Soporte Técnico / Servicios / Tilsor

Connecting from a Client Process

Creation Date: 01-DEC-1994
Last Revision Date: 31-MAR-2000

4.1 Establishing a Connection
A connection from a client tool is established using the following
sequence of events:

a) The client process is given a username and password and a
connection alias.
Eg: sqlplus scott/tiger@MYALIAS

b) The client process looks in the 'sqlnet.ora' file to determine
which form of name resolution should be used. The name
resolution order is determined by the 'NAMES.DIRECTORY_PATH'.

In Net8 the default search order is: TNSNAMES, ONAMES, HOSTNAME
This basically means we look in TNSNAMES files, then use
Oracle Names, then use the Hostname Adapter (if applicable).
In these notes we consider the default TNSNAMES lookup option

c) If set the SQLNET.ORA parameter NAMES.DEFAULT_DOMAIN is also
noted. This parameter is a frequent cause of problems. It is
appended to the ALIAS when performing name resolution if the
supplied ALIAS is not already fully qualified.
scott/tiger@MYALIAS looks for MYALIAS.UK.ORACLE.COM
scott/tiger@MYALIAS.WORLD looks for MYALIAS.WORLD

d) The client process looks in:

$HOME/.tnsnames.ora .

for an expansion of the alias. If this file does not exist or
the alias is not listed then the configuration file used is the
first readable file found from the following:


Only ONE of these 'system' tnsnames.ora files is loaded.

If within the loaded file there is more than one match
for 'MYALIAS' a list of matching alias expansions is built.
Eg: Assume MYALIAS resolves to:

* A special feature called AUTOMATIC_IPC may build an expansion
of the alias automatically. This is discussed later.

e) If the alias cannot be resolved in the tnsnames.ora files a
call may be made to any configured Oracle Names servers.
This article does not discuss Oracle Names.

f) Assuming the alias is resolved a call is made to the resulting
address. This address should equate to a listener end point.
The client process calls the address asking for a particular
Eg: sqlplus will call (ADDRESS=(PROTOCOL=IPC) (KEY=mysid))
to try and contact a listener.

g) If no response is received from this address the client will
look at any further expansions of the alias and try contacting
each of these in turn. Note that there are ADDRESS_LIST and
DESCRIPTION_LIST options to the alias expansion which can
give lists of locations to try. These options are discussed

h) Assuming a listener is listening on the specified address it
tries to find the requested 'service'. In the example this
means the listener would look to see if it knows of a service
called 'mysid'. If this does not exist the client is sent
a refusal (ORA-12505: TNS:listener could not resolve SID given
in connect descriptor)
If the service is located then depending on the machine and
protocol the listener will either:

i) Spawn a server process and pass the connection off
to this server process.


ii) Tell the client to call back on a different address
to contact a server (usually a dispatcher) in which case
the client closes the connection to the listener and
calls the address it has been given.

i) The client should now be in communication with a 'server'

j) Once a connection has been established there may then be a
problem or an error. It is difficult to isolate the cause of
this sort of error. See later in the article for errors AFTER
a connection has been established.

4.2 Configuration
A 'client' process in a SQL*Net setup uses the file 'tnsnames.ora'
to locate alias expansions. It will also check for a file called
$HOME/.tnsnames.ora first enabling individual users to over-ride
the system defaults. You are advised NOT to use the hidden
'.tnsnames.ora' files as it can cause confusion.

The important thing to remember when configuring the client side
'tnsnames.ora' file is that aliases should be specifying:

a) A listener end point to call.
b) A service to request once the listener has been contacted.

An example 'tnsnames.ora' file is shown in the following section.

You are again advised to use the environment variable TNS_ADMIN to
specify the directory where the tnsnames.ora file is located.
If TNS_ADMIN is not set then files will be picked up from /etc if
present, then from $ORACLE_HOME/network/admin.

4.3 Example 'tnsnames.ora':
This is an example tnsnames.ora file. You should substitute the
relevant details for 'mysid' , 'mtsservice' , 'myhost' and the
'PORT=' definition.

On earlier releases of SQL*Net V2 the (KEY=mysid) values in this
file must be changed to use (KEY=n) where 'n' is an arbitrary number
above 100. The number 'n' must be the same as in the listener.ora
file. Later releases of SQL*Net can use a text string here such as the
'SID' as it will hash the string to a number.

# FILENAME: tnsnames.ora
# PURPOSE: Defines full connection details for given 'alias' names in
# the form 'ALIAS=( connection information )
# Example IPC connection aliases
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
v713.me.com = <-- Socket DEDICATED connection

mv713.me.com = <-- Socket MTS connection
(CONNECT_DATA= (SID=mtsservice))

# Example TCP/IP connection aliases
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
tcp_v713.me.com = <-- TCP/IP Dedicated connection

tcp_mv713.me.com = <-- TCP/IP MTS connection
(CONNECT_DATA= (SID=mtsservice))
# ---------------------------------------------------------------------

4.4 Automatic IPC
On some versions of SQL*Net V2 an expansion of the connect alias will
be generated automatically as an IPC connection to 'KEY=alias' asking
for 'SID=alias'. This expansion will usually be used as the first
attempted connection method to a listener. If the alias is resolved
then an IPC connection will be attempted BEFORE using the connect
details from the resolved alias.

This can be VERY misleading when trying to test loopback connections
or when trying to identify a problem. You are advised to turn this
feature off by adding the following line to the sqlnet.ora file:


AUTOMATIC_IPC defaults to OFF in Net8.

4.5 Attempting a Client connection
a) Create a directory in which to keep the Net configuration files.

b) Set the environment variable TNS_ADMIN to the name of this directory.
Ensure this is EXPORTED into the environment.

c) Create a tnsnames.ora file either using the example OR using a
network configuration tool.

d) Check the tnsnames.ora file satisfies the manual checks listed for
the listener.ora file earlier in this article.

e) Ensure the user has NO $HOME/.tnsnames.ora file

f) Attempt the connection.
If the connect attempt FAILS you need to determine how far through
the connect sequence the process got before the failure.
To do this you should enable client side tracing. This is discussed
later in this article.

4.6 Common Errors trying to Connect from a Client

1. Error: ORA-12154: TNS:could not resolve service name
Cause: The Net layer cannot find a definition for the
alias supplied. Eg: sqlplus scott/tiger@alias
Check: TNS_ADMIN is set in your environment

There is a readable tnsnames.ora file in $TNS_ADMIN

The tnsnames.ora file contains a line of the form 'alias=(...)'
for the alias you are specifying.

Lines in the tnsnames.ora have NAMES.DEFAULT_DOMAIN appended
to them if this is set in sqlnet.ora.
Eg: MYALIAS.WORLD= ... rather than just MYALIAS=...

If none of these show an error enable client side tracing
at level 16 and see what has been written to the client trace
file. There should be a section headed 'tnsnames.ora TABLE HAS
THE FOLLOWING CONTENTS' which will list the aliases loaded.

2. Error: ORA-12203: TNS:unable to connect to destination
Cause: There are many causes of this error
Check: Further qualify the error by checking the sqlnet.log file.
This should be produced in the local directory UNLESS you
have specified 'log_directory_client' in the configuration
file. There should be a 'Tns error structure' reported in
this file. It is a good idea to delete the sqlnet.log file
and re-try the connect to make sure you see the correct error
structure and not an older error.

If no log can be found enable client side tracing and see the
trace file - it should also show the error stack.

Once you have an error stack check the lower level errors
as below.

It is also wise to check the listener.log file as a connect
attempt may have reached the listener.

3. Error: In the sqlnet.log file:
TNS-12538: TNS:no such protocol adapter
Cause: The alias has resolved to an entry of the form (PROTOCOL=XXX)
but XXX is not an installed protocol adapter.
Check: Ensure the protocol adapter has been installed under your
Oracle distribution thus:
grep -i adapter $ORACLE_HOME/orainst/unix.rgs
This should list the adapters that have been installed.

Next check if the adapter has been linked into the executable
you are using. Eg: for sqlplus you should check:
adapters $ORACLE_HOME/bin/sqlplus
This will list the adapters installed in the named program.
If your adapter is not listed relink the product. Eg: for
cd $ORACLE_HOME/sqlplus/lib
make -f sqlplus.mk install
It would also be wise to relink the listener and any other
commonly used tools. Eg: To relink the listener:
cd $ORACLE_HOME/network/lib
make -f network.mk install

(Note: 'adapters' may not work correctly on Oracle8 products)

4. Error: In the sqlnet.log file:
TNS-12541: TNS:no listener
Cause: The alias has been resolved but there appears to be no
listener on the specified address/es.
Check: Look in the sqlnet.log file for the address list that the
client process tried to connect to. This should be in the form
The client could not contact a listener at ANY of the listed
Check where you have listeners make sure they are running using:
lsnrctl status
For each running listener check what addresses it is listening
on - you will need to look as the listener.log file/s for this.
If you appear to be calling a valid address then it is most
likely that there is a name resolution issue. For example:
for TCP/IP the (HOST=hostname) part of the address needs to
be expanded to a TCP/IP address. Check your /etc/hosts file
and check if you are running NIS (Yellow Pages) - the address
for 'hostname' should be the same wherever it is resolved.
Hard code an actual address into (HOST=...) and retry the
If you still have this error check that you can telnet to
the address being specified in (HOST=...), and check this is
the expected host.

5. Error: In the sqlnet.log file:
TNS-12206: TNS:navigation error
TNS-12547: TNS:lost contact
Cause: Client got in touch with the listener but when trying to send a
data packet the connection had been closed.
Check: Raise the value of CONNECT_TIMEOUT_LISTENER in listener.ora to
30 seconds or more. Eg: Add a line CONNECT_TIMEOUT_LISTENER=30
to your listener.ora file.
If the error still occurs get client and listener trace at
level 16. (See section 7 or [NOTE:16658.1] )

Links: Contents [NOTE:16651.1]
Section 5 [NOTE:16656.1]