Location and general use of the sqlnet.ora File

The sqlnet.ora file is a significant configuration file used in Oracle Network Services. This file is typically found on the client side as well as on the database server side, within the NETWORK/ADMIN subdirectory of the Oracle home directory. It is crucial to understand that the sqlnet.ora file's precise location may differ depending on the environment, the Oracle software version, and the operating system on which it's installed.
The primary role of the sqlnet.ora file is to manage and control the communication interface between an Oracle client and an Oracle server. This file is a key part of Oracle Net Services, Oracle's solution for enabling network communication within a database environment. It provides the ability to communicate with multiple databases across different hosts, platforms, and operating systems.
The sqlnet.ora file allows the DBA (Database Administrator) or a developer to configure and fine-tune Oracle network communication. This can include aspects such as client-server connection establishment, encryption settings, trace parameters, and various advanced networking features.
Here are some important parameters that can be configured within the sqlnet.ora file:

1. NAMES.DIRECTORY_PATH: This parameter determines the order of the naming methods used when a client attempts to connect to a database.
2. SQLNET.AUTHENTICATION_SERVICES: Specifies the authentication services to use for network connections.
3. TRACE_LEVEL_CLIENT: It defines the level of detail for client trace information.
4. SQLNET.EXPIRE_TIME: This is a keep-alive parameter. It specifies the time interval, in minutes, to send a probe to verify that client/server connections are active.
5. SQLNET.ENCRYPTION_SERVER, SQLNET.ENCRYPTION_CLIENT, SQLNET.ENCRYPTION_TYPES_SERVER, SQLNET.ENCRYPTION_TYPES_CLIENT: These parameters enable you to control the use of data encryption between the client and the server.
6. WALLET_LOCATION: This parameter specifies the location of the Oracle wallet, a secure software container used to store authentication and encryption keys.

Remember, any modifications made to the sqlnet.ora file will only take effect after the listener or Oracle database instance is restarted. Furthermore, the changes you make in the sqlnet.ora file on the database server side don't affect clients, and vice versa. That's because the sqlnet.ora file is read by each Oracle process at startup.

The basic purpose of the sqlnet.ora file is to provide server-specific information about default locations and thresholds for network services. This special file is created for all Oracle servers and nodes on the network and includes the following information:

1. The time interval between the sending of two probes to determine whether a client/server connection is still alive
   (This is called dead connection detection.)
2. Optional tracing and logging parameters
3. Client parameters for use with Oracle Names
4. The location of the Net trace and log files
5. Other optional parameters

In Oracle7, the default location of sqlnet.ora was in the $ORACLE_HOME/network/admin directory on a UNIX server, and in ORACLE_HOME\network\admin on Windows servers.

IFILE=/tmp/listener_em.ora
IFILE=/tmp/listener_cust1.ora
IFILE=/tmp/listener_cust2.ora

SQL*Net/Net can automatically detect and terminate connections that are no longer valid. This feature is particularly useful for environments in which the clients are PCs, because users may reboot their PCs or otherwise terminate their sessions without logging out of the database. Rebooting a PC does not in and of itself cause the corresponding database session to terminate, because the underlying transport, such as TCP/IP, does not recognize it as such. The worst-case scenario is that the user reboots his PC while he has a lock on a table. Although you can assign a profile to your users that limits connect time and idle time, you usually have to set these limits high enough to accommodate users who want to go out to lunch without logging out of the application (e.g., several hours). You can use dead connection detection to search and destroy invalid connections every 10 minutes or so. To do so, you must specify the optional parameter SQLNET.EXPIRE_TIME in the sqlnet.ora file. The number you specify is the frequency in minutes with which SQL*Net/Net probes connections to confirm their validity. Sessions that are dead or invalid are terminated. If dead connection detection is enabled, 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 that returns an error, it causes the server to terminate the connection.
Of course, there is a certain amount of overhead associated with using dead connection detection:

1. Additional network traffic for the dead connection probes every SQLNET.EXPIRE_TIME minutes.
2. Potential performance degradation on the Oracle server which must distinguish between connection probing events and other events. You should perform your own analysis to determine whether your platform is adversely affected.

Some protocols have their own dead connection detection algorithms, which may obviate the need to use SQL*Net/Net's version.
In the next lesson, we will look at a this same file for an Oracle server.