How a client connects to Oracle

A connection has three important parameters: username, his password and the net service name (username/password@net_service_name).

The machine trying to open an Oracle connection consults the sqlnet.ora file in order to determine how the net service name should be resolved. This resolution is specified with the parameter names.directory_path in this very file.
The default search order, if this parameter is not set, is: TNSNAMES, ONAMES, HOSTNAME

The sqlnet.ora parameter names.default_domain is appended to the connection alias, if this parameter is set.

The net service name (possible appended with names.default_domain) is then tried to be located in the following files: $HOME/.tnsnames.ora, $TNS_ADMIN/tnsnames.ora, /etc/tnsnames.ora and $ORACLE_HOME/network/admin/tnsnames.ora.
On Windows, also the local application directory is searched.
The first file that contains the alias will be used, the rest will not be looked at.

If no tnsnames.ora file containts the alias, the search goes on to ONAMES.

Assuming there was a match in a tnsnames.ora file, the listener end point is contacted.

If there is a listener on such an endpoint, the listener tries to find the requested service.
If there is no such service, a ORA-12505: TNS:listener could not resolve SID given in connect descriptor will be issued.

Assuming the service could be located by the listener, it will either

• Spawn a server process
• Tell the client to contact a server (mostly dispatcher)

If everything has gone successfully, the client can now use the connection with the server process to communicate with oracle. Handler is a process that acts as a connection point from the listener to the database server. A service handler can either be a dispatcher or a dedicated server.

The listener forwards the client's request to the service handler.

See also On a breakable Oracle where I show (using a Perl proxy what information SQL*Plus sends when it initiates a connection to an Oracle server.