2 Identifying and Accessing the Database

About Connect Descriptors

A connect descriptor is comprised of one or more protocol addresses of the listener and the connect information for the destination service in the tnsnames.ora file. Example 2-1 shows a connect descriptor mapped to the sales database.

sales=
 (DESCRIPTION= 
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) 
  (CONNECT_DATA= 
     (SID=sales)
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales)))

As shown in Example 2-1, the connect descriptor contains the following parameters:

• The ADDRESS section contains the following:

  • PROTOCOL parameter, which identifies the listener protocol address. The protocol is tcp for TCP/IP.

  • HOST parameter, which identifies the host name. The host is sales-server.

  • PORT parameter, which identifies the port. The port is 1521, the default port number.

• The CONNECT_DATA section contains the following:

  • SID parameter, which identifies the SID of the Oracle database. The SID is sales.

  • SERVICE_NAME parameter, which identifies the service. The destination service name is a database service named sales.us.example.com.

    The value for this connect descriptor parameter comes from the SERVICE_NAMES initialization parameter (SERVICE_NAMES uses a final S) in the initialization parameter file. The SERVICE_NAMES initialization parameter is typically the global database name, which includes the database name and domain name. In the example, sales.us.example.com has a database name of sales and a domain of us.example.com.

  • INSTANCE_NAME parameter, which identifies the database instance. The instance name is optional.

    The INSTANCE_NAME parameter in the initialization parameter file defaults to the SID entered during installation or database creation.

About the Protocol Address

The address portion of the connect descriptor is the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server. The listener receives incoming client connection requests and sends these requests to the database server. After the connection is established, the client and database server communicate directly.

The listener is configured to accept requests from clients at a protocol address. This address defines the protocol the listener is listening on and any other protocol-specific information. For example, the listener could be configured to listen at the following protocol address:

(DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))

The preceding example shows a TCP/IP protocol address that specifies the host of the listener and a port number. Client connect descriptors configured with this same protocol address can send connection requests to this listener.

About Service Registration

The connect descriptor specifies the database service name with which clients seek to establish a connection. The listener knows which services can handle connection requests because an Oracle database dynamically registers this information with the listener. This process of registration is called service registration. Registration also provides the listener with information about the database instances and the service handlers available for each instance. A service handler can be a dispatcher or dedicated server.

(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)
    (INSTANCE_NAME=sales1)))

(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)
     (SERVER=shared)))

About Dispatchers

The shared server architecture uses a dispatcher process to direct client connections to a common request queue. An idle shared server process from a shared pool of server processes picks up a request from the common queue. This approach enables a small pool of server processes to serve a large number of clients. A significant advantage of the shared server model over the dedicated server model is reduced system resources, enabling support of an increased number of users.

The listener uses the dispatcher as a type of service handler to which it can direct client requests. When a client request arrives, the listener performs one of the following actions:

• Hands the connection request directly to a dispatcher.

• Issues a redirect message to the client, containing the protocol address of a dispatcher. The client then terminates the network session to the listener and establishes a network session to the dispatcher, using the network address provided in the redirect message.

The listener uses direct hand off whenever possible. Redirect messages are used, for example, when dispatchers are remote to the listener.

Figure 2-5 shows the listener handing a connection request directly to a dispatcher.

1. The listener receives a client connection request.

2. The listener hands the connect request directly to the dispatcher.

3. The client is now connected to the dispatcher.

Figure 2-5 Direct Hand-Off to a Dispatcher

Description of "Figure 2-5 Direct Hand-Off to a Dispatcher"

Figure 2-6 shows the role of a dispatcher in a redirected connection.

1. The listener receives a client connection request.

2. The listener provides the location of the dispatcher to the client in a redirect message.

3. The client connects directly to the dispatcher.

Figure 2-6 Redirected Connection to a Dispatcher

Description of "Figure 2-6 Redirected Connection to a Dispatcher"

About Dedicated Server Processes

In a dedicated server configuration, the listener starts a separate dedicated server process for each incoming client connection request dedicated to servicing the client. After the session is complete, the dedicated server process terminates. Because a dedicated server process has to be started for each connection, this configuration may require more system resources than shared server configurations.

A dedicated server process is a type of service handler that the listener starts when it receives a client request. To complete a client/server connection, one of the following actions occurs:

• The dedicated server inherits the connection request from the listener.

• The dedicated server informs the listener of its listening protocol address. The listener passes the protocol address to the client in a redirect message and terminates the connection. The client connects to the dedicated server directly using the protocol address.

One of the preceding actions is selected based on the operating system and the transport protocol.

If the client and database exist on the same computer, then a client connection can be passed directly to a dedicated server process without going through the listener. This is known as a bequeath protocol. The application initiating the session spawns a dedicated server process for the connection request. This happens automatically if the application used to start the database is on the same computer as the database.

Figure 2-7 shows the listener passing a client connection request to a dedicated server process.

1. The listener receives a client connection request.

2. The listener starts a dedicated server process, and the dedicated server inherits the connection request from the listener.

3. The client is now connected directly to the dedicated server.

Figure 2-7 Connection to a Dedicated Server Process

Description of "Figure 2-7 Connection to a Dedicated Server Process"

Figure 2-8 shows the role of a dedicated server in a redirected connection.

1. The listener receives a client connection request.

2. The listener starts a dedicated server process.

3. The listener provides the location of the dedicated server process to the client in a redirect message.

4. The client connects directly to the dedicated server.

Figure 2-8 Redirected Connection to a Dedicated Server Process

Description of "Figure 2-8 Redirected Connection to a Dedicated Server Process"

About Database Resident Connection Pooling

Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios in which an application acquires a database connection, works on it for a relatively short duration, and then releases it. Database resident connection pooling pools "dedicated" servers. A pooled server is the equivalent of a server foreground process and a database session combined.

Database resident connection pooling complements middle-tier connection pools that share connections between threads in a middle-tier process. In addition, it enables sharing of database connections across middle-tier processes on the same middle-tier host and even across middle-tier hosts. This results in significant reduction in key database resources needed to support a large number of client connections, thereby reducing the database tier memory footprint and boosting the scalability of both middle-tier and database tiers. Having a pool of readily available servers has the additional benefit of reducing the cost of creating and closing client connections.

Database resident connection pooling provides pooling for dedicated connections across client applications and processes. This feature is useful for applications that must maintain persistent connections to the database and optimize server resources (such as memory).

Clients obtaining connections out of the database resident connection pool are persistently connected to a background process, the connection broker, instead of the dedicated servers. The connection broker implements the pool functionality and performs the multiplexing of inbound connections from the clients to a pool of dedicated servers with sessions.

When a client must perform database work, the connection broker picks up a dedicated server from the pool and assigns it to the client. Subsequently, the client is directly connected to the dedicated server until the request is served. After the server finishes processing the client request, the server goes back into the pool and the connection from the client is restored to the connection broker.

Figure 2-9 shows the process.

Figure 2-9 Dedicated Server Processes Handling Connections Through the Connection Broker Process

Description of "Figure 2-9 Dedicated Server Processes Handling Connections Through the Connection Broker Process"

Choosing a Naming Method

Selecting the appropriate naming method for mapping names to connect descriptors depends upon the size of the organization.

• For a small organization with only a few databases, use Easy Connect naming to make TCP/IP connections with the host name of the database server or local naming to store names in tnsnames.ora file on the clients.

• For large organizations with several databases, use directory naming to store names in a centralized directory server.

• For an Internet network, configure the application Web servers needed to connect to the databases with the local naming method.

Table 2-1 summarizes the relative advantages and disadvantages of each naming method and provides recommendations for using them in the network.

A naming method configuration consists of the following steps:

1. Select a naming method.

2. Map connect descriptors to the names.

3. Configure clients to use the naming method.

Establishing a Client Session using a Naming Method

A typical process for establishing a client session using a naming method is as follows:

1. The client initiates a connect request by providing a connect identifier.

2. The connect identifier is resolved to a connect descriptor by a naming method.

3. The client makes the connection request to the address provided in the connect descriptor.

4. A listener receives the request and directs it to the appropriate database server.

5. The connection is accepted by the database server.

Entering a Connect String

After the network components are started you should be able to make a connection across the network. How you make a connection depends upon the naming method, and the tool used for the connection.

The connect string takes the following format:

CONNECT username@connect_identifier

On most operating systems, you can define a default connect identifier. When using the default, a connect identifier does not need to be specified in the connect string. To define a default connect identifier, use the TWO_TASK environment variable on Linux and UNIX platforms or the LOCAL environment variable or registry entry on Microsoft Windows.

For example, if the TWO_TASK environment variable is set to sales, then you can connect to a database from SQL*Plus with CONNECT username rather than CONNECT username@sales. Oracle Net checks the TWO_TASK variable, and uses the value sales as the connect identifier.

CONNECT scott@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))'
Enter password: password

CONNECT scott@'cn=sales, cn=OracleContext, dc=us, dc=example, dc=com'
Enter password: password

CONNECT scott@'sales@"good"example.com'
Enter password: password

CONNECT scott@"cn=sales, cn=OracleContext, ou=Mary's Dept, o=example"
Enter password: password

About Connect-time Failover

The connect-time failover feature enables clients to connect to another listener if the initial connection to the first listener fails. The number of listener protocol addresses determines how many listeners are tried. Without connect-time failover, Oracle Net attempts a connection with only one listener.

About Transparent Application Failover

The Transparent Application Failover (TAF) feature is a run-time failover for high availability environments, such as Oracle Real Application Clusters. TAF fails over and reestablishes application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails and, optionally, resume a SELECT statement that was in progress. The reconnection happens automatically from within the Oracle Call Interface (OCI) library.

About Client Load Balancing

The client load balancing feature enables clients to randomize connection requests among the listeners. Oracle Net progresses through the list of protocol addresses in a random sequence, balancing the load on the various listeners. Without client load balancing, Oracle Net progresses through the list of protocol addresses sequentially until one succeeds.

About Connection Load Balancing

The connection load balancing feature improves connection performance by balancing the number of active connections among multiple dispatchers. In a single-instance environment, the listener selects the least-loaded dispatcher to handle the incoming client requests. In an Oracle Real Application Clusters environment, connection load balancing can balance the number of active connections among multiple instances.

Due to dynamic service registration, a listener is always aware of all instances and dispatchers regardless of their location. Depending on the load information, a listener decides which instance and, if shared server is configured, to which dispatcher to send the incoming client request.

In a shared server configuration, a listener selects a dispatcher in the following order:

1. Least-loaded node

2. Least-loaded instance

3. Least-loaded dispatcher for that instance

In a dedicated server configuration, a listener selects an instance in the following order:

1. Least-loaded node

2. Least-loaded instance

If a database service has multiple instances on multiple nodes, then the listener chooses the least-loaded instance on the least-loaded node. If shared server is configured, then the least-loaded dispatcher of the selected instance is chosen.