Location and general use of the sqlnet.ora File

The `sqlnet.ora` file is a crucial configuration file in Oracle Network Services that defines client and server network parameters for Oracle 11g R2. It primarily manages network encryption, authentication, timeouts, logging, and tracing options.
Location of sqlnet.ora The `sqlnet.ora` file is typically found in the following locations:

1. On the Server:
   • $ORACLE_HOME/network/admin/sqlnet.ora (Linux/Unix)
   • %ORACLE_HOME%\network\admin\sqlnet.ora (Windows)
2. On the Client:
   • The same path applies for client installations using Oracle Net Services.
3. Alternative Location:
   • If the TNS_ADMIN environment variable is set, it points to the directory where sqlnet.ora resides. This allows flexibility in storing configuration files outside of the default location.

General Use of sqlnet.ora The `sqlnet.ora` file controls how Oracle Network Services operate by defining parameters related to:

1. Network Encryption and Security:
   • SQLNET.ENCRYPTION_CLIENT and SQLNET.ENCRYPTION_SERVER: Enforce encryption policies for secure communication.
   • SQLNET.CRYPTO_CHECKSUM_CLIENT and SQLNET.CRYPTO_CHECKSUM_SERVER: Ensure data integrity in transit.
2. Authentication Methods:
   • SQLNET.AUTHENTICATION_SERVICES: Defines authentication services such as Kerberos, RADIUS, and SSL.
   • Example:
     

     SQLNET.AUTHENTICATION_SERVICES = (NTS)  -- Windows Native Authentication
     

3. Connection Timeouts:
   • SQLNET.INBOUND_CONNECT_TIMEOUT: Specifies the time limit (in seconds) for a client to establish a connection.
   • SQLNET.EXPIRE_TIME: Sets the interval (in minutes) for checking idle connections.
4. Logging and Tracing:
   • TRACE_LEVEL_CLIENT and TRACE_LEVEL_SERVER: Control logging verbosity for troubleshooting.
   • LOG_DIRECTORY_CLIENT and LOG_DIRECTORY_SERVER: Define locations for logs.
5. Name Resolution and TNS Configuration:
   • NAMES.DIRECTORY_PATH: Determines the order of name resolution methods like TNSNAMES, LDAP, or EZCONNECT.
   • Example:
     

     NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
     

Conclusion The `sqlnet.ora` file in Oracle 11g R2 plays a vital role in configuring security, authentication, connection handling, and diagnostics in Oracle Network Services. Its proper configuration is essential for secure, efficient, and manageable database connections.

1. Basic Purpose of `sqlnet.ora`
   • The basic purpose of the `sqlnet.ora` file is to provide configuration settings for Oracle Net Services, which control various aspects of network communication between Oracle clients and servers. These settings include default locations, thresholds, and other configurations.
2. This special file is created for all Oracle servers and nodes on the network
   • The `sqlnet.ora` file is created and used on both Oracle database servers and clients. However, it is not automatically created in every instance and may need to be manually configured or generated, depending on the setup.
3. Information it includes:
   1. Time Interval (Dead Connection Detection)
      • The `SQLNET.EXPIRE_TIME` parameter, as described, specifies the time interval for dead connection detection, where probes are sent to check if the connection is still alive. This feature is important for preventing orphaned sessions in both Oracle client and server environments.
   2. Optional Tracing and Logging Parameters
      • The `sqlnet.ora`file can include tracing (`TRACE_LEVEL_CLIENT`, `TRACE_LEVEL_SERVER`) and logging (`SQLNET.LOG_FILE`) parameters, allowing for diagnostics and logging of network events.
   3. Name Resolution: In modern Oracle environments, name resolution is typically handled using TNSNAMES.ORA, LDAP, or other methods.
   4. The Location of Net Trace and Log Files
      • The `sqlnet.ora` file does specify the locations for trace files (`SQLNET.TRACE_DIRECTORY`) and log files (`SQLNET.LOG_DIRECTORY`).
   5. Other Optional Parameters
      • The `sqlnet.ora` file can include various optional parameters such as encryption settings, authentication services, timeouts, and more.

Oracle 12c was introduced in 2013. It marked a significant release as it introduced the multitenant architecture, allowing for the use of container databases (CDBs) and pluggable databases (PDBs), which was a major innovation in database management.

1. The settings in the sqlnet.ora file apply to all pluggable databases (PDBs) in a multitenant container database environment.
2. Oracle Net Services supports the IFILE parameter^[2] in the sqlnet.ora file, with up to three levels of nesting. The parameter is added manually to the file. The following is an example of the syntax:

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

Refer to Oracle Database Reference for additional information. 3) In the read-only Oracle home mode, the sqlnet.ora file default location is ORACLE_BASE_HOME/network/admin.
4) In the read-only Oracle home mode, the parameters that default to ORACLE_HOME location change to default to ORACLE_BASE_HOME location.

SQLNET.EXPIRE_TIME parameter is still used in Oracle 12c and later versions, including Oracle 12c. This parameter serves the same purpose as in earlier versions, which is to detect dead connections between the Oracle database server and client sessions.
Purpose of `SQLNET.EXPIRE_TIME`:

• The SQLNET.EXPIRE_TIME parameter specifies the time interval, in minutes, after which Oracle Net sends a probe packet to the client to check if the client connection is still alive.
• If the connection is dead (e.g., due to network failure or the client being unreachable), the server can clean up the session and release any associated resources.

Example: In the `sqlnet.ora` file, you can set this parameter like this:

SQLNET.EXPIRE_TIME = 10

This configuration would send a probe to check for a dead connection every 10 minutes.
Notes:

• It is particularly useful in shared server environments or environments where long-running idle sessions can cause resource issues.
• The parameter helps to avoid orphaned sessions that would otherwise stay connected indefinitely.

`SQLNET.EXPIRE_TIME` is still used in Oracle 12c, and its functionality remains unchanged from earlier versions.

Statements with respect to Oracle Network Services from Oracle 12c to Oracle 23c.

1. Sessions that are dead or invalid are terminated.
   • Correct: This is accurate. Oracle Network Services includes mechanisms to detect and terminate sessions that are no longer valid or "dead." A dead session might be one where the client has crashed, the network connection was lost, or the client process was killed.
2. If dead connection detection is enabled, Oracle Network Services sends a probe periodically to determine whether there is an invalid connection that should be terminated.
   • Correct: This refers to the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file. When this parameter is set, Oracle sends a probe at regular intervals (in minutes) to check if a client connection is still active. This feature is called Dead Connection Detection (DCD).
   • If DCD is enabled, Oracle periodically sends a small packet to the client to verify whether it is still responsive. If the client fails to respond, the session is considered dead, and Oracle Network Services initiates termination of that session.
3. If it finds a dead connection, or a connection that returns an error, it causes the server to terminate the connection.
   • Correct: If the probe detects that the connection is dead (no response from the client) or the connection returns an error (e.g., the client is unreachable), Oracle will automatically terminate the session on the server side, releasing the associated resources.

Conclusion: The described behavior of Dead Connection Detection (DCD)^[3] and how Oracle Network Services handles dead or invalid sessions is accurate for Oracle 12c through Oracle 23c. This feature helps maintain resource efficiency and prevent orphaned sessions from consuming unnecessary resources.

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 Oracle Network Services. In the next lesson, we will look at a this same file for an Oracle server.

[1] Container Database (CDB): In Oracle, a Container Database (CDB) is a multitenant architecture that allows multiple pluggable databases (PDBs) to share common resources like memory and undo space. Think of it as a large apartment building (CDB) with individual units (PDBs) that are isolated from each other but share the building's infrastructure.

[2] `IFILE` parameter: The `IFILE` parameter in the `sqlnet.ora` file allows you to include other parameter files within your main `sqlnet.ora` file, similar to how you might use an "include" statement in a programming language. This helps to organize your network configuration by separating different sets of parameters into their own files, which can then be centrally managed and reused across multiple databases or clients.

[3] Dead Connection Detection (DCD): Dead Connection Detection (DCD) is a feature in Oracle Network Services that helps reclaim resources consumed by inactive or terminated client connections. It periodically checks the status of connections and if a connection is found to be unresponsive for a specified time, it is terminated. This prevents resource leakage and ensures the database server remains responsive.