Network Topology   «Prev  Next»

Lesson 3 SQL*Net and Net components
Objective Define the components of Oracle network tools.

Oracle Net Services and Components

As we discussed earlier, Oracle Net Services require different parameter files depending upon whether the computer is defined as a client, a server, or both.
  1. A Net client requires a tnsnames.ora file for outgoing connections.
  2. A Net server requires a listener.ora file that listens for incoming connections.
  3. In addition, sometimes a sqlnet.ora file and a protocol.ora file are created for the Net environment.
However, creating the files and establishing initial connectivity is only the beginning. Once you have created the Net infrastructure, you must create a mechanism to reference the remote database from within SQL statements. This is done by creating database links that will store three items:
  1. The TNS service name
  2. The remote USER ID
  3. The remote password
Let us take a look at how the pieces fit together.
  1. Suppose you issue the query select * from customer@raleigh. Oracle begins by looking up "raleigh" in the database link table to get the TNS service name (RAL in this example). The database link would have been previously created using the following syntax:


Question: What is the purpose of the 1) sqlnet.ora and 2) protocol.ora files in the Oracle Netwrk Services environment?
  1. The sqlnet.ora File: The sqlnet.ora file is a significant configuration file in the Oracle Network Services environment, playing a crucial role in the functionality and performance of the Oracle network stack. It functions as a client-side and server-side configuration file, providing Oracle Net Services with crucial configuration details. The parameters within this file can control aspects such as client-server connection settings, encryption, tracing, naming methods, and other network settings.
    Key attributes defined within the sqlnet.ora file include:
    1. SQLNET.AUTHENTICATION_SERVICES: Specifies the authentication services to use for network connections.
    2. NAMES.DIRECTORY_PATH: Dictates the Oracle naming methods the client should use when resolving a net service name to a connect descriptor.
    3. SQLNET.ENCRYPTION_SERVER: Enforces the server to encrypt data.
    4. SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT: Specifies the data integrity algorithms that a client can use when connecting to a server.

    Changes to the sqlnet.ora file can have substantial impacts on the performance, security, and stability of both Oracle databases and clients. Therefore, modifications to this file should only be done with a full understanding of the parameters being adjusted.
  2. The protocol.ora File: The protocol.ora file, on the other hand, is not as widely used as the sqlnet.ora file. It is a configuration file used for specifying protocol-specific parameters for Oracle Net Services, acting at a lower level of the Oracle network stack than the sqlnet.ora file.
    The protocol.ora file allows the modification of low-level network parameters that can have significant impacts on the performance and reliability of network connections in specific circumstances. Parameters within this file can control elements such as the default protocol to use for connections, send and receive buffer sizes, or the TCP/IP version to use.
    Although the protocol.ora file can provide more granular control over network parameters, it is important to note that incorrect settings can negatively impact the stability and performance of Oracle network connections. Therefore, similar to the sqlnet.ora file, modifications to the protocol.ora file should only be made by individuals who understand the implications of the changes being made.


If the global_names parameter has been set to TRUE in the initsid.ora file, the name entered in a database link must be the global database name.
  1. Next, Oracle looks up RAL in the tnsnames.ora file to get the host name for the remote computer. At this point it may also need to go to the "hosts" file to look up the IP address for the host name.
  2. Once it has the IP address, Oracle gets the protocol from tnsnames.ora and issues the request to the remote server.
  3. The listener on the remote server intercepts the request and creates a process on the server. This process connects to Oracle using the User ID and password from the database link.
  4. Only at this point are the customer rows fetched from the remote server and passed to the requesting client.
Note that almost all of the processing work is done on the remote database. For example, if we had requested the customer rows to be sorted (that is, select * from customer@raleigh order by customer_name;), the sorting would have taken place on the remote server before the rows were passed to the client. It is important to understand this concept, since remote requests may make heavy use of the TEMP tablespace on the remote server for sorting purposes. Now that we have seen the overall flow, let us take a close look at the tnsnames.ora file.

Oracle Integration Cloud Service