Understanding SQL*Net
Configuring Clients: SQLNET.ORA
The SQLNET.ORA file is created for all clients and nodes on the network. It contains five types of information:
- the amount of time between probes sent to determine whether a client-server connection is still alive (dead connection detection)
- names resolution services
These parameters are described in the following sections.
Dead Connection Detection
The optional parameter SQLNET.EXPIRE_TIME determines how often SQL*Net sends a probe to verify that a client-server connection is still active. If a client is abnormally terminated, a connection may be left open indefinitely unless identified and closed by the system. If this parameter is specified, SQL*Net sends a probe periodically to determine whether there is an invalid connection that should be terminated. If it finds a dead connection, or a connection no longer in use, it returns an error, causing the server process to exit.
Specify this parameter in the Connection Expire Time field of the Client Profile property sheet of Network Manager. Enter the time, in minutes, between probes for a dead connection. The range of possible values is from one to a very large number. However, a value of approximately 10 is recommended. If no value is entered in this field, the broken connections may be maintained indefinitely.
Note: The time set in this parameter is not necessarily the amount of time a dead connection will remain. This parameter sets the time between probes for dead connections. Depending on the underlying protocol, it may be somewhat longer before a dead process is shut down.
Dead connection detection has some costs associated with it.
- Additional network traffic is generated to probe for dead connections. A probe packet is very small, but one is sent on each connection at the interval specified in the SQLNET.EXPIRE_TIME parameter in the SQLNET.ORA file.
- When dead connection detection is enabled, the Oracle Server may need to do additional processing to distinguish the connection probing event from other events that occur, depending on which operating system is in use. For example, additional processing is required on UNIX. As a result, there could be some small amount of degradation of performance in the server. The best way to determine whether you will be affected is to test the performance of your application with and without the dead connection detection feature enabled.
- For some protocols, the generic SQL*Net dead connection detection feature is no better than the native mechanism available in the underlying transport protocol. In that case, it is not necessary to enable it.
In short, you should evaluate carefully whether you would benefit from enabling the dead connection detection feature. It should only be turned on if necessary.
Optional Logging and Tracing Parameters
If you select any optional logging and tracing parameters in the Client Profile property sheet of Network Manager, the following parameters appear in the SQLNET.ORA file:
Note: Comparable parameters may be created for servers (for example, LOG_FILE_SERVER, TRACE_DIRECTORY_SERVER). You must add any logging and tracing parameters for servers to the SQLNET.ORA file by hand; they are not generated by Network Manager or by the SQLNET.ORA Editor.
You can add the following optional tracing parameters for the TNSPING utility to SQLNET.ORA using the SQLNET.ORA Editor. (They produce messages similar to the SQL*Net trace parameters mentioned above.)
For more information about the logging and tracing parameters in SQLNET.ORA, see Chapters 2and 3 in the Oracle Network Products Troubleshooting Guide.
Default Domains
Whether or not you are using Oracle Names, the SQLNET.ORA file includes a parameter that shows the default domain. This parameter is normally set through Network Manager, but can be modified using the SQLNET.ORA Editor.
Name Resolution Services
The order in which different names resolution services are attempted is determined by the NAMES.DIRECTORY_PATH parameter. By default, TNSNAMES.ORA file is the first method listed, with Oracle Names next. If you have installed a native naming adapter, it would appear on the list also. You can change the order in which the names services appear, and therefore are used by the client, by changing them in the Client Profile using Network Manager. By default, Network Manager generates the following parameter and value in the SQLNET.ORA file:
NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES)
Oracle Names Parameters
If you are using Oracle Names, another parameter, NAMES.PREFERRED_SERVERS, is required. This parameter includes one or more addresses of the Names Servers the client prefers to use. Several optional Oracle Names tracing parameters may also appear; they are described in Appendix B of the Oracle Names Administrator's Guide. These parameters are created using the Oracle Network Manager.
Additional SQLNET.ORA Parameters
The SQLNET.ORA file is used primarily for specifying the Dead Connection Detection parameter, tracing parameters, and default domain information. However, there are additional optional parameters which provide other functions you may find useful. The following parameters must be edited manually in the SQLNET.ORA file; they are not affected by Network Manager.
Turning Off IPCs
If for some reason you do not want IPC addresses to be sought automatically on some nodes in your network, you should add the following parameter to the SQLNET.ORA files for those nodes:
AUTOMATIC_IPC=OFF
Without this parameter, the default is for a connection to look for an IPC address.
Using a Dedicated Server
Generally when the listener receives a connection request, it hands off the request to an existing process. That is, it makes use of the multi-threaded server. If you prefer that the listener spawn a dedicated server task or process for connections to this database, add the following line to the SQLNET.ORA file for the listener node:
USE_DEDICATED_SERVER=ON
The default is OFF.
Note: For further information about dedicated server processes and the multi-threaded server, see Chapter 2 in the Oracle7 Server Distributed Systems, Volume I.
It is important that any manually-edited entries be made in SQLNET.ORA after the file is distributed to the destination. Because this file is generated to be common to all clients with the same client profile, changing it for specific nodes or a specific operating system's file structure before moving it to other nodes may have unintended results.
Note: Also, you can configure a service name alias to use a dedicated server process (instead of the multi-threaded server) in Network Manager. After generating configuration files, then add the USE_DEDICATED_SERVER=ON to the SQLNET.ORA file for the listener node.
Disabling Out of Band Breaks
If you want to disable out-of-band breaks, set the DISABLE_OOB=ON parameter in SQLNET.ORA on clients (and servers that will act as clients) to ON. The default is OFF. If you are using an Interchange, out-of-band breaks are not implemented, regardless of how this parameter is set.
DISABLE_OOB=OFF|ON
Currently, you need to add this parameter manually to SQLNET.ORA; you cannot configure it through Network Manager.
Sample SQLNET.ORA Files
This sample SQLNET.ORA file is for a client in a network that does not include Oracle Names and that accepts the default trace parameters. No value is specified for SQLNET.EXPIRE_TIME, which means that dead connection detection is not enabled.
TRACE_LEVEL_CLIENT = OFF
sqlnet.expire_time =
names.default_domain = world
name.default_zone = world
The NAMES.DEFAULT_DOMAIN parameter is created whether or not Oracle Names is part of the network. Oracle Network Manager puts a comparable parameter, NAME.DEFAULT_ZONE, into the file for backward compatibility with an earlier version of SQL*Net.
The following SQLNET.ORA file is for a client in a network that includes Oracle Names. A value has been included for SQLNET.EXPIRE_TIME.
TRACE_LEVEL_CLIENT = OFF
sqlnet.expire_time = 10
names.default_domain = world
name.default_zone = world
names.preferred_servers =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpcom)
(PROTOCOL = TCP)
(Host = vance.world)
(Port = 1522)
)
)
name.preferred_servers =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcpcom)
(PROTOCOL = TCP)
(Host = vance.world)
(Port = 1522)
)
)
This file includes a parameter that shows the client's preferred Names Server. As in the previous sample, there are two different versions of this parameter to provide backward compatibility with an earlier version of the product. Oracle Network Manager creates these parameters automatically.