Building a high availability database environment using WebSphere middleware: Part 2: Using Oracle Real Application Cluster (RAC) with WebSphere Process Server

Here is a strategy that enables you to configure any IBM® WebSphere® Process Server installation for Oracle® Real Application Cluster (RAC) so you can achieve the benefits of high availability with Oracle databases. This content is part of the IBM WebSphere Developer Technical Journal.

Share:

Dr. Debasish Banerjee (debasish@us.ibm.com), WebSphere Consultant, IBM

Dr. Debasish Banerjee is presently a WebSphere consultant in IBM Software Services. He started his WebSphere career as the WebSphere internationalization architect. Extreme transaction processing, distributed cache, elastic computing, and cloud computing are his current areas of interest. Debasish received his Ph. D. in the field of combinator-based Functional Programming languages.



Soloman Barghouthi (soloman@us.ibm.com), Advisory Software Engineer, IBM

Soloman Barghouthi is a Senior Software Engineer at the IBM Rochester Lab. Soloman is the architect and leader of the WebSphere Application Server EJBContainer team. Soloman is a database expert and has published many articles on database/WebSphere Application Server interaction.



Eric Herness (herness@us.ibm.com), Distinguished Engineer, IBM

Eric Herness is an IBM Distinguished Engineer from the IBM development lab in Rochester, MN, USA. He is the Chief Architect for WebSphere Business Integration (WBI) products. Eric is a senior member of the WebSphere Foundation Architecture Board and a member of the Software Group Architecture Board Steering Committee. Eric has deep experience in distributed systems and object technology. He has had key lead architectural roles in WebSphere going back over a decade. These have included lead roles and technical contributions to Component Broker, J2EE/EJB, Websphere Enterprise Edition and WBI Server Foundation. Currently Eric provides architectural guidance and direction for WBI, which includes BPM products, enterprise application integration products and process integration products in the WebSphere portfolio.



13 June 2007

Also available in Chinese

Introduction

IBM WebSphere Process Server requires several databases to operate, and Oracle is one of many popular databases that are sometimes used in WebSphere Process Server installations. Though there can be heated debate about the advantages of its fundamental share-everything architecture, Oracle Real Application Cluster (RAC) is used for attaining high availability, reliability, work load management, and scalability of Oracle databases.

While some may be under the mistaken impression that Oracle RAC cannot be used for any of the databases used for WebSphere Process Server -- even in the latest Version 6.0.2, it is nonetheless relatively simple to use Oracle RAC in present WebSphere Process Server releases by changing a few relevant data source configurations. This article describes a simply way to perform the configuration alterations necessary for successfully using Oracle RAC for WebSphere Process Server databases.


WebSphere Process Server databases

Depending on the installation, WebSphere Process Server might need to have these four databases available (in relevant places throughout this article, the term database can be substituted with database schema):

  • Common repository database: A cell-wide database, with a default name of WPRCSDB. Typically, an empty database is created first, then a WebSphere Process Server wizard creates all the necessary tables. For Network Deployment configurations of WebSphere Process Server, the tables in the database can be created during creation of the deployment manager profile. Starting with WebSphere Process Server V6.0.2.x, table creation can be delayed, since the deployment manager profile creation process does not require the existence of this database.

  • Business process database: A deployment target-specific database with a default name of BPEDB, containing the tables related to business process choreography. The tables in the database are typically created by executing a supplied DDL script.

  • Common Event Infrastructure database: If the Common Event Infrastructure (CEI) is used in a WebSphere Process Server installation, a deployment target-specific database is needed here also. The CEI database is created by executing a generated DDL script and has a default name of "event."

  • Messaging engine databases: In a typical WebSphere Process Server installation, there can be four messaging engines, and each needs its own database. Following best practices, the database for a messaging engine is created by executing a generated DDL script.


Oracle RAC and data sources

When you create an Oracle JDBC data source with a WebSphere product, you specify an Oracle URL which, along with the driver type, contains the Oracle database server host name, port number, and the Oracle database name. A typical Oracle URL may look something like:

jdbc:oracle:thin:@<hostname>:<port number>:<DBName>

Configuring Oracle RAC is a non-trivial task. The Oracle database administrator (DBA) will consider many factors related to failover, load balancing, and others while creating and configuring an RAC. However, to the WebSphere Application Server runtime, an Oracle RAC is essentially just another database to be accessed using an appropriate JDBC driver.

There are several ways you can configure the RAC environment in a WebSphere product. One approach is to let the JDBC driver balance the database connection requests and, hence, also the workload balancing. Another viable alternative is to let the WebSphere Workload Management (WLM) component perform the connection balancing; this can be achieved by configuring each WebSphere cluster member to point to one -- and only one -- Oracle database server. This article uses the former strategy. In this approach, the Oracle JDBC driver will recognize and interact with the underlying RAC and get connections from the appropriate database servers, thereby maintaining the failover and load balancing characteristics as configured by the DBA.

However, for the JDBC driver to work with RAC, somewhat complex Oracle URLs need to be specified when defining WebSphere JBDC data sources. Here is a sample URL for a RAC consisting of two Oracle database servers with the intention of balancing the connection load across them:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=
   (ADDRESS=(PROTOCOL=TCP)(HOST= myoraclehost1.ibm.com)(PORT=1521))
   (ADDRESS=(PROTOCOL=TCP)(HOST= myoraclehost2.ibm.com)(PORT=1521))
   (FAILOVER=on)(LOAD_BALANCE=on) 
   (CONNECT_DATA=(SERVER=DEDICATED)
   (SERVICE_NAME=dbservice)))

Again, in this approach for using Oracle RAC in a WebSphere product, you have to simply specify the RAC-ified URL as the Oracle URL when defining the relevant Oracle JDBC data sources.


Oracle in WebSphere Process Server

These data sources are used by internal WebSphere Process Server applications and their names and scopes may change across releases.

Using Oracle databases for all the four WebSphere Process Server databases is rather straightforward (see Resources). The WebSphere Process Server infrastructure accesses these databases through data sources. Among the four categories of infrastructural WebSphere Process Server data sources, the messaging engine data source should be created manually, while the other three categories are typically created indirectly by wizards or scripts:

  • Common Repository data source: In Netowrk Deployment environments, the tables in this database can be created during the creation of the Deployment Manager profile. For a pre-existing database, you need to specify these attributes in the Deployment Manager Profile creation wizard (Figures 1 and 2):

    Figure 1. Configure WebSphere Process Server database
    Figure 1. Configure WebSphere Process Server database
    Figure 2. Oracle database attributes
    Figure 2. Oracle database attributes

    From these specified values, the Deployment Manager Profile creation wizard creates the cell-scoped WebSphere Process Server data source with the jndi name jdbc/WPSDB (Figure 3).

    Figure 3. Data source created
    Figure 3. Data source created
  • Business Process Execution data source: In Step 1 of the business process container installation wizard, specify these parameters as Custom Properties (Figure 4):

    Figure 4. Configure business process container
    Figure 4. Configure business process container
    databaseName=<DBName>
    serverName=<Fully qualified Oracle server name or IP address>
    portNumber=<Oracle port number>
    dataSourceName=URL=jdbc:oracle:thin:@<hostname>:<port number>:<DBName>

    In the process of installing the business process container in a WebSphere Process Server cluster, the wizard creates the BPEDataSourceOracle_<Cluster Name> data source with jdbc/BPEDB_<Cluster Name> as the JNDI name. In Version 6.0.1.x, this data source gets created at the cell scope; in Version 6.0.2.0, the data source gets defined at cluster scope.

  • CEI data sources: The CEI data source creation is a two step process. You must first specify the Oracle database name, JDBC driver location, driver type, database server host name, and Oracle port number attributes in the OracleResponseFile.txt file. The file is then used as the parameter to a meta-script which generates the required scripts for creating and configuring the data sources.

    After the execution of the J2EE artifact creation script, two Oracle data sources, event (jdbc/cei) and event_catalog (jdbc/eventcatalog), are configured (Figure 5).

    Figure 5. Create JDBC data sources
    Figure 5. Create JDBC data sources

    The scope of both CEI data sources can be controlled by specifying the SCOPE attribute in the OracleResponseFile.txt file.

RAC in WebSphere Process Server

The three data sources above suggesting a single (classic Oracle) database server, whereas the sample specification shown earlier illustrates a more typical RAC environment, which consists of multiple database servers. To use RAC in present WebSphere Process Server installations, work with an Oracle DBA to put this simple strategy into place:

  1. Configure a non-RAC Oracle data source, even if the Oracle environment is already a RAC one. When you configure the Oracle URL, use one of the Oracle servers as the host name in the URL for all the relevant WebSphere Process Server wizards and scripts. (Providing a single Oracle server might be necessary for generating the schema and tables.)

  2. Change the Oracle URL for all data sources created by WebSphere Process Server wizards and scripts to the actual RAC URL specific to the environment. For example, suppose you create a data source using the Deployment Manager Profile creation wizard with these values:

    • Database driver: Oracle Thin
    • Pre-created Oracle database schema named WPRCSDB
    • Database server name: myoraclehost1.ibm.com
    • Port: 1521

    After the deployment manager profile is created, a data source to access WPRCSDB will be created. The attributes of the data source might look something like Figure 6, where you would see the Oracle URL as:

    jdbc:oracle:thin:@myoraclehost1.ibm.com:1521:WPRCSDB

    Figure 6. Created data source
    Figure 6. Created data source

    To make this WebSphere Process Server data source work for RAC, all you need to do is to alter this classic single server specific Oracle data source properties URL created by the wizard to the RAC URL, shown in Figure 7. (The RAC URL should be provided by the Oracle DBA and might look similar to the sample URL shown above.)

    Figure 7. Replace single server URL with RAC URL
    Figure 7. Replace single server URL with RAC URL

    The same URL alteration should also be performed for all the other data sources created by WebSphere Process Server wizards and scripts.

  3. Restart the deployment manager and any WebSphere Process Server instance that is running.

Verify the connectivity of the modified data source with the test connection facility in the administrative console. Although the URLs in this article deal with the Oracle thin driver, the Oracle OCI driver can also be used in the Oracle URL. The same URL alteration strategy is applicable for and Oracle OCI driver as well.

One additional note: When manually creating the messaging engine data sources, you can directly specify the RAC URL for the Oracle data source properties, and completely avoid the "classic Oracle to Oracle RAC URL" alteration steps.


Conclusion

Though Oracle RAC is officially supported in newer WebSphere Process Server V6.0.2.x and later releases, you can use the URL alteration strategy described in this article to configure any WebSphere Process Server installation for Oracle RAC environments.


Acknowledgements

The authors thank Karri Carlson-Neumann for reviewing this article for technical accuracy and for providing valuable suggestions.

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Business process management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business process management, WebSphere
ArticleID=226123
ArticleTitle=Building a high availability database environment using WebSphere middleware: Part 2: Using Oracle Real Application Cluster (RAC) with WebSphere Process Server
publish-date=06132007