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:

Oracle Integration Cloud Service
CREATE DATABASE LINK RALEIGH
  CONNECT TO RALEIGH_USER 
  IDENTIFIED BY SECRET_PASSWORD 
  USING `RAL'

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.