Oracle Database Access with Python

Oracle Net Services

Provides enterprise wide connectivity solutions in distributed, heterogeneous computing environments.

Oracle Net

Oracle Net, a component of Oracle Net Services, enables a network session from a client application to an Oracle Database server. Once a network session is established, Oracle Net acts as the data courier for both the client application and the database server.

Database services:

An Oracle database is represented to clients as a service, which means that the database performs work on behalf of clients. A database can have one or more services associated with it.

Database Instances

A database has at least one instance. An instance is comprised of a memory area called the System Global Area (SGA) and Oracle background processes. The memory and processes of an instance efficiently manage the associated database's data and serve the database users.

Service Accessibility

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. The following example shows a connect descriptor that enables clients to connect to a database service called sales.us.example.com.

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

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 hands these requests to the database server. After the connection is established, the client and database server communicate directly.

Connect Data

The connect descriptor also specifies the database service name with which clients seek to establish a connection. The listener knows which services for which it 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. Service handlers act as connection points to an Oracle database server. A service handler can be a dispatcher or a dedicated server.

Instance Name

If connecting to a specific instance of the database is required, clients can also specify the INSTANCE_NAME of a particular instance in the connect descriptor. This feature can be useful if you have an Oracle Real Application Clusters configuration. Forexample, the following connect descriptor specifies an instance name of sales1 that is associated with sales.us.example.com.

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

Service Handlers

Alternatively, clients that always want to use a particular service handler type can use a connect descriptor that specifies the service handler type. In the following example, a connect descriptor is configured to use a dispatcher for a shared server configuration, as indicated by (SERVER=shared).

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

If you want the client to use a dedicated server, you can specify (SERVER=dedicated) in place of (SERVER=shared). If the SERVER parameter is not set, shared server configuration is assumed. However, the client will use a dedicated server if no dispatchers are available.

If database resident connection pooling is enabled on the server, then you can specify (SERVER=pooled) to get a connection from the pool. If database resident connection pooling is not enabled on the server, then the client request is rejected.

When the listener receives the client request, it selects one of the service handlers that were previously registered. 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:

1. Hands the connect request directly off to a dispatcher. (HANDOFF)

2. 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. (REDIRECT)

3. Spawns a dedicated server process and passes the client connection to the dedicated server process. (BEQUEATH)

Enhanced Service Accessibility with Multiple Listeners

For some configurations, such as Oracle Real Application Clusters, multiple listeners on multiple nodes can be configured to handle client connection requests for the same database service.

In the following example, sales.us.example.com can connect to sales.us.example.com using listeners on either sales1-server or sales2-server.

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

A multiple-listener configuration also enables you to leverage the following failover and load balancing features, either singly or in combination with each other:

1. Connect-Time Failover

2. Transparent Application Failover

3. Client Load Balancing

4. Connection Load Balancing

Connect-Time Failover

The connect-time failover 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.

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.

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.

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 also has the capability to alance 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, 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.

Listener

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.

Service registration

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:

1. Names of the database services provided by the database

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

3. Service handlers (dispatchers and dedicated servers) available for the instance, including their type, protocol addresses, and current and maximum load

This information enables the listener to direct a client's request appropriately. If the listener is not running when an instance starts, PMON is not able to register the service information. PMON attempts to connect to the listener periodically. However, it may take up to 60 seconds before PMON registers with the listener after it has been started. To initiate service registration immediately after the listener is started, use the SQL statement ALTER SYSTEM REGISTER. This is especially useful in high-availability configurations.

Service Handlers

This section includes the following topics:

1. Dispatchers

2. Dedicated Server Processes

3. Database Resident Connection Pooling

Dedicated Server Processes

PMON registers information about dedicated server processes with the listener. This enables the listener to start up a dedicated server process when a client request arrives and forward the request to it.

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 establishment, one of the following actions occurs:

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

2. 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.

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

Note: One of the options is selected based on the operating system and the transport protocol.

Note: In order for remote clients to connect to dedicated servers, the listener and the database instance must be running on the same computer.

Disadvantages:

1. Increases memory requirement: Each time a connection is received, one new dedicated server process has to be started.

2. Increases processing delay.

3. Authentication is required for each connection.

Shared Server Processes

Shared server processes are used in the shared server architecture. With shared server architectures, client processes ultimately connect to a dispatcher. The PMON process registers the location and load of the dispatchers with the listener, enabling the listener to forward requests to the least loaded dispatcher.

A dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit. A virtual circuit is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives. An idle shared server picks up the virtual circuit from the common queue, services the request, and relinquishes the virtual circuit before attempting to retrieve another virtual circuit from the common queue. This approach enables a small pool of server processes to serve a large number of clients.

Advantages:

1. The oracle shared server architecture increases the scalability of applications.

2. Increases the number of clients that can be simultaneously connected to the database.

3. Reduces the processing delay: Each time new connection request is received, no server is needed to be started.

4. Reduces memory requirement: Shared server is Idle in configurations with a large number of connections bacause it reduces the server memory requirements.

Disadvantages:

1. Authentication is required for each connection

Database Resident Connection Pooling

Applications may be deployed in the following ways:

1. As multiple processes

2. On multiple hosts

3. As multiple processes on multiple hosts

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 to 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.

Advantage:

1. Authentication is required only once for each session.

Oracle Connection Manager Architecture

Oracle Connection Manager is a software component that resides on its own computer, separate from a client or an oracle database server.

Oracle Connection Manager is a router through which a client connection request is sent either to its next hop or directly to the database server. Clients who route connection requests through an Oracle Connection Manager can take advantage of the session multiplexing and access control features configured on that Oracle Connection Manager.

Oracle Connection Manager consists of three components:

1. Listener

2. CMGW (Connection Manager gateway)

3. CMADMIN (Connection Manager Administration)

Listener:

The listener receives client connections and evaluates against a set of rules whether to deny or allow access. If it allows access, the listener forwards a request to a gateway process, selecting the one with the fewest connections.

CMGW:

The CMGW process, in turn, forwards the request to another Oracle Connection Manager or directly to the database server, relaying data until the connection terminates.

If a connection to the server already exists, the gateway multiplexes, or funnels, its connections through the existing connection.

CMADMIN:

Monitors the health of the gateway processes and the listener, shutting down or starting up processes as needed. It registers the location and load of the gateway processes with the listener, and it answers requests from the Oracle Connection Manager Control utility.

Access control filter

It proxies and screens requests for the database server.

Multiplexer

It multiplexes database sessions. In its session multiplexing role, Oracle Connection Manager funnels multiple sessions through a single transport protocol connection to a particular destination.

In this way, Oracle Connection Manager reduces the demand on resources needed to maintain multiple sessions between two processes by enabling the Oracle Database server to use fewer connection end points for incoming requests. As an access control filter, Oracle Connection Manager controls access to Oracle databases.

Connection Pooling

When thousands of clients are running interactive Web applications, many of these sessions may be idle at a given time. The connection pooling feature enables the database server to timeout an idle session and use the connection to service an active session. The idle logical session remains open, and the physical connection is automatically reestablished when the next request comes from that session.

Session Multiplexing

Session Multiplexing Oracle Connection Manager, an Oracle Net Services component, enables multiple client network sessions to be multiplexed, or funneled, through a single network connection to a database.

The session multiplexing feature reduces the demand on resources needed to maintain multiple network sessions between two processes by enabling the server to use fewer network connection endpoints for incoming requests. In this way the total number of network sessions that a server can handle is increased.

Two-Task Common(TTC)

Character set differences can occur if the client and database server are running on different operating systems. The presentation layer resolves any differences. It is optimized for each connection to perform conversion only when required.

The presentation layer used by client/server applications is Two-Task Common (TTC). TTC provides character set and data type conversion between different character sets or formats on the client and database server.

At the time of initial connection, TTC is responsible for evaluating differences in internal data and character set representations and determining whether conversions are required for the two computers to communicate.

Default Configuration for External Procedures

An external procedure is a procedure called from another program, but written in a different language. An example would be a PL/SQL program calling one or more C routines that are required to perform special-purpose processing.

When an application calls an external procedure, Oracle Database starts an external procedure agent named extproc. Using the network connection established by Oracle Database, the application passes the following information to the agent:

SLL: library object file is updated and executable should be rebuilt.

DLL: executable is not needed to be built

1. DLL or shared library name

2. External procedure name

3. Any parameters

The agent then loads the DLL or the shared library, and runs the external procedure and passes back to the application any values returned by the external procedure. The agent must reside on the same computer as the application making the external procedure call.

When you use the default configuration for external procedures, the extproc agent is spawned directly by Oracle Database. There are no configuration changes required for either listener.ora or tnsnames.ora.

When the default configuration for external procedures is used, define the environment variables to be used by external procedures in the extproc.ora file located in the $ORACLE_HOME/hs/admin directory on UNIX operating systems or the %ORACLE_HOME%\hs\admin directory on Windows.

Note: On Windows, the executable must reside in the ORACLE_HOME\bin directory.

Environment Variables: ENVS When the default configuration for external procedures is used, define the environment variables to be used by external procedures in the extproc.ora file located in the $ORACLE_HOME/hs/admin directory on UNIX operating sytems or the %ORACLE_HOME%\hs\admin directory on Windows.

Note: When extproc.ora is in use, it precedes the same environment variables of ENVS in listener.ora.

Syntax: SET name=value

Example: SET EXTPROC_DLLS=ANY

Specify the EXTPROC_DLLS environment variable to restrict the DLLs that extproc is allowed to load. Without the EXTPROC_DLLS environment variable, extproc loads DLLs from $ORACLE_HOME/lib on UNIX operating systems an

1. Configure and run a separate or existing listener to serve external procedures. Oracle Net Configuration Assistant configures a listener to accept connections for both the database and external procedures during a database server installation. In addition, Oracle Net Configuration Assistant configures a net service name for the external procedures in tnsnames.ora file on the database server. The external procedure agent will only be able to load DLLS from the bin or lib directories in the ORACLE_HOME.

Example 13–1 shows a sample configuration in the listener.ora file.

Example 13–1 listener.ora File with a Sample External Procedure Setup

LISTENER= (DESCRIPTION=(ADDRESS_LIST=(ADDRESS= (PROTOCOL=tcp)(HOST=sale-server)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER= (SID_LIST=(SID_DESC=(GLOBAL_DBNAME=sales.us.example.com)(ORACLE_HOME=/oracle)(SID_NAME=sales)) (SID_DESC=(SID_NAME=plsextproc)(ORACLE_HOME=/oracle)

(PROGRAM=extproc)))

Example 13–2 shows a sample configuration in the tnsnames.ora file.

Example 13–2 tnsnames.ora File a Sample External Procedure Setup

EXTPROC_CONNECTION_DATA= (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))(CONNECT_DATA=(SID=plsextproc)))

The extproc agent spawned by the listener inherits the operating system privileges of the listener. Therefore, if you configure a separate listener, run with operating system privileges lower than those of the listener for the database.

Configuring Transparent Application Failover

TAF instructs Oracle Net to fail over a failed connection to a different listener. This enables the user to continue to work using the new connection as if the original connection had never failed.

TAF involves manual configuration of a net service name that includes the FAILOVER_MODE parameter included in the CONNECT_DATA section of the connect descriptor.

This sections covers the following topics:

• About TAF
• What TAF Restores
• TAF Database Configurations
• FAILOVER_MODE Parameters
• TAF Implementation
• TAF Verification

*About TAF*

Transparent Application Failover (TAF) is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance.

Notifications are used by the server to trigger TAF callbacks on the client-side.

TAF is configured using either client-side specified TNS connect string or using server-side service attributes. However, if both methods are used to configure TAF, the server-side service attributes will supersede the client-side settings. The server-side service attributes are the preferred way to set up TAF.

TAF can operate in one of two modes, Session Failover and Select Failover. Session Failover will re-create lost connections and sessions. Select Failover will replay queries that were in progress.

When there is a failure, callback functions will be initiated on the client-side by means of OCI callbacks. This will work with standard OCI connections as well as Connection Pool and Session Pool connections. Please see the OCI manual for more details on callbacks, Connection Pools, and Session Pools.

TAF will work with RAC. For more details and recommended configurations, please see the RAC Administration Guide.

TAF will operate with Physical Data Guard to provide automatic failover.

(CONNECT_DATA= (SERVICE_NAME=sales.us.example.com)(FAILOVER_MODE=(TYPE=select)(METHOD=basic))))

Naming Method Configuration Overview

To connect to a service, clients use a connect identifier in the connect string to connect to a service. The connect identifier can be a connect descriptor or a simple name that maps to a connect descriptor. The connect descriptor contains:

1. Network route to the service, including the location of the listener through a protocol address

2. Oracle8i or later release database service name or Oracle8 database Oracle

System Identifier (SID)

A simple name is resolved to a connect descriptor by a naming method. A naming method configuration consists of the following steps:

1. Select a naming method.

2. Map connect descriptors to simple names.

3. Configure clients to use the naming method.

A connect descriptor is comprised of one or more protocol addresses of the listener and the connect information for the destination service.

The following example shows a connect descriptor mapped to simple name called sales:

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

Easy Connect Naming:

Easy Connect Naming Enables clients to connect to a database server without any configuration. Clients use a connect string for a simple TCP/IP address, which includes a host name and optional port and service name:

CONNECT [[mailto:username./password@[//]host[:port][/service_name][:server][/instance_name][username./password@[//]host[:port][/service_name][:server][/instance_name]]]

Configuring the Local Naming Method

There will be a machine to hold the name resolution information.

The local naming method adds net service names to the tnsnames.ora file. Each net service name maps to a connect descriptor. The following example shows a net service name mapped to a connect descriptor:

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

In this example, the net service name sales is mapped to the connect descriptor contained in DESCRIPTION. DESCRIPTION contains the protocol address and identifies the destination database service.

Configuring the Directory Naming Method (ldap.ora)

With the directory naming method, connect identifiers are mapped to connect descriptors contained in an LDAP-compliant directory server, including Oracle Internet Directory and Microsoft Active Directory. A directory provides central administration of database services and net service names, making it easier to add or relocate services.

A database service entry is created with Database Configuration Assistant during installation; net service name and net service alias entries can be created with Oracle Enterprise Manager or Oracle Net Manager. To modify Oracle Net attributes of a database service entry and the net service name entries, use Oracle Enterprise Manager or Oracle Net Manager.

 

Skip to end of metadata

Go to start of metadata

 

Dispatcher configuration