Oracle® Fusion Middleware

Configuring and Managing JDBC Data Sources for Oracle WebLogic Server

12c Release 1 (12.1.1)

E24367-06

August 2014

This book contains JDBC data source configuration and administration information.

Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server, 12c Release 1 (12.1.1)

E24367-06

Copyright © 2007, 2014, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Contents

Preface

- Documentation Accessibility

- Conventions

1 Introduction and Roadmap

- Document Scope and Audience

- Guide to this Document

- Related Documentation

- JDBC Samples and Tutorials

- Avitek Medical Records Application (MedRec) and Tutorials

- JDBC Examples in the WebLogic Server Distribution

- New and Changed JDBC Data Source Features in This Release

- CapacityIncrement Attribute

- MinCapacity Attribute

- Define Fatal Error Codes

- Data Source Profile Logging

- Application-scoped Drivers

- Oracle BI Server Support

- Keep Connection After Global Transaction

- Session Affinity Policy

- Secure RMI Driver Communication

- Proxy Authentication

- Set Database Credentials on a Connection

- Connection Harvesting

- Connection Labeling

- New Debug Scopes

- ONS Debugging

2 Configuring WebLogic JDBC Resources

- Understanding JDBC Resources in WebLogic Server

- Ownership of Configured JDBC Resources

- Data Source Configuration Files

- JDBC System Modules

- JDBC Application Modules

- Including Drivers in EAR/WAR Files

- 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 Resources

- Creating High-Availability JDBC Resources

3 Configuring JDBC Data Sources

- Understanding JDBC Data Sources

- Types of WebLogic Server JDBC Data Sources

- Creating a JDBC Data Source

- JDBC Data Source Properties

- Data Source Names

- JNDI Names

- Selecting a Database Type

- Selecting a JDBC Driver

- Configure Transaction Options

- Configure Connection Properties

- Configuring Connection Properties for Oracle BI Server

- Test Connections

- Target the Data Source

- Configuring Connection Pool Features

- Enabling JDBC Driver-Level Features

- Enabling Connection-based System Properties

- Initializing Database Connections with SQL Code

- Advanced Connection Properties

- Define Fatal Error Codes

- Configuring Oracle Parameters

- Configuring an ONS Client

- Tuning Generic Data Source Connection Pools

- 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

- JDBC Data Source Factories (Deprecated)

4 Using GridLink Data Sources

- What is a GridLink Data Source

- Fast Connection Failover

- Runtime Connection Load Balancing

- Graceful Handling for Oracle RAC Outages

- Handling for Oracle RAC Outages Prior to Oracle RAC 11.2

- GridLink Affinity

- Session Affinity Policy

- XA Affinity Policy

- SCAN Addresses

- Secure Communication using Oracle Wallet

- Creating a GridLink Data Source

- JDBC Data Source Properties

- Data Source Names

- JNDI Names

- Select an XA or Non-XA Driver

- Configure Transaction Options

- Configure Connection Properties

- Enter Connection Properties

- Enter a Complete URL

- Test Connections

- Configure an ONS Client Configuration

- Secure ONS Client Communication

- Test ONS Client Configuration

- Target the Data Source

- Using Socket Direct Protocol

- Configuring Runtime Load Balancing

- Configuring Connection Pool Features

- Enabling JDBC Driver-Level Features

- Enabling Connection-based System Properties

- Initializing Database Connections with SQL Code

- Configuring Oracle Parameters

- Configuring an ONS Client

- Enabling FAN Events

- Configuring Generic Data Source Connection Testing

- Using a Wallet File

- Configuring Oracle Parameters

- Tuning GridLink Data Source Connection Pools

- Setting Database Security Credentials

- Using a User Name/Password

- Set Client ID On Connection

- Monitoring GridLink JDBC Resources

- Viewing Run-Time Statistics

- JDBCOracleDataSourceInstanceRuntimeMBean

- JDBCDataSourceRuntimeMBean

- ONSDaemonRuntimeMBean

- Debug GridLink Data Sources

- JDBC Debugging Scopes

- UCP JDK Logging

- Enable Debugging Using the Command Line

- Best Practices for GridLink Data Sources

- Catch and Handle Exceptions

5 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

6 Advanced Configurations for Oracle Drivers and Databases

- Options to Improve Driver Performance

- Proxy Authentication for Oracle Databases

- How to Configure Proxy Authentication

- Setting Database Credentials on a Connection

- Considerations When Setting Database Credentials on a Connection

- Configuring Connection Harvesting

- Enable Connection Harvesting

- Make Connections Harvestable

- Recover Harvested Connections

- Labeling Connections

- Implementing Labeling Callbacks

- Creating a Labeling Callback

- Example Labeling Callback

- Registering a Labeling Callback

- Removing a Labeling Callback

- Applying Connection Labels

- Reserving Labeled Connections

- Checking Unmatched labels

- Removing a Connection Label

7 JDBC Data Source 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

- Multiple Non-XA Participants

8 Using Roles and Policies to Secure JDBC Data Sources

- Setting Security Policies for JDBC Resources

- Security Roles for JDBC MBeans

- JDBC Domain Configuration MBeans

- JDBC System Module MBeans

9 Deploying Data Sources on Servers and Clusters

- Deploying Data Sources on Servers and Clusters

- Minimizing Server Startup Hang Caused By an Unresponsive Database

10 Tuning Data Source Connection Pools

- 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

- Minimized Connection Request Delays After Loss of DBMS Connectivity

- 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

- Using Unwrapped Data Type Objects

- How to Disable Wrapping

- Disable Wrapping using the Administration Console

- Disable Wrapping using WLST

11 Using WebLogic Server with Oracle RAC

- Overview of Oracle Real Application Clusters

- Software Requirements

- JDBC Driver Requirements

- Hardware Requirements

- WebLogic Server Cluster

- Oracle RAC Cluster

- Shared Storage

- Configuration Options in WebLogic Server with Oracle RAC

- Choosing a WebLogic Server Configuration for Use with Oracle RAC

- Validating Connections when using WebLogic Server with Oracle RAC

- Additional Considerations When Using WebLogic Server with Oracle RAC

12 Using JDBC Drivers with WebLogic Server

- JDBC Driver Support

- JDBC Drivers Installed with WebLogic Server

- Setting the Environment for a Third-Party JDBC Driver

- Globalization Support for the Oracle 11g Thin Driver

- Using the Oracle Thin Driver in Debug Mode

- Using the Oracle Thin Driver Over SSL

13 Monitoring WebLogic JDBC Resources

- Viewing Run-Time Statistics

- Data Source Statistics

- Prepared Statement Cache Statistics

- Profile Logging

- 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)

- Connection Unwrap (WEBLOGIC.JDBC.CONN.UNWRAP)

- Example Profile Information Record Log

- Accessing Diagnostic Data

- Callbacks for Monitoring Driver-Level Statistics (Deprecated)

- 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

- Setting Debugging for UCP/ONS

- Debugging UCP

- Debugging ONS

- Request Dyeing

14 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 Connection Pool

- Resetting a Connection Pool

- Suspending a Connection Pool

- Resuming a Connection Pool

- Shutting Down a Data Source

- Starting a Data Source

- Managing DBMS Network Failures

A 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

B WebLogic Server 12.1.1 Support for Oracle 12c Database

- Using an Oracle 12c Database with WebLogic Server 12.1.1

- Using 11g Drivers with Oracle 12c Database

- Using 12c Drivers with Oracle 12c Database

- Required Oracle 12c Driver Files

- Download Oracle 12c Database Files

- Update the WebLogic Server CLASSPATH

- Other Considerations

- JDBC 4.1 Support for JDK 7

- Using Application Continuity

- How Application Continuity Works

- Requirements and Considerations

- Configuring Application Continuity

- Selecting the Driver for Application Continuity

- Using a Connection Callback

- Disabling Application Continuity for a Connection

- Configuring Logging for Application Continuity

- Global Database Services

- Requirements and Considerations

- Creating a GridLink DataSource for GDS Connectivity

- Container Database with Pluggable Databases

- Creating Service for PDB Access

- Setting the PDB using JDBC

- Automatic ONS Listener Support

- Database Resident Connection Pooling Support

C Using Multi Data Sources with Oracle RAC

- Overview of Oracle Real Application Clusters

- Oracle RAC Scalability with WebLogic Server Multi Data Sources

- Oracle RAC Availability with WebLogic Server Multi Data Sources

- Oracle RAC Load Balancing with WebLogic Server Multi Data Sources

- Software Requirements

- JDBC Driver Requirements

- Hardware Requirements

- WebLogic Server Cluster

- Oracle RAC Cluster

- Shared Storage

- Configuring Multi Data Sources with Oracle RAC

- Choosing a Multi Data Source Configuration for Use with Oracle RAC

- Configuring Multi Data Sources for use with Oracle RAC

- Attributes of a Multi Data Source

- Configuration Considerations for Failover

- Multi Data Source-Managed Failover and Load Balancing

- Delays During Failover

- Failure Handling Walkthrough for Global Transactions

- Configuring the Listener Process for Each Oracle RAC Instance

- Configuring Multi Data Sources When Remote Listeners are Enabled or Disabled

- Additional Configuration Considerations

- 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

- Configuring Connections to Services on Oracle RAC Nodes

- Configuring a Data Source to Connect to a Service

- Service Connection Configurations

- Workload Management

- Load Balancing

- Connection Pool Capacity Planning

- XA Considerations and Limitations when using multi Data Sources with Oracle RAC

- Oracle RAC XA Requirements when using multi Data Sources

- Use Multi Data Sources

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

- Transaction IDs Must Be Unique Within the Oracle RAC Cluster

- Known Limitations When Using Oracle RAC with multi Data Sources

- Potential for Data Deadlocks in Some Failure Scenarios

- Potential for Transactions Completed Out of Sequence

- Known Issue Occurring After Database Server Crash

- JDBC Store Recovery with Oracle RAC

- Configuring a JDBC Store for Use with Oracle RAC

- Automatic Retry

D Using Connect-Time Failover with Oracle RAC (Deprecated)

- Using Connect-Time Failover without Global Transactions

- Attributes of a Connect-Time Failover Configuration without Global Transactions

- Sample Configuration Code

E Using Fast Connection Failover with Oracle RAC

- JDBC Driver Configuration for use with Oracle Fast Connection Failover

Preface

This preface describes the document accessibility features and conventions used in this guide—Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

1 Introduction and Roadmap

This section describes the contents and organization of this guide—Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.

- Document Scope and Audience

- Guide to this Document

- Related Documentation

- JDBC Samples and Tutorials

- New and Changed JDBC Data Source Features in This Release

Document Scope and Audience

This document is a resource for software developers and system administrators who develop and support applications that use the Java Database Connectivity (JDBC) API. It also contains information that is useful for business analysts and system architects who are evaluating WebLogic Server. The topics in this document are relevant during the evaluation, design, development, pre-production, and production phases of a software project.

This document does not address specific JDBC programming topics. For links to WebLogic Server documentation and resources for this topic, see Related Documentation.

It is assumed that the reader is familiar with Java EE and JDBC concepts. This document emphasizes the value-added features provided by WebLogic Server.

Guide to this Document

- This chapter, Chapter 1, "Introduction and Roadmap," introduces the organization of this guide and lists new features in the current release.

- Chapter 2, "Configuring WebLogic JDBC Resources," provides an overview of WebLogic JDBC resources.

- Chapter 3, "Configuring JDBC Data Sources," describes WebLogic JDBC data source configuration.

- Chapter 4, "Using GridLink Data Sources," describes WebLogic JDBC GridLink data source configuration.

- Chapter 5, "Configuring JDBC Multi Data Sources," describes WebLogic JDBC multi data source configuration.

- Chapter 6, "Advanced Configurations for Oracle Drivers and Databases," provides advanced configuration options that can provide improved data source and driver performance when using Oracle drivers and databases.

- Chapter 7, "JDBC Data Source Transaction Options," provides information on XA, non-XA, and Global Transaction options for WebLogic data sources.

- Chapter 8, "Using Roles and Policies to Secure JDBC Data Sources," provides information on how WebLogic Server uses roles and policies to secure JDBC data sources.

- Chapter 9, "Deploying Data Sources on Servers and Clusters," provides information on how to deploy data sources on servers and clusters.

- Chapter 10, "Tuning Data Source Connection Pools," provides information on how to properly tune the connection pool attributes in JDBC data sources in your WebLogic Server domain to improve application and system performance.

- Chapter 11, "Using WebLogic Server with Oracle RAC," describes how to configure WebLogic Server for use with Oracle Real Application Clusters.

- Chapter 12, "Using JDBC Drivers with WebLogic Server," describes how to use JDBC drivers from other sources in your WebLogic JDBC data source configuration.

- Chapter 13, "Monitoring WebLogic JDBC Resources," describes how to monitor JDBC resources, gather profile information about database connection usage, and enable JDBC debugging.

- Chapter 14, "Managing WebLogic JDBC Resources," describes how to administer data sources.

- Appendix A, "Configuring JDBC Application Modules for Deployment," describes how to package a WebLogic JDBC module with your enterprise application.

- Appendix B, "WebLogic Server 12.1.1 Support for Oracle 12c Database," provides information on how to configure WebLogic Server Release 12.1.1 to run with an Oracle 12c database.

- Appendix C, "Using Multi Data Sources with Oracle RAC," describes how to configure generic data sources for use with Oracle Real Application Clusters.

Related Documentation

This document contains JDBC data source configuration and administration information.

For comprehensive guidelines for developing, deploying, and monitoring WebLogic Server applications, see the following documents:

- Programming JDBC for Oracle WebLogic Server is a guide to JDBC API programming with WebLogic Server.

- Developing Applications for Oracle WebLogic Server is a g