B Using Multi Data Sources with Oracle RAC

Learn how to configure and use Multi Data Sources on Oracle Real Application Clusters (RAC) with WebLogic Server. Oracle continues to support Multi Data Sources configurations for legacy application environments using RAC.

Both Oracle RAC and WebLogic Server are complex systems. To use them together requires specific configuration on both systems, as well as clustering software and a shared storage solution. This section describes the configuration required at a high level. For more details about configuring Oracle RAC, your clustering software, your operating system, and your storage solution, see the documentation from the respective vendors.

Note:

Oracle recommends using Active GridLink data sources when developing new Oracle RAC applications and when legacy applications do not use Multi Data Sources. See Using WebLogic Server with Oracle RAC.

Overview of Oracle RAC

Oracle RAC is a software component you can add to a high-availability solution that enables users on multiple machines to access a single database with increased performance. Oracle RAC comprises two or more Oracle database instances running on two or more clustered machines and accessing a shared storage device via cluster technology.

To support this architecture, the machines that host the database instances are linked by a high-speed interconnect to form the cluster. The interconnect is a physical network used as a means of communication between the nodes of the cluster. Cluster functionality is provided by the operating system, Oracle Automatic Storage Management (ASM), or compatible third party clustering software. Figure B-1 shows an Oracle RAC configuration.

Figure B-1 Oracle Real Application Clusters Configuration

Description of Figure B-1 follows
Description of "Figure B-1 Oracle Real Application Clusters Configuration"

Oracle RAC offers features in the following areas:

Oracle RAC Scalability with WebLogic Server Multi Data Sources

An Oracle RAC installation appears like a single standard Oracle database and is maintained using the same tools and practices. All the nodes in the cluster execute transactions against the same database and Oracle RAC coordinates each node's access to the shared data to maintain consistency and ensure integrity. You can add nodes to the cluster easily and there is no need to partition data when you add them. This means that you can horizontally scale the database tier as usage and demand grows by adding Oracle RAC nodes, storage, or both.

As the number of nodes in an Oracle RAC increases, you scale the number of generic data sources by the number of nodes added to the Oracle RAC. This requires a complex configuration (requiring n+1 data sources where n is the number of Generic data sources plus a Multi Data Source) that requires administrative intervention when the Oracle RAC topology changes.

Oracle RAC Availability with WebLogic Server Multi Data Sources

A Multi Data Source provides an ordered list of generic data sources to use to satisfy connection requests. Normally, every connection request to this kind of Multi Data Source is served by the first Generic data source in the list. If a database connection test fails and the connection cannot be replaced, or if the Generic data sources is suspended, a connection is sought sequentially from the next Generic data source on the list. See Failover and Multi Data Source-Managed Failover and Load Balancing.

Oracle RAC Load Balancing with WebLogic Server Multi Data Sources

Multi Data Sources provide load balancing for XA and non-XA environments. TheGeneric data sources that form a multi data sourceMulti Data Source are accessed using a round-robin scheme. When switching connections, WebLogic Server selects a connection from the next Generic data source in the order listed.

Software Requirements

Learn about the software required to use WebLogic Server with Oracle RAC.

To use WebLogic Server with Oracle RAC, you must install the following software on each Oracle RAC node:

  • Operating system patches required to support Oracle RAC. See the release notes from Oracle for details.

  • Oracle 11g database management system

  • Clustering software for your operating system. See the Oracle documentation for supported clustering software and cluster configurations.

  • Shared storage software, such as Oracle Automated Storage Management (ASM). Note that some clustering software includes a file storage solution, in which case additional shared storage software is not required.

    Note:

    See Supported Configurations in What's New in Oracle WebLogic Server for the latest WebLogic Server hardware platform and operating system support, and for the Oracle RAC versions supported by WebLogic Server versions and service packs. See the Oracle documentation for hardware and software requirements required for running the Oracle RAC software.

JDBC Driver Requirements

To use WebLogic Server with Oracle RAC, your WebLogic generic data sources must use the Oracle JDBC Thin driver 11g or later to create database connections.

Hardware Requirements

A typical WebLogic Server/Oracle RAC system includes a WebLogic Server cluster, an Oracle RAC cluster, and hardware for shared storage.

WebLogic Server Cluster

The WebLogic Server cluster can be configured in many ways and with various hardware options. See Administering Clusters for Oracle WebLogic Server for more details about configuring a WebLogic Server cluster.

Oracle RAC Cluster

For the latest hardware requirements for Oracle RAC, see the Oracle RAC documentation. However, to use Oracle RAC with WebLogic Server, you must run Oracle RAC instances on robust, production-quality hardware. The Oracle RAC configuration must deliver database processing performance appropriate for reasonably-anticipated application load requirements. Unusual database response delays can lead to unexpected behavior during database failover scenarios.

Shared Storage

In an Oracle RAC configuration, all data files, control files, and parameter files are shared for use by all Oracle RAC instances. An HA storage solution that uses one of the following architectures is recommended:

  • Direct Attached Storage (DAS), such as a dual ported disk array or a Storage Area Network (SAN)

  • Network Attached Storage (NAS)

For a complete list of supported storage solutions, see your Oracle documentation.

Configuring Multi Data Sources with Oracle RAC

When using Multi Data Sources with Oracle RAC, you must configure your WebLogic Domain so that it can interact with Oracle RAC instances and so that it performs as expected.

Choosing a Multi Data Source Configuration for Use with Oracle RAC

WebLogic Server Multi Data Sources support several configuration options for using Oracle RAC:

Configuring Multi Data Sources for use with Oracle RAC

To connect WebLogic Server to multiple Oracle RAC nodes using Multi Data Sources, first configure a Generic data source for each Oracle RAC instance in your Oracle RAC cluster with the Oracle Thin driver. Then configure a Multi Data Source, using either the algorithm for load balancing or the algorithm for failover, and add generic data sources to it.

Figure B-2 shows a typical Multi Data Source configuration.

Figure B-2 Multi Data Source Configuration

Description of Figure B-2 follows
Description of "Figure B-2 Multi Data Source Configuration"

You can use the WebLogic Server Administration Console or any other means that you prefer to configure your domain, such as the WebLogic Scripting Tool (WLST) or a JMX program. For information about configuring a WebLogic JDBC Multi Data Source see Configuring JDBC Multi Data Sources. For information on how to configure the Generic data sources in a Multi Data Source to connect to services running on Oracle RAC nodes, see Configuring Connections to Services on Oracle RAC Nodes.

To use a database connection in this configuration, your applications look up one v on the JNDI tree and then request a connection. The Multi Data Source determines which generic data source to use to satisfy the connection request based on the algorithm type specified in the configuration (that is, failover or load balancing).

Attributes of a Multi Data Source

The multi data source may have the following attributes, depending on the role of Oracle RAC in your system—load balancing or failover:

  • AlgorithmType="Load-Balancing" or AlgorithmType="Failover"

    With the Load-Balancing option, connection requests are distributed among available generic data sources; with the Failover option, connection requests are served by the first available pool in the list. When a generic data source becomes defunct, connection requests are served by the next generic data source in the list.

  • FailoverRequestIfBusy="true"

    With the Failover algorithm, this attribute enables failover when all connections in a generic data source are in use.

  • TestFrequencySeconds="120"

    This attribute controls the frequency at which WebLogic Server checks the health of generic data sources previously marked as unhealthy to see if connections can be recreated and if the generic data source can be re-enabled. For more details see Configuring JDBC Multi Data Sources.

    For fast failover of Oracle RAC nodes, set this value to a smaller interval, for example, 10 (seconds).

Configuration Considerations for Failover

Consider the following information when configuring for failover.

Multi Data Source-Managed Failover and Load Balancing

Multi Data Sources offer failover and load balancing for global transactions. For a description of Multi Data Source failover features, see Multi Data Source Failover Enhancements.

With this configuration, pictured in Figure B-2, you get:

  • Fast failover controlled by the Multi Data Source

  • Automatic failback by the WebLogic Server health monitor

The Multi Data Source handles failover for database connections when an Oracle RAC node becomes unavailable. When WebLogic Server tests a connection and the connection fails, it attempts to recreate the connection. If that attempt fails, the server disables the Generic data source and routes connection requests to other Generic data sources (which correspond to other Oracle RAC nodes) in the Multi Data Source. WebLogic Server periodically tries to recreate the database connections in the disabled Generic data source. When WebLogic Server is successful in recreating the connections, it next re-enables the Generic data source and begins routing connection requests to the Generic data source again. Because of the connection request routing and automatic health checking features, there is minimal delay in satisfying connection requests after a failure.

Delays During Failover

Occasionally, when one Oracle RAC node fails over to another, there may be a delay before the data associated with a transaction branch in progress on the now failed node is available throughout the cluster. This prevents incomplete transactions from being properly completed, which could further result in data locking in the database. To protect against the potential consequences of such a delay, WebLogic Server provides two configuration attributes that enable XA call retry for Oracle RAC: XARetryDurationSeconds and XARetryIntervalSeconds.

When a server acting as Coordinator returns to service, it takes the following actions during recovery:

  • The Transaction Manager reads the transaction checkpoints and the resource checkpoints from the TLog.

  • The transactions read from the TLOG (transaction checkpoints) become active and the state is set to committing. The TM tries to commit these transactions just as it does for other runtime transactions. If the commit fails a retry-commit process takes place until AbandonTimeoutSeconds after a grace period has expired.

  • The TM calls xa.recover on resources read from the TLOG (resource checkpoints) to obtain a list of pending transactions. If the xa.recover call fails, the TM retries the xa.recover call on the resource every XARetryIntervalSeconds for a period of XARetryDurationSeconds.

Use the following formula to determine the value for XARetryDurationSeconds:

XARetryDurationSeconds = (longest transaction timeout for transactions that use connections from the generic data source) + (delay before XIDs are available on all Oracle RAC nodes, typically less than 5 minutes)

For example, if your application sets the longest transaction timeout as 180 seconds, you should set XARetryDurationSeconds to 180 seconds + 300 seconds, for a total of 480 seconds.

Note:

It is generally better to set XARetryDurationSeconds higher than minimally necessary to make sure that all transactions are completed properly. Setting the value higher than minimally required should not affect application performance during normal operations. The additional processing only affects transactions that have been prepared but have failed to complete.

You can also optionally set a value for XARetryIntervalSeconds. This value determines the time between XA retry calls. By default, the value is 60 seconds. Decreasing the value will decrease the amount of time between XA retry attempts. The default value should suffice in most cases.

To enable XARetryDurationSeconds and XARetryIntervalSeconds from the WebLogic Server Administration Console, use the following steps:

  1. If you have not already done so, in the Change Center of the WebLogic Server Administration Console, click Lock & Edit.

  2. In the Domain Structure tree, expand Services > JDBC, then select Data Sources.

  3. On the Summary of Data Sources page, click the data source name.

  4. Select the Configuration: Connection Pool tab.

  5. Scroll down and click Advanced to show the advanced connection pool options.

  6. Update XA Retry Duration and XA Retry Interval.

  7. Click Save.

Optionally, you can use WebLogic Scripting Tool (WLST) or a JMX program.

Failure Handling Walkthrough for Global Transactions

What happens to in-flight transactions to a database node if that node fails? When the primary Oracle RAC node fails? Does WebLogic Server support transparent failover? To answer these and other questions about how WebLogic Server handles failures, let's walk through the transaction processing steps and describe how a failure would be handled at each stage along the way.

The first stage at which a failure may occur is before the application calls for the transaction to be committed. If a database or Oracle RAC node fails at this stage, the application receives an exception and must get a new connection and make a new attempt at processing the transaction. WebLogic Server does not support transparent failover.

If a failure occurs after the application has called for the transaction to be committed, the handling of any in-flight transaction depends upon whether the PREPARE operation is complete. If the PREPARE operation is not complete, the transaction manager rolls back the transaction and sends the application an exception for the failed transaction. If the PREPARE operation is complete, the transaction manager attempts to drive the in-flight transaction to completion using another node.

If a failure occurs during the COMMIT operation, the transaction manager attempts to retry the COMMIT operation several times. Note that the connection is blocked during these attempts. If the COMMIT operation is not successful during the first set of retry attempts, the application receives an exception. The transaction manager then continues to retry the COMMIT operation periodically until it is successful; if the transaction cannot be completed successfully within the abandon time period, the transaction is driven to completion heuristically.

Configuring the Listener Process for Each Oracle RAC Instance

For Oracle RAC, the listener process establishes a communication pathway between a user process and an Oracle instance. When you use Oracle RAC with WebLogic Server, the user process is typically a data source.

When a multi data source is created, it attempts to create a pool of database connections for applications to borrow. If a pooled database connection becomes inoperative or if the generic data source is configured to grow in capacity, the data source attempts to create additional database connections up to the maximum specified in the configuration file. In all of these instances, the Oracle listener process handles the connection request on the Oracle RAC instance.

Figure B-3 shows the Oracle listener process functionality.

Figure B-3 Oracle Listener Process Functionality

Description of Figure B-3 follows
Description of "Figure B-3 Oracle Listener Process Functionality"

To enable this functionality, you have two options:

  • Use local listeners. Configure the listener process for each Oracle RAC instance in the Oracle cluster. WebLogic Server requires that you configure a local listener on each Oracle RAC instance. Each database instance should be configured to register only with its local listener.

    Oracle instances can be configured to register with the listener statically in the listener.ora file or registered dynamically using the instance initialization parameter local_listener, or both. Oracle recommends using dynamic registration.

    A listener can start either a shared dispatcher process or a dedicated process. When using with WebLogic Server, Oracle recommends using dedicated processes.

  • Use remote listeners. WLS requires that you specify both the SERVICE_NAME and the INSTANCE_NAME in the JDBC URL for the generic data sources in the multi data source. See Configuring Multi Data Sources When Remote Listeners are Enabled or Disabled.

Configuring Multi Data Sources When Remote Listeners are Enabled or Disabled

If the server-side load balancing feature has been enabled for the Oracle RAC backend (using remote_listeners), the JDBC URL that you use in the generic data sources of a multi data source configuration should include the INSTANCE_NAME. For example, the URL can be specified in the following format:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host-vip)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=dbservice)(INSTANCE_NAME=inst1)))

If specifying the INSTANCE_NAME in the URL is not possible, remote listeners must be disabled. To disable remote listeners, delete any listed remote listeners in spfile.ora on each Oracle RAC node. For example:

*.remote_listener="

In this case, the recommended URL that you use in the generic data sources of a multi data source configuration is:

jdbc:oracle:thin:@host-vip:port/dbservice

or

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host-vip)(PORT=1521))(
CONNECT_DATA=(SERVICE_NAME=dbservice)))

Additional Configuration Considerations

In some deployments of Oracle RAC, you may need to set parameters in addition to the out of the box configuration of a data source in an Oracle RAC configuration. The additional parameters are:

  • Set oracle.jdbc.ReadTimeout=300000 (300000 milliseconds) for each generic data source.

    The actual value of the ReadTimeout parameter used may differ based on your application environment.

  • If the network is not reliable, it is difficult for a client to detect the frequent disconnections when the server is abruptly disconnected. By default, a client running on Linux takes 7200 seconds (2 hours) to sense the abrupt disconnections. This value is equal to the value of the tcp_keepalive_time property. To configure the application to detect the disconnections faster, set the value of the tcp_keepalive_time, tcp_keepalive_interval, and tcp_keepalive_probes properties to a lower value at the operating system level.

    Note:

    Setting a low value for the tcp_keepalive_interval property leads to frequent probe packets on the network, which can make the system slower. Set the value of this property based on system requirements of your application environment.

    For example, set tcp_keepalive_time=600 for a system running a WebLogic Server managed server.

  • Specify the ENABLE=BROKEN parameter in the DESCRIPTION clause in the connection descriptor. For example:

    jdbc:oracle:thin:@(DESCRIPTION=(enable=broken)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.mycompany.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl.country.myCorp.com)(INSTANCE_NAME=orcl1)))

The following code snippet provides an example generic data source configuration:

<url>jdbc:oracle:thin:@(DESCRIPTION=(enable=broken)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip.country.myCorp.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl.country.myCorp.com)(INSTANCE_NAME=orcl1)))</url>
<driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>
<properties>
<property>
<name>oracle.jdbc.ReadTimeout</name>
<value>300000</value>
</property>
<property>
<name>user</name>
<value>jmsuser</value>
</property>
<property>
<name>oracle.net.CONNECT_TIMEOUT</name>
<value>10000</value>
</property>
</properties>

Using Multi Data Sources with Global Transactions

In this configuration, a Multi Data Source "pins" a transaction to one and only one Oracle RAC instance. Individual transactions are load balanced with the initial connection request for the transaction.

Failover is handled at the Multi Data Source level when a Oracle RAC instance becomes unavailable. If there is a failure on a Oracle RAC instance before PREPARE, the transaction is lost. If there is a failure after PREPARE, the transaction is failed over to another instance.

Rules for Data Sources within a Multi Data Source Using Global Transactions

The following rules apply to the XA data sources within a Multi Data Source:

  • All the data sources must be homogeneous. In other words, either all of them must use an XA driver or none of them can use an XA driver.

  • If you choose to specify them, all XA-related attributes must be set to the same values for each generic data source. The attributes include the following:

    • XARetryDurationSeconds

    • SupportsLocalTransaction

    • KeepXAConnTillTxComplete

    • NeedTxCtxOnClose

    • XAEndOnlyOnce

    • NewXAConnForCommit

    • RollbackLocalTxUponConnClose

    • RecoverOnlyOnce

    • KeepLogicalConnOpenOnRelease

      Note:

      If you are not using Active GridLink data sources, Oracle recommends the use of Multi Data Sources for failover and load balancing across Oracle RAC instances for XA and non-XA environments. For more information on using Multi Data Sources in non-XA environments, see Using Multi Data Sources without Global Transactions.

Required Attributes of Data Sources within a Multi Data Source Using Global Transactions

Each generic data source within the multi data source should have the following attributes:

  • Oracle JDBC Thin driver. For example:

    <url>jdbc:oracle:thin:@host1:1521:SNRAC1</url> 
    <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>
    
  • KeepXAConnTillTxComplete="true"

    • Forces the generic data source to reserve a physical database connection and provide the same connection to an application throughout transaction processing until the distributed transaction is complete.

    • Required for proper transaction processing with Oracle RAC.

  • XARetryDurationSeconds="300"

    • Enables the WebLogic Server transaction manager to retry XA recover, commit, and rollback calls for the specified amount of time.

  • TestConnectionsOnReserve="true"

    • Enables testing of a database connection when an application reserves a connection from the generic data source. See Test Connections on Reserve to Enable Fail-Over for more details about this attribute.

    • Required to enable failover to another Oracle RAC node.

  • TestTableName="name_of_small_table" The name of the table used to test a physical database connection. For more details about this attribute, see Connection Testing Options for a Data Source.

Sample Configuration Code

Sample configuration code for a multi data source and two associated generic data sources is shown below.

<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source"
  xmlns:sec="http://xmlns.oracle.com/weblogic/security"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:wls="http://xmlns.oracle.com/weblogic"
  xsi:schemaLocation="http://xmlns.oracle.com/weblogic/domain/1.0/domain.xsd">
  <name>oracleRACXAPool</name> 
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@host1:1521:SNRAC1</url> 
    <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name> 
    <properties>
      <property>
        <name>user</name> 
        <value>wlsqa</value> 
      </property>
    </properties>
    <password-encrypted>{3DES}aP/xScCS8uI=</password-encrypted> 
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name> 
    <profile-type>0</profile-type> 
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>oracleRACXAJndiName</jndi-name>
    <global-transactions-protocol>TwoPhaseCommit
        </global-transactions-protocol>
  </jdbc-data-source-params>
  <jdbc-xa-params>
    <keep-xa-conn-till-tx-complete>true</keep-xa-conn-till-tx-complete> 
    <xa-end-only-once>true</xa-end-only-once> 
    <xa-set-transaction-timeout>true</xa-set-transaction-timeout> 
    <xa-transaction-timeout>120</xa-transaction-timeout> 
    <xa-retry-duration-seconds>300</xa-retry-duration-seconds> 
  </jdbc-xa-params>
</jdbc-data-source>

<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source"
  xmlns:sec="http://xmlns.oracle.com/weblogic/security"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:wls="http://xmlns.oracle.com/weblogic"
  xsi:schemaLocation="http://xmlns.oracle.com/weblogic/domain/1.0/domain.xsd">
  <name>oracleRACXAPool2</name> 
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@host2:1521:SNRAC2</url> 
    <driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name> 
    <properties>
      <property>
        <name>user</name> 
        <value>wlsqa</value> 
      </property>
    </properties>
    <password-encrypted>{3DES}aP/xScCS8uI=</password-encrypted> 
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name> 
    <profile-type>0</profile-type> 
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>oracleRACXAJndiName2</jndi-name> 
    <global-transactions-protocol>TwoPhaseCommit
        </global-transactions-protocol> 
  </jdbc-data-source-params>
  <jdbc-xa-params>
    <keep-xa-conn-till-tx-complete>true</keep-xa-conn-till-tx-complete> 
    <xa-end-only-once>true</xa-end-only-once> 
    <xa-set-transaction-timeout>true</xa-set-transaction-timeout> 
    <xa-transaction-timeout>120</xa-transaction-timeout> 
    <xa-retry-duration-seconds>300</xa-retry-duration-seconds> 
  </jdbc-xa-params>
</jdbc-data-source>

<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source"
  xmlns:sec="http://xmlns.oracle.com/weblogic/security"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:wls="http://xmlns.oracle.com/weblogic"
  xsi:schemaLocation="http://xmlns.oracle.com/weblogic/domain/1.0/domain.xsd">
  <name>oracleRACXAMDS</name> 
  <jdbc-data-source-params>
    <jndi-name>oracleRACMDSJndiName</jndi-name> 
    <algorithm-type>Load-Balancing</algorithm-type> 
    <data-source-list>oracleRACXAPool,oracleRACXAPool2</data-source-list> 
  </jdbc-data-source-params>
</jdbc-data-source>

Using Multi Data Sources without Global Transactions

Learn about the configurations that use Oracle RAC with Multi Data Sources in an application that does not require global transactions.

Attributes of Data Sources within a Multi Data Source Not Using Global Transactions

Generic data sources must have the following attributes:

  • Oracle JDBC Thin driver. For example:

     <url>jdbc:oracle:thin:@host1:1521:SNRAC1</url> 
     <driver-name>oracle.jdbc.OracleDriver</driver-name>
    
  • TestConnectionsOnReserve="true"

    • Enables testing of a database connection when an application reserves a connection from the Generic data source. Test Connections on Reserve to Enable Fail-Over for more details about this attribute.

    • Required to enable failover and connection request routing within a Multi Data Source (effectively, failover to another Oracle RAC node).

  • TestTableName="name_of_small_table"

Sample Configuration Code

Sample configuration code for a WebLogic JDBC multi data source and associated generic data sources is shown below.

<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source"
  xmlns:sec="http://xmlns.oracle.com/weblogic/security"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:wls="http://xmlns.oracle.com/weblogic"
  xsi:schemaLocation="http://xmlns.oracle.com/weblogic/domain/1.0/domain.xsd">
  <name>jdbcPool</name> 
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@host1:1521:snrac1</url> 
    <driver-name>oracle.jdbc.OracleDriver</driver-name> 
    <properties>
      <property>
        <name>user</name> 
        <value>wlsqa</value> 
      </property>
    </properties>
    <password-encrypted>{3DES}aP/xScCS8uI=</password-encrypted> 
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <test-connections-on-reserve>true</test-connections-on-reserve> 
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name> 
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>jdbcDataSource</jndi-name> 
  </jdbc-data-source-params>
</jdbc-data-source>

<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source"
  xmlns:sec="http://xmlns.oracle.com/weblogic/security"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:wls="http://xmlns.oracle.com/weblogic"
  xsi:schemaLocation="http://xmlns.oracle.com/weblogic/domain/1.0/domain.xsd">
  <name>jdbcPool2</name> 
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@host2:1521:SNRAC2</url> 
    <driver-name>oracle.jdbc.OracleDriver</driver-name> 
    <properties>
      <property>
        <name>user</name> 
        <value>wlsqa</value> 
      </property>
    </properties>
    <password-encrypted>{3DES}aP/xScCS8uI=</password-encrypted> 
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <test-connections-on-reserve>true</test-connections-on-reserve> 
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name> 
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>jdbcDataSource2</jndi-name> 
    <global-transactions-protocol>OnePhaseCommit
         </global-transactions-protocol> 
  </jdbc-data-source-params>
</jdbc-data-source>

<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source"
  xmlns:sec="http://xmlns.oracle.com/weblogic/security"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:wls="http://xmlns.oracle.com/weblogic"
  xsi:schemaLocation="http://xmlns.oracle.com/weblogic/domain/1.0/domain.xsd">
  <name>jdbcNonXAMultiPool</name> 
  <jdbc-data-source-params>
    <jndi-name>jdbcDataSource</jndi-name> 
    <algorithm-type>Failover</algorithm-type> 
    <data-source-list>jdbcPool,jdbcPool2</data-source-list> 
    <failover-request-if-busy>true</failover-request-if-busy> 
  </jdbc-data-source-params>
</jdbc-data-source>

Note:

Line breaks added for readability.

Configuring Connections to Services on Oracle RAC Nodes

If you rely on Oracle services in your Oracle RAC cluster for workload management, you must use Multi Data Sources to connect to those services instead of you using a Service ID (SID). A WebLogic Server Generic data source can be configured to connect only to a specific service on a specific Oracle RAC node, providing both workload management and load balancing.

In general, to connect to Oracle RAC services, you need to:

  • Create a Multi Data Source for each service to which you want to connect.

  • Within each Multi Data Source, create one Generic data source for each Oracle RAC node in the cluster on which the service will be configured, whether or not the service will be actively running on each node.

Configuring a Data Source to Connect to a Service, describes special considerations for configuring these data sources. Service Connection Configurations, shows example configurations for either load balancing or workload management.

Configuring a Data Source to Connect to a Service

You configure a Generic data source to connect to a service running on an Oracle RAC node in the same way as you configure any Generic data source (using WLST, the WebLogic Server Administration Console, or the Configuration Wizard), with the following exceptions:

  • initial-capacity="0"

    This prevents pool creation failure for inactive pools at WLS startup, and enables WLS to create the Generic data source even if it can't connect to the service on the node. Without setting this option to 0, Generic data source creation will fail and the server may fail to boot normally.

    In the WebLogic Server Administration Console, edit the generic data source after creating it, and set Initial Capacity to 0.

  • Oracle JDBC Thin (or Thin XA) driver. For example:

    For non-XA:

    driver-name="oracle.jdbc.OracleDriver"
    url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RAC1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Service_1)(INSTANCE_NAME=DB_02)))"
    

    If configuring via the WebLogic Server Administration Console, select Oracles's Driver (Thin) for RAC Service-Instance connections from the Database Driver drop-down and specify the service in the Service Name field.

    For XA:

    driver-name="oracle.jdbc.xa.client.OracleXADataSource"
    url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RAC1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=Service1)(INSTANCE_NAME=DBase1)))"
    

    If configuring via the WebLogic Server Administration Console, select Oracle's Driver (Thin XA) for RAC Service-Instance connections from the Database Driver drop-down and specify the service in the Service Name field.

    Note:

    The SERVICE_NAME must be the same for all Generic data sources in a given Multi Data Source.

    Specify a different HOST NAME and/or port for each Generic data source in a given Multi Data Source.

  • When specifying max-capacity (Maximum Capacity in the WebLogic Server Administration Console) for the connection pool, you need to consider the connection capacity of each of the Oracle RAC nodes in your configuration, and the total number of connections from all Generic data sources. See Connection Pool Capacity Planning, for more information.

Selecting the Appropriate Multi Data Source Algorithm

For service connection scenarios, Oracle recommends that you configure your Multi Data Source with the Load Balancing algorithm. If the Multi Data Source is configured with the Load Balancing algorithm, its connection pools are used in a round robin fashion. In this case, workload is load-balanced across all of the Oracle RAC nodes on which the associated service is currently active.

If the Multi Data Source is configured with the Failover algorithm, the first Multi Data Source is used to connect to the service on its associated Oracle RAC node, until a connection attempt fails for any reason (for example, the Oracle RAC node becomes unavailable or there are no more connections available in the Multi Data Source). At that point, the second Multi Data Source is used to connect to the service on its associated Oracle RAC node, and so on. In this case, the Oracle RAC node to which the first Multi Data Source is connected will experience more use than the remaining nodes on which the service is running.

Service Connection Configurations

You can design your configuration to provide:

Workload Management

In a workload management configuration, each Multi Data Source has one Generic data source configured for a given service on each Oracle RAC node, regardless of whether the service you are connecting to is active or inactive on a given Oracle RAC node. This lets you quickly start an inactive service on a node and create connections to that service should another node become unavailable due to unplanned downtime or scheduled maintenance. It also lets you quickly increase or decrease the available capacity for a given service based on workload demands.

When you start the service on a node, the associated Generic data source detects that the service is now active, and the Generic data source will then start making connections to that node as needed. When you stop a service on a given node, the associated Generic data source can no longer make connections to that node, and will become inactive until the service is restarted on that node.

The WLS Generic data source performs connection testing. This lets the Generic data source adjust to changes in the topology of the Oracle RAC configuration. The Generic data source performs polling to see if its associated service is active or inactive. The connection test fails if the service is no longer available on the Oracle RAC node.

Figure B-4 Workload Management using Multi Data Sources

Description of Figure B-4 follows
Description of "Figure B-4 Workload Management using Multi Data Sources"

In this example, Service 1 is active on Oracle RAC Nodes 1, 2, and 3, while Service 2 is inactive on those nodes. Service 2 is active on Oracle RAC Nodes 4 and 5, while Service 1 is inactive on those nodes.

If Oracle RAC Node 1 becomes unavailable for any reason, you can start Service 1 on Oracle RAC Node 4. WebLogic Server will detect that the service is running on Node 4, and will begin making connections from the associated backup generic data source to Node 4 as needed.

Load Balancing

In a load balancing configuration, there are multiple services running concurrently on each Oracle RAC node. Each WLS Multi Data Source has an active connection pool configured to connect to a given service on each of the nodes. In this scenario, you would configure each Multi Data Source to use the Load Balancing algorithm.

Figure B-5 Load Balancing with Multi Data Sources

Description of Figure B-5 follows
Description of "Figure B-5 Load Balancing with Multi Data Sources"

In this example, Service 1 and Service 2 are each actively running on all of the available Oracle RAC nodes. As a result, all of the connection pools in each multi data source will actively make connections in a round-robin fashion, balancing workload among the five nodes.

Connection Pool Capacity Planning

It is important to note the Maximum Capacity value you specify for a Generic data source can cause the connection capacity to a given Oracle RAC node to be exceeded. You must consider the following factors when determining how to set this value for each of your Generic data sources:

  • The maximum number of simultaneous connections that a Oracle RAC node can support. This is based on the available memory on a given Oracle RAC node and the amount of memory consumed by each service connection (which can vary for each service). Memory consumption by each connection is a major limitation on the amount of work that can be generated from the WLS servers. Exceeding the amount of available memory by creating too many connections from your WLS Generic data sources to a given Oracle RAC node can result in degraded performance on the Oracle RAC node, or could lead to failed connections.

    Available memory for a node should be based on the PGA target attribute (per session memory).

  • The maximum number of Generic data sources that can potentially create connections to a given Oracle RAC node, and the number of WebLogic server instances to which each Generic data source or Multi Data Source is targeted. For example, if you have one Generic data source that is targeted to three WLS servers, that Generic data source counts as three Generic data sources, as each server uses its own instance of the Generic data source. This is the case whether the servers are part of a cluster or are independent server instances.

  • The maximum number of services that may be actively running on a given Oracle RAC node, and the memory consumed on the node by each connection to each service.

  • The expected relative workload for each service on a given node. For example, the expected workload of Service1 may be double that of the expected workload of Service2.

    Regardless of whether or not a service is always active on a node, you should allocate resources for that service in the event you have to start it on the node.

  • Always use the worst-case scenario when setting the Maximum Capacity value for your generic data sources. For example, assume that all available services will be actively running on the Oracle RAC node associated with each generic data source.

The following example explains how you could go about determining each Generic data sources Maximum Capacity value. Keep in mind that this is a very simple example intended to illustrate the issue conceptually, and that real-world situations are much more complicated. In general, it is best to under-configure yourGeneric data sources with a low Maximum Capacity value, monitor your Oracle RAC nodes for memory usage and performance, then adjust the Maximum Capacity values upward until you are approaching the maximum capacity of the associated Oracle RAC nodes.

Example

Suppose you have the following basic configuration:

  • Five Oracle RAC nodes, each with 16 GB of memory.

  • Two services actively running on each Oracle RAC node. Service1 uses 10 MB per connection, Service2 uses 20MB per connection.

  • Workload for each service is the same, that is, each service will generate an equivalent number of connections on a given Oracle RAC node.

  • Two WebLogic Server clusters. Cluster1 has five servers. Cluster2 has four servers.

  • For a given Oracle RAC node, one generic data source is targeted to Cluster1 and is configured to connect to Service1.

  • For a given Oracle RAC node, one generic data source is targeted to Cluster 2 and is configured to connect to Service2.

Because Service2 uses twice as much memory per connection as Service1, you should allocate approximately 10 GB of the node's memory for Service 2 and approximately 5GB for Service1.

Because Cluster1 has five WLS servers, there will be five Generic data sources making connection requests to this Oracle RAC node. This gives you 1 GB of memory available for connections from a given generic data source (5GB/5). Each connection requires 10 MB of memory, so the Maximum Capacity value for each generic data source targeted to Cluster1 should be 100 or lower.

Because Cluster 2 has four WLS servers, there will be four Generic data sources making connection requests to this Oracle RAC node. This gives you 2.5 GB of memory available for connections from a given generic data source (10GB/4). Each connection requires 20 MB, so the Maximum Capacity value for each generic data source targeted to Cluster2 should be 125 or lower.

If Service 2 generates more workload than Service1, you would have to adjust these values appropriately (increase the Maximum Capacity value for the generic data source connecting to Service2, decrease the value for the generic data source connecting to Service1). As long as:

(Max. connections to Service1 x memory used per connection) + (Max. connections to Service2 x memory used per connection) < Available memory

you can avoid the potential for performance degradation or connection failures.

Alternatively, in a simple configuration, such as is shown in Figure B-6, the Maximum Capacity value you specify for each of your generic data sources can be loosely determined using the following formula:

Maximum connection pool capacity = Maximum number of connections to Oracle RAC nodes/(Number of WebLogic Server instances x Nmber of generic data sources targeted to each instance x Number of active Oracle RAC services configured x Number of Oracle RAC Nodes)

where:

Maximum number of connections to Oracle RAC nodes is determined by total memory available on all nodes divided by the memory consumed by each connection.

Number of WebLogic Server instances is the number of server instances to which the Generic data sources are targeted. If the generic data sources is targeted to a WLS cluster, this is the number of servers in the cluster.

In the example in Figure B-6:

  • assume that a maximum of 4000 total connections can be made to the group of Oracle RAC nodes, based on 8GB of available memory per Oracle RAC node, and 10 MB of memory used per connection.

  • there are a total of five server instances to which the Generic data sources are targeted

  • there are five generic data sources targeted to each server instance

  • there are two services running on each Oracle RAC node, and

  • there are five Oracle RAC nodes.

In this configuration, the Maximum Capacity value you would enter for each of your Generic data sources would be:

Maximum connection pool capacity = 4000/(5 server instances x 5 generic data sources x 2 services x 5 Oracle RAC nodes)

which would give you a Maximum Capacity value of 16 for each of your Generic data sources.

Figure B-6 Example Multi Data Source Connection Configuration

Description of Figure B-6 follows
Description of "Figure B-6 Example Multi Data Source Connection Configuration"

Keep in mind that this formula is just a general guideline for configuring your generic data sources, as many configurations will be too complex for you to use such a simple calculation.

When calculating the Maximum Capacity value you should use, always consider the worst-case scenario that you will have in your overall configuration. It is best to under-configure this value for normal operation than to have it over-configured when a worst-case situation develops. You can always monitor your Oracle RAC nodes to determine if it is safe to increase the Maximum Capacity value for any of your Generic data sources.

Using SCAN Addresses with Multi Data Sources

Use Single Client Access Name (SCAN) for providing connection to time listener failover and load-balancing.

SCAN is not recommended for use with Multi Data Source. This can be a problem if your configuration is set up to use SCAN (for example, you can't use non-scan addresses if the database listener is set up to use SCAN).

Connection load-balancing cannot be used with a Multi Data Source because the Multi Data Source must be in control of handling the connection load balancing and failover. To turn off this capability, use a URL with an INSTANCE_NAME attribute. Each of the Generic data sources in the Multi Data Source should point to a different instance. When the Multi Data Source recognizes that an instance is down on the first Generic data source, it guides connections to the instance on the first Generic data source that is not down. When SCAN used with an INSTANCE_NAME attribute, the Multi Data Source provides load-balancing, failover of connections, and continues to provide a more reliable way to get to a listener.

If you need to configure SCAN address for a Multi Data Source, configure each Generic data source member with a URL that has a different INSTANCE_NAME value. For example:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=scanname)(PORT=scanport))(CONNECT_DATA=(SERVICE_NAME=myservice)(INSTANCE_NAME=myinstance)))

Note:

If you add a node, you need to manually add a Generic data source member and add it to the Multi Data Source.

Another way to avoid having SCAN do connection load-balancing is to specify a service name that is available on a single instance and omit the instance name. Each member data source must have it's own unique service name and each service name must be available on only one instance that doesn't overlap with any other member data source.

XA Considerations and Limitations when using Multi Data Sources with Oracle RAC

Learn about the certain requirements and limitations you need to consider when using XA (Global transactions) withMulti Data Sources on Oracle RAC.

Oracle RAC XA Requirements when using Multi Data Sources

Oracle RAC has the following requirements when using Multi Data Sources with Global transactions:
  • Always use a Multi Data Source when using XA transactions with Multi Data Sources for Oracle RAC.

  • Global transactions must be initiated, prepared, and concluded in the same instance of the Oracle RAC cluster. WebLogic Server Generic data sources manage this for you when you set KeepXAConnTillTxComplete="true" in the Generic data source configuration.

  • When using global transactions, transaction IDs (XIDs) must be unique within the Oracle RAC cluster. However, neither the Oracle Thin driver nor an Oracle RAC instance can determine if an XID is unique within the Oracle RAC cluster. Transactions with the same XID can execute SQL code on different instances of the Oracle RAC cluster without any exception.

Known Issue Occurring After Database Server Crash

If, while a transaction is being processed, the database server instance crashes after the PREPARE operation is complete but before the results of that operation have been written to the transaction log, a COMMIT call from a client for that transaction may hang for several minutes and possibly until the TCP timeout period has expired. The window of time in which this might occur is small and the problem occurs rarely. There is no workaround for the issue at this time.

JDBC Store Recovery with Oracle RAC

If you are using a JDBC Store with Oracle RAC, there are features and limitations to consider that concern Oracle RAC node failover.

For a list of the major services that use the JDBC store, see Monitoring Store Connections in Administering the WebLogic Persistent Store.

Configuring a JDBC Store for Use with Oracle RAC

The way that a JDBC Store works limits the options you have for configuring one for use with Oracle RAC. You cannot configure a JDBC store to use a generic data source that is configured to support global transactions. The JDBC store must use a generic data source that uses a non-XA JDBC driver. For more information about this configuration option, see Using Multi Data Sources without Global Transactions.

A JDBC Store holds on to a connection until that connection fails, at which point it moves on to the next connection and repeats the process. Therefore you cannot implement load balancing with a JDBC Store, including using a load balancing multi data source. You should configure a multi data source for a JDBC store to use the Failover algorithm.

In short, for a JDBC store:

  • Use a non-XA driver

  • Configure the multi data source for Failover mode.

Automatic Retry for JMS Connections

JMS has a limited connection retry mechanism which enables it to silently react to the failure of the Oracle RAC node that hosts its database connection. If the database has experienced either a minor network 'hiccup' or a Oracle RAC database has failed over to another node, the second connection attempt (the retry) will succeed to the next Oracle RAC node.

The time within which this retry is attempted and the number of retries attempted are limited to minimize the negative effects that an extended connection retry time could cause. If the database connection remains unavailable for a long period of time, the delay can impede the ability of JMS to properly continue its processing (for example, to maintain proper message ordering). Also, the transaction manager could declare the JMS resource of a transaction to be dead if there is not enough processing progress made within this time period, or out-of memory conditions could arise. There are system-level tuning guidelines that can help minimize the Oracle RAC failover time frame which is critical to the success of the automatic retry.

The tight loop on the automatic retry is particularly important when JMS processing occurs with transactions. If an I/O failure occurs in the JDBC Store, the store record is in an unknown state which will put the message itself in an unknown state. To prevent the message from being committed in this unknown state, JMS will mark the transaction associated with the message as a "failedTransaction." Any future attempts by the transaction manager to finishing committing the message will cause JMS to throw a javax.transaction.xa.XAException with an errorCode set to XAException.XAER_RMERR. This exception is an indication to the transaction manager that a transient error has occurred in the resource manager (JMS) and that the transaction manager should retry commit processing. The retry logic provides a second attempt to establish the connection before JMS communicates any failure to the upper layer which would translate into an RMERR. If the RMERR is generated, then the only way to recover the message and complete the transaction is to either restart WebLogic Server or configure Automatic Service Migration (ASM) restart-in-place option for Singleton Services. Otherwise, when the I/O fails, the transaction is marked in a way that cannot be recovered until the JMS server is restarted.

The automatic connection retry logic is currently governed by an option on WebLogic Server as follows:

-Dweblogic.store.jdbc.IORetryDelayMillis=x

Where x is the number of milliseconds to elapse before the connection to the database is retried. The default value is 1000 milliseconds. This value is restricted to the range 0 to 15000 milliseconds, and the retry is only be attempted once. If a failure occurs on the second attempt, an exception is propagated up the call stack and a manual restart is required to recover the messages associated with the failed transaction.

Note:

In the event that an automatic retry attempt is not successful, you must restart WebLogic Server. Automatic Service Migration (ASM) restart-in-place option for Singleton Services can be used to trigger an automatic restart of failed JMS Services.

The automatic retry delay only applies to the connection retry mechanism. There is no configurable retry delay available for JDBC Store I/O failures.