Configuring and Managing WebLogic JDBC

Introduction and Roadmap

Document Scope and Audience

Guide to this Document

JDBC Samples and Tutorials

Avitek Medical Records Application (MedRec) and Tutorials

JDBC Examples in the WebLogic Server Distribution

New and Changed JDBC Features in This Release

JDBC 3.0 Support

RowSets Enhancements

Modular Deployment of System and Stand-Alone JDBC Resources

Simplified JDBC Resource Configuration

Support for the J2EE Management Model (JSR-77)

Support for Logging Last Resource and Other Transaction Options

Credential Mapping for WebLogic Server User IDs to Database User IDs

Other Data Source/Connection Pool Enhancements

Multi Data Source Enhancements

New WLST Script Examples for JDBC Administration

JDBC Monitoring and Diagnostics Enhancements

Updated WebLogic Type 4 JDBC Drivers

Removed, Deprecated, and Changed JDBC Features, Methods, Interfaces, and MBeans

Configuring WebLogic JDBC Resources

Understanding JDBC Resources in WebLogic Server

Ownership of Configured JDBC Resources

JDBC Configuration Files

JDBC System Modules

JDBC Application Modules

JDBC Module File Naming Requirements

JDBC Modules in Versioned Applications

JDBC Schema

JMX and WLST Access for JDBC Resources

JDBC MBeans for System Resources

JDBC Management Objects in the J2EE Management Model (JSR-77 Support)

Using WLST to Create JDBC System Resources

How to Modify and Monitor JDBC Resources

Best Practices when Using WLST to Configure JDBC

Overview of Clustered JDBC

Configuring JDBC Data Sources

Understanding JDBC Data Sources

Creating a JDBC Data Source

General Data Source Options

Selecting a JDBC Driver

JDBC Data Source Names

Binding a Data Source to the JNDI Tree with Multiple Names

Transaction Options

Enabling Support for Global Transactions with a Non-XA JDBC Driver

Understanding the Logging Last Resource Transaction Option

Advantages to Using the Logging Last Resource Optimization

Enabling the Logging Last Resource Transaction Optimization

Programming Considerations and Limitations for LLR Data Sources

Administrative Considerations and Limitations for LLR Data Sources

Understanding the Emulate Two-Phase Commit Transaction Option

Limitations and Risks When Emulating Two-Phase Commit Using a Non-XA Driver

Connection Pool Features

Selecting a JDBC Driver

Enabling JDBC Driver-Level Features

Database Password Handling in a JDBC Data Source Connection Pool

Configuring Credential Mapping for a Data Source

Initializing Database Connections with SQL Code

Tuning Data Source Connection Pool Options

Increasing Performance with the Statement Cache

Statement Cache Algorithms

Statement Cache Size

Usage Restrictions for the Statement Cache

Connection Testing Options for a Data Source

Database Connection Testing Semantics

Database Connection Testing Configuration Recommendations

Default Test Table Name

Enabling Connection Creation Retries

Enabling Connection Requests to Wait for a Connection

Connection Reserve Timeout

Limiting the Number of Waiting Connection Requests

Automatically Recovering Leaked Connections

Avoiding Server Lockup with the Correct Number of Connections

Limiting Statement Processing Time with Statement Timeout

Using Pinned-To-Thread Property to Increase Performance

Changes to Connection Pool Administration Operations When PinnedToThread is Enabled

Additional Database Resource Costs When PinnedToThread is Enabled

Deploying Data Sources on Servers and Clusters

Minimizing Server Startup Hang Caused By an Unresponsive Database

Securing JDBC Data Sources

Setting Security Policies for JDBC Resources

Security Roles for JDBC MBeans

JDBC Domain Configuration MBeans

JDBC System Module MBeans

JDBC Data Source Factories (Deprecated)

Configuring JDBC Multi Data Sources

Multi Data Source Features

Creating and Configuring Multi Data Sources

Choosing the Multi Data Source Algorithm

Failover

Load Balancing

Multi Data Source Fail-Over Limitations and Requirements

Test Connections on Reserve to Enable Fail-Over

No Fail-Over for In-Use Connections

Multi Data Source Failover Enhancements

Connection Request Routing Enhancements When a Data Source Fails

Automatic Re-enablement on Recovery of a Failed Data Source within a Multi Data Source

Enabling Failover for Busy Data Sources in a Multi Data Source

Controlling Multi Data Source Failover with a Callback

Callback Handler Requirements

Callback Handler Configuration

How It Works—Failover

Controlling Multi Data Source Failback with a Callback

How It Works—Failback

Deploying JDBC Multi Data Sources on Servers and Clusters

Using Third-Party JDBC Drivers with WebLogic Server

Third-Party JDBC Drivers Installed with WebLogic Server

Setting the Environment for a Type-4 Third-Party JDBC Driver

Globalization Support for the Oracle 10g Thin Driver

Using the Oracle Thin Driver in Debug Mode

Monitoring WebLogic JDBC Resources

Viewing Runtime Statistics

Data Source Statistics

Prepared Statement Cache Statistics

Collecting Profile Information

Profile Types

Connection Usage (PROFILE_TYPE_CONN_USAGE_STR)

Connection Reservation Wait (PROFILE_TYPE_CONN_RESV_WAIT_STR)

Connection Reservation Failed (PROFILE_TYPE_CONN_RESV_FAIL_STR)

Connection Leak (PROFILE_TYPE_CONN_LEAK_STR)

Connection Last Usage (PROFILE_TYPE_CONN_LAST_USAGE_STR)

Connection Multithreaded Usage (PROFILE_TYPE_CONN_MT_USAGE_STR)

Statement Cache Entry (PROFILE_TYPE_STMT_CACHE_ENTRY_STR)

Statements Usage (PROFILE_TYPE_STMT_USAGE_STR)

Accessing Diagnostic Data

Callbacks for Monitoring Driver-Level Statistics

Debugging JDBC Data Sources

Enabling Debugging

Enable Debugging Using the Command Line

Enable Debugging Using the WebLogic Server Administration Console

Enable Debugging Using the WebLogic Scripting Tool

Changes to the config.xml File

JDBC Debugging Scopes

Request Dyeing

Managing WebLogic JDBC Resources

Testing Data Sources and Database Connections

Managing the Statement Cache for a Data Source

Clearing the Statement Cache for a Data Source

Clearing the Statement Cache for a Single Connection

Shrinking a Data Source

Resetting a Data Source

Suspending a Data Source

Resuming a Data Source

Shutting Down a Data Source

Restarting a Data Source

Configuring JDBC Application Modules for Deployment

Packaging a JDBC Module with an Enterprise Application: Main Steps

Creating Packaged JDBC Modules

Creating a JDBC Data Source Module Using the Administration Console

JDBC Packaged Module Requirements

JDBC Application Module Limitations

Creating a JDBC Data Source Module

Creating a JDBC Multi Data Source Module

Encrypting Database Passwords in a JDBC Module

Application Scoping for a Packaged JDBC Module

Referencing a JDBC Module in J2EE Descriptor Files

Packaged JDBC Module References in weblogic-application.xml

Packaged JDBC Module References in Other Descriptors

Packaging an Enterprise Application with a JDBC Module

Deploying an Enterprise Application with a JDBC Module

Getting a Database Connection from a Packaged JDBC Module

Using WebLogic Server with Oracle RAC

Overview of Oracle Real Application Clusters

Oracle RAC Scalability with WebLogic Server

Oracle RAC Availability with WebLogic Server

Oracle RAC Load Balancing with WebLogic Server

Oracle RAC Failover with WebLogic Server

Environment

Hardware Requirements

WebLogic Server Cluster

Oracle RAC Cluster

Shared Storage

Software Requirements

Configuration Considerations for Oracle

Configuring the Listener Process for Each Oracle RAC Instance

Disabling Remote Listeners

Configuration Options in WebLogic Server with Oracle RAC

Choosing a WebLogic Server Configuration for Use with Oracle RAC

Required JDBC Drivers

Configuration Considerations for Failover

Multi Data Source-Managed Failover

Connect-Time Failover

Delays During Failover

Failure Handling Walkthrough for Global Transactions

Using Multi Data Sources with Oracle RAC

Attributes of a Multi Data Source

Using Multi Data Sources with Global Transactions

Rules for Data Sources within a Multi Data Source Using Global Transactions

Required Attributes of Data Sources within a Multi Data Source Using Global Transactions

Sample Configuration Code

Using Multi Data Sources without Global Transactions

Attributes of Data Sources within a Multi Data Source Not Using Global Transactions

Sample Configuration Code

Using Connect-Time Failover with Oracle RAC

Using Connect-Time Failover without Global Transactions

Attributes of a Connect-Time Failover Configuration without Global Transactions

Sample Configuration Code

XA Considerations and Limitations with Oracle RAC

Required JDBC Driver Configuration for Use with XA

Oracle 9i RAC XA Requirements

A Global Transaction Must Be Initiated, Prepared, and Concluded in the Same Instance of the RAC Cluster

Transaction IDs Must Be Unique Within the RAC Cluster

Known Limitations When Using Oracle 9i RAC with WebLogic Server

Potential for Inconsistent Transaction Completion (Data Loss) in Some Failure Conditions