Lesson 7 | The sqlnet.ora File |
Objective | Describe the location, general use, and key configuration parameters of the sqlnet.ora file in Oracle Network Services for Oracle 11g R2, and highlight differences with Oracle 19c. |
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.
The sqlnet.ora
file is typically located at:
$ORACLE_HOME/network/admin/sqlnet.ora
%ORACLE_HOME%\network\admin\sqlnet.ora
TNS_ADMIN
environment variable is set, it specifies the directory containing sqlnet.ora
, allowing flexibility for non-default locations.ORACLE_BASE_HOME/network/admin
for read-only Oracle home configurations.The sqlnet.ora
file configures how Oracle Net Services operate, controlling:
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.SQLNET.AUTHENTICATION_SERVICES
specifies authentication protocols (e.g., Kerberos, RADIUS, SSL).SQLNET.AUTHENTICATION_SERVICES = (NTS) # Windows Native Authentication
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.SQLNET.EXPIRE_TIME = 10 # Probes sent every 10 minutes
TRACE_LEVEL_CLIENT
, TRACE_LEVEL_SERVER
, LOG_DIRECTORY_CLIENT
, and LOG_DIRECTORY_SERVER
control diagnostic logging and tracing for troubleshooting.NAMES.DIRECTORY_PATH
determines the order of name resolution methods (e.g., TNSNAMES, LDAP, EZCONNECT).NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
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:
SQLNET.EXPIRE_TIME
.Introduced in 2013, Oracle 12c brought the multitenant architecture with Container Databases (CDBs) and Pluggable Databases (PDBs). In this environment:
sqlnet.ora
settings apply globally to all PDBs within a CDB.IFILE
parameter allows inclusion of additional configuration files (up to three levels of nesting) for modular configuration management.IFILE=/tmp/listener_em.ora IFILE=/tmp/listener_cust1.ora IFILE=/tmp/listener_cust2.ora
While the core functionality of sqlnet.ora
remains consistent, key differences include:
sqlnet.ora
settings apply to all PDBs in a CDB.sqlnet.ora
may shift to ORACLE_BASE_HOME/network/admin
in read-only Oracle home mode.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.