Configuring TNSNAMES.ORA, SQLNET.ORA,and LISTENER.ORA in Net8 [ID 69725.1]

Applies to:

Oracle Net Services - Version: 7 to 8.0
Information in this document applies to any platform.

Purpose

This article describes configuration of sqlnet.ora, tnsnames.ora and listener.ora. This article has been written focusing versions 7 to 8.0.

Scope and Application

This article is intended for database administrators who would like to configure Net8

Configuring TNSNAMES.ORA, SQLNET.ORA,and LISTENER.ORA in Net8

The LISTENER.ORA File:
===================

Database listeners are configured to listen on one or more port addresses specified in the configuration file "listener.ora". After a listener is started, it receives client connect requests and responds to them on behalf of the database. In addition to configuring one listener for multiple databases, you can also configure multiple listeners to listen on behalf of a single database. This configuration helps to perform load balancing. A listener can also listen on multiple protocols. The names of the listeners and their addresses must be unique on the machine on which they reside.


Types of Connection Requests:
=====================

When a connection request is made by a client to a server, the connection can be made by in of three ways: a bequeath session, a redirect session to a prespawned dedicated server process, or a redirect session to a dispatcher multithreaded server. The type of connection is transparent to the user.

1. In a bequeath session, the listener spawns a dedicated server process and passes or bequeaths the connection request to the server process. The listener and the server must exist on the same node, and the operating system or protocol must allow a connection to pass between two different processes on the same machine. A bequeath session starts when a client connects to a listener. The listener determines whether or not the request can be serviced. The listener refuses a connection request if the requested server is not known or unavailable. If the listener agrees to service the connection request, it spawns a new dedicated server process to serve the incoming session. Next, the listener bequeaths the session to the newly spawned dedicated server process. A session is established between the client and the dedicated server process and data flows directly between the client and the dedicated server process. Meanwhile, the listener continues to listen for other incoming session requests. When the client disconnects from the server, the dedicated server process associated with the client also terminates. The bequeath method uses less system resources in comparison to the other methods of processing client connection requests. If a dedicated server does not have any prespawned server processes, bequeath is the default method.

2. A listener can also process an incoming connection request by using a redirect session to a dedicated server process. You need to set the PRESPAWN_MAX parameter in the LISTENER.ORA file. This defines the maximum number of prespawned server processes that can exist at a time. The redirect session to a dedicated server process reduces the connection time but uses more system resources. When the listener is started and the dedicated server processes are spawned, a client can request the listener for a connection. If the request is successful, the listener redirects the client request to a prespawned server by issuing a Redirect message with the network address of the prespawned server. When the client receives the Redirect message, it disconnects from the listener and connects to the prespawned server address. A session is established and the listener spawns another dedicated server process to replace the one used by the client. Meanwhile, the listener continues to listen for other incoming sessions.

3. When an Oracle server is configured as a Multi-threaded Server, incoming connection requests are redirected by a listener to a dispatcher process. The dispatcher processes the request by allocating shared server processes to the clients. This enables many clients to connect to the same server without the need to spawn a server process or to have prespawned dedicated server processes. When configured as a Multi-threaded Server and a database instance is started, dispatchers are started on the basis of the configuration parameters defined in the INIT.ORA. The address of each dispatcher is registered with the listener associated with the database. This helps the listener to monitor the use of each dispatcher and redirect client requests to the least-used dispatcher. The client connects to the listener. The listener receives the connection request and determines whether or not the request can be serviced. If the listener accepts the connection request, it issues a Redirect message to the client containing the network address of the least-used dispatcher for the server. After the client receives the Redirect message, the client disconnects from the listener and connects to the dispatcher address given by the listener. The dispatcher establishes the session with the client and updates the listener with the new load value due to the presence of the new session. The dispatcher also handles the allocation or deallocation of the shared server processes.


Sample LISTENER.ORA File:
====================

When a listener is started it reads the contents of the "listener.ora" file to find out the database services for which it should listen. This file should be located on the node on which the listener is located.

1. The default name of a listener is LISTENER, but it can be customised.

2. The ADDRESS_LIST is also known as the LISTENER_ADDRESS parameter. It contains a block of addresses at which the listener will listen for incoming connections. Each of the addresses defined under the ADDRESS_LIST parameter contains protocol-specific information about how a listener receives and processes a connection request.

3. The INTER-PROCESS COMMUNICATION (IPC) addresses identify the connection requests sent by applications on the same node as the listener, the KEY value is equal to the service name of the database.

4. The TCP address identifies incoming TCP connections from the network clients attempting to connect to a listener port. The default is port 1521. The clients use the ports defined in their TNSNAMES.ORA files to connect to the listener. The listener redirects the connection based on the host defined in the ADDRESS parameter. (TCP is shown here as a common protocol).

5. The SID_LIST_LISTENER_NAME contains information about the SIDs of the databases for which a listener can listen. If more than one SID is defined for a listener, the SID_LIST parameter is defined in the default LISTENER.ORA file.

6. The SID_DESC usually contains three parameters under it. These parameters are GLOBAL_DBNAME, ORACLE_HOME, and SID_NAME. The GLOBAL_DBNAME parameter contains the global database name, which uniquely identifies a database in a hierarchical naming structure. The ORACLE_HOME parameter contains the path of the home directory of the database. This enables the control utility to locate the listener executable file. The SID_NAME parameter defines the name of the database system identifier (SID) on behalf of which the listener accepts connections.

Multiple Listeners on a Database:
======================

If multiple listeners service a single database, a client will sequentially select a listener for its connection requests. This enables the listeners to share the burden of incoming connection requests. To allow multiple listeners to service a single database and enable the client to select a listener at random, a different listener address must be configured for the same service name in the "listener.ora" file.

The "listener.ora" file above is configured for the two listeners LISTENER1 and LISTENER2. The addresses of the two listeners are different in terms of the port numbers to which they listen. LISTENER1 listens to the port number 1521, and LISTENER2 listens to the port number 1522. However, the SID information for both the listeners is the same.

TNSNAMES.ORA File:
==================

Oracle uses three types of Naming methodology in configuring network connections between database servers and client connections (i.e. the Oracle Application Server and SQL*Plus). Oracle Names is a centralized management scheme where an Oracle Names Server performs network address resolution similar to a DNS server. Another method is the native naming method which resolves service names through NIS. The third method, Local Naming, is the one we will discuss here. Local naming resolves service names to network addresses by using information configured in the TNSNAMES.ORA file that is stored locally on each client node. The TNSNAMES.ORA file is comprised of two parts; Service Names and Connect Descriptors.

With Local Naming, clients must have a TNSNAMES.ORA file, with service names that map to the service addresses listed in the Database server's LISTENER.ORA file before they can connect. Client configuration is accomplished by creating a list of service names (which are aliases for a database network address) and addresses of network destinations (database server name or IP address) in the TNSNAMES.ORA file.

It is possible to have both a system and a local version of the TNSNAMES.ORA file. When a client connection is requested the service name or parameter is first searched in the local version of the configuration file. If the service is not found in the local version it is searched in the system version. The system version is located in the "ORACLE_HOMENET80ADMIN" directory. A local version can exist in the current working directory where the application is running. For example, if, on Windows NT you start SQL*Plus in "ORACLE_HOMEBIN", then Net8 looks for a local TNSNAMES.ORA in "ORANTBIN" before looking for the system version. As a consequence you could have multiple local files in various directories. It is recommended that only one TNSNAMES.ORA file exist and that it be located in the default "ORACLE_HOMENET80ADMIN" directory. On Unix platforms, setting the environment variable $TNS_ADMIN= will direct Net8 to look for the file in that location. On Windows NT, the TNS_ADMIN variable is located in the registry.

The TNSNAMES.ORA file can be configured with a utility called Oracle Net8 Assistant or Oracle Net8 Easy Config provided by Oracle. There should also be a generic copy located in the "ORACLE_HOMENET80ADMINSAMPLE" directory, which can be edited by hand.


Sample TNSNAMES.ORA File Entry:
==========================

Using a connect string of V805.US.ORACLE.COM, the above entry added to a TNSNAMES.ORA files would allow you to connect to a Database server residing on:

network address dan.us.oracle.com
using port 1521
sid ORCL


This entry in the TNSNAMES.ORA would also match up to a configuration entry in the LISTENER.ORA file of the database service you wish to connect to:



TNSNAMES.ORA:
=============

In the above case I have used the fully qualified domain name (FQDN) in the HOST entry. It is acceptable to use only the hostname (i.e. DAN) or IP address as long as the hostname is successfully resolved by the underlying network configuration e.g. hosts file, Domain Name Server (DNS) or other.


TNSNAMES.ORA:
=============

SQLNET.ORA:
===========

In the above example the NAMES.DEFAULT_DOMAIN = us.oracle.com. This domain name is appended to the service name if not specified in the TNSNAMES.ORA configuration. The generic sample populates NAMES.DEFAULT_DOMAIN = WORLD, which should be changed to the domain of the network.

Another common entry in the TNSNAMES.ORA file that will allow for connections when the database and client are on the same node is the Bequeath Address:

SQLNET.ORA File:
================

The SQLNET.ORA file contains optional parameters, used by clients (one server can be a client to another server) on the network for logging, tracing, and security. It is also used as the server locator if Oracle Names is used as the method of resolving network addresses. The section labeled "NAMES.PREFERRED_SERVERS =" would list the different names servers and their search order in the same way a DNS db file would list its NS records. The focus of this document is on the tracing, logging, and security parameters.


Sample SQLNET.ORA File (Enhanced):
===========================

Advanced Networking Option (ANO) Parameters:
==================================

Matrix for the CHECKSUM and ENCRYPTION values:
=======================================

If you do not specify a value for any of these parameters, ANO defaults the value for that parameter to ACCEPTED.

ACCEPTED The service becomes active if the other side states that the service is requested or required

REJECTED The service is not enabled at all and the connection fails if the other side states that the service is required

REQUESTED The service becomes active if the other side states that the service is accepted, requested or required

REQUIRED The service becomes active only if the other side accepts, requests or requires the service.
The connection will fail if the other side states that the service is rejected.



LISTENER.ORA:
=============

References

- SQL*Net, Net8, Oracle Net Services - Tracing and Logging at a Glance[@more@]