This chapter describes considerations for high availability Oracle database access.
The sections in this chapter are as follows:
Section 4.1, "Oracle Real Application Clusters and Fusion Middleware"
Section 4.2, "Protecting Idle Connections from Firewall Timeouts"
Section 4.4, "Using SCAN Addresses with Oracle Database 11g (11.2)"
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:
Section 4.1.1, "Java-Based Oracle Fusion Middleware Components Deployed to Oracle WebLogic Server"
Section 4.1.4, "Configuring GridLink Data Sources with Oracle RAC"
Section 4.1.5, "Configuring Multi Data Sources with Oracle RAC"
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.
A GridLink data source includes the features of generic data sources plus the following support for Oracle RAC:
Single Client Access Name (SCAN) Addresses
Oracle provides GridLink data sources and multi data sources to support high availability, load balancing, and failover of database connections. Oracle recommends the following data source types depending on the Oracle RAC Database version you have:
If you use Oracle RAC database version 11g Release 2 and later, use GridLink data sources.
If you use an Oracle RAC database version earlier than 11g Release 2 or a non-Oracle database, use multi data sources.
Note:
Oracle recommends using the GridLink data sources with Oracle RAC database for maximum availability. For versions of Oracle RAC databases where GridLink data sources are not supported, Oracle recommends using multi data sources for high availability.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:
When you deploy Oracle Fusion Middleware against an Oracle RAC back end 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
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.
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:
Log on to SQL*Plus as a system user, for example:
sqlplus "/ as sysdba"
Grant select on sys.dba_pending_transactions
to public
.
Grant run on sys.dbms_xa
to public
.
Grant force any transaction to user
.
Note:
Ensure that thedistributed_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.
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:
Log on to SQL*Plus as a user with sysdba privileges. For example:
sqlplus "/ as sysdba"
Grant select on sys.dba_pending_transactions to the appropriate_user.
Grant force any transaction to the appropriate_user.
Note:
Grant these privileges to the soainfra schema owner, as determined by the RCU operations.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.
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.
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.
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:
Access the data source configuration.
Select the Transaction tab.
Set XA Transaction Timeout to a larger value, for example, 300.
Select the Set XA Transaction Timeout checkbox. You must select this checkbox for the new XA transaction timeout value to take effect.
Click Save.
Repeat this configuration for all individual data sources of an XA multi data source.
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.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.
To configure an Oracle Virtual Directory database adapter for an Oracle RAC database using the Oracle Directory Services Manager:
In the Connection screen, select Use Custom URL from the URL Type list.
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:
In the Connection screen, for JDBC Thin, specify the database adapter parameter oracleNetConnectTimeout for the timeout parameter in seconds.
For JDBC OCI, specify TCP.CONNECT_TIMEOUT=n
in the sqlnet.ora in ORACLE_INSTANCE/config
directory.
Oracle Fusion Middleware 11g includes some non-Java components, such as Oracle Internet Directory. 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.
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.
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.
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-4 describes supported scenarios when you configure against RAC:
Table 4-4 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. |