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

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 Java EE 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

Heuristic Completions and Data Inconsistency

Cannot Recover Pending Transactions

Possible Performance Loss with Non-XA Resources in Multi-Server Configurations

Only One Non-XA Participant

Connection Pool Features

Selecting a JDBC Driver

Enabling JDBC Driver-Level Features

Initializing Database Connections with SQL Code

Setting Database Security Credentials

Types of Data Source Pools

Using a User Name/Password

Set Client ID On Connection

Identity-based Connection Pooling

How Heterogeneous Connections are Created

Using Identity-based Pooling with Global Transactions

Using Identity-based Pooling with LLR

Tuning Data Source Connection Pool Options

Increasing Performance with the Statement Cache

Statement Cache Algorithms

LRU (Least Recently Used)

Fixed

Statement Cache Size

Usage Restrictions for the Statement Cache

Calling a Stored Statement After a Database Change May Cause Errors

Using setNull In a Prepared Statement

Statements in the Cache May Reserve Database Cursors

Connection Testing Options for a Data Source

Database Connection Testing Semantics

Connection Testing When Database Connections are Created

Periodic Connection Testing

Testing Reserved Connections

Minimized Connection Test Delay After Database Connectivity Loss

Minimized Connection Request Delay After Connection Test Failures

Minimizing Connection Request Delay with Seconds to Trust an Idle Pool Connection

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

Security for 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

Removing a Database Node

Adding a Database Node

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 11g 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

Starting 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

Deploying JDBC Modules to New Domains

Application Scoping for a Packaged JDBC Module

Referencing a JDBC Module in Java EE 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

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

Using Fast Connection Failover

Required JDBC Driver Configuration for use with Oracle Fast Connection Failover

XA Considerations and Limitations with Oracle RAC

Required JDBC Driver Configuration for Use with XA

Oracle 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 RAC with WebLogic Server

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

Potential for Data Deadlocks in Some Failure Scenarios

Potential for Transactions Completed Out of Sequence