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:
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
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 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
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
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 220.127.116.11, 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
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:
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.)
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:
- Pre-created Oracle database schema named
- Database server name:
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:
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
The same URL alteration should also be performed for all the other data sources created by WebSphere Process Server wizards and scripts.
- Database driver:
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.
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.
The authors thank Karri Carlson-Neumann for reviewing this article for technical accuracy and for providing valuable suggestions.
- WebSphere Process Server V6.0.2 product information
- Configure WebSphere Process Server with an Oracle database
- Oracle Real Application Clusters 10g: An Oracle Technical White Paper
- Technote: Oracle RAC limitation when running with XA
- IBM developerWorks: WebSphere Process Server resources