4 Considerations for High Availability Oracle Database Access

This chapter describes considerations for high availability Oracle database access.

The sections in this chapter are as follows:

4.1 Oracle Real Application Clusters and Fusion Middleware

Most Fusion Middleware components use a database as the persistent store for their data. You can configure the Oracle database back end in any number of high availability configurations, including Cold Failover Clusters, Oracle Real Application Clusters, Oracle Data Guard, or Oracle Streams. For more information, see the Oracle Database High Availability Overview. This chapter describes considerations for Oracle Fusion Middleware configured with a high availability Oracle database, Oracle Real Application Clusters.

Oracle Real Application Cluster (Oracle RAC) is a computing environment that harnesses the processing power of multiple, interconnected computers. Along with a collection of hardware (cluster), it unites the processing power of each component to become one robust computing environment. Oracle RAC simultaneously provides a highly scalable and highly available database for Oracle Fusion Middleware.

Every Oracle RAC instance in the cluster has equal access and authority, therefore, node and instance failure may affect performance, but doesn't result in downtime; the database service is available or can be made available on surviving server instances.

For more information on Oracle RAC, see the Oracle Real Application Clusters Administration and Deployment Guide.

Oracle Fusion Middleware provides the best integration with an Oracle database in a high availability environment. When Oracle Fusion Middleware behaves as a client for the database (either as a java or system client) it uses special communication and monitoring capabilities that provide fast failover and minimal middle tier disruption in reaction to database failure scenarios.

You can categorize Oracle Fusion Middleware components that access the database as follows:

  • Java-based components deployed to Oracle WebLogic Server

  • Java-based components that are standalone Java Clients

  • Non-Java components

This chapter contains the following sections:

4.1.1 Java-Based Oracle Fusion Middleware Components Deployed to Oracle WebLogic Server

All Oracle Fusion Middleware components deployed to Oracle WebLogic Server support Oracle RAC. For establishing connection pools, Oracle Fusion Middleware supports GridLink data sources and multi data sources for the Oracle RAC back end for both XA and non-XA JDBC drivers. Oracle Fusion Middleware deployments do not support other connection failover features supported by Oracle JDBC drivers for Oracle RAC. See component specific guides for multi data source configuration details.

When an Oracle RAC node or instance fails, WebLogic Server or the Oracle Thin driver redirect session requests to another node in the cluster. There is no failover of existing connections, however, new connection requests from the application are managed using existing connections in the Oracle WebLogic pool or by new connections to the working Oracle RAC instance. When the database is the transaction manager, in-flight transactions typically roll back. When the WebLogic Server is the transaction manager, in-flight transactions fail over; they are driven to completion or rolled back based on the transaction state when failure occurs. If the application requires load balancing across Oracle RAC nodes, WebLogic Server supports this capability by using JDBC GridLink data sources or JDBC multi data sources configured for load balancing. See Run Time Connection Load Balancing in the Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server guide for more information on GridLink load balancing.

4.1.2 GridLink Data Sources and Oracle RAC

A GridLink data source includes the features of generic data sources plus the following support for Oracle RAC:

See Using GridLink Data Sources in the Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server guide for more information on the following topics:

4.1.3 Using Multi Data Sources with Oracle RAC

When you deploy Oracle Fusion Middleware against Oracle RAC back ends it is configured out of the box with multi data sources. The multi data sources have constituent data sources for each RAC instance providing the database service. Oracle recommends that you add an additional data source to the multi data source on the Fusion Middleware tier when you configure additional RAC instances that offer the database service. Ensure that each constituent data source that you create for the multi data source are configured identically for properties in Section 4.1.5, "Configuring Multi Data Sources with Oracle RAC."

When you migrate the database from a non-RAC to a RAC database, you must create an equivalent, new multi data source for each data source that is affected. The multi data source that you create must have consistent data sources for each RAC instance. The data source values must be identical to the original single instance data source for the properties in Section 4.1.5, "Configuring Multi Data Sources with Oracle RAC." For example, if the single instance data source driver is oracle.jdbc.xa.client.OracleXADataSource, it must be oracle.jdbc.xa.client.OracleXADataSource for each constituent data source of the new multi data source.

Figure 4-1 Multi Data Source Configuration

Multi Data Source Configuration
Description of "Figure 4-1 Multi Data Source Configuration"

4.1.3.1 Configuring Multi Data Sources for MDS Repositories

Applications that use an MDS database-based repository can be configured for high availability Oracle database access. With this configuration, failure detection, recovery, and retry by MDS, as well as by the WebLogic infrastructure, result in the application's read-only MDS operations being protected from Oracle RAC database planned and unplanned downtimes.

Multi data sources are exposed as MDS repositories in the Fusion Middleware Control navigation tree. These multi data sources can be selected during deployment plan customization of application deployment, and can be used with MDS WLST commands.

  • Configuring an application to retry read-only operations

    To configure an application to retry the connection, you can configure the RetryConnection attribute of the application's MDS AppConfig MBean. See the Oracle Fusion Middleware Administrator's Guide for more information.

  • Registering an MDS multi data source

    In addition to the steps specified in Section 4.1.5, "Configuring Multi Data Sources with Oracle RAC," consider the following:

    • The child data sources that constitute a multi data source used for an MDS repository must be configured as non-XA data sources.

    • The multi data source's name must be pre-fixed with mds-. This ensures that the multi data source can be recognized as an MDS repository that can be used for MDS management functionality through Fusion Middleware Control, WLST, and JDeveloper.

      Note:

      When an MDS data source is added as a child of a multi data source, this data source is no longer exposed as an MDS repository. For example, it does not appear under the Metadata Repositories folder in the Fusion Middleware Control navigation tree, you cannot perform MDS repository operations on it, and it does not appear in the list of selectable repositories during deployment.

  • Converting a data source to a multi data source

    There are two considerations when converting an data source to a multi data source to make sure the application is configured correctly:

    • To create a new multi data source with a new, unique name, redeploy the application and select this new multi data source as the MDS repository during deployment plan customization.

    • To avoid redeploying the application, you can delete the data source and recreate the new multi data source using the same name and jndi-name attributes.

4.1.3.2 Oracle RAC Configuration Requirements

This section describes requirements for Oracle RAC configuration:

  • XA Requirements: Many Oracle components participate in distributed transactions, or are part of container managed transactions. These components require the back-end database setup for XA recovery by Oracle WebLogic Transaction Manager. For repositories created using RCU, this is done automatically. For other databases participating in XA transactions, ensure that XA pre-requisites are met:

    1. Log on to SQL*Plus as a system user, for example:

      sqlplus "/ as sysdba"

    2. Grant select on sys.dba_pending_transactions to public.

    3. Grant run on sys.dbms_xa to public.

    4. Grant force any transaction to user.

      Note:

      Ensure that the distributed_lock_timeout parameter for the Oracle database is set to a value higher that the JTA timeout. It should be higher than the highest value on the middle tier - between the default for WebLogic Server, a specific configuration for a data source, or one used by a component for a transaction.)

  • Server-side Load Balancing: If the server-side load balancing feature is enabled for the Oracle RAC back end (using remote_listeners), the JDBC URL used in the data sources of a multi data source configuration should include the INSTANCE_NAME. For example, you can specify the URL in the following format:

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

    By default, the out-of-box installation assumes that remote_listener has been configured and creates the URL for data sources in a multi data source accordingly. Any multi data source created outside of the typical installation and configuration should follow the format described in this section.

    If remote_listeners cannot be specified on the Oracle RAC side, and server side load balancing has been disabled, specifying the INSTANCE_NAME in the URL is not necessary. To disable remote listeners, delete any listed remote listeners in spfile.ora file on each Oracle RAC node. For example:

    *.remote_listener="

    In this case, the recommended URL that you use in the 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)))

  • Services: When configuring Oracle Fusion Middleware for the Oracle database and specifically for Oracle RAC, Oracle recommends using the Oracle Services feature. Create the service_name provided as part of the database service location specifically for the application.

4.1.3.3 Configuring Schemas for Transactional Recovery Privileges

You need the appropriate database privileges to enable the Oracle WebLogic Server transaction manager to query for transaction state information and issue the appropriate commands, such as commit and rollback, during recovery of in-flight transactions after a WebLogic Server container failure.

To configure the schemas for transactional recovery privileges:

  1. Log on to SQL*Plus as a user with sysdba privileges. For example:

    sqlplus "/ as sysdba"

  2. Grant select on sys.dba_pending_transactions to the appropriate_user.

  3. Grant force any transaction to the appropriate_user.

    Note:

    Grant these privileges to the soainfra schema owner, as determined by the RCU operations.

4.1.4 Configuring GridLink Data Sources with Oracle RAC

How you configure a GridLink data source depends on the Oracle component that you are working with and the domain you are creating. For detailed procedures, go to the section that describes the component type you are working with. For example, for SOA, see Section 5.13.20.2, "Creating a GridLink or Multi Data Source Using the Oracle WebLogic Administration Console".

Note:

Oracle recommends that you use Oracle Single Client Access Name (SCAN) addresses to specify the host and port for both the TNS listener and the ONS listener in the WebLogic console. You do not need to update a GridLink data source containing SCAN addresses if you add or remove Oracle RAC nodes. Contact your network administrator for appropriately configured SCAN URLs for your environment. See SCAN Addresses in the Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server guide.

For a generic overview of how to configure a GridLink data source, see Creating a GridLink Data Source in the Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server guide.

4.1.5 Configuring Multi Data Sources with Oracle RAC

You can configure multi data sources using the following:

  • Oracle Fusion Middleware Configuration Wizard during WebLogic Server domain creation

  • Oracle Universal Installer Java EE component configuration for Identity Management or Oracle Portal, Forms, Reports, and Discoverer.

  • Oracle WebLogic Server Administration Console

  • WLST Commands

Multi data sources support load balancing for both XA and non-XA data sources, including all Oracle database versions that Oracle Fusion Middleware components support.

Multi data sources encapsulate individual data sources that pool connections to specific instances of Oracle RAC. For multi data sources created manually, or modified after initial configuration, Oracle strongly recommends the following XA and Non-XA data source property values for optimal high availability behavior. Make changes only after careful consideration and testing if your environment requires that you do so:

Table 4-1 Recommended Multi Data Source Configuration

Property Name Value

test-frequency-seconds

5

algorithm-type

Load-Balancing

For individual data sources, Oracle recommends the following for high availability environments. Oracle recommends that you set any other parameters according to application requirements.

Table 4-2 XA Data Source Configuration

Property Name Value

Driver

oracle.jdbc.xa.client.OracleXADataSource

Property command

<property>

<name>oracle.net.CONNECT_TIMEOUT</name>

<value>10000</value>

</property>

initial-capacity

0

connection-creation-retry-frequency-seconds

10

test-frequency-seconds

300

test-connections-on-reserve

true

test-table-name

SQL SELECT 1 FROM DUAL

seconds-to-trust-an-idle-pool-connection

0

global-transactions-protocol

TwoPhaseCommit

keep-xa-conn-till-tx-complete

true

xa-retry-duration-seconds

300

xa-retry-interval-seconds

60

Table 4-3 Non-XA Data Source Configuration

Property Name Value

Driver

oracle.jdbc.OracleDriver

Property to set

<property>

<name>oracle.net.CONNECT_TIMEOUT</name>

<value>10000</value>

</property>

initial-capacity

0

connection-creation-retry-frequency-seconds

10

test-frequency-seconds

300

test-connections-on-reserve

true

test-table-name

SQL SELECT 1 FROM DUAL

seconds-to-trust-an-idle-pool-connection

0

global-transactions-protocol

None

For examples of recommended multi data sources, see Appendix B, "Recommended Multi Data Sources."

Increasing Transaction Timeout for XA Data Sources

If you see WARNING messages in the server logs that include the following exception:

javax.transaction.SystemException: Timeout during commit processing

[ javax.transaction.SystemException: Timeout during commit processing

This message may indicate the XA timeout value you have in your setup must be increased. You can increase XA timeout for individual data sources when these warnings appear.

To increase this setting, use Administration Console:

  1. Access the data source configuration.

  2. Select the Transaction tab.

  3. Set XA Transaction Timeout to a larger value, for example, 300.

  4. Select the Set XA Transaction Timeout checkbox. You must select this checkbox for the new XA transaction timeout value to take effect.

  5. Click Save.

Repeat this configuration for all individual data sources of an XA multi data source.

4.1.6 JDBC Clients

Java J2SE-based Oracle Fusion Middleware components are optimized to work with the high availability features of Oracle RAC. You can deploy the components to use both the Oracle thin JDBC driver or the OCI based JDBC drivers.

The JDBC Thin client is a pure Java, Type IV driver. It is lightweight, easy to install and provides high performance, comparable to the performance of the JDBC Oracle Call Interface (OCI) driver. The JDBC Thin driver communicates with the server using TTC, a protocol developed by Oracle to access data from Oracle database. The driver enables a direct connection to the database by providing an implementation of TCP/IP that implements Oracle Net and TTC on top of Java sockets. The JDBC OCI client is a Type II driver and provides connections to JDBC clients over the Oracle Net. It uses the client side installation of Oracle Net and a deployment can customize behavior using Oracle Net configuration on the middle tier.

Note:

These JDBC clients are used as part of standalone Java J2SE programs.

Oracle Virtual Directory

When used with database adapters, Oracle Virtual Directory connects to a database, and the connections are not pooled. For details about configuring database adapters for Oracle RAC, see "Creating Database Adapters" in the Oracle Fusion Middleware Administrator's Guide for Oracle Virtual Directory.

Database URL

To configure an Oracle Virtual Directory database adapter for an Oracle RAC database using the Oracle Directory Services Manager:

  1. In the Connection screen, select Use Custom URL from the URL Type list.

  2. In the Database URL field, enter the URL to connect to the Oracle RAC database, for example:

    JDBC Thin

    jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_
BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=host-name-1)(PORT=1521))(ADDRESS=
(PROTOCOL=TCP)(HOST=host-name-2)(PORT=1521)))(CONNECT_
DATA=(SERVER=DEDICATED)(SERVICE_NAME=database-service-name)))

    JDBC OCI

    jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_
BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=host-name-1)(PORT=1521))(ADDRESS=
(PROTOCOL=TCP)(HOST=host-name-2)(PORT=1521)))(CONNECT_
DATA=(SERVER=DEDICATED)(SERVICE_NAME=database-service-name)))

Connection Timeout Configuration

To configure the connection timeout for an Oracle RAC database using the Oracle Directory Services Manager:

  1. In the Connection screen, for JDBC Thin, specify the database adapter parameter oracleNetConnectTimeout for the timeout parameter in seconds.

  2. For JDBC OCI, specify TCP.CONNECT_TIMEOUT=n in the sqlnet.ora in ORACLE_INSTANCE/config directory.

4.1.7 System Clients

Oracle Fusion Middleware 11g includes some non-Java components. These components are primarily C-based and include Oracle Internet Directory (OID), Oracle Forms, Oracle Reports, Oracle Discoverer, and Oracle Portal. These components use the Oracle Call Interface layer to interact with Oracle databases. For Oracle RAC-based systems, some components integrate with the Oracle high availability Event Notification database feature.

High availability Event Notification provides a signal to the non-Java application if database failure occurs. The applications can register a callback on the environment to monitor the database connection. When a database failure related to the non-Java client occurs, the callback is invoked. This callback contains information about the database failure, including the event payload, and a list of connections (server handles) that were disconnected as a result of the failure.

If another instance, for example, instance C, of the same database, goes down, the client is not notified, since it does not affect any of the client's connections.

High availability Event Notification improves the application response time if database failure occurs. Without Event Notification, database failure would result in the connection being broken only after the TCP time out expired, which could take minutes. With high availability Event Notification, OCI automatically breaks and cleans up standalone, connection pool, and session pool connections and the application callback is invoked within seconds of failure. If any server handles are TAF-enabled, OCI automatically engages failover.

The following section describes the recommended setting for non-Java client connections to Oracle RAC databases.

4.1.7.1 Oracle Internet Directory

Oracle Internet Directory integrates with high availability Event Notification. Oracle recommends using the Oracle Enterprise Manager Cluster Managed Services Page to create database services that client applications use to connect to the database.

You can also use SQL*Plus to configure your Oracle RAC database service.

To enable high availability event motivation for an Oracle RAC database connection:

  1. Set the AQ_HA_NOTIFICATIONS attribute to TRUE and server-side Transparent Application Failover (TAF) settings are enabled. The failover retries and failover delay can be adjusted based on the requirements of the deployment. So for the database service used by OID, Oracle recommends setting Oracle RAC DBMS_SERVICE property values according to Table 4-4.

    Table 4-4 OID Database Services Property Settings

    Property Name Value

    AQ_HA_NOTIFICATIONS

    TRUE

    FAILOVER_METHOD

    DBMS_SERVICE.FAILOVER_METHOD_BASIC

    FAILOVER_TYPE

    DBMS_SERVICE.FAILOVER_TYPE_SELECT

    FAILOVER_RETRIES

    5

    FAILOVER_DELAY

    5

  2. Oracle also recommends setting TCP connect timeouts for the Oracle Net configuration. To configure this setting, specify TCP.CONNECT_TIMEOUT=n in the sqlnet.ora file in the ORACLE_INSTANCE/config directory.

4.1.7.2 Oracle Forms

Oracle Forms also integrates with high availability event notification. To enable this feature for Oracle Forms:

  1. Use the Oracle Enterprise Manager Cluster Managed Services Page to create database services. For Oracle Forms, set the Oracle RAC DBMS_SERVICE property values according to Table 4-5. The following is recommended to be set using the package of Oracle database.

    Table 4-5 Oracle Forms Database Services Property Settings

    Property Name Value

    AQ_HA_NOTIFICATIONS

    TRUE

    FAILOVER_METHOD

    DBMS_SERVICE.FAILOVER_METHOD_NONE

    FAILOVER_TYPE

    DBMS_SERVICE.FAILOVER_TYPE_NONE

  2. Oracle also recommends setting TCP connect timeouts for the Oracle Net configuration. To configure this setting, specify TCP.CONNECT_TIMEOUT=n in the sqlnet.ora file in the ORACLE_INSTANCE/config directory.

4.1.7.3 Oracle Portal

To configure Oracle Portal for optimal behavior in a high availability environment, set TCP connect timeouts for the Oracle Net configuration. To configure this setting, specify TCP.CONNECT_TIMEOUT=n in the sqlnet.ora file in the ORACLE_INSTANCE/config directory.

Oracle Portal also uses the death detection feature of mod plsql.

mod_plsql maintains a pool of connections to the database, and reuses established database connections for subsequent requests. If there is no response from a database connection in a connection pool, mod_plsql detects this, discards the dead connection, and creates a fresh database connection for subsequent requests.

The dead database connection detection feature of mod_plsql eliminates the occurrence of random errors when a database node or instance goes down. This feature is also extremely useful in high availability configurations, such as Oracle RAC. If a node in an Oracle RAC cluster goes down, mod_plsql detects this and immediately starts servicing requests using the other Oracle RAC nodes.

By default, when an Oracle RAC node or database instance goes down and mod_plsql had previously pooled connections to the node, the first mod_plsql request which uses a dead connection in its pool results in a failure response (HTTP-503) being sent back to the end-user. mod_plsql uses this failure to trigger the detection and removal of all dead connections in its pool. mod_plsql pings all connection pools that were created before the node failure. This ping operation is performed at the time of processing the next request that uses a pooled connection. If the ping operation fails, the database connection is discarded and a new connection is created and processed.

Note:

If, after node failure, multiple mod_plsql requests come in concurrently and mod_plsql has not yet detected the first dead connection, there may be multiple failures at that instant.

mod_plsql provides two configuration options for tuning the dead database connection detection feature:

  • Specifying the Option to Detect Dead Database Connections

  • Specifying the Connection Validation and Timeout Period

Specifying the Option to Detect Dead Database Connections.

mod_plsql corrects connections after it detects a failure that could be caused by a database node going down. The PlsqlConnectionValidation parameter controls this.

Setting PlsqlConnectionValidation to Automatic (default) makes the mod_plsql module test all pooled database connections that were created before a failed request.

Setting PlsqlConnectionValidation to AlwaysValidate makes mod_plsql test all pooled database connections before issuing any request. Although the AlwaysValidate configuration option ensures greater availability, it also introduces additional performance overhead.

You can specify the timeout period for mod_plsql to test a bad database connection in a connection pool. The PlsqlConnectionTimeout parameter, which specifies the maximum time mod_plsql should wait for the test request to complete before it assumes that a connection is not usable. For more on this parameter, see the mod_plsql section in the Oracle Fusion Middleware Administrator's Guide for Oracle HTTP Server.

Specifying the Connection Validation and Timeout Period

When the PlsqlConnectionValidation parameter is set to Automatic or AlwaysValidate, mod_plsql tests pooled database connections.

You can specify the timeout period for mod_plsql to test a bad database connection in a connection pool. This is controlled by the PlsqlConnectionTimeout parameter, which specifies the maximum time mod_plsql should wait for the test request to complete before it assumes that a connection is not usable.

For details on the PlsqlConnectionTimeout, PlsqlConnectionValidation, and PlsqlConnectionTimeout parameters, refer to the "mod_plsql" section in the Oracle Fusion Middleware Administrator's Guide for Oracle HTTP Server.

4.1.7.4 Oracle Reports and Oracle Discoverer

To configure Oracle Reports and Oracle Discovery for optimal behavior in a high availability environment, set TCP connect timeouts for the Oracle Net configuration. To configure this setting, specify TCP.CONNECT_TIMEOUT=n in the sqlnet.ora file in the ORACLE_INSTANCE/config directory.

Oracle Discoverer also uses a TNS entry to connect to the Oracle RAC database:

frdisco = (DESCRIPTION = (LOAD_BALANCE = ON) (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stajo05-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = stajo06-vip)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = orcl.example.com)))

Note:

When Oracle Discoverer is configured to be connected to a customer Oracle RAC database, Oracle recommends using the following TNS connect string:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host1-vip)(PORT=1521))
 (ADDRESS=(PROTOCOL=TCP)(HOST=host2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAM
E=orcl)(FAILOVER_MODE=(TYPE=select)(METHOD=basic)(RETRIES=20)(DELAY=15))))

4.2 Protecting Idle Connections from Firewall Timeouts

Because most production deployments involve firewalls and database connections are made across firewalls, Oracle recommends configuring the firewall not to timeout the database connection. For Oracle RAC case, this specifically means not timing out the connections made on Oracle RAC VIPs and the database listener port.

If such a configuration is not possible, on the database server side, set SQLNET.EXPIRE_TIME=n in ORACLE_HOME/network/admin/sqlnet.ora. For Oracle RAC, this needs to be set on all the Oracle Homes. The n is in minutes. It should be set to less than the known value of the network device (firewall) timeout. Since the order of these times is normally more than ten minutes, and in some cases hours, the value should be set to the highest possible value.

4.3 Troubleshooting

If an Oracle RAC instance goes down, WebLogic Server determines the database status using a SELECT 1 FROM DUAL query. This query typically takes less than a few seconds to complete. However, if the database response is slow, WebLogic Server gives up and assumes the database is unavailable. The following is an example of the type of exception that results in the logs:

<Mar 30, 2009 2:14:37 PM CDT> <Error> <JDBC> <BEA-001112> <Test "SELECT 1 FROM
 DUAL" set up for pool SOADataSource-rac1" failed with exception:
 oracle.jdbc.xa.OracleXAException".> [TopLink Warning]: 2009.03.30
 14:14:37.890--UnitOfWork(14568040)--Exception [TOPLINK-4002] (Oracle TopLink -
 11g Release 1 (11.1.1.1.0) (Build 090304)):
 oracle.toplink.exceptions.DatabaseException Internal Exception:
 java.sql.SQLException: Internal error: Cannot obtain XAConnection Creation of
 XAConnection for pool SOADataSource failed after waitSecs:30 :
 weblogic.common.ResourceException: SOADataSource(SOADataSource-rac1): Pool
 SOADataSource-rac1 has been @ disabled because of hanging connection tests,
 cannot allocate resources to applications. We waited 10938 milliseconds. A
 typical test has been taking 16.

You can set the WebLogic Server parameter, -Dweblogic.resourcepool.max_test_wait_secs=30 to increase the time WebLogic Server waits for a response from the database. This parameter is located in the setDomainEnv.sh file. By setting this parameter, WebLogic Server waits 30 seconds for the database to respond to the SELECT 1 FROM DUAL query before giving up.

4.4 Using SCAN Addresses with Oracle Database 11g (11.2)

If your 11.2 RDBMS Oracle RAC database is not configured with Single Client Access Name (SCAN), you can provide details of the Oracle RAC instances in the Configuration Wizard and Oracle Universal Installer, just as you entered them for previous database releases. The instance address is in the host:port format.

If your 11.2 RDBMS Oracle RAC database is configured with SCAN, provide Oracle RAC instance details with the SCAN address. In Fusion Middleware wiring to an Oracle RAC instance, each Oracle RAC instance is uniquely identified using the service name, instance name, host, and port. Because the host:port address of all such instances is the SCAN host:port, Oracle recommends that you use this same common address for all instances.

Follow these guidelines based on the installation type:

  • In RCU installations against an Oracle RAC database, specify the hostname as scan-hostname-address.

  • In Oracle Universal Installer installations, specify the following for Oracle RAC databases depending on the input format Oracle Universal Installer requires.

    scan-address-hostname:port:instance1^scan-address-hostname:port:instance2@servicename

    or:

    scan-address-hostname:port^scan-address-hostname:port@servicename

  • In Configuration Wizard installations that use a multi data source, the scan-address-hostname,port,service-name must be the same for each constituent data source. The instance names must be specific for each constituent data source and targeted to the Oracle RAC end instances.

    GridLink with SCAN does not use an instance name. The following example shows a GridLink connection string that does not use the RAC instance:

    jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<scan-host-name>)(PORT=<scan-port>)))(CONNECT_DATA=(SERVICE_NAME=rac-service-name)))

  • If the connect string is specified explicitly, use the following base format:

    (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=scan-hostname-address)
(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=service-name))) when the whole Oracle
RAC database needs to be specified 

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=scan-hostname-address)
(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=service-name)(INSTANCE_NAME=inst1)))
when a specific Oracle RAC instance needs to be specified

Note:

For more information about SCAN, see Single Client Access Name (SCAN) for the Cluster in the Oracle Grid Infrastructure Installation Guide and SCAN Addresses for Simplified Client Access in the Oracle Real Application Clusters Installation Guide.

SCAN Run Time Implications and Limitations

Table 4-6 describes supported scenarios when you configure against RAC:

Table 4-6 SCAN Run Time Implications and Limitations

Scenario Description High Availability Run time Outcome and Limitations

1. Non SCAN

Multi data source with each subordinate multi data source pointing to a separate RAC instance

Gives run time High Availability of the database connections, which the WLS multi data source implementation manages.

2. SCAN

Multi data source with each subordinate data source pointing to the SCAN address

Gives run time High Availability of the database connections, which the WLS multi data source implementation manages.

Limitation: Even if you reference a SCAN address, you are using the limited High Availability features of the WLS multi data source.

3. SCAN

A single data source pointing to the SCAN address.

Does not give runtime High Availability of the database Connections.

Limitation: A SCAN address virtualizes the entry point to the RAC instances, however, if you specify a single Data Source on WLS, doing so does not provide High Availability. The reason for this is that each server is effectively bound to a single RAC instance.

4. SCAN

GridLink data source pointing to the SCAN address

You must have the correct database version that supports SCAN and set up ONS correctly, using the FAN enabled setting to receive the ONS status messages that the database sends.