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

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

Creation Date: 25-APR-1999
Last Revision Date: 04-APR-2000

Configuring TNSNAMES.ORA, SQLNET.ORA and LISTENER.ORA FILES 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 name of the listener
must be unique on the machine on which it resides.


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:
=========================

# E:\ORANT\NET80\ADMIN\LISTENER.ORA Configuration
File: E:\orant\net80\admin\listener.ora
# Generated by Oracle Net8 Assistant

* 1 * LISTENER =
* 2 *(ADDRESS_LIST =
* 3 * (ADDRESS = ( PROTOCOL = IPC ) ( KEY = oracle.us.oracle.com ) )
(ADDRESS = ( PROTOCOL = IPC ) ( KEY = ORCL ) )
* 4 * (ADDRESS = ( PROTOCOL = TCP ) ( HOST = dan.us.oracle.com)
( PORT = 1521 ) )
(ADDRESS = ( PROTOCOL = TCP ) ( HOST = dan.us.oracle.com)
( PORT = 1526 ) )
)

* 5 * SID_LIST_LISTENER =
( SID_LIST =
* 6 * (SID_DESC =
( GLOBAL_DBNAME = ORCL.us.oracle.com)
( ORACLE_HOME = E:\orant )
( SID_NAME = ORCL )
)
(SID_DESC =
( GLOBAL_DBNAME = ORC1.us.oracle.com )
( SID_NAME = ORC1 )
)
(SID_DESC =
( GLOBAL_DBNAME = ORC2.us.oracle.com)
( SID_NAME = ORC2 )
)
)

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 must 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. The IPC addresses
also identify the information sent or registered by a database dispatcher.
If the addresses identify a database dispatcher, the KEY value is equal
to the database system identifier (SID).

4. The TCP address identifies incoming TCP connections from the network
clients attempting to connect to a listener port, default is port 1521.
The clients use the ports defined in their TNSNAMES.ORA file 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 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:
=================================

LISTENER1 =
(ADDRESS = ( PROTOCOL = TCP ) ( HOST = dan.us.oracle.com )
( PORT = 1521 ) )

SID_LIST_LISTENER1 =
(SID_DESC =
( SID_NAME = ORCL )
)

LISTENER2 =
(ADDRESS = ( PROTOCOL = TCP ) ( HOST = dan.us.oracle.com )
( PORT = 1522 ) )

SID_LIST_LISTENER2 =
(SID_DESC =
( SID_NAME = ORCL )
)


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.

Before a database server can receive connections from clients, the 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. 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_HOME\NET80\ADMIN" 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_HOME\BIN",
then Net8 looks for a local TNSNAMES.ORA in "ORANT\BIN" 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_HOME\NET80\ADMIN" directory. On Unix
platforms, setting the environment variable $TNS_ADMIN= <location_of_file>
will direct Net8 to look for the file in that location. On Windows NT, the
TNS_ADMIN in 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_HOME\NET80\ADMIN\SAMPLE" directory,
which can be edited by hand.


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

V805.US.ORACLE.COM = <-- V805 is the service alias,
US.ORACLE.COM is the domain
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = <-- section matches LISTENER.ORA
(PROTOCOL = TCP) <-- protocol
(Host = DAN.US.ORACLE.COM) <-- Database server host name
or IP address
(Port = 1521) <-- port the session will connect to
)
)
(CONNECT_DATA = (SID = ORCL)) <-- sid = the database sid
)

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
and connect string/service name (alias) V805
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:
=============

V805.US.ORACLE.COM=
(DESCRIPTION=
(ADDRESS_LIST= _____
(ADDRESS= |
(PROTOCOL= TCP) |-------------|
(Host = DAN.US.ORACLE.COM) | |
(Port= 1521) _____| |
) |
) |
(CONNECT_DATA = (SID=ORCL)) <--| |
) | |
| |
| |
LISTENER.ORA | |
| |
| |
LISTENER= ______ | |
(ADDRESS_LIST= | | |
(ADDRESS= | | |
(PROTOCOL= TCP) |------------------|
(Host = DAN.US.ORACLE.COM) | |
(Port= 1521) ______| |
) |
) |
SID_LIST_LISTENER= |
(SID_LIST= |
(SID_DESC= |
(GLOBAL_DBNAME= orcl.oracle.com) |
(SID_NAME = ORCL) <--|
)
)


In the above cases I have used the fully qualified domain name (FQDN) in
the host entry. It is acceptable to use only the host name (i.e. DAN) as
the domain information will be retrieved from the SQLNET.ORA file, if it is
not specified in TNSNAMES.ORA file (see below). Of course you will need to
have a SQLNET.ORA file present in the "ORACLE_HOME\NET80\ADMIN" directory
for the domain to be appended. The SQLNET.ORA file is not necessary for
client connections, and thus it is a good habit to specify the fully
qualified domain name in the TNSNAMES.ORA file.


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

V805.US.ORACLE.COM=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL= TCP
(Host = DAN.US.ORACLE.COM)
(Port= 1521)
)
)
(CONNECT_DATA = (SID=ORCL))
)


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

AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
NAMES.DEFAULT_DOMAIN = us.oracle.com
SQLNET.CRYPTO_SEED = "12101751101259991325"
NAMES.DIRECTORY_PATH = (TNSNAMES,ONAMES)


In the above example the NAMES.DEFAULT_DOMAIN = us.oracle.com, this domain
will be appended to the host name if it is not specified in the TNSNAMES.ORA
configuration. The generic sample will populate NAMES.DEFAULT_DOMAIN = WORLD,
this 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:


BEQ-LOCAL.US.ORACLE.COM = <-- beq-local for a local connection
US.ORACLE.COM = domain
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = BEQ)
(PROGRAM = oracle8) <-- identifies the Oracle8 exe
(ARGV0 = oracle8ORCL) <-- identifies the sid
(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') <-- identifies
) the source of the connection (local)
)
(CONNECT_DATA = (SID = ORCL)) <-- sid = default sid of database
)


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):
==================================

NAMES.DEFAULT_DOMAIN = us.oracle.com <-- domain is appended to host name
if not specified in TNSNAMES.ORA

NAMES.DIRECTORY_PATH = (TNSNAMES,ONAMES) <-- search order Net8 uses when
deciding which configuration files
to search

AUTOMATIC_IPC = OFF <-- interprocess communication variable
for local connection requires an
entry in tnsnames.ora

TRACE_LEVEL_CLIENT = OFF <-- set this to a number between 1-16
if tracing is desired (higher
yields more detail)

TRACE_FILE_CLIENT = <-- name of file to which trace is
written (default is sqlnet.trc)

TRACE_DIRECTORY_CLIENT = <-- name of the directory where the
trace file is placed (default is
current working directory)

TRACE_UNIQUE_CLIENT = OFF <-- a unique trace file is created for
each client. If set to off a new
trace will overwrite existing file

LOG_FILE_CLIENT <-- sets the name of the log file
(default is sqlnet.log)

LOG_DIRECTORY_CLIENT <-- name of the directory where the
log file is placed (default is the
current working directory)


SQLNET.EXPIRE_TIME= 0 <-- dead connection detection time
parameter. Set a value to alert
the server to ping the client to
detect whether the client has
terminated unexpectedly and
release the resources associated
with it.


NAMES.PREFERRED_SERVERS = ** <-- this section would list the
Oracle Name Servers and their
search order
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = TCP_COMMUNITY)
(PROTOCOL = TCP)
(Host = nameserver4.us.oracle.com)
(Port = 1575)
)
)


NOTE: Oracle Names is not widely used, it is included for informational
purposes only.

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


SQLNET.CRYPTO_SEED = "12101751101259991325" <-- this is the character
string used to increase
the complexity of data
encryption

SQLNET.CRYPTO_CHECKSUM_CLIENT= requested <-- checksumming is turned on
and is "asked for" by the
client

SQLNET.CRYPTO_CHECKSUM_TYPE_CLIENT= MD5 <-- type of checksum method
used by client

SQLNET.ENCRIPTION_TYPES_CLIENT= (des40) <-- encryption algorithm used
by client if not specified
by the server

SQLNET.ENCRIPTION_CLIENT= rejected <-- depending on the servers
connection will send
encrypted information or
connection will fail **


SQLNET.CRYPTO_CHECKSUM_SERVER= required <-- ANO checksuming is turned
on and is required by the
server

SQLNET.ENCRIPTION_TYPES_SERVER= (des40) <-- encryption algorithm
preferred by server

SQLNET.ENCRIPTION_SERVER=accepted <-- depending on the client
connection will send
encrypted information or
connection will fail **

Matrix for the CHECKSUM and ENCRIPTION 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 the service is required

REQUESTED The service becomes active if the other side states 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.