Tuesday, April 12, 2011

Oracle Connection Process

To connect to a database service, clients use a connect descriptor that provides the location of the database and the name of the database service. Connect descriptor is a specially formatted description of the destination for a network connection and is usually described in tnsnames.ora.

Assume you have the following connect descriptor in your tnsnames.ora:
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
Now, when you issue a connect, say like this:

$> sqlplus scott/tiger@orcl

tns will look in your tnsnames.ora for an entry called 'orcl'. Finding it, tns sends a request through the normal network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = orcl).

What happens behind the scene?

The database server receives an initial connection from a client application through the listener. The listener brokers client requests, handing off the requests to the Oracle database server. Every time a client requests a network session with a database server, a listener receives the initial request.

Each listener is configured with one or more protocol addresses that specify its listening endpoints. Clients configured with one of these protocol addresses can send connection requests to the listener.

Once a client request has reached the listener, the listener selects an appropriate service handler to service the client's request and forwards the client's request to it. The listener determines if a database service and its service handlers are available through service registration. During service registration, the PMON process—an instance background process—provides the listener with information about the following:
  • Names of the database services provided by the database

  • Name of the instance associated with the services and its current and maximum load

  • Service handlers (dispatchers and dedicated servers) available for the instance, including their type, protocol addresses, and current and maximum load
Depending on the type of handler selected, the communication protocol used, and the operating system of the database server, the listener performs one of the following actions:
  • Hands the connect request directly off to a dispatcher.
  • Sends a redirect message back to the client with the location of the dispatcher or dedicated server process. The client then connects directly to the dispatcher or dedicated server process.
  • Spawns a dedicated server process and passes the client connection to the dedicated server process.
Once the listener has completed the connection operation for the client, the client communicates with the Oracle database server without the listener's involvement. The listener resumes listening for incoming network sessions.

No comments: