Characteristics of the "sqlnet.ora file" for an Oracle shared server 11g

The `sqlnet.ora` file is a configuration file used by Oracle Net Services to define network connectivity options and parameters for an Oracle Database. For an Oracle shared server running on Oracle 11g, the `sqlnet.ora` file plays a crucial role in configuring various network-related behaviors, including authentication methods, logging, tracing, and other communication-related settings. Here are the key characteristics of the `sqlnet.ora` file in such a configuration:

1. Authentication Settings
   • SQLNET.AUTHENTICATION_SERVICES: Specifies the authentication services to be used, like NONE, NTS (for Windows NT authentication), or KERBEROS5. In a shared server environment, this parameter determines how clients authenticate to the database.
     

     SQLNET.AUTHENTICATION_SERVICES = (NONE)

2. Encryption and Integrity
   • SQLNET.ENCRYPTION_CLIENT / SQLNET.ENCRYPTION_SERVER: These parameters define whether encryption is required for client-server communication. In a shared server environment, this could ensure secure communication between clients and shared server processes.
     

     SQLNET.ENCRYPTION_SERVER = REQUIRED
     SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
     

   • SQLNET.CRYPTO_CHECKSUM_CLIENT / SQLNET.CRYPTO_CHECKSUM_SERVER: Configures message integrity (checksumming) between the client and server to prevent tampering during data transmission.
     

     SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
     SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)
     

3. Logging and Tracing
   • SQLNET.LOG_DIRECTORY: Specifies the location where log files will be stored.

     SQLNET.LOG_DIRECTORY = /path/to/log

   • SQLNET.LOG_FILE: Sets the name of the log file for recording network events.

     SQLNET.LOG_FILE = sqlnet.log

   • TRACE_LEVEL_CLIENT / TRACE_LEVEL_SERVER: Defines the level of detail to be recorded in trace files (e.g., OFF, USER, ADMIN, or SUPPORT). These are helpful for diagnosing network issues in a shared server environment.
     

     TRACE_LEVEL_SERVER = ADMIN

4. Naming and Resolution
   • NAMES.DIRECTORY_PATH: Specifies the naming methods to be used by Oracle Net Services for resolving network service names. Common methods include TNSNAMES, LDAP, or ONAMES.
     

     NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME)

   • SQLNET.ALLOWED_LOGON_VERSION_SERVER: Specifies the minimum authentication protocol version allowed for clients. This can be useful for enhancing security in shared server environments by restricting older protocol versions.
     

     SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11
     

Connection Timeouts

• SQLNET.INBOUND_CONNECT_TIMEOUT: Defines the timeout period for inbound connections, ensuring that clients do not hang while trying to connect to the server.

  SQLNET.INBOUND_CONNECT_TIMEOUT = 60

TCP/IP Settings

• TCP.CONNECT_TIMEOUT / TCP.RECV_TIMEOUT / TCP.SEND_TIMEOUT: These parameters manage various aspects of TCP communication timeouts between clients and the Oracle shared server.

  TCP.CONNECT_TIMEOUT = 10

Dead Connection Detection

• SQLNET.EXPIRE_TIME: Specifies the time interval (in minutes) after which Oracle checks for a dead connection, useful in shared server environments to clean up connections that are no longer active.

  SQLNET.EXPIRE_TIME = 10

Security

• SEC_USER_AUDIT_TRAIL: Defines whether to audit user actions over the network. This can be set to OS for operating system audit trails or DB for database auditing.

  SEC_USER_AUDIT_TRAIL = DB

Connection Load Balancing

• In a shared server environment, you might configure settings that help distribute client connections across different servers in a cluster or shared server pool. However, connection load balancing is more commonly configured in the listener.ora file and tnsnames.ora, but certain elements like session persistence could be referenced here.

The `sqlnet.ora` file is an essential part of network configuration for Oracle databases, including those running in a shared server mode, ensuring that all aspects of client-server communication are securely managed, authenticated, logged, and optimized for performance.
sqlnet.ora File Parameters

TRACE_LEVEL_client = ADMIN
TRACE_LEVEL_server = ADMIN
TRACE_LEVEL_client = 16
TRACE_LEVEL_server = 16
TRACE_DIRECTORY_CLIENT = /ora8/dba/oracle/7.3.3.0.0/network/trace
SQLNET.EXPIRE_TIME = 60
LOG_DIRECTORY_CLIENT = /ora8/dba/oracle/7.3.3.0.0/network/log
AUTOMATIC_IPC=off
names.default_domain = world

The code listed above examines the parameters that describe a typical sqlnet.ora file for an Oracle server.

While all of the SQL*Net parameters are self-explanatory, there are some that are more important than others:

1. Trace levels (client and server) specify the amount of detail SQL*Net will provide in the trace files. The larger the number, the greater the detail. We will cover this in depth later in the course.
2. Directory locations tell SQL*Net where to place the log and trace files. If you do not specify a default directory for trace files, they will be placed in your current directory on the UNIX server. For example, a UNIX user called FRED would find his trace file in the home directory for the FRED user unless he specified another directory. Hence, it is a good idea always to specify the trace and log directory locations. If the locations are not specified (or if you do not have a sqlnet.ora file), the logs are directed to $ORACLE_HOME/rdbms/log.
3. Expire time is an optional parameter that 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.

The file called sqlnet.log shows all failed attempts to connect to remote databases. Oracle does not clean up this file and will append to it forever, so you may want to go to the log_directory_client directory and delete the sqlnet.log file.
View the code below to see a sample of the contents of a sqlnet.log file.
(The error indicates that the remote listener process is not running.)

Oracle TNS (Transparent Network Substrate) error message related to a

TNS-12203: TNS: unable to connect to destination error.

Here's the relevant Oracle-related information and script.

Fatal OSN connect error 12203, connecting to:
  (DESCRIPTION=(CONNECT_DATA=(SID=fred)(CID=(PROGRAM=)(HOST=hostname)(USER=oracle)))
  (ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=fred))(ADDRESS=(PROTOCOL=TCP)(HOST=apbd09)(PORT=1527))))

VERSION INFORMATION:
  TNS for IBM/AIX RISC System/6000: Version 2.3.4.0.0 - Production
  Unix Domain Socket IPC NT Protocol Adapter for IBM/AIX RISC System/6000:
  Version 2.3.4.0.0 - Production
  TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 2.3.4.0.0 - Production
  Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 2.3.4.0.0 - Production
  Time: 23-SEP-98 08:04:11
  Tracing not turned on.

TNS error struct:
  nr err code: 12203
  TNS-12203: TNS: unable to connect to destination
  ns main err code: 12541
  TNS-12541: TNS: no listener
  ns secondary err code: 12560
  nt main err code: 511
  TNS-00511: No listener
  nt secondary err code: 79
  nt OS err code: 0

Key Error Codes:

1. TNS-12203: TNS: unable to connect to destination
   • This is a network connection error indicating that Oracle could not connect to the specified database destination.
2. TNS-12541: TNS: no listener
   • This indicates that the Oracle listener is not running or is unreachable. Without the listener, Oracle cannot route client requests to the correct database.
3. TNS-00511: No listener
   • A more detailed network transport error indicating that the listener process is either down or incorrectly configured.

Interpretation:

• The error suggests that the Oracle client is trying to connect to a service using a listener on host `apbd09` and port 1527, but the listener is not running.
• To resolve this, you would typically check if the Oracle Listener is running on the target machine and ensure that the listener.ora file is correctly configured.

We will investigate the log files in detail in a later module.

The Fatal OSN connect error 12203 occurs in Oracle Network Services when an Oracle client is unable to establish a connection to the Oracle server. This error is typically related to issues in the Oracle Net Services layer, particularly with the Oracle SQL*Net (OSN) protocol, which handles network communication between Oracle clients and servers.
Specific Causes of OSN Connect Error 12203: For the following list of 6 numbered elements, put the numbered elements in a HTML ordered list and the child elements in an unordered list.

1. Listener Not Running:
   • The Oracle Listener (which listens for client connection requests) may not be running on the server.
   • The client may be attempting to connect to the wrong listener.
2. Incorrect or Missing `listener.ora` or `tnsnames.ora` Configuration:
   • The `tnsnames.ora` file on the client might have incorrect or incomplete connection details, such as the wrong host, port, or service name.
   • The `listener.ora` file on the server could be misconfigured, preventing the listener from properly routing client connection requests.


3. Port or Firewall Issues:
   • The connection port (commonly 1521 for Oracle) might be blocked by a firewall or misconfigured.
   • The Oracle client could be trying to connect to a wrong or closed port.
4. Network Issues:
   • Network problems like DNS resolution failures, incorrect network settings, or timeouts can prevent successful connections.
   • The host specified in the connection string may be unreachable or misconfigured.
5. Version Mismatch:
   • A mismatch between Oracle client and server versions can cause communication errors.
6. Incorrect Service Name or SID:
   • The service name or SID provided in the connection string may not match the configuration on the Oracle server.

Common Troubleshooting Steps:

1. Check Listener Status:
   • Use the lsnrctl status command to check if the Oracle listener is running and configured correctly on the server.

       lsnrctl status
       

2. Verify tnsnames.ora Configuration:
   • Ensure the client’s tnsnames.ora file contains the correct connection details for the host, port, and service name or SID.

       # Example entry in tnsnames.ora
       ORCL =
         (DESCRIPTION =
           (ADDRESS = (PROTOCOL = TCP)(HOST = your_server_host)(PORT = 1521))
           (CONNECT_DATA =
             (SERVICE_NAME = your_service_name)
           )
         )
       

3. Verify Network Connectivity:
   • Test network connectivity between the client and server using ping or telnet to ensure the server is reachable and the correct port is open.

       ping your_server_host
       telnet your_server_host 1521
       

4. Check for Port Conflicts:
   • Ensure that the specified port (e.g., 1521 or 1527) is open and not in use by another process.
5. Review Logs:
   • Check the Oracle server’s alert.log and listener log for any errors or issues related to network connections.

Example Error Message: You might see a message like the following when encountering OSN connect error 12203:

ORA-12203: TNS:unable to connect to destination

This error indicates that the Oracle client is unable to complete the network handshake required to connect to the Oracle server, often due to the reasons described above. By resolving the underlying cause, such as starting the listener, correcting configuration files, or fixing network issues, the Fatal OSN connect error 12203 can typically be resolved.

Oracle Net (which is Oracle's network interface), was formerly known as Net8 when used in Oracle8, and SQL*Net when used with Oracle7 and previous versions of Oracle. You can use Oracle Net over a wide variety of network protocols, although TCP/IP is by far the most common protocol today.
The sqlnet.ora file on a client machine contains parameters that govern the behavior of the client. The attributes that can be modified fall into five categories:

1. Dead connection detection
2. Tracing and logging
3. Default domains
4. Oracle Names parameters
5. Other optional parameters

Note: The expire_time parameter only polls from the server to the client. Hence, when a connection goes down, the server process will terminate, but the client screen may terminate with any number of strange messages.

In the illustration above, we see the expire_time=10, meaning that Net will send a "ping" to the client once every 10 minutes. If there is no response, the Oracle process is terminated and the session to Oracle is closed. There are at least two circumstances under which this may occur:

1. The network connection to the server is lost.
2. The UNIX connection to Oracle is lost. (This is called a "zombie" process.)

Now that we haved covered the basics of the parameter files, let us look at how processes are established between Oracle clients and servers.

Before moving on to the next lesson, click the Exercise link below to practice working with sqlnet.ora and tnsnames.ora files.
Master Tnsnames.ora File - Exercise