Network configuration listener and tnsnames in oracle

Network configuration listener and tnsnames in oracle

Oracle Network Configuration
===========================
In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration.

•Assumptions
•Listener.ora
•Tnsnames.ora
•Sqlnet.ora
•Testing

Assumptions
==========
The example files below are relevant for an Oracle installation and instance with the following values.

Parameter Value
=================
HOST hostname
ORACLE_HOME /u01/app/oracle/product/9.2.0
ORACLE_SID ORCL
DOMAIN world

Listener.ora
============
The listerner.ora file contains server side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server. Here is an example of a listener.ora file from Windows 2000.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL.WORLD)
(ORACLE_HOME = /u01/app/oracle/product/9.2.0)
(SID_NAME = ORCL)
)
)After the “listener.ora” file is amended the listener should be restarted or reloaded to allow the new configuation to take effect.

C:> lsnrctl stop
C:> lsnrctl start

C:> lsnrctl reload

Tnsnames.ora
============

The “tnsnames.ora” file contains client side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” or “$ORACLE_HOME/net80/admin” directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a “tnsnames.ora” file.

ORCL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL.WORLD)
)
)

Sqlnet.ora
==========

The “sqlnet.ora” file contains client side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” or “$ORACLE_HOME/net80/admin” directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of an “sqlnet.ora” file.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD

# The following entry is necessary on Windows if OS authentication is required.
SQLNET.AUTHENTICATION_SERVICES= (NTS)

Testing
=======
Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (ORCL).

$ sqlplus scott/tiger@orcl

Performance
============

The tcp.nodelay parameter in the protocol.ora file
==================================================

Oracle Net, by default, waits until the buffer is filled before transmitting data. Therefore, requests aren’t always sent immediately to their destinations. This is most common when large amounts of data are streamed from one end to another, and Oracle Net does not transmit the packet until the buffer is full. Adding a protocol.ora file, and specifying a tcp.nodelay to stop buffer flushing delays, can remedy this problem.

The protocol.ora file can be specified to indicate no data buffering for all TCP/IP implementations. The parameter can be used on both the client and server. The protocol.ora statement is:

tcp.nodelay = yes

Specifying this parameter causes TCP buffering to be skipped so that every request is sent immediately. Keep in mind, however, that network traffic can increase due to smaller and more frequent packet transmission, causing slowdowns in the network.

The tcp.nodelay parameter should be used only if TCP timeouts are encountered. Setting tcp.nodelay can cause a huge improvement in performance when there is high-volume traffic between database servers.

The automatic_ipc parameter of the sqlnet.ora file
===================================================

The automatic_ipc parameter bypasses the network layer, thereby speeding local connections to the database. When automatic_ipc=on, Oracle Net checks to see if a local database is defined by the same alias. If so, network layers are bypassed as the connection is translated directly to the local IPC connections. This is useful on database servers, but it’s absolutely useless for Oracle Net clients.

The automatic_ipc parameter should be used only on the database server when an Oracle Net connection must be made to the local database. If local connections are not needed or required, set this parameter to off; with this setting, all Oracle Net clients can improve performance.

The SDU and TDU parameters in the tnsnames.ora file
===================================================

The session data unit (SDU) and transport date unit (TDU) parameters are located in the tnsnames.ora and listener.ora files. SDU specifies the size of the packets to send over the network. Ideally, SDU should not surpass the size of the maximum transmission unit (MTU). MTU is a fixed value that depends on the actual network implementation used. Oracle recommends that SDU be set equal to MTU.

The TDU is the default packet size used within Oracle Net to group data together. The TDU parameter should ideally be a multiple of the SDU parameter. The default value for both SDU and TDU is 2,048, and the maximum value is 32,767 bytes.

The following guidelines apply to SDU and TDU:

• The SDU should never be set greater than TDU because you’ll waste network resources by shipping wasted space in each packet.

• If your users are connecting via modem lines, you may want to set SDU and TDU to smaller values because of the frequent resends that occur over modem lines.

• On fast network connections (T1 or T3 lines), you should set SDU and TDU equal to the MTU for your network. On standard Ethernet networks, the default MTU size is set to 1,514 bytes. On standard token ring networks, the default MTU size is 4,202.

• If the Multi-Threaded Server (MTS) is used, you must also set the mts_dispatchers with the proper MTU TDU configuration.

The SDU and TDU settings are a direct function of the connection speed between the hosts. For fast T1 lines, set SDU=TDU=MTU. For slower modem lines, experiment with smaller values of SDU and TDU.

The queuesize parameter in the listener.ora file
===============================================

The number of requests the listener can store while Oracle is working to establish a connection is determined by the undocumented queuesize parameter. This parameter is used only for very high-volume databases, where the listener spawns thousands of connections per hour. The number of expected simultaneous connections should be equal to the size of the queuesize parameter. Here’s an example of this parameter in the listener.ora file:

LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = marvin)
(PORT = 1521)
(QUEUESIZE = 32)
)
)

A disadvantage of this parameter is that it preallocates resources for anticipated requests, therefore using more system memory and resources. You may want to consider using MTS and prespawned Oracle connections if you have high-volume connections into a dedicated listener. Also, note that some versions of UNIX do not allow queues greater than five, and there are some restrictions of the MTS queue size.