Oracle Net Services

Oracle Net Services In today's internet world networking is essential where and data and information exchange is becoming commonplace. The internet has opened new and varied ways of doing business. To be able to successfully run an e-commerce website, the website should be scalable and available 24x7. We are looking at a scenario where clients can and must be able to access applications and database servers across the globe from any location with maximum security and availability. To match the growing internet market and create complete and reliable applications that can be accessed from anywhere, Oracle offered a comprehensive suite of networking services. Oracle Net Services is Oracle solution to Networking issues. Oracle Net services provide enterprise wide connectivity solutions in distributed heterogeneous computing environments. Using Oracle Net Services, Oracle tries to bring to the table all that is required for providing a complete networking solution. The main functions provided by Oracle Net Services include: Connectivity Manageability Internet and Intranet Scalability Network Security

Lesson 1: Client server concepts and networking components One of the main features of the Oracle Net Services is the solutions provided to connectivity issues. Oracle Net, is a component of Oracle Net Services that enables a network session from a client application to an Oracle database server. Once a network session is established, Oracle net acts as a data courier for both the client application and the database server. Messages between the client application and the database server are performed using Oracle Net. Client/Server Applications In a typical client-server environment, there are two main components, namely the client and the server. The client hosts the application and is responsible for data validation, presentation and making data requests to the database. The server is another machine, which hosts the Oracle database. Oracle Net enables connections from traditional client/server applications to Oracle database servers. For client and database server connectivity Oracle Net is a software component that is located on each of these components. Many books refer to this component as the Oracle Net foundation layer. Oracle net is layered on top of a network Oracle protocol support that consists of how applications access the network and how data is subdivided into packets for transmission across the network. The TCP/IP protocol can be one such industry-standard protocol that enables computer-level connectivity. The Oracle Net foundation layer uses Oracle protocol support to communicate with the following industry-standard network protocols such as TCP/IP, TCP/IP with SSL, Named Pipes and SDP. Application Oracle Net

TCP/IP

RDBMS Oracle Net

Client Database Web Client Connections through an Application Server The client-server model is no longer popular and has been taken over by the n-tier architecture. We will specifically discuss the 3-tier architecture which consists of the thin client, an application server and the database server. This architecture permits client connections from web browsers to an Oracle database server. This architecture supports large enterprise-wide applications that maybe need to be accessed by thousands of users simultaneously. Rather than hosting the application on the client, the application server or the middle-tier is responsible for hosting and supporting applications. The client is a browser that communicates to a Web Server with the HTTP protocol to make he initial connection request. The web or application server sends the request to an application,

where it is processed. The application then uses Oracle net to communicate with an Oracle database server that is also configured with Oracle net.

TCP/IP Network HTTP Protocol Client Web Browser

Application

Application

Oracle Net

Oracle Net

Application Web Server

TCP/IP Network

Database Server

Database The three basic components have the following characteristics: • The Client – Uses the HTTP (Hypertext Transport Protocol) that provides the language that enables Web browsers and application Web servers to communicate. • The Application Web – An application web server manages applications for a website, controls access to the data and responds to requests from Web browsers. The application on the Web server communicates with the database and performs the job requested by the Web server. Manageability Issues Oracle Net Services offer a number of manageability features that enable you to configure and manage networking components. Some such features include location transparency, centralized configuration and management and scalability. Location Transparency – Consider a company that has three databases. Each database presents a specific service to a client application. The databases are used by sales, human resources and marketing applications. Each database is represented by one or more services. A service is identified by a service name, for e.g. sales.us.acme.com. The client uses this service name to identify the database that it needs to access. The information about the database service and its location in the network is transparent to the client. The information needed for a connection is stored in a repository.

1

Sales = Hr = Mktg =

Sales.us.acme.com 2 Hr.us.acme.com Client Mktg.us.acme.com

Centralized Configuration and Management Oracle Net allows you to effectively manage large networking environments where you would need to configure a distributed Oracle network. Database administrators can create a centralized repository in an LDAP-compliant directory server. This directory server can act as a centralized repository for all information on database network components, user and corporate policies, and user authentication and security. This kind of environment replaces the client-side and server-side localized configuration files. All computers on a heterogeneous network such as clients, application web servers and Oracle database servers can connect to a centralized directory server. Oracle Internet Directory Server Client

Database

Client Database Client

Application server

Internet and Intranet Scalability Oracle Net provides scalability features that enables you to maximize system resources and improve performance. One such feature that has been around for a while in Oracle is the Oracle Shared Server. This architecture increases the scalability of applications and the number of clients that can be simultaneously connected to the database. The shared server architecture is particularly useful in OLTP environments that are characterized by many short transactions occurring simultaneously. The shared server is discussed in length in the next chapter. Several other features that are enhanced with Oracle Net services and configurable through Shared server include connection pooling and session multiplexing. Connection pooling is a feature that can be configured in an Oracle Shared server environment. 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. Therefore, Web applications can allow large number of concurrent users to be accommodated with existing hardware. 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. This enables you to increase the total number of network sessions that a server can handle. This feature is possible using an Oracle Net Services component known as Oracle Connection Manager. In the figure displayed the Oracle Connection Manager is run on the same computer as an application Web server, the application Web server can route multiple client sessions through Oracle connection manager to ensure that those sessions have continuous access to an Oracle database server.

Oracle Connection Manager

Web Application Server Clients using web browser

Database

Network Security Data access and secure transfer of data are important considerations when deploying Oracle. Granting and denying access to a database is crucial for a secure network environment. Oracle Net Services enables database access control using firewall access control and protocol access control. Firewall Access Control – Is possible using the Oracle Connection Manager. Oracle Connection Manager can be configured to grant or deny client access to a particular database service on a computer. The database administrator can specify filtering rules that allow or restrict specific client access to a server, based on criteria such as source host name or IP address for clients, destination host names or IP addresses for servers, destination database service names. In the figure displayed, the first and seconds are permitted access to the database; however the third client is denied access. The connection manager acts as the firewall, preventing the client access to the database.

Database

Clients using web browser

Oracle Connection Manager

In the case of Protocol Access Control, the database server can be configured with access control parameters in the sqlnet.ora configuration file. These parameters specify whether clients are allowed or denied access based on the protocol.

Lesson : A suite of Networking Components The connectivity, manageability, scalability, and security features are provided by the following components: • Oracle Net • Oracle Net Listener • Oracle Connection Manager • Networking Tools • Oracle Advanced Security Oracle Net as we have already discussed consists of the software layer that maintains client and database connectivity. Oracle Net Listener The Oracle Net Listener commonly known as the listener is responsible for receiving the initial connection made by a client. It resides on the Oracle database server side and brokers client requests to the server. The listener is configured with a protocol address, the default being 1521. Clients that are configured with the same protocol address can send connection requests to the listener. Once a connection is established, the client and the database server communicate directly with one another.

Listener Client

Database

Database Server

Oracle Connection Manager We have already discussed this component of Oracle Net Services. To summarize, it is a software component that resides on its own computer, usually representing a middle-tier. It proxies and screens requests for the database server. In addition 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. This 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. Networking Tools

Oracle Net services provide a number of tools both GUI and command-line that can help you easily configure, manage, and monitor the network. •

Oracle Net Configuration Assistant – Is a Graphical User Interface tool that enables you to configure listeners and naming methods.

•

Oracle Enterprise Manager – is a web-based tool that allows administering and configuration functionality across multiple file system. It provides an integrated environment for configuring and managing Oracle Net Services.

•

Oracle Net Manager – is a tool that provides configuration functionality for an Oracle home on a local client or server host. It provides built-in wizards and utilities that enable you to test connectivity, migrate data from one naming method to another.

Oracle Advanced Security This is a separately licensable product. It provides a suites of security features ranging networking encryption, cryptographic check-summing, single sign-on services, and security protocols. Oracle Advanced Security integrates industry standards and delivers unparalleled security to the Oracle network.

Lesson 2: Server-side configuration As part of this discussion we will concern ourselves only with the Oracle Net Listener. The Oracle Net listener is the component that is configured on the Oracle server side. Its main function is to receive initial connection from client applications. It brokers a client request and hands it off to the server. On the database server, the listener is configured with a protocol address, information about the supported services and parameters that control its runtime behavior. The listener is a process running on a node that listens for incoming connections on behalf of a database or a number of databases. Once a connection has been established the client and the Oracle database server communicate directly with one another. The listener configuration is stored in a configuration file named listener.ora.

Listener process

The following are the characteristics of a listener: o A listener process can listen for more than one database o Multiple listeners can listen on behalf of a single database to achieve load balancing. o The listener can listen for multiple protocols o The default name of the listener is LISTENER. o The name of the listener must be unique per listener.ora file. A default listener is called LISTENER. It supports no services upon startup, and listens on the TCP/IP protocol address: (ADDRESS = (PROTOCOL=TCP) (HOST=host_name) (PORT=1521) The listener listens on a default port of 1521. If is possible to create additional listeners using the Oracle Net Manager tool. The listener knows the services for which it can handle connection requests. An Oracle database dynamically registers this information with the listener. The process of registration is called service registration. Service registration relies on the PMON process to register instance information with the listener. When an instance is started, initialization parameters about the listener are read from the initialization parameter file by which PMON registers information with the listener. If the listener is not up when the instance starts, PMON will not register information with the listener. PMON will continue attempting to contact the listener. The listener will reject any connections made to an unregistered service. It provides the listener with information about the database instances and 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. Static Service Registration is required in order for a listener to accept client requests from an Oracle 8 or earlier release database, the listener.ora file must be configured. The static configuration is required for the services such as external procedures and Hetergeneous Services. In dynamic service registration the listener.ora file does not require the SID_LIST_LISTENER_NAME parameter that specifies information on the databases server by the listener. With this method, connect-time failover is enabled and connection load balancing is enabled for shared servers. When the listener receives a 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: • 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 is managed by using a utility known as the Listener Control Utility. It is an operating system tool that can be invoked from an operating system command prompt. The executable file used to invoke it is called lsnrctl found in the $ORACLE_HOME/bin directory. Given below is the command used to invoke the Listener Control utility. After it has successfully started the utility displays the LSNRCTL prompt. From here you can issue the commands required to manage the different listeners on the machine. $lnsrctl LSNRCTL> command [listener_name] One of the listeners will be the default listener. A command that you issue without specifying the name of the listener will be act upon the default listener. STARTING AND STOPPING THE LISTENER The listener and database server must be running in order for the database server to receive connections. To start the listener from the command line, you would enter: The START command is used to start the listener. The argument for the START command is the name of the listener. If you do not specify an argument the current listener is started. A current listener can be started using the SET LISTENER command, and if one has not been set the listener called LISTENER will be started.

Starting the Listener on the UNIX platform: LSNRCTL> start [listener_name] or $lsnrctl start [listener_name] Starting the listener of Windows NT, 1. Select Services from the Start -> Settings -> Administration Tools menu 2. Locate the listener service, OracleOracle_homeTNSListener 3. Click Start to start the service Or To stop the listener using the MS-DOS prompt Enter PROMPT> lsnrctl LSNRCTL> STOP Stopping the listener on the UNIX platform: LSNRCTL> stop [listener_name] or Stopping the listener of Windows NT, 1. Select Services from the Start -> Settings -> Administration Tools menu 2. Locate the listener service, OracleOracle_homeTNSListener 3. Click Stop to stop the service. Or To stop the listener using the MS-DOS prompt Enter PROMPT> lsnrctl LSNRCTL> STOP The file that is used to configure the listener utility is the LISTENER.ORA file located in %ORACLE_HOME%\NETWORK\ADMIN folder on a Windows platform. Shown below is a sample listener.ora file. The LISTENER entry defines the listening protocol address for a listener named LISTENER, and the SID_LIST_LISTENER entry provides information about the services statically supported by the listener LISTENER. LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=blx-server)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=plsextproc) (ORACLE_HOME=/home/oracle10g)

(PROGRAM=extproc)))

Additional commands associated with the listener: CHANGE_PASSWORD: Dynamically changes the encrypted password of a listener. EXIT: Quits the LSNRCTL utility. HELP: Provides the list of all available LSNRCTL commands. QUIT: Provides the functionality of the EXIT command. RELOAD: Shuts down everything except listener addresses and re-reads the listener.ora file. You use this command to add or change services without actually stopping the listener. SAVE_CONFIG: Creates a backup of your listener configuration file (called listener.bak) and updates the listener.ora file itself to reflect any changes SERVICES: Provides detailed information about services and instances registered and the service handlers allocated to each instance. SET parameter: This command sets a listener parameter. SHOW parameter: This command lists the value of a listener parameter. STATUS: Provides basic status information about a listener, including a summary of listener configuration settings, the listening protocol addresses, and a summary of services registered with the listener. Example: Setting a password: To set a new encrypted password with the CHANGE_PASSWORD command, issue the following commands from the Listener Control utility: LSNRCTL> CHANGE_PASSWORD Old password: New password: takd01 Reenter new password: takd01 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tpc)(HOST=sales-server)(PORT=1521))) Password changed for LISTENER The command completed successfully LSNRCTL> SAVE_CONFIG

Example : The SET command can be used to set the listener parameters. To set the level of tracing for the listener to ADMIN, you would issue: LSNRCTL> SET trc_level ADMIN Example: The SHOW command can be used to display the values of parameters. To display the value of the level of tracing you would issue: LSNRCTL> SHOW trc_level Given below is a complete description of the parameters that can be set using the SET command. SET CURRENT_LISTENER- Sets or shows parameters when multiple listeners are used. SET LOG_DIRECTORY- Sets a nondefault location for the log file or to return the location to the default.

SET LOG_FILE- Sets a nondefault name for the log file. SET LOG_STATUS- Turns listener logging on or off. SET PASSWORD- Changes the password sent from the LSNRCTL utility to the listener process for authentication purposes only. SET SAVE_CONFIG_ON_STOP- Saves any changes made by the LSNRCTL SET command permanently if the parameter is on. All parameters are saved right before the listener exits. SET TRC_DIRECTORY- Sets a nondefault location for the trace file or to return the location to the default. SET TRC_FILE- Sets a nondefault name for the trace file. SET TRC_LEVEL- Turns on tracing for the listener. Note: The SHOW command has the corresponding parameters of the SET command except SET PASSWORD.

Lesson 3: Client-side configuration In a distributed processing environment, the clients need to connect to a database service using a connect identifier or connect string. The connect identifier can be a connect descriptor or a simple name that maps to a connect descriptor. The connect descriptor contains: • Network route to the service, including the location of the listener through a protocol address. • Oracle 8i or later release database service name or Oracle release 8.0 database Oracle System identifier (SID). If a simple name is provided it would need to be resolved using a naming method. 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 methods. A connect descriptor comprises of one or more protocol addresses of the listener and connect data information for the destination service. In the example below, a connect descriptor is mapped to a simple name called PROD. prod= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=prod-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=prod.us.acme.com)))

The ADDRESS section contains the listener protocol address, and the CONNECT_DATA section that contains the destination service information. In this example, the destination service is a database service named prod.us.acme.com. In an Oracle 10g, 9i or 8i database, you must identify the service using SERVICE_NAME parameter. Optionally, you can identify an instance with the INSTANCE_NAME parameter. prod= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=prod-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=prod.us.acme.com) (INSTANCE_NAME=prod)))

The SERVICE_NAME parameter is typically the global database name, comprising the database name and domain name, entered during installation or database creation. For example prod.us.acme.com has a database name of prod and a domain of us.acme.com. The INSTANCE_NAME parameter defaults to the SID entered during installation or database creation. The different naming methods supported by Oracle Net include:

Naming Method Local Naming

Directory Naming Easy Connect Naming

External Naming

Description Resolves a net service name stored in a tnsnames.ora file stored on a client. Local naming is appropriate for simple distributed networks with a small number of services that change infrequently. Resolves a database service name, net service name, or net service alias stored in a centralized LDAP-compliant directory server. Enables clients to connect to a database server without any configuration. The method is recommended for simple TCP/IP networks. Clients use a connect string for a simple TCP/IP address, consisting of a host name and an optional port or service name. CONNECT username/password@host[:port][/service_name] Resolves service information stored in a third-party naming service.

As part of this discussion, we will deal with Local Naming and Easy Connect Naming. Configuring Local Naming The local naming method adds net service names to the tnsnames.ora file. Each net service name maps to a connect descriptor. In the example shown below, a net service name is mapped to a connect descriptor. prod= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=prod-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=prod.us.acme.com)))

Local Naming configuration can be done using the Oracle Net Configuration Assistant. This tool is launched by the Universal Installer after software installation. You can also configure the tnsnames.ora file after installation. Other methods to configure Net service names include the Oracle Enterprise Manager and the Oracle Net Manager. A description of how to configure local naming using the Oracle Net Configuration Assistant is given below: Oracle Net Configuration Assistant

1. Start Oracle Net Configuration Assistant. The Welcome page appears.

2. Select Local Net Service Name Configuration, and then click Next. The Net Service Name Configuration page displays. 3. Click Add, and then Next. The Service Name Configuration page appears.

4. At the next screen you should enter the service name of the database or the service you want to access.

5. Next select the protocol, you would be using, in our example we select TCP which is the default protocol. 6. The next step involves specifying the host name of the machine on which the database is located and the port of the listener process.

7. You may or may not choose to perform a test. 8. Finally specify the name of the net service name. The above configuration updates the tnsnames.ora in the $ORACLE_HOME/network/admin folder. PROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =PROD-SERVER)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROD)) )

In the sqlnet.ora file the NAMES.DIRECTORY_PATH parameter indicates which is the preferred name resolution method. The parameter specifies the order of naming methods Oracle Net uses to resolve connect identifiers to connect descriptors. In the example the preferred method is tnsnames, which is local naming. However if it is unable to resolve the name using tnsnames, it will use hostname resolution. NAMES.DIRECTORY_PATH=(tnsnames, hostname)

The Easy Connect Naming Method The easy connect naming method eliminates the need for service name lookup in the tnsnames.ora file for TCP/IP environments. This method provides out-of-the-box TCP/IP connectivity to databases. If extends the functionality of the host naming method by enabling clients to connect to a database server with an optional port and service name in addition to the host name of the database. CONNECT username/password@[//host[:port][/service_name] where: //: Optional and // indicates a URL is being specified. host: Required and indicates the hostname or IP address of the database server computer port: Optional, and specifies the listening port. service_name : Optional and specifies the service name of the database. For example, the following connect strings connect the client to database service prod.us.acme.com with a listening endpoint of 1521 on database server prod-server. CONNECT username/ password@prod-server:1521/prod.us.acme.com CONNECT username/ password@//prod-server/prod.us.acme.com CONNECT username/ password@//prod-server.us.acme.com/prod.us.oracle.com

These connect strings convert into the following connect descriptor: (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=prod-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=prod.us.acme.com)))

Easy connect naming is automatically configured at installation. Prior to using it, you may want to ensure that EZCONNECT is specified by the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file. NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

Lesson 4: The Oracle Net Manager The Oracle Net Manager is a graphical user interface that permits both client and server side configuration. To launch Oracle Net Manager from Windows you would select Start -> Programs -> Oracle - -> Configuration and Migration Tools -> Net Manager. The Profile Option on the Navigation Tree permits you to configure the client side profile. Any changes you make through Profile will modify the SQLNET.ORA file. You would select Profile to modify the default domain name or the preferred name resolution method. The Service Naming option allows you to configure local naming method. You can specify the net service name, the protocol used, the host name of the machine hosting the database service and so on. The interface is similar to the Network Configuration Assistant discussed earlier. The Listeners option allows you to configure, create and delete listeners. This is for server-side configuration.

Lesson 5: Use TNSPING to test Oracle Net connectivity The TNSPING utility determines whether or not a service (for example, an Oracle database or any other Oracle service) on an Oracle Net network can be successfully reached. When using the TNSPING utility to determine if you can successfully connect from client to server (or a server to another server), it displays an estimate of the round trip time (in milliseconds) it takes to reach the Oracle Net service. To invoke the TNSPING utility, enter the following command from an operating system command prompt: tnsping net_service_name count where: net_service_name : must exist in the tnsnames.ora file or the name service in use such as NIS or DCE. count (optional) determines how many times the program attempts to reach the server. If the net service name specified is a database name, TNSPING attempts to contact the corresponding listener. It does not determine whether or not the database itself is running. Example: To connect to a database using a net service name of prod, the following is entered: tnsping prod TNS Ping Utility for Solaris: Version 10.1.0.2.0 on 15-NOV-2003 14:46:28 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prod-server)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod.us.acme.com))) OK (10 msec)

To determine whether a connection can be made to the sales database, and to specify that TNSPING should try to connect six times and then give up, use the syntax: tnsping prod 6 Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prod-server)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod.us.acme.com))) OK (10 msec) OK (0 msec) OK (10 msec) OK (0 msec) OK (10 msec) OK (10 msec)

Lesson 6: Database Control page It is possible to view information regarding the listener from the Database Control Page. The listener link opens the Listener page. The gives general information about the status of the listener, the port it listens on and so on.

You can also access the Net Services Administration page from the Database Control Home page to configure listeners, local naming and so on. The screenshot displays the Net Services Administration page.

Lesson 7: Enabling Advanced Features of Oracle Net Services In this lesson we look at certain advanced connect data parameters and features such as load balancing and failover. Creating a List of Listener Protocol Addresses A database service may be accessed by more than one network route, or protocol address. In the following example, prod.us.acme.com can connect to prod.us.acme.com using listeners on either prod1-server or prod2-server. prod.us.acme.com= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=prod1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=prod2-server)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=prod.us.acme.com)))

When a database service is accessible by multiple listener protocol addresses, it is necessary to specify the order in which the addresses are to be used. The addresses can be chosen randomly or tried sequentially. When multiple protocol addresses have been configured for a net service name or database service, you can configure the parameters as described in the table. Parameter SOURCE_ROUTE (Source Routing)

Description When set to on, instructs Oracle Net to use each address in the order presented until the destination is reached. This parameter is required for reaching the destination using a specific route, that is, by specific machines. The parameter is used to enable connections to Oracle Connection manager. Hence to use each address in order until the destination is reached set SOURCE_ROUTE=on

FAILOVER (Connect-time Failover)

At connect time, instructs Oracle Net to fail over to a different listener if the first listener fails when set to on. The number of addresses in the list determines how many addresses are tried. When set to off, instructs Oracle Net to try one address. Connect-Time Failover is turned on by default for multiple address lists (ADDRESS_LIST), connect descriptors (DESCRIPTION) and multiple connect descriptors (DESCRIPTION_LIST). Therefore, to try each address in order until one succeeds set FAILOVER=on Important: Oracle Corporation recommends not setting the GLOBAL_DBNAME parameter in the SID_LIST_ listener_name section of the listener.ora file. A statically configured global database name disables connect-time failover.

LOAD_BALANCE

When set to on, instructs Oracle Net to progress through the list

(Client Load Balancing)

of protocol addresses in a random sequence, balancing the load on the various listeners. When set to off, instructs Oracle Net to try the addresses sequentially until one succeeds. Client load balancing is turned on by default by multiple connect descriptors (DESCRIPTION_LIST) To try one address selected at random you would set LOAD_BALANCE=on To try each address, randomly, until one succeeds you would set LOAD_BALANCE=on and FAILOVER=on

It is not possible to set client load balancing or connect-time failover with source routing. While connect-time failover and client load balancing select an address from a list, source routing connects to each address in the list sequentially. Note: To use only the first address, you would set all parameters OFF such as: LOAD_BALANCE=off FAILOVER=off SOURCE_ROUTE=off The following example shows a tnsnames.ora file configured for client load balancing: prod.us.acme.com= (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=tcp)(HOST=prod1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=prod2-server)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=prod.us.acme.com)))

The following example shows a tnsnames.ora file configured for connect-time failover: prod.us.acme.com= (DESCRIPTION= (ADDRESS_LIST= (LOAD_BALANCE=off) (FAILOVER=ON) (ADDRESS=(PROTOCOL=tcp)(HOST=prod1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=prod2-server)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=prod.us.acme.com)))

Connection Load Balancing The connection load balancing feature improves connection performance by balancing the number of active connections among multiple dispatchers. In an Oracle Real Application Clusters environment, connection load balancing also has the capability to balance the number of active connections among multiple instances. Since the PMON process can register with remote listeners, a listener can always be 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, which dispatcher to send the incoming client request to. 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 In an Oracle 9i Real Application Clusters environment requires that the dispatcher on each instance be cross registered with other listeners on the other nodes. This is achieved by the use of the LISTENER attribute of the DISPATCHERS parameter. Connection Load Balancing for Shared Server Configuration Consider a RAC shared server database with two instances prod1 and prod2 of the same service prod.us.acme.com. The instance prod1 and prod2 reside on computers prod1server and prod2-server respectively. Prod1 has one dispatcher and prod2 has two dispatchers. Listeners named listener run nodes 1 and 2 respectively. The listener attribute in the DISPATCHERS parameter has been configured to allow for service registration of information to both listeners. In the example shown, The listeners_prod value in (LISTENER=listeners_prod) can be then resolved through a local tnsnames.ora file on the both servers as follows: listeners_prod= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=prod1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=prod2-server)(PORT=1521))) Steps to determine connection load balancing: 1. PMON determines which instances prod1 and prod2 register with both listeners. The listeners are updated on the load of the instances and dispatchers dynamically. The following load is registered. 1 Minute Node Load Average : Prod1-server : 600 Prod2-server : 400 Number of Connections to Instance : Prod1 : 200 Prod2: 300 Number of Connections to Dispatchers : Dispatcher1 : 200 Dispatcher2: 100 Dispatcher3: 200 In the example, prod2-server is the least loaded node, prod2 is the least loaded instance, and dispatcher2 is the least loaded dispatcher.

2. The client sends a connect request. A connect description as shown below is configured on the client. In this case, the connect descriptor is configured to try each protocol address randomly until one succeeds: prod.us.acme.com= (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS=(PROTOCOL=tcp)(HOST=prod1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=prod2-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=prod.us.acme.com)))

Listener

Listener

PROD1

PROD2 Dispatcher Dispatcher

Dispatcher

PROD1-SERVER

PROD2-SERVER

SERVICE_NAMES=prod.us.acme.com INSTANCE_NAME=prod1 DISPATCHERS=(PROTOCOL=tcp) (DISPATCHERS=1) (LISTENER=listeners_prod)

SERVICE_NAMES=prod.us.acme.com INSTANCE_NAME=prod2 DISPATCHERS=(PROTOCOL = tcp) (DISPATCHERS=2) (LISTENER=listeners_prod)

The listener on prod1-server was randomly chosen to receive the client connect request. The listener on prod1-server compares the load of the instances prod1 and prod2. The comparison takes into account the load on nodes prod1-server and prod2-server respectively. Since prod2-server is less loaded than prod1server, the listener selects prod2-server over prod1-server.

The listener then compares the load on dispatchers dispatchers2 and dispatcher3. Because dispatcher2 is less loaded than dispatcher3, the listener redirects the client connect request to dispatcher2. The client connects directly to dispatcher2. Configuring Transparent Application Failover (TAF) TAF instructs Oracle Net to fail over a failed connection to a different listener. This enables a 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. TAF supports two different types of failover: SESSION and SELECT. When a connection to an instance is lost, SESSION failover results only in the establishment of a new connection to a backup instance. Any work in progress is lost. SELECT is more complex and enables certain types of read-only applications to fail over without losing any work. When SELECT failover is implemented Oracle Net services keep track of any SQL statements issued in the current transaction along with the number of rows that have been fetched. If connection to the instance is lost, Oracle Net establishes a connection to a backup instance, re-executes the SELECT statements and the positions the cursors so the client can continue fetching rows as if nothing had happened. TAF also supports two failover methods: BASIC and PRECONNECT. In both cases, you specify a net service name to use for the backup connection in case the primary connection fails. The difference lies when a connection to the backup instance is made. In the case of BASIC failover method is used, the connection to the backup instance is made only if and when the primary connection fails. In the case of a PRECONNECT failover, the connection to the backup instance is made at the same time as the connection to the primary instance. Having a backup connection already in place can reduce the time needed for a failover in the event that one needs to take place. The price you pay is the additional overhead of always having the backup connection open. Example: TAF with Connect-time failover and client load balancing In this example, Oracle Net connects randomly to one of the protocol addresses on prod1server or prod2-server. If the instance fails after the connection, the TAF application fails over to the other node's listener, reserving any SELECT statements in progress. prod.us.acme.com= (DESCRIPTION= (LOAD_BALANCE=on) (FAILOVER=on) (ADDRESS= (PROTOCOL=tcp)(HOST=prod1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=prod2-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=prod.us.acme.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic))))

TAF also provides the ability to automatically retry connecting with the RETRIES and DELAY parameters if the first connection attempt fails. In the following example, Oracle Net attempts to connect to the listener on prod. The example below contains a single listening address with multiple retries possible (upto 20) when connection failure occurs. If the failover connection fails, Oracle Net waits 15 seconds before trying to reconnect again. prod.us.acme.com= (description= (address=(protocol=tcp)host=prod1-server)(port=1521)) (connect_data= (service_name=prod.us.acme.com) (failover_mode= (type=select) (method=basic) (retries=20) (delay=15))))