5 Database Considerations

As you configure database connections for Oracle Fusion Middleware in a high availability setup, you must make decisions about Oracle Real Application Clusters (Oracle RAC).

Oracle RAC is a commonly-deployed database high availability solution. Most components use a database as the persistent store for their data. When you use an Oracle database, you can configure it in a variety of highly available configurations.

For more on database options, see Oracle Database High Availability Overview.

About Oracle Real Application Clusters

A cluster comprises multiple interconnected computers or servers that appear as one server to end users and applications. With Oracle RAC, you can cluster an Oracle database so that it is highly scalable and highly available.

All Oracle Fusion Middleware components that you deploy to Oracle WebLogic Server support Oracle RAC.

Every Oracle RAC instance in a cluster has equal access and authority. 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.

Roadmap for Setting Up Oracle Real Application Clusters

Use this roadmap to set up Oracle RAC.

Table 5-1 outlines tasks and information to set up Oracle RAC.

Table 5-1 Roadmap for Setting up Oracle RAC

Task/Topic More Information

About Oracle RAC

Introduction to Oracle RAC in Oracle Real Application Clusters Administration and Deployment Guide

Installing Oracle RAC

Oracle Real Application Clusters Administration and Deployment Guide

Managing Oracle RAC

Overview of Managing Oracle RAC Environments in Oracle Real Application Clusters Administration and Deployment Guide

Configuring and tuning GridLink and multi data sources

Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server

Configuring Single Client Access Name (SCAN) URLs. (To specify the host and port for TNS and ONS listeners in WebLogic console.)

SCAN Addresses in Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

About RAC Database Connections and Failover

To establish connection pools, Oracle Fusion Middleware supports Active GridLink data sources and multi data sources for the Oracle RAC back end (for both XA and non-XA JDBC drivers). These data sources also support load balancing across Oracle RAC nodes.

If an Oracle RAC node or instance fails, WebLogic Server or Oracle Thin JDBC driver redirects session requests to another node in the cluster. Existing connections don't failover. However, new application connection requests are managed using existing connections in the WebLogic pool or by new connections to the working Oracle RAC instance.

If the database is the transaction manager, in-flight transactions typically roll back.

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

For more on XA Transactions, see About XA Transactions.

About XA Transactions

XA transaction support enables access to multiple resources (such as databases, application servers, message queues, transactional caches) within one transaction.

A non-XA transaction always involves just one resource.

An XA transaction involves a coordinating transaction manager with one or more databases, or other resources such as JMS, all involved in a single global transaction.

Java EE uses the terms JTA transaction, XA transaction, user transaction, and global transaction interchangeably to refer to one global transaction. This transaction type may include operations on multiple different XA- capable or non-XA resources and even different resource types. A JTA transaction is always associated with the current thread, and may pass from server to server as one application calls another. A common example of an XA transaction is one that includes both a WebLogic JMS operation and a JDBC (database) operation.

About Data Sources

A data source is an abstraction that components use to obtain connections to a relational database.

Connection information, such as the URL or user name and password, is set on a data source object as properties. The application's code does not need to explicitly define the properties. Due to this abstraction, you can build applications in a portable manner, because they are not tied to a specific back-end database. The database can change without affecting application code.

Active GridLink data sources and multi data sources support database connection high availability, load balancing, and failover. Oracle recommends the following data source types depending on your Oracle RAC database version:

  • If you use Oracle RAC database version 11g Release 2 and later, use Active 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 Active GridLink data sources with Oracle RAC database for maximum availability. For Oracle RAC database versions that don't support Active GridLink data sources, Oracle recommends using multi data sources for high availability.

Active GridLink Data Sources

An Active GridLink data source provides connectivity between WebLogic Server and an Oracle database service, which may include multiple Oracle RAC clusters.

An Active GridLink data source has features of generic data sources, plus the following support for Oracle RAC:

  • Uses the ONS to respond to state changes in an Oracle RAC.

  • Responds to Fast Application Notification (FAN) events to provide Fast Connection Failover (FCF), Runtime Connection Load-Balancing, and RAC instance graceful shutdown. FAN is a notification mechanism that Oracle RAC uses to quickly alert applications about configuration and workload.

  • Provides Affinities (or XA Affinity) policies to ensure all database operations for a session are directed to the same instance of a RAC cluster for optimal performance.

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

Multi Data Sources

A multi data source is an abstraction around a group of data sources that provides load balancing or failover processing. Multi data sources support load balancing for both XA and non-XA data sources.

A failover multi data source provides an ordered list of data sources to fulfill connection requests. Normally, every connection request to this kind of multi data source is served by the first data source in the list.

A load-balancing multi data source chooses from a circular list of datasources in a round robin method. The stream of incoming connection requests is spread evenly around the datasources. If a database connection test fails and the connection can't be replaced, or if the data source is suspended, a connection is sought from the next data source on the list.

Multi data sources are bound to the JNDI tree or local application context just like regular data sources. Applications look up a multi data source on the JNDI tree or in the local application context (java:comp/env) just as they do for data sources, and then request a database connection. The multi data source determines which data source to use to satisfy the request depending on the algorithm selected in the multi data source configuration: load balancing or failover.

Note:

To configure Multi Data Sources with Oracle RAC, see Using Multi Data Sources with Oracle RAC in Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

Configuring Active GridLink Data Sources with Oracle RAC

You configure component data sources as Active GridLink data sources for a RAC database during domain creation.

How you configure an Active GridLink data source depends on:

  • The Oracle component that you are working with

  • The domain you are creating

Note:

To create and configure Active GridLink data sources, see Using Active GridLink Data Sources in Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

Requirements to Configure Component Data Sources as Active Gridlink Data Sources

Your system must meet certain requirements before you configure component data sources as Active GridLink data sources to use with an Oracle RAC database.

  • You use Oracle RAC database version 11g Release 2 or later.

  • You have run RCU to create component schemas.

  • You use the Configuration Wizard to create or configure a domain and have arrived at the JDBC Component Schema screen where you select Component Datasources.

Configuring Component Data Sources as Active GridLink Data Sources

You configure component data sources as Active GridLink data sources for a RAC database during domain creation.

To configure component data sources as Active GridLink data sources:

  1. In the JDBC Component Schema screen, select one or more component schemas to configure GridLink data sources for.
  2. Select Convert to GridLink then select Next.
  3. In the GridLink Oracle RAC Component Schema screen, select one of the GridLink JDBC drivers.
  4. In the Service Name field, enter the service name of the database using lowercase characters. For example, mydb.example.com.
  5. In the Schema Owner field, enter the name of the database schema owner for the corresponding component.
  6. In the Schema Password field, enter the password for the database schema owner.
  7. In the Service Listener, Port, and Protocol field, enter the SCAN address and port for the RAC database being used. The protocol for Ethernet is TCP; for Infiniband it is SDP. Click Add to enter multiple listener addresses.

    You can identify the SCAN address by querying the appropriate parameter in the database using the TCP protocol:

    show parameter remote_listener
     
    NAME TYPE VALUE
    --------------------------------------------------------------------
    remote_listener string db-scan.example.com:1521
     

    You can also identify the SCAN address by using the srvctl config scan command. Use the command srvctl config scan_listener to identify the SCAN listener port.

  8. Select Enable FAN to receive and process FAN events. Enter one or more ONS daemon listen addresses and port information. Select Add to enter more entries.

    Note:

    Verify that the ONS daemon listen address(es) that you enter is valid. The domain creation process do not validate the address.

    To determine the Scan (ONS) port, use the RAC srvctl command on the Oracle Database server, as the following example shows:

    srvctl config nodeapps -s
    
    ONS exists: Local port 6100, remote port 6200, EM port 2016
    
  9. Select Enable SSL for SSL communication with ONS. Enter the Wallet File, which has SSL certificates, and the Wallet Password.
  10. Select Next. Verify that all connections are successful.

Note:

See:

  • JDBC Component Schema in Oracle Fusion Middleware Creating WebLogic Domains Using the Configuration Wizard for information about the JDBC Component Schema screen.

  • GridLink Oracle RAC Component Schema in Oracle Fusion Middleware Creating WebLogic Domains Using the Configuration Wizard for information about configuring component schemas.

  • Using Active GridLink Data Sources in Administering JDBC Data Sources for Oracle WebLogic Server for information on GridLink RAC data sources.

Using SCAN Addresses for Hosts and Ports

Oracle recommends using Oracle Single Client Access Name (SCAN) addresses to specify the host and port for the TNS listener and ONS listener in the WebLogic console.

You do not need to update an Active 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 Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

Configuring Multi Data Sources

There are multiple tools available to configure multi data sources.

  • Oracle Fusion Middleware Configuration Wizard (during WebLogic Server domain creation)

  • Oracle WebLogic Server Administration Console

  • WLST Commands

Configuring Multi Data Sources with Oracle RAC

Configuring Multi Data Sources with Oracle RAC has specific requirements and steps that you must complete.

Requirements to Configure Multi Data Sources with Oracle RAC

Verify that your system meets the following requirements before you configure component data sources as multi data sources to use with an Oracle RAC database.

  • You are using an Oracle RAC database.

  • You have run RCU to create component schemas.

  • You are using the Configuration Wizard to create or configure a domain and have arrived at the JDBC Component Schema Screen where you select Component Schemas. Before you arrive at the JDBC Component Schema screen, you must select the option Manual Configuration in the Database Configuration Type screen.

Configuring Component Data Sources as Multi Data Sources

When you configure component data sources as multi data sources, you select data sources to convert then enter database information.

To configure component data sources as multi data sources:

  1. In the Component Datasources screen, select one or more component schemas to configure RAC Multiple data sources for.
  2. Select Convert to RAC multi data source then select Next.
  3. In the Oracle RAC Multi Data Source Component Schema screen, the JDBC driver Oracle's Driver (Thin) for RAC Service-Instance connections; Versions:10 and later.
  4. In the Service Name field, enter the database service name enter in lowercase, for example, mydb.example.com.
  5. In the Schema Owner field, enter the username of the database schema owner for the corresponding component.
  6. In the Schema Password field, enter the password for the database schema owner.
  7. In the Host Name, Instance Name, and Port field, enter the RAC node hostname, database instance name, and port. Click Add to enter multiple listener addresses.
  8. Click Next.Verify that all connections are successful.
About Adding Multi Data Sources For RAC Databases

Multi data sources have constituent data sources for each RAC instance that provides a database service. If you add an instance to the RAC back end, Oracle recommends adding an additional data source to the multi data source on the Fusion Middleware tier.

When you migrate a database from a non-RAC to a RAC database, you must create an equivalent, new multi data source for each affected data source. Multi data sources that you create must have constituent data sources for each RAC instance. Data source property values must be identical to the original single instance data source for properties in Configuring Multi Data Sources with Oracle RAC. For example, if a 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.

Modifying or Creating Multi Data Sources After Initial Configuration

For multi data sources that you create manually or modify after initial configuration, Oracle strongly recommends specific XA and non-XA data source property values for optimal high availability. Make changes only after careful consideration and testing if your environment requires that you do so.

Table 5-2 describes XA and non-XA data source property values that Oracle recommends.

Table 5-2 Recommended Multi Data Source Configuration

Property Name Recommended Value

test-frequency-seconds

5

algorithm-type

Load-Balancing

For individual data sources, Oracle recommends the configuration values in Table 5-3 for high availability environments. Oracle recommends that you set any other parameters according to application requirements.

Table 5-3 XA Data Source Configuration

Property Name Recommended 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

Troubleshooting Warning Messages (Increasing Transaction Timeout for XA Data Sources)

If WARNING messages in server logs have the following exception, you may need to increase the XA timeout value in your setup.

[javax.transaction.SystemException: Timeout during commit processing

To increase the transaction timeout for the XA Data Sources setting, use the Administration Console:

  1. Access the data source configuration.
  2. Select the Transaction tab.
  3. Set the 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.

Table 5-4 Non-XA Data Source Configuration

Property Name Recommended 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

Configuring Schemas for Transactional Recovery Privileges

You want to enable WebLogic Server transaction manager to perform schema tasks.

You must have sysdba privileges to enable transaction manager privileges:

  • Query for transaction state information.

  • Issue the appropriate commands, such as commit and rollback, during recovery of in-flight transactions after a WebLogic Server container failure.

To configure 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.

Configuring Multi Data Sources for MDS Repositories

You can configure applications that use an MDS database-based repository for high availability Oracle database access.

With this configuration, failure detection, recovery, and retry by MDS (and by WebLogic infrastructure) protect application read-only MDS operations from Oracle RAC database planned and unplanned downtimes

The Fusion Middleware Control navigation tree exposes multi data sources as MDS repositories. You can select these multi data sources when you customize application deployment and use them with MDS WLST commands.

  • Configuring an application to retry read-only operations

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

  • Registering an MDS multi data source

    In addition to steps in Configuring Multi Data Sources with Oracle RAC, note the following:

    • You must configure child data sources that comprise a multi data source used for an MDS repository as non-XA data sources.

    • A multi data source's name must have the prefix mds-. This ensures it is recognized as an MDS repository.

      Note:

      When you add a MDS data source as a child of a multi data source, this data source is no longer exposed as an MDS repository. It does not appear under the Metadata Repositories folder in the Fusion Middleware Control navigation tree. You can not 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

    Keep in mind when you convert a data source to a multi data source:

    • 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 a new multi data source using the same name and jndi-name attributes.