The sqlnet.ora File in Oracle Network Services

Overview of the sqlnet.ora File

The sqlnet.ora file is a critical configuration file in Oracle Network Services, used on both Oracle database servers and clients to define network communication parameters. It manages settings for security, authentication, connection timeouts, name resolution, and diagnostics.

Location of sqlnet.ora

The sqlnet.ora file is typically located at:

• Server and Client (Default):
  • Linux/Unix: $ORACLE_HOME/network/admin/sqlnet.ora
  • Windows: %ORACLE_HOME%\network\admin\sqlnet.ora
• Alternative Location:
  • If the TNS_ADMIN environment variable is set, it specifies the directory containing sqlnet.ora, allowing flexibility for non-default locations.
• Oracle 12c and Later (Read-Only Oracle Home Mode):
  • The default location shifts to ORACLE_BASE_HOME/network/admin for read-only Oracle home configurations.

General Use of sqlnet.ora

The sqlnet.ora file configures how Oracle Net Services operate, controlling:

1. Network Encryption and Security:
   • Parameters like SQLNET.ENCRYPTION_CLIENT and SQLNET.ENCRYPTION_SERVER enforce encryption for secure data transmission.
   • SQLNET.CRYPTO_CHECKSUM_CLIENT and SQLNET.CRYPTO_CHECKSUM_SERVER ensure data integrity.
2. Authentication Methods:
   • SQLNET.AUTHENTICATION_SERVICES specifies authentication protocols (e.g., Kerberos, RADIUS, SSL).
   • Example:

     SQLNET.AUTHENTICATION_SERVICES = (NTS)  # Windows Native Authentication

3. Connection Timeouts:
   • SQLNET.INBOUND_CONNECT_TIMEOUT sets the time limit (in seconds) for a client to establish a connection.
   • SQLNET.EXPIRE_TIME defines the interval (in minutes) for Dead Connection Detection (DCD), sending probes to check if client connections are active. If a connection is unresponsive, Oracle terminates it to free resources.
   • Example:

     SQLNET.EXPIRE_TIME = 10  # Probes sent every 10 minutes

4. Logging and Tracing:
   • Parameters like TRACE_LEVEL_CLIENT, TRACE_LEVEL_SERVER, LOG_DIRECTORY_CLIENT, and LOG_DIRECTORY_SERVER control diagnostic logging and tracing for troubleshooting.
5. Name Resolution:
   • NAMES.DIRECTORY_PATH determines the order of name resolution methods (e.g., TNSNAMES, LDAP, EZCONNECT).
   • Example:

     NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

Dead Connection Detection (DCD)

The SQLNET.EXPIRE_TIME parameter enables DCD, which periodically sends probe packets to detect unresponsive client connections. This is particularly useful in shared server environments to prevent resource leakage from orphaned sessions. DCD functionality remains consistent from Oracle 11g R2 to Oracle 23c, with no significant changes in its implementation.

Considerations:

• DCD introduces minimal network traffic due to periodic probes.
• It may cause slight performance overhead as the server distinguishes probes from other events. Evaluate its impact based on your platform’s needs.
• Some network protocols have built-in connection detection, potentially reducing the need for SQLNET.EXPIRE_TIME.

Oracle 12c and Later: Multitenant Architecture

Introduced in 2013, Oracle 12c brought the multitenant architecture with Container Databases (CDBs) and Pluggable Databases (PDBs). In this environment:

• The sqlnet.ora settings apply globally to all PDBs within a CDB.
• The IFILE parameter allows inclusion of additional configuration files (up to three levels of nesting) for modular configuration management.
• Example:

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

Differences Between Oracle 11g R2 and Oracle 19c

While the core functionality of sqlnet.ora remains consistent, key differences include:

• Multitenant Support: Oracle 19c fully supports the multitenant architecture introduced in 12c, where sqlnet.ora settings apply to all PDBs in a CDB.
• Enhanced Security: Oracle 19c introduces stricter default encryption and checksum algorithms (e.g., SHA-2 support) compared to 11g R2.
• Read-Only Oracle Home: In Oracle 19c, the default location for sqlnet.ora may shift to ORACLE_BASE_HOME/network/admin in read-only Oracle home mode.
• Deprecated Parameters: Some parameters in 11g R2 (e.g., older encryption algorithms) are deprecated or updated in 19c to align with modern security standards.

Conclusion

The sqlnet.ora file is essential for configuring Oracle Net Services, enabling secure, efficient, and manageable database connections. Its parameters govern encryption, authentication, timeouts, diagnostics, and name resolution. While its core role remains unchanged from Oracle 11g R2 to 19c, enhancements in multitenant support and security in later versions reflect Oracle’s evolution. Proper configuration of sqlnet.ora ensures robust network communication and resource management.

For further details, refer to the Oracle Database Reference or explore resources like Cloud DBA Oracle.