Symptom

Oracle Server vs. Client

The Oracle relational database installation involves the Oracle server setup (in larger installations, the database is usually on a dedicated server), which is accessed by the Oracle client setup.  The Oracle client must be installed on the Process Commander server for Process Commander to be able to connect to the Oracle server installation.  Process Commander works well with the Oracle server version 9.2.0.4.xx; the following information is about the client side only.

Date vs. Timestamp Columns

Oracle has been making an attempt to make their database better comply with JDBC standards and definitions.  Therefore, beginning with the Oracle 9i release, Oracle added Timestamp (types to their products, in addition to the Date (type) column.  With the introduction of Oracle 9.2.0.4, the Oracle JDBC driver no longer maps the Date columns to Java types in the same manner as it mapped them in earlier 9i versions or in 8i versions.  Process Commander V4.1SP4 or higher and V4.2 support prior versions of 9i and therefore  must continue to use the Date (type) column. For a discussion of the issue,   see the Oracle Technical Network FAQ at #08_01

Oracle 9i Client Overview

Several versions of Oracle 9i clients are currently used, including:

• 9.2.0.1.0
• 9.2.0.3.0
• 9.2.0.4.0
• 9.2.0.5.0

Some versions of Oracle's 9.2.0.4 client were shipped with incorrect JDBC classes, which cause problems.  Information about this issue can be found on Oracle's site at http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm

In addition, on Oracle’s support website Metalink, the article "Bug 3150122 Unix: 9.2.0.4 JDBC Patch is incomplete" further discusses the problem.

Therefore: V4.1 and V4.2 do not support Oracle client 9.2.0.4.  If you have Oracle 9.2.0.4 client, upgrade the client to 9.2.0.5 r for Process Commander to function correctly. 

Solution

The full list of Process Commander compatibility with the Oracle 9i clients is::

• V4 Releases support the 9.2.0.3.xx and earlier versions of the Oracle client JDBC driver.
• V4 Releases DO NOT support the 9.2.0.4.0 version of the JDBC driver, due to issues with the JDBC drivers handling the Date columns.
• V4 supports the 9.2.0.5.xx version of the JDBC driver using a compatibility switch.

As noted above, the 9.2.0.4 server setup works fine, as long as the 9.2.0.4 client is not used.  Both the 9.2.0.3 client and the 9.2.0.5 client (with the compatibility switch set) will work with the 9.2.0.4 server.

IMPORTANT:  Database password encryption is not supported with the Oracle 9.2.0.5 client implementation.  Sites that want password encryption with an Oracle database must use the 9.2.0.3 or earlier Oracle client.

Configuring the Oracle 9i Client version 9.2.0.5 to work with Process Commander

For new installations :

1. Back up the Oracle database.

2. Install Oracle 9i Client 9.2.0.5. 

If you have already installed Oracle 9i Client 9.2.0.4, you must upgrade the Oracle client to 9.2.0.5.  Refer to Oracle instructions for upgrading the client installation.

3.  Configure the Relational Database and choose the JDBC Connection type.

This would be one of the initial steps in the Process Commander installation process.  Reference the V4.2 Install Guide for detailed installation instructions on these options.

4.  Create the oracle.conf file.

NOTE:  This step and the editing of the pegarules.xml file (next step) must both be done before using the Deployment Manager.

The oracle.conf file contains the Oracle “compatibility switch” which allows the 9.2.0.5 client to access a database with the Date (type) column.  This file must also include the userid and password that Process Commander uses to access the file.

The format of the oracle.conf file is as follows:

user=userid
password=password
oracle.jdbc.V8Compatible=true

Example:

user=pruser
password=pruserpassword
oracle.jdbc.V8Compatible=true

This file may be located anywhere on the system.  However, Pegasystems recommends that it is placed in the WEB-INF/classes directory (which is where the pegarules.xml file and log4j.xml configuration files are stored).   As an example, for WebLogic, this location is:

BEAinstallroot\user_projects\domains\domain-name\server-name\stage\contextroot\WEB-INF\classes\oracle.conf

Example:

e:/weblogic/user_projects/domains/pegarules/myserver/stage/prweb/WEB-INF/classes/oracle.conf

At this step in the process, update both the pegarules.xml file and the oracle.conf file with the userid and password in for Process Commander.

5.  Edit the pegarules.xml file. 

Add the propertiesFile into the pegarules.xml file in the database node.  The original pegarules.xml file looks like this:

<node name="databases">
<!--   This node defines the PegaRULES database. Do NOT change the name!-->
     <node name="PegaRULES">
           <map>
             <entry key="password" value="pruserpassword"/>
             <entry key="url" value="jdbc:oracle:oci:@tns_name”/>
              <entry key="userName" value="pruser"/>
            </map>
      </node>
<!-- End PegaRULES database definition -->

The propertiesFile setting holds the path to the oracle.conf file. Edit it to add the url, name, & password information:

<node name="databases">
<!--
   This node defines the PegaRULES database. Do NOT change the name!
-->
     <node name="PegaRULES">
        <map>
             <entry key="password" value="pruserpassword"/>
             <entry key="url" value="jdbc:oracle:oci:@tns_name”/>
             <entry key="userName" value="pruser"/>
             <entry key="propertiesFile" value=" e:/weblogic/user_projects/domains/pegarules/myserver/stage/prweb/WEB-INF/classes/oracle.conf"/>
         </map>
      </node>
<!-- End PegaRULES database definition -->

6.  Finish the Process Commander installation.

 For existing installations:

Many of the steps in the process of configuring an existing installation are the same as the new installation.  For the detail on these steps, refer to the above explanations.

1.  Back up the Oracle database.

2.  Install Oracle 9i Client 9.2.0.5. 

3.  In the Process Commander server, copy over existing Oracle client files.

In the existing installation, locate the files that were installed for the Oracle client that is being replaced.  Files such as the classes12.zip file, which may have been copied to a directory within the application server, need to be updated with the newest 9.2.0.5 version.

4.  Update JDBC configuration (if necessary).

Depending upon the configuration of the Oracle client and the application server being used, it may be necessary to update the JDBC configuration, to ensure that the Process Commander installation references the new JDBC drivers.

For example, for Tomcat, the only “deployment” step is to copy the classes12.zip file into the Tomcat \common\lib directory.  If the new 9.2.0.5 version of the classes12.zip file is copied into the same area, no other changes should be necessary.  However, if the new 9.2.0.5 client install is into a different directory that WebSphere or WebLogic was pointing at, redoing the JDBC configuration for that application server may be necessary, to point at the new Oracle Client directory.

5.  Create the oracle.conf file.

6.  Edit the pegarules.xml file to include the propertiesFile entry. 

Article originally written 10/13/2004.