Skip Headers
Oracle® Application Server Containers for J2EE Services Guide
10g Release 2 (10.1.2) for Windows or UNIX
B14012-02
  Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

4 Data Sources

This chapter describes how to configure and use data sources in your Oracle Application Server Containers for J2EE (OC4J) application. A data source is a vendor-independent encapsulation of a connection to a database server. A data source instantiates an object that implements the javax.sql.DataSource interface.

This chapter covers the following topics:

Introduction

A data source is a Java object that implements the javax.sql.DataSource interface. Data sources offer a portable, vendor-independent method for creating JDBC connections. Data sources are factories that return JDBC connections to a database. J2EE applications use JNDI to look up DataSource objects. Each JDBC 2.0 driver provides its own implementation of a DataSource object, which can be bound into the JNDI name space. After this data source object has been bound, you can retrieve it through a JNDI lookup. Because data sources are vendor-independent, we recommend that J2EE applications retrieve connections to data servers using data sources.

Types of Data Sources

In OC4J, Data Sources are classified as follows:

Table 4-1 summarizes the key differences between the data source types.

Table 4-1 Features of Emulated, Nonemulated, and Native Data Sources

Emulated Not Emulated
No JTA
Native Data Source
  • Vendor extensions

  • Vendor JDBC pool/cache

  • No JTA

JTA Emulated Data Source
  • Lightweight transactions

  • One-phase commit

  • OC4J pool/cache

Nonemulated Data Source
  • Full transactions

  • Two-phase commit

  • Oracle JDBC pool/cache



:

Note

If you access a nonemulated data source by the ejb-location, then you are using the OC4J pool and cache. If you use OracleConnectionCacheImpl, then you can access both OC4J and Oracle JDBC pool and cache.


Figure 4-1 summarizes the decision tree that should guide you when choosing a data source type.

Figure 4-1 Choosing a Data Source Type

Description of dsdec.gif follows
Description of the illustration dsdec.gif

The following sections describe each data source type in detail.

Emulated Data Sources

Emulated data sources are data sources that emulate the XA protocol for JTA transactions. Emulated data sources offer OC4J caching, pooling, and Oracle JDBC extensions for Oracle data sources. Historically, emulated data sources were necessary because many JDBC drivers did not provide XA capabilities. Today even though most JDBC drivers do provide XA capabilities, there are still cases in which emulated XA is preferred (such as transactions that do not require two-phase commit.)

Connections obtained from emulated data sources are extremely fast, because the connections emulate the XA API without providing full XA global transactional support. In particular, emulated data sources do not support two-phase commit. Oracle recommends that you use emulated data sources for local transactions, or when your application uses global transactions without requiring two-phase commit. For information on the limitations of two-phase commit, see Chapter 7, "Java Transaction API".

The following is a data-sources.xml configuration entry for an emulated data source:

<data-source
    class="com.evermind.sql.DriverManagerDataSource"
    name=ÓOracleDSÓ
    location="jdbc/OracleCoreDS"
    xa-location="OracleDS"
    ejb-location="jdbc/OracleDS"
    connection-driver="oracle.jdbc.driver.OracleDriver"
    username="scott"
    password="tiger"
    url="jdbc:oracle:thin:@//localhost:5521/oracle.regress.rdbms.dev.us.oracle.com"
    inactivity-timeout=Ó30Ó
/>

When defining an emulated data source in data-sources.xml, you must provide values for the location, ejb-location, and xa-location attributes. However, when looking up an emulated data source through JNDI, you should look it up by the value specified with the ejb-location attribute. For example:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");
// This lookup could also be done as
// DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/OracleDS");
Connection con = ds.getConnection();

This connection opens a database session for scott/tiger.


Note: :

Previous releases supported the location and xa-location attributes for retrieving data source objects. These attributes are now strongly deprecated. Applications, EJBs, servlets, and JSPs should use only the JNDI name ejb-location in emulated data source definitions for retrieving the data source. You must specify all three values for emulated data sources, although only ejb-location is actually used.

If you use an emulated data source inside a global transaction, you must exercise caution. Because the XAResource that you enlist with the transaction manager is an emulated XAResource, the transaction will not be a true two-phase commit transaction. If you want true two-phase commit semantics in global transactions, then you must use a nonemulated data source. For information on the limitations of two-phase commit, see Chapter 7, "Java Transaction API".

Retrieving multiple connections from a data source using the same user name and password within a single global transaction causes the logical connections to share a single physical connection. The following code shows two connections—conn1 and conn2—that share a single physical connection. They are both retrieved from the same data source object. They also authenticate with the same user name and password.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
Connection conn1 = ds.getConnection("scott", "tiger");
Connection conn2 = ds.getConnection("scott", "tiger");

Nonemulated Data Sources

Nonemulated data sources provide full (nonemulated) JTA services, including two-phase commit capabilities for global transactions. Nonemulated data sources offer pooling, caching, distributed transactions capabilities, and vendor JDBC extensions (currently, only Oracle JDBC extensions). For information on the limitations of two-phase commit, see Chapter 7, "Java Transaction API".

Oracle recommends that you use nonemulated data sources for distributed database communications, recovery, and reliability. Nonemulated data sources share physical connections for logical connections to the same database for the same user.


Note:

You must use a Java-enabled database to run a nonemulated data source. When you use a nonemulated data source and a non-Java-enabled database, deploying any MDB application (AQJMS) generates an exception, thrown to OC4J stdout. If you switch to an emulated data source or a Java-enabled database, deployment proceeds correctly.


Note:

When an application using a nonemulated data source is undeployed, the physical database connection is not removed from OC4J until OC4J is restarted.

The following is a data-sources.xml configuration entry for a nonemulated data source:

<data-source
  class="com.evermind.sql.OrionCMTDataSource"
  location="jdbc/OracleDS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@//localhost:5521/oracle.regress.rdbms.dev.us.oracle.com"
</data-source>

JNDI lookups should be performed using the value of the location attribute.

Here are the expected attribute definitions:

  • location is the JNDI name to which this data source is bound within the JNDI name space. Use location in the JNDI lookup for retrieving this data source.

  • url, username, and password identify the database and default user name and password to use when connections are retrieved with this data source.

  • class defines what type of data source class to bind in the name space.

Native Data Sources

Native data sources are JDBC-vendor supplied implementations of the DataSource. They expose vendor's JDBC driver capabilities including caching, pooling, and vendor specific extensions. Exercise caution when using native data sources, because OC4J cannot enlist them inside global transactions, and they can be used by EJBs or other components requiring global transaction semantics.

Native data source implementations can be used directly without an emulator. OC4J supports the use of native data sources directly and benefits from their vendor-specific pooling, caching, extensions, and properties. However, native data sources do not provide JTA services (such as begin, commit, and rollback)

The following is a data-sources.xml configuration entry for a native data source:

<data-source
    class="com.my.DataSourceImplementationClass"
    name=ÓNativeDSÓ
    location="jdbc/NativeDS"
    username="user"
    password="pswd"
    url="jdbc:myDataSourceURL"
</data-source>

JNDI lookups can be performed only through the value of the location attribute.

Mixing Data Sources

A single application can use several different types of data sources.

If your application mixes data sources, note the following issues:

  • Only emulated and nonemulated data sources support JTA transactions.

    You cannot enlist connections obtained from native data sources in a JTA transaction.

  • Only nonemulated data sources support true two-phase commit (emulated data sources emulate two-phase commit).

    To enlist multiple connections in a two-phase commit transaction, all connections must use nonemulated data sources. For information on the limitations of two-phase commit, see Chapter 7, "Java Transaction API".

  • If you have opened a JTA transaction and want to obtain a connection that does not participate in the transaction, then use a native data source to obtain the connection.

  • If your application does not use JTA transactions, you can obtain connections from any data source.

  • If your application has opened a javax.transaction.UserTransaction, all future transaction work must be performed through that object.

    If you try to invoke the connection's rollback() or commit() methods, then you will receive the following SQLException:

    calling commit() [or rollback()] is not allowed on a container-managed transactions Connection 
    
    

    The following example explains what happens:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("JDBC/OracleCMTDS1"); // Using JTA DataSources
Connection conn1 = ds.getConnection("scott", "tiger");
javax.transaction.UserTransaction ut =     (javax.transaction.UserTransaction)ic.lookup("java:comp/UserTransaction");
ut.begin();
conn1.query();
conn1.commit(); // not allowed, returns error: calling commit[or rollback] is not allowed 
                // on a container-managed transaction connection

Defining Data Sources

You define OC4J data sources in the data-sources.xml file.

The data-sources.xml file installed with OC4J includes predefined, default data sources that may be sufficient for your needs. If not, you must define your own.

Table 4-2 summarizes the configuration requirements for each type of data source.

Table 4-2 Data Source Configuration Summary

Configuration Nonemulated Emulated Native
Data source class OrionCMTDataSource DriverManagerDataSource

OracleConnection- CacheImpl
Connection-driver N/A vendor specific

OracleDriver for Oracle extensions

N/A
JNDI Context specification location location

ejb-location

xa-location

location
JNDI Context lookup

location ejb-location location
URL Oracle driver URL Vendor-specific

Oracle: Thin or OCI (TAF with OCI)

Vendor-specific

Oracle: Thin or OCI (TAF with OCI)

Additional configuration Oracle database commit coordinator

Database link for two-phase commit coordinator

None Cache scheme


Note:

For documentation of OrionCMTDataSource and its elements, see http://www.orionserver.com/docs/api/orion/com/evermind/sql/OrionCMTDataSource.html

Table 4-3 summarizes the characteristics for each type of data source.

Table 4-3 Data Source Characteristics

Characteristic Nonemulated Emulated Native
Pool and cache support Oracle JDBC driver pool OC4J connection pool vendor specific

Oracle

Vendor extension support Oracle only Oracle only vendor specific

Oracle

JTA support Full XA (one- or two-phase commit) Emulated XA (one-phase commit) Not supported
J2CA support No Yes Yes


Note:

If you access a nonemulated data source by the ejb-location, then you are using the OC4J pool and cache. If you use OracleConnectionCacheImpl, then you can access both OC4J and Oracle JDBC pool and cache.

To define a new data source object:

  1. Select a location for the data-sources.xml file (see "Configuration Files").

  2. Understand data source attributes (see "Data Source Attributes").

  3. Define a data source either by using the Oracle Enterprise Manager 10g (See "Defining Data Sources in Oracle Enterprise Manager 10g") or by manually editing configuration files. (See "Defining Data Sources in the XML Configuration File").

Configuration Files

One main configuration file establishes data sources at the OC4J server level: J2EE_HOME/config/data-sources.xml.

Each application also has a separate JNDI name space. The files web.xml, ejb-jar.xml, orion-ejb-jar.xml, and orion-web.xml contain entries that you can use to map application JNDI names to data sources, as the next section describes.

Defining Location of the Data Source XML Configuration File

Your application can know about the data sources defined in this file only if the application.xml file knows about it. The path attribute in the <data-sources> tag in the application.xml file must contain the name and path to your data-sources.xml file, as follows:

<data-sources path="data-sources.xml"/>

The path attribute of the <data-sources> tag contains a full path name for the data-sources.xml file. The path can be absolute, or it can be relative to where the application.xml is located. Both the application.xml and data-sources.xml files are located in the J2EE_HOME/config/application.xml directory. Thus, the path contains only the name of the data-sources.xml file.

Application-Specific Data Source XML Configuration File

Each application can define its own data-sources.xml file in its EAR file. This is done by having the reference to the data-sources.xml file in the orion-application.xml file packaged in the EAR file.

To configure this:

  1. Locate the data-sources.xml and orion-application.xml files in your application's META-INF directory.

  2. Edit the orion-application.xml file to add a <data-sources> tag as follows:

    <orion-application>
        <data-sources path="./data-sources.xml"/>
    </orion-application>
    

Data Source Attributes

A data source can take many attributes. Some are required; most are optional. The required attributes are marked below. The attributes are specified in a <data-source> tag.

Table 4-4 lists and describes the data source attributes.

In addition to the data-source attributes described in Table 4-4, you can also add property subnodes to a data-source. These are used to configure generic properties on a data source object (following Java Bean conventions.) A property node has a name and value attribute used to specify the name and value of a data source bean property.

All OC4J data source attributes are applicable to the infrastructure database as well. For more information on the infrastructure database, see Oracle High Availability Architecture and Best Practices.

Table 4-4 Data Source Attributes

Attribute Name Description Default Value
class Names the class that implements the data source.

For nonemulated, this can be com.evermind.sql.OrionCMTDataSource.

For emulated, this should be com.evermind.sql.DriverManagerDataSource.

(This value is required.)

N/A
location The JNDI logical name for the data source object. OC4J binds the class instance into the application JNDI name space with this name. This JNDI lookup name is used for nonemulated data sources. See also Table 4-2, "Data Source Configuration Summary". N/A
name The data source name. Must be unique within the application. None
connection-driver The JDBC-driver class name for this data source, used by some data sources that deal with java.sql.Connection.

For most data sources, the driver is oracle.jdbc.driver.OracleDriver. This attribute applies only to emulated data sources for which the class attribute is com.evermind.sql.DriverManagerDataSource.

None
username Default user name used when getting data source connections. None
password Default password used when getting data source connections. See also "Password Indirection". None
URL The URL for database connections. None
xa-location The logical name of an XA data source. Applies to emulated data sources only. See also Table 4-2, "Data Source Configuration Summary". None
ejb-location Use this attribute for JTA single-phase commit transactions or for looking up emulated data sources. If you use it to retrieve the data source, you can map the returned connection to oracle.jdbc.OracleConnection. See also Table 4-2, "Data Source Configuration Summary". None
stmt-cache-size A performance tuning attribute set to a non-zero value to enable JDBC statement caching and to define the maximum number of statements cached. Enabled to avoid the overhead of repeated cursor creation, and statement parsing and creation. Applicable only for emulated data sources for which connection-driver is oracle.jdbc.driver.OracleDriver and class is com.evermind.sql.DriverManagerDataSource. 0 (disabled)
inactivity-timeout Time (in seconds) to cache an unused connection before closing it. 60 seconds
connection-retry-interval Time (in seconds) to wait before retrying a failed connection attempt. 1 second
max-connections The maximum number of open connections for a pooled data source. Depends on the data source type
min-connections The minimum number of open connections for a pooled data source. OC4J does not open these connections until the DataSource.getConnection method is invoked. 0
wait-timeout The number of seconds to wait for a free connection if the pool has reached max-connections used. 60
max-connect- attempts The number of times to retry making a connection. Useful when the network or environment is unstable for any reason that makes connection attempts fail. 3
clean-available- connections- threshold This optional attribute specifies the threshold (in seconds) for when a cleanup of available connections will occur. For example, if a connection is bad, the available connections are cleaned up. If another connection is bad (that is, it throws an exception), and if the threshold time has elapsed, then the available connections are cleaned up again. If the threshold time has not elapsed, then the available connections are not cleaned up again. 30
rac-enabled This optional attribute specifies whether the system is enabled for Real Application Clusters (RAC). For information on using this flag with an infrastructure database, see Oracle High Availability Architecture and Best Practices. For information on using this flag with a user database, see "Using DataDirect JDBC Drivers" and "High Availability Support for Data Sources".

If the data source points to an RAC database, set this property to true. This enables OC4J to manage its connection pool in a way that performs better during RAC instance failures.

false
schema This optional attribute specifies the database-schema associated with a data source. It is especially useful when using CMP with additional data types or third-party databases. For information on using this attribute, see "Associating a Database Schema with a Data Source" . None

The following example shows the use of the clean-available-connections-threshold and rac-enabled attributes:

<data-source
    class="com.evermind.sql.OrionCMTDataSource"
    name="NEDS1"
    location="jdbc/NELoc1"
    connection-driver="oracle.jdbc.driver.OracleDriver"
    min-connections="5"
    max-connections="10"
    clean-available-connections-threshold="35"
    rac-enabled="true"
    username="scott"
    password="tiger"
    url="jdbc:oracle:thin:@//localhost:5521/oracle.regress.rdbms.dev.us.oracle.com"
    inactivity-timeout="30"
    max-connect-attempts="5"
/>

For each data source you define, OC4J may create and bind within JNDI up to four data sources: one each for location, ejb-location, xa-location, and pool-location. The type of data source selected is determined by the values associated with data-sources.xml attributes class, connection-driver, and url, and the JNDI context in which the data source object is created and looked up. For more information about data source types, see "Types of Data Sources".

Defining Data Sources in Oracle Enterprise Manager 10g

You can define any type of data source with the Oracle Enterprise Manager 10g.

The Data Sources Primer chapter of the Oracle Application Server Containers for J2EE User's Guide describes how to define data sources.

See the Oracle Application Server Containers for J2EE User's Guide to find out how to use the Administrative tools. See the Oracle Enterprise Manager Administrator's Guide for information on Oracle Enterprise Manager 10g.

This section presents a brief overview of these procedures.

Use the Oracle Enterprise Manager 10g and drill down to the Data Source page. OC4J parses the data-sources.xml file when it starts, instantiates data source objects, and binds them into the server JNDI name space. When you add a new data source specification, you must restart the OC4J server to make the new data source available for lookup.

To define emulated data sources, follow the same steps as for defining nonemulated data sources, up to the step in which you define the JNDI location. In the procedure for defining nonemulated data sources, the screen shot shows one field, Location, to be filled out. For defining an emulated data source, fill out the three fields Location, XA-Location, and EJB-Location.


Note:

Previous releases supported the location and xa-location attributes for retrieving data source objects. These attributes are now strongly deprecated; applications, EJBs, servlets, and JSPs should use only the JNDI name ejb-location in emulated data source definitions for retrieving the data source. You must specify all three values must be specified for emulated data sources, but only ejb-location is actually used.

Defining Data Sources in the XML Configuration File

The $J2EE_HOME/config/data-sources.xml file is preinstalled with a default data source. For most uses, this default is all you need. However, you can also add your own customized data source definitions.

The default data source is an emulated data source.

For more information about data source types, see "Types of Data Sources".

The following is a simple emulated data source definition that you can modify for most applications:

<data-source
  class="com.evermind.sql.DriverManagerDataSource"
  name="OracleDS"
  location="jdbc/OracleCoreDS"
  xa-location="OracleDS"  
  ejb-location="jdbc/OracleDS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@//localhost:5521/oracle.regress.rdbms.dev.us.oracle.com"
  inactivity-timeout="30"
/>

See "Data Source Attributes" for details on all data source attributes.

Fatal Error Code Enhancement

For each data source defined in data-sources.xml, you can define fatal error codes that indicate that the back-end database with which the data source communicates is no longer accessible. When OC4J detects one of these error codes (stated when a SQLException is thrown by the JDBC driver), OC4J will clean its connection pool. That is, it closes all connections in the connection pool. For Oracle, the predefined fatal error codes are: 3113, 3114, 1033, 1034, 1089, and 1090.

Use the following procedure to add additional fatal error codes for Oracle.

Use the <fatal-error-codes> element, which is a subtag of the <data-source> element. The <fatal-error-codes> element uses the child element <error-code> to define one fatal error code. You can define 0 - n <error-code> elements for each <fatal-error-codes> element. For example, for fatal error codes 10, 20, and 30, the data source definition would look like this:

<data-source 
class="com.evermind.sql.DriverManagerDataSource" 
name="ds" 
location="jdbc/ds" 
xa-location="jdbc/xa/ds" 
ejb-location="jdbc/ejb/ds" 
@ connection-driver="oracle.jdbc.driver.OracleDriver" 
username="scott" 
@ password="tiger" 

@ url="jdbc:oracle:thin:@//localhost:1521/oracle.regress.rdbms.dev.us.oracle.com">
 
         <fatal-error-codes> 
            <error-code code='10'/> 
            <error-code code='20'/> 
            <error-code code='30'/> 
         </fatal-error-codes> 

</data-source> 

Password Indirection

The data-sources.xml file requires passwords for authentication. Embedding these passwords into deployment and configuration files poses a security risk, especially if the permissions on this file allow it to be read by any user. To avoid this problem, OC4J supports password indirection.

An indirect password is made up of a special indirection symbol (->) and a user name (or user name and realm). When OC4J encounters an indirect password, it uses its privileged access to retrieve the password associated with the specified user from the security store provided by a user manager.

For more information on creating users and passwords, and working with a user manager, see the section on password management in the Oracle Application Server Containers for J2EE Security Guide.

For example, the sample code under "Emulated Data Sources" contains the following line:

password="tiger"

You could replace that with the indirection symbol (->) and a user name (scott) as follows:

password="->scott"

This assumes that a user named scott with the password tiger has been created in a user manager.

Because OC4J has privileged access to the security store, it can retrieve the password (tiger) associated with this user (scott).

There are two ways to configure password indirection:

Configuring an Indirect Password with Oracle Enterprise Manager 10g

To configure an indirect password using the Oracle Enterprise Manager 10g:

  1. Log into the Oracle Enterprise Manager 10g

  2. Select a target of type OC4J.

  3. Select Administer. The Oracle Enterprise Manager 10g for Oracle Application Server home page is displayed.

  4. Select Administration.

  5. Select Data Sources. A list of data sources is displayed.

  6. Click in the Select column to select a data source.

  7. Click Edit. The Edit Data Source page is displayed as shown in Figure 4-2.

    Figure 4-2 Edit Data Source Page

    Description of EMIndpw.gif follows
    Description of the illustration EMIndpw.gif

  8. In the Username and Password area, click Use Indirect Password, and enter the appropriate value in the Indirect Password field.

  9. Click Apply.

Configuring an Indirect Password Manually

To configure an indirect password for a data source manually:

  1. Edit the appropriate OC4J XML configuration or deployment file:

    • data-sources.xmlpassword attribute of <data-source> element

    • ra.xml<res-password> element

    • rmi.xmlpassword attribute of <cluster> element

    • application.xmlpassword attributes of <resource-provider> and <commit-coordinator> elements

    • jms.xml<password> element

    • internal-settings.xml<sep-property> element, attributes name=" keystore-password" and name=" truststore-password"

  2. To make any of these passwords indirect, replace the literal password string with a string containing "->", followed either by the username or by the realm and username separated by a slash ("/").

For example: <data-source password="->Scott">

This causes the User Manager to look up the user name "Scott" and use the password stored for that user.

Associating a Database Schema with a Data Source

The data source identifies a database instance. The data source schema attribute allows you to associate a data source with a database-schema.xml file that you can customize for its particular database.

When using container-managed persistence (CMP), the container is responsible for creating the database schema necessary to persist a bean. Associating a data source with a database-schema.xml file allows you to influence what SQL is ultimately generated by the container. This can help you solve problems such as accommodating additional data types supported in your application (such as java.math.BigDecimal) but not in your database.

The database-schema.xml File

A database-schema.xml file contains a database-schema element as shown in Example 4-1. It is made up of the attributes listed in Table 4-5.

Example 4-1 The database-schema Element

<database-schema case-sensitive="true" max-table-name-length="30" name="MyDatabase" not-null="not null" null="null" primary-key="primary key">
    <type-mapping type="java.math.BigDecimal" name="number(20,8)" />
    <disallowed-field name="order" />
</database-schema>

Table 4-5 database-schema.xml File Attributes

Attribute Description
case-sensitive Specifies whether this database treats names as case sensitive (true) or not (false). This applies to names specified by disallowed-field subelements.
max-table-name-length This optional attribute specifies the maximum length for table names for this database. Names longer than this value will be truncated.
name The name of this database.
not-null Specifies the keyword used by this database to indicate a not-null constraint.
null Specifies the keyword used by this database to indicate a null constraint.
primary-key Specifies the keyword used by this database to indicate a primary-key constraint.

The <database-schema> element may contain any number of the following subelements:

<type-mapping>

This subelement is used to map a Java type to the corresponding type appropriate for this database instance. It contains two attributes:

  • name: the name of the database type

  • type: the name of the Java type

<disallowed-field>

This subelement identifies a name that you must not use because it is a reserved word in this database instance. It contains one attribute:

  • name: the name of the reserved word

Example Configuration

This example shows how to map a data type supported in your application (java.math.BigDecimal) to a data type supported by the underlying database.

  1. Define the mapping for java.math.BigDecimal in your database-schemas/oracle.xml file as follows:

    <type-mapping type="java.math.BigDecimal" name="number(20,8)" />
    
    
  2. Use this schema in your <data-source> element as follows:

    <data-source 
        class="com.evermind.sql.DriverManagerDataSource" 
        name="OracleDS" 
        ejb-location="jdbc/OracleDS" 
        schema="database-schemas/oracle.xml" 
        connection-driver="oracle.jdbc.driver.OracleDriver" 
        username="scott" 
        password="tiger" 
        url="jdbc:oracle:thin:@//localhost:5521/oracle.regress.rdbms.dev.us.oracle.com"
        clean-available-connections-threshold="30" 
        rac-enabled="false" 
        inactivity-timeout="30" 
    /> 
    
    
    
  3. Use this <data-source> for your EJBs:

    <orion-ejb-jar>
       <enterprise-beans> 
         <entity-deployment name="BigDecimalTest" data-source="jdbc/OracleDS" /> 
       </enterprise-beans> 
    
    
  4. Deploy your ejb and OOC4J creates the appropriate tables.

Using Data Sources

The following sections describe how to use data sources in your application:

For information on data source methods, refer to your J2EE API documentation.

Portable Data Source Lookup

When the OC4J server starts, the data sources in the data-sources.xml file in the j2ee/home/config directory are added to the OC4J JNDI tree. When you look up a data source using JNDI, specify the JNDI lookup as follows:

DataSource ds = ic.lookup("jdbc/OracleCMTDS1");

The OC4J server looks in its own internal JNDI tree for this data source.

However, we recommend—and it is much more portable—for an application to look up a data source in the application JNDI tree, using the portable java:comp/env mechanism. Place an entry pointing to the data source in the application web.xml or ejb-jar.xml files, using the <resource-ref> tag. For example:

<resource-ref>
     <res-ref-name>jdbc/OracleDS</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
</resource-ref>

where <res-ref-name> can be one of the following:

  • The actual JNDI name—such as jdbc/OracleDS—that is defined in the data-sources.xml file. In this situation, no mapping is necessary. The preceding code example demonstrates this. The <res-ref-name> is the same as the JNDI name bound in the data-sources.xml file.

    Retrieve this data source without using java:comp/env, as shown by the following JNDI lookup:

    InitialContext ic = new InitialContext();
    DataSource ds = ic.lookup("jdbc/OracleDS");
    
    
  • A logical name that is mapped to the actual JNDI name in the OC4J-specific files, orion-web.xml or orion-ejb-jar.xml. The OC4J-specific XML files then define a mapping from the logical name in the web.xml or ejb-jar.xml file to the actual JNDI name that is defined in the data-sources.xml file.

Example 4-2 Mapping Logical JNDI Name to Actual JNDI Name

The following code demonstrates the second of the two preceding options. If you want to choose a logical name of "jdbc/OracleMappedDS" to be used within your code for the JNDI retrieval, then place the following in your web.xml or ejb-jar.xml files:

<resource-ref>
     <res-ref-name>jdbc/OracleMappedDS</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
</resource-ref>

For the actual JNDI name to be found, you must have a <resource-ref-mapping> element that maps the jdbc/OracleMappedDS to the actual JNDI name in the data-sources.xml file. If you are using the default emulated data source, then the ejb-location will be defined with jdbc/OracleDS as the actual JNDI name. For example:

<resource-ref-mapping name="jdbc/OracleMappedDS" location="jdbc/OracleDS" />

You can then look up the data source in the application JNDI name space using the Java statements:

InitialContext ic = new InitialContext();
DataSource ds = ic.lookup("jdbc/OracleMappedDS");

Retrieving a Connection from a Data Source

One way to modify data in your database is to retrieve a JDBC connection and use JDBC statements. We recommend that you, instead, use data source objects in your JDBC operations.


Note:

Data sources always return logical connections.

Perform the following steps to modify data within your database:

  1. Retrieve the DataSource object through a JNDI lookup on the data source definition in the data-sources.xml file.

    The lookup is performed on the logical name of the default data source, which is an emulated data source defined in the ejb-location tag in the data-sources.xml file.

    You must always cast or narrow the object that JNDI returns to the DataSource, because the JNDI lookup() method returns a Java object.

  2. Create a connection to the database represented by the DataSource object.

After you have the connection, you can construct and execute JDBC statements against this database that is specified by the data source.

The following code represents the preceding steps:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");
Connection conn = ds.getConnection();

Use the following methods of the DataSource object in your application code to retrieve a connection to your database:

  • getConnection();

    The user name and password are those that are defined in the data source definition.

  • getConnection(String username, String password);

    This user name and password overrides the user name and password that are defined in the data source definition.

If the data source refers to an Oracle database, then you can cast the connection object that is returned on the getConnection method to oracle.jdbc.OracleConnection, and use all the Oracle extensions. See "Using Oracle JDBC Extensions" for details.

The following example illustrates this:

oracle.jdbc.OracleConnection conn = 
  (oracle.jdbc.OracleConnection) ds.getConnection();

After you retrieve a connection, you can execute SQL statements against the database through JDBC.

Refer to "Retrieving Connections with a Nonemulated Data Source" for information on handling common connection retrieval error conditions.

Retrieving Connections with a Nonemulated Data Source

The physical behavior of a nonemulated data source object changes, depending on whether you retrieve a connection from the data source outside of or within a global transaction. The following sections discuss these differences:

Retrieving a Connection Outside a Global Transaction

If you retrieve a connection from a nonemulated data source and you are not involved in a global transaction, then every getConnection method returns a logical handle. When the connection is used for work, a physical connection is created for each connection created. Thus, if you create two connections outside of a global transaction, then both connections use a separate physical connection. When you close each connection, it is returned to a pool to be used by the next connection retrieval.

Retrieving a Connection Within a Global Transaction

If you retrieve a connection from a nonemulated data source and you are involved in a global JTA transaction, then all physical connections retrieved from the same DataSource object by the same user within the transaction share the same physical connection.

For example, if you start a transaction and retrieve two connections from the jdbc/OracleCMTDS1 DataSource with the scott user, then both connections share the physical connection. In the following example, both conn1 and conn2 share the same physical connection.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
txn.begin(); //start txn
Connection conn1 = ds.getConnection("scott", "tiger");
Connection conn2 = ds.getConnection("scott", "tiger");

However, separate physical connections are retrieved for connections that are retrieved from separate DataSource objects. The following example shows that both conn1 and conn2 are retrieved from different DataSource objects: jdbc/OracleCMTDS1 and jdbc/OracleCMTDS2. Both conn1 and conn2 will exist upon a separate physical connection.

Context ic = new InitialContext();
DataSource ds1 = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
DataSource ds2 = (DataSource) ic.lookup("jdbc/OracleCMTDS2");
txn.begin; //start txn
Connection conn1 = ds1.getConnection();
Connection conn2 = ds2.getConnection();

Connection Retrieval Error Conditions

The following mistakes can create an error condition:

Using Different User Names for Two Connections to a Single Data Source

When you retrieve a connection from a DataSource object with a user name and password, this user name and password are used on all subsequent connection retrievals within the same transaction. This is true for all data source types.

For example, suppose an application retrieves a connection from the jdbc/OracleCMTDS1 data source with the scott user name. When the application retrieves a second connection from the same data source with a different user name, such as adams, the second user name (adams) is ignored. Instead, the original user name (scott) is used.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
txn.begin(); //start txn
Connection conn1 = ds.getConnection("scott", "tiger"); //uses scott/tiger 
Connection conn2 = ds.getConnection("adams", "woods"); //uses scott/tiger also

Thus, you cannot authenticate using two different users to the same data source. If you try to access the tables as "adams/woods", you enter into an error condition.

Improperly configured OCI JDBC Driver

If you are using the OCI JDBC driver, ensure that you have configured it according to the recommendations in "Using the OCI JDBC Drivers" .

Using Two-Phase Commits and Data Sources

The Oracle two-phase commit coordinator is a DTC (distributed transaction coordinator) engine that performs two-phase commits with appropriate recovery. The two-phase commit engine is responsible for ensuring that when the transaction ends, all changes to all databases are either totally committed or fully rolled back. The two-phase commit engine can be one of the databases that participates in the global transaction, or it can be a separate database. If multiple databases or multiple sessions in the same database participate in a transaction, then you must specify a two-phase commit coordinator. Otherwise, you cannot commit the transaction.

Specify a commit coordinator in one of the following ways:

For example:

<commit-coordinator>
  <commit-class class="com.evermind.server.OracleTwoPhaseCommitDriver" />
  <property name="datasource" value="jdbc/OracleCommitDS" />
  <property name="username" value="system" />
  <property name="password" value="manager" />
</commit-coordinator>


Notes:

  • The password attribute of the <commit-coordinator> element supports password indirection. For more information, see the section on password management in the Oracle Application Server Containers for J2EE Security Guide.

  • Two-phase commits may be configured only for nonemulated data sources. For more information on data source types, see "Types of Data Sources"


If you specify a user name and password in the global application.xml file, then these values override the values in the datasource.xml file. If these values are null, then the user name and password in the datasource.xml file are used to connect to the commit coordinator.

The user name and password used to connect to the commit coordinator (for example, System) must have "force any transaction" privilege. By default, during installation, the commit-coordinator is specified in the global application.xml file, with the user name and password set as null.

Each data source participating in a two-phase commit should specify dblink information in the OrionCMTDatasource data source file This dblink should be the name of the dblink that was created in the commit coordinator database to connect to this database.

For example, if db1 is the database for the commit coordinator and db2 and db3 are participating in the global transactions, then you create link2 and link3 in the db1 database as shown in the following example.

connect commit_user/commit_user
create database link link2 using "inst1_db2"; // link from db1 to db2
create database link link3 using "inst1_db3"; // link from db1 to db3;

Next, define a data source called jdbc/OracleCommitDS in the application.xml file:

<data-source
    class="com.evermind.sql.OrionCMTDataSource"
    name="OracleCommitDS"
    location="jdbc/OracleCommitDS"
    connection-driver="oracle.jdbc.driver.OracleDriver"
    username="system"
    password="manager"
    url="jdbc:oracle:thin:@//localhost:5521/db1.regress.rdbms.dev.us.oracle.com"
   inactivity-timeout="30"/>

Here is the data source description of db2 that participates in the global transaction. Note that link2, which was created in db1, is specified as a property here:

<data-source
     class="com.evermind.sql.OrionCMTDataSource"
     name="OracleDB2"
     location="jdbc/OracleDB2"
     connection-driver="oracle.jdbc.driver.OracleDriver"
     username="scott"
     password="tiger"
     url="jdbc:oracle:thin:@//localhost:5521/db2.regress.rdbms.dev.us.oracle.com"
     inactivity-timeout="30">
     <property name="dblink"
               value="LINK2.REGRESS.RDBMS.EXAMPLE.COM"/>
</data-source>

Here is the data source description of db3 that participates in the global transaction. Note that link3, which is created in db1, is specified as a property here:

<data-source
     class="com.evermind.sql.OrionCMTDataSource"
     name="OracleDB3"
     location="jdbc/OracleDB3"
     connection-driver="oracle.jdbc.driver.OracleDriver"
     username="scott"
     password="tiger"
     url="jdbc:oracle:thin:@//localhost:5521/db3.regress.rdbms.dev.us.oracle.com"
     inactivity-timeout="30">
     <property name="dblink"
               value="LINK3.REGRESS.RDBMS.EXAMPLE.COM"/>
</data-source>

For information on the limitations of two-phase commit, see Chapter 7, "Java Transaction API".

Using Oracle JDBC Extensions

To use Oracle JDBC extensions, cast the returned connection to oracle.jdbc.OracleConnection, as follows:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
oracle.jdbc.OracleConnection conn =    (oracle.jdbc.OracleConnection) ds.getConnection();

You can use any of the Oracle extensions on the returned connection, conn.

// you can create oracle.jdbc.* objects using this connection oracle.jdbc.Statement orclStmt =   (oracle.jdbc.OracleStatement)conn.createStatement(); // assume table is varray_table oracle.jdbc.OracleResultSet rs =   orclStmt.executeQuery("SELECT * FROM " + tableName); while (rs.next())  {   oracle.sql.ARRAY array = rs.getARRAY(1); ... }

Using Connection Caching Schemes

You can define the connection caching scheme to use within the data source definition. There are three types of connection caching schemes: DYNAMIC_SCHEME, FIXED_WAIT_SCHEME, and FIXED_RETURN_NULL_SCHEME. For a description of these schemes, see the Connection Pooling and Caching chapter of the Oracle9i JDBC Developer's Guide and Reference, found at the following location:

http://st-doc.us.oracle.com/9.0/920/java.920/a96654/toc.htm

To specify a caching scheme, specify an integer or string value for a <property> element named cacheScheme. Table 4-6 shows the supported values.

Table 4-6 Connection Caching Schemes

Value Cache Scheme
1 DYNAMIC_SCHEME
2 FIXED_WAIT_SCHEME
3 FIXED_RETURN_NULL_SCHEME


Note:

The connection cache scheme discussion in this section applies only to native data sources. It does not apply to any other data source.

The following example is a data source using the DYNAMIC_SCHEME.

<data-source
  class="oracle.jdbc.pool.OracleConnectionCacheImpl"
  name="OracleDS"
  location="jdbc/pool/OracleCache"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@//hostname:TTC port number/SERVICE
  inactivity-timeout="30">
  <property name="cacheScheme" value="1" />
</data-source>

In this example, for the <property name> element, you could also specify value="DYNAMIC_SCHEME".

When you create a data source in data-sources.xml, be aware of the following: When class is set to oracle.jdbc.pool.OracleConnectionCacheImpl, you must not specify the ejb-location, xa-location, and pooled-location attributes. Specify only the location attribute. Accessing the data source using any other attribute with JNDI causes unpredictable cleanup of cached connections in the event that the database goes down.

Using the OCI JDBC Drivers

The examples of Oracle data source definitions in this chapter use the Oracle JDBC Thin driver. However, you can use the Oracle JDBC OCI driver as well. Do the following before you start the OC4J server:

The URL to use in the url attribute of the <data-source> element definition can have any of these forms:

Notes on Oracle JDBC-OCI driver upgrade in the Oracle Application Server

It is not possible to upgrade to an arbitrary Oracle JDBC-OCI driver version due to client library compatibility constraints. Upgrading to OCI driver versions with matching Oracle Client libraries that are installed within the Oracle Application Server 10g (10.1.2) is supported. For example, Oracle JDBC 10.1.x drivers are supported, but the Oracle JDBC 9.2.x drivers are not.

Where the use of JDBC-OCI within the Oracle Application Server is supported, it is also necessary for the opmn.xml entry for each OC4J instance to propagate appropriate ORACLE_HOME and library path values to its startup environment.

The environment variable ORACLE_HOME is common to all platforms, but the name of the environment variable that specifies the library path is different depending on the operating systems:

  • LD_LIBRARY_PATH for Solaris

  • SLIB_PATH for AIX

  • SHLIB_PATH for HP-UX

  • PATH for Windows

The generic syntax for specifying the library paths in opmn.xml looks like this:

<prop name="<LIB_PATH_VARIABLE>" value="<LIB_PATH_VARIABLE_VALUE>"/>

where <LIB_PATH_VARIABLE> should be replaced with the appropriate platform-specific variable name that specifies the library path, and

<LIB_PATH_VARIABLE_VALUE>

should be replaced with that variable's value.

Here is an example, assuming the Solaris OS:

    <process-type id="OC4J_SECURITY" module-id="OC4J">
      <environment>
        <variable id="ORACLE_HOME"
  value="/u01/app/oracle/product/inf10120"/>
        <variable
          id="LD_LIBRARY_PATH"
          value="/u01/app/oracle/product/inf10120/lib"
        />
      </environment>
      ...
   

Using DataDirect JDBC Drivers

When your application must connect to heterogeneous databases, use DataDirect JDBC drivers. DataDirect JDBC drivers are not meant to be used with an Oracle database but for connecting to non-Oracle databases, such as Microsoft, SQLServer, Sybase, and DB2. If you want to use DataDirect drivers with OC4J, then add corresponding entries for each database in the data-sources.xml file.

Installing and Setting Up DataDirect JDBC Drivers

Install the DataDirect JDBC drivers as described in the DataDirect Connect for JDBC User's Guide and Reference

After you have installed the drivers, follow these instructions to set them up.


Note:

In the following instructions, note these definitions:
  • OC4J_INSTALL: In a standalone OC4J environment, the directory into which you unzip the file oc4j_extended.zip. In an Oracle Application Server, OC4J_INSTALL is ORACLE_HOME.

  • In both a standalone OC4J environment and an Oracle Application Server, DDJD_INSTALL is the directory into which you unzip the content of the DataDirect JDBC drivers.

  • In a standalone OC4J environment, INSTANCE_NAME is home.

  • In an Oracle Application Server, INSTANCE_NAME is the OC4J instance into which you install the DataDirect JDBC drivers.


  1. Unzip the content of the DataDirect JDBC drivers to the directory DDJD_INSTALL.

  2. Create the directory OC4J_INSTALL/j2ee/INSTANCE_NAME/applib if it does not already exist.

  3. Copy the DataDirect JDBC drivers in DDJD_INSTALL/lib to the OC4J_INSTALL/j2ee/INSTANCE_NAME/applib directory.

  4. Verify that the file application.xml contains a library entry that references the j2ee/home/applib location, as follows:

    <library path="../../INSTANCE_NAME/applib" />
    
    
  5. Add data sources to the file data-source.xml as described in "Example DataDirect Data Source Entries".

Example DataDirect Data Source Entries

This section shows an example data source entry for each of the following non-Oracle databases:

You can also use vendor-specific data sources in the class attribute directly. That is, it is not necessary to use an OC4J-specific data source in the class attribute.

For more detailed information, refer to the DataDirect Connect for JDBC User's Guide and Reference.


Note:

OC4J does not work with non-Oracle data sources in the non-emulated case. That is, you cannot use a non-Oracle data source in a two-phase commit transaction.

SQLServer

The following is an example of a data source entry for SQLServer.

<data-source 
   class="com.evermind.sql.DriverManagerDataSource"
   name="MerantDS" 
   location="jdbc/MerantCoreSSDS" 
   xa-location="jdbc/xa/MerantSSXADS" 
   ejb-location="jdbc/MerantSSDS" 
   connection-driver="com.oracle.ias.jdbc.sqlserver.SQLServerDriver"
   username="test" 
   password="secret" 
   url="jdbc:oracle:sqlserver://hostname:port;User=test;Password=secret"
   inactivity-timeout="30" 
 />

DB2

Here is a data source configuration sample for a DB2 database:

<data-source 
  class="com.evermind.sql.DriverManagerDataSource"
  name="MerantDS" 
  location="jdbc/MerantDB2DS" 
  xa-location="jdbc/xa/MerantDB2XADS" 
  ejb-location="jdbc/MerantDB2DS" 
  connection-driver="com.oracle.ias.jdbc.db2.DB2Driver"
  username="test" 
  password="secret" 
  url="jdbc:oracle:db2://hostname:port;LocationName=jdbc;CollectionId=default;"
  inactivity-timeout="30" 
/>

Sybase

Here is a data source configuration sample for a Sybase database:

<data-source 
  class="com.evermind.sql.DriverManagerDataSource"
  name="MerantDS" 
  location="jdbc/MerantCoreSybaseDS" 
  xa-location="jdbc/xa/MerantSybaseXADS" 
  ejb-location="jdbc/MerantSybaseDS" 
  connection-driver="com.oracle.ias.jdbc.sybase.SybaseDriver"
  username="test" 
  password="secret" 
  url="jdbc:oracle:sybase://hostname:port;User=test;Password=secret"
  inactivity-timeout="30" 
/>

High Availability Support for Data Sources

This section discusses high availability (HA) support for data sources.

A high availability (HA) architecture must encompass redundancy across all components, achieve fast client failover for all types of outages, provide consistent high performance, and provide protection from user errors, corruptions, and site disasters, while being easy to deploy, manage, and scale.

Oracle Maximum Availability Architecture (MAA)

The Oracle Maximum Availability Architecture (MAA) provides recommendations and configuration instructions to help you choose and implement an Oracle platform availability architecture that best fits your availability requirements.

The main MAA recommendations are:

  • Use redundant middle-tier or application tier (Oracle Application Server), network, and storage infrastructure.

  • Use Oracle Data Guard to protect from human errors and data failures, and to recover from site failures.

  • Use Real Application Clusters (RAC) at each site to protect from host and instance failures.

  • Use sound operational best practices (such as fast-start check pointing to control the amount of time required to recover from an instance failure).

For more information about MAA, see

http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm 

Oracle Data Guard

Oracle Data Guard is software integrated with the Oracle database that maintains a real-time copy of a production database, called a standby database, and keeps this instance synchronized with its redundant mate. Oracle Data Guard manages the two databases using log transport services, managed recovery, switchover, and failover features.

Real Application Clusters (RAC)

RAC uses two or more nodes or computers, each running an Oracle instance that accesses a single database residing on shared-disk storage. In a RAC environment, all active instances can concurrently execute transactions against the shared database. RAC automatically coordinates each instance's access to the shared data to provide data consistency and data integrity.

RAC depends on two types of failover mechanisms:

  • Network failover implemented in the network layer.

  • Transparent Application Failover (TAF) implemented on top of the network layer.

Network Failover

Network failover is the default failover and is the only type of failover available when using the JDBC Thin driver. Network failure ensures that newer database connections created after a database instance in an RAC cluster goes down are created against a backup or surviving database instance in that cluster, even though the TNS alias that was used to create the newer database connection was for the database instance that went down. When network failover is the only available failover mechanism, then existing connections are not automatically reconnected to surviving RAC instances. These existing connections are no longer usable, and you will get ORA-03113 exceptions if you try to use them. Ongoing database operations (including AQ operations) can fail with a wide variety of exceptions when failover occurs in a RAC cluster configured to perform only network failover.

TAF Failover

TAF failover is available only when using the JDBC OCI driver. To enable it, you must set the FAILOVER_MODE as part of the CONNECT_DATA portion of the TNS alias used to create the JDBC connection.

TAF is a runtime failover for high-availability environments, such as RAC and Data Guard, that refers to the failover and re-establishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails, and optionally resume a SELECT statement in progress. This reconnect happens automatically from within the Oracle Call Interface (OCI) library.

TAF provides a best effort failover mechanism for ongoing operations on a database connection created against a database instance that is part of a RAC cluster. It also attempts to ensure that existing connections that are not in use at failover time are reconnected to a backup or surviving database instance. However, TAF is not always able to replay transactional operations that occur past the last committed transaction. When this happens, it usually throws an ORA-25408 ("cannot safely replay call") error. It is then your application's responsibility to explicitly roll back the current transaction before the database connection can be used again. Your application must also replay all the operations past the last committed transaction to return to the same state as that before the failover occurred.

TAF protects or fails over:

  • Database connections

  • User session states

  • Prepared statements

  • Active cursors (SELECT statements) that began returning results at the time of failure

TAF neither protects nor fails over:

  • Applications not using OCI8 or higher

  • Server-side program variables, such as PL/SQL package states

  • Active Update transactions (see "Acknowledging TAF Exceptions")

High Availability (HA) Support in OC4J

You can integrate OC4J with RAC, Data Guard, and TAF as part of your HA architecture.

The following sections describe configuration issues specific to OC4J that relate directly to HA. Use this information in conjunction with MAA recommendations and procedures.

The following sections discuss OC4J HA configuration issues:

Configuring Network Failover with OC4J

To configure OC4J to use network failover:

  1. Configure a network failover-enabled data source in data-sources.xml.

    For example:

    <data-source
        class="com.evermind.sql.DriverManagerDataSource"
        name="OracleDS"
        location="jdbc/OracleCoreDS"
        xa-location="jdbc/xa/OracleXADS"
        ejb-location="jdbc/OracleDS"
        connection-driver="oracle.jdbc.driver.OracleDriver"
        username="scott"
        password="tiger"
        url="jdbc:oracle:thin:@(DESCRIPTION=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=TCP) (HOST=host1) (PORT=1521))
            (ADDRESS=(PROTOCOL=TCP) (HOST=host2) (PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=service_name)))"
        inactivity-timeout="300"
        connection-retry-interval="2"
        max-connect-attempts="60"
        max-connections="60"
        min-connections="12"
    />
    
    

    In this example, note the url element. As long as two or more hosts are specified, the JDBC client randomly chooses one of the alternatives if the current host is unreachable.

    For details on data source configuration, see "Defining Data Sources".

Configuring Transparent Application Failover (TAF) with OC4J

To configure OC4J for use with TAF:

  1. Configure a TAF descriptor as described in "Configuring a TAF Descriptor (tnsnames.ora)".

  2. Configure a TAF-enabled data source in data-sources.xml. For example:

    <data-source
        class="com.evermind.sql.DriverManagerDataSource"
        name="OracleDS"
        location="jdbc/OracleCoreDS"
        xa-location="jdbc/xa/OracleXADS"
        ejb-location="jdbc/OracleDS"
        connection-driver="oracle.jdbc.driver.OracleDriver"
        username="scott"
        password="tiger"
        url="jdbc:oracle:oci8:@(description=(load_balance=on)(failover=on)
          (address=(protocol=tcp)(host=db-node1)(port=1521))
          (address=(protocol=tcp)(host=db-node2)(port=1521))
          (address=(protocol=tcp)(host=db-node3)(port=1521))
          (address=(protocol=tcp)(host=db-node4)(port=1521))
          (connect_data=
            (service_name=db.us.oracle.com)
            (failover_mode=(type=select)(method=basic)(retries=20)(delay=15))))"
        rac-enabled="true"
        inactivity-timeout="300"
        connection-retry-interval="2"
        max-connect-attempts="60"
        max-connections="60"
        min-connections="12"
    />
    
    

    In this example, note that the url element failover is on and failover_mode is defined. As long as two or more hosts are specified, the JDBC client randomly chooses one of the alternatives if the current host is unreachable. For a description of failover_mode options, see Table 4-7, "TAF Configuration Options".

    For details on data source configuration, see "Defining Data Sources".

  3. Configure Oracle JMS as the Resource Provider for JMS in the orion-application.xml file. For example:

    <resource-provider 
        class="oracle.jms.OjmsContext" name="cartojms1">
        <description> OJMS/AQ </description>
        <property name="datasource" value="jdbc/CartEmulatedDS"></property>
    </resource-provider>
    
    

Note:

Only data sources configured to use the JDBC OCI client can be configured for use with TAF.

Configuring a TAF Descriptor (tnsnames.ora)

TAF is configured using Net8 parameters in the tnsnames.ora file.

TAF can be configured by including a FAILOVER_MODE parameter under the CONNECT_DATA section of a connect descriptor. TAF supports the subparameters described in Table 4-7.

Table 4-7 TAF Configuration Options

Subparameter Description
BACKUP Specify a different net service name for backup connections. A backup should be specified when using the PRECONNECT METHOD to pre-establish connections.
TYPE Specify the type of fail over. Three types of Oracle Net failover functionality are available by default to Oracle Call Interface (OCI) applications:
  • SESSION: Set to failover the session. If a user's connection is lost, then a new session is automatically created for the user on the backup. This type of failover does not attempt to recover selects.

  • SELECT: Set to enable users with open cursors to continue fetching on them after failure. However, this mode involves overhead on the client side in normal select operations.

  • NONE: This is the default. No failover functionality is used. This can also be explicitly specified to prevent failover from happening.

METHOD Determines how fast failover occurs from the primary node to the backup node:
  • BASIC: Set to establish connections at failover time. This option requires almost no work on the backup server until failover time.

  • PRECONNECT: Set to pre-established connections. This provides faster failover, but requires that the backup instance be able to support all connections from every supported instance.

RETRIES Specify the number of times to attempt to connect after a failover. If DELAY is specified, RETRIES defaults to five retry attempts.

Note: If a callback function is registered, then this subparameter is ignored.

DELAY Specify the amount of time, in seconds, to wait between connect attempts. If RETRIES is specified, DELAY defaults to one second.

Note: If a callback function is registered, then this subparameter is ignored.


In the following example, Oracle Net connects randomly to one of the protocol addresses on sales1-server or sales2-server. If the instance fails after the connection, then the TAF application fails over to the listener on another node.

sales.us.acme.com=
    (DESCRIPTION=
        (LOAD_BALANCE=on)
        (FAILOVER=on)
        (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
        (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))
        (CONNECT_DATA=
            (SERVICE_NAME=sales.us.acme.com)
            (FAILOVER_MODE=
                (TYPE=session)
                (METHOD=basic)
                (RETRIES=20)
                (DELAY=15))))

For more information on configuring TAF, refer to the "Oracle9i Net Services Administrator's Guide".

Connection Pooling

If you have a transaction spanning two beans and each bean gets a JDBC connection to the same database but different instances, then on commit, OC4J issues a simple commit (instead of a Two-Phase Commit), which makes the transaction suspect. If your application encounters such transactions, use either TAF or connection pooling, but not both.

In case of an instance failure, dead connections are cleaned from both the OC4J connection pool and from the JDBC type 2 connection pool.

If a database goes down and getConnection() is called, and if connection pooling is used, then the pool is cleaned up. The caller must catch the exception on the getConnection() call, and retry. In some cases, the OC4J container does the retries.

OC4J cleans up a connection pool when the connection is detected to be bad. That is, if getConnection() throws a SQLException with error code 3113 or 3114.

When an exception occurs while using a user connection handle, it is useful for OC4J to detect whether the exception is due to a database connection error or to a database operational error. The most common error codes thrown by the database when a connection error occurs are 3113 and 3114. These errors are returned typically for in-flight connections that get dropped. In addition, new connection attempts may receive error codes 1033, 1034, 1089 and 1090.

Fast-connection cleanup is implemented in both non-RAC and RAC environments.

In a non-RAC environment, when ajava.sql.SQLException is thrown, all unallocated connections are removed from the pool.

In a RAC environment, when ajava.sql.SQLException is thrown, first the states of all unallocated connections are checked. Connections that are alive are left alone. Otherwise, they are removed from the pool.

Acknowledging TAF Exceptions

Active Update transactions are rolled back at the time of failure because TAF cannot preserve active transactions after failover. TAF requires an acknowledgement from the application that a failure has occurred through a rollback command. In other words, the application receives an error message until a ROLLBACK is submitted.

A common failure scenario is as follows:

  1. A JDBC Connection fails or is switched over by TAF.

  2. TAF issues an exception.

  3. TAF waits for an acknowledgement from the application in the form of a ROLLBACK.

  4. The application rolls back the transaction and replays it.

Using Oracle Call Interface (OCI) callbacks and failover events, your application can customize TAF operations to automatically provide the required acknowledgement.

Your application (J2EE components) can capture the failure status of an Oracle instance and customize TAF by providing a function that the OCI library will automatically call during failover, using OCI callback capabilities. Table 4-8 describes the failover events defined in the OCI API.

Table 4-8 OCI API Failover Events

Symbol Value Meaning
FO_BEGIN 1 A lost connection has been detected and failover is starting.
FO_END 2 A successful completion of failover.
FO_ABORT 3 An unsuccessful failover with no option of retrying.
FO_REAUTH 4 A user handle has been re-authenticated.
FO_ERROR 5 A failover was temporarily unsuccessful but the application has the opportunity to handle the error and retry.
FO_RETRY 6 Retry failover.
FO_EVENT_UNKNOWN 7 A bad or unknown failover event.

For more information, see the Oracle Call Interface Programmer's Guide.

SQL Exception Handling

Depending on the driver type used, SQL Exceptions will have different error codes and transaction replay may or may not be supported.

These error codes are obtained by making a getErrorCode() call on the java.sql.SQLException thrown to the caller.

Table 4-9 summarizes these issues by driver type.

Table 4-9 SQL Exceptions and Driver Type

Driver Error Code Servlet Layer Session Bean (CMT, BMT) Entity Bean (CMP)
Thin JDBC 17410 Replay works. Replay works (ignore "No _activetransaction" error). Replay not supported.
OCI 3113, 3114 Replay works. Replay not supported. Replay not supported.
OCI/TAF
After application sends acknowledgement to TAF (see "Acknowledging TAF Exceptions"), replay on surviving node works. After application sends acknowledgement to TAF (see "Acknowledging TAF Exceptions"), replay on surviving node works. If application sends acknowledgement to TAF (see "Acknowledging TAF Exceptions"), then OC4J proceeds transparently.