Configure Database Connectivity

3 Configure Database Connectivity

In WebLogic Server, you configure database connectivity through JDBC data sources, either in your WebLogic domain configuration or in your enterprise application.

Using JDBC Drivers with WebLogic Server
WebLogic Server uses JDBC drivers to provide access to various databases. WebLogic Server comes with a default set of JDBC drivers but third-party JDBC drivers can also be used.
Using Oracle JDBC Driver Extensions
In 23ai and following versions, the Oracle JDBC Driver can be extended through a Java Service Provider Interface (SPI). The SPI provides the URL, user, password, and JDBC parameters from an external source.
JDBC Diagnosability
In 23ai, diagnosability and logging are enhanced to improve the user experience.
Configuring JDBC Data Sources
In WebLogic Server, you configure database connectivity by adding JDBC data sources to your WebLogic domain. Configuring data sources requires several steps including choosing a type of data source, creating the data source, configuring connection pools and Oracle database parameters and so on.

Using JDBC Drivers with WebLogic Server

WebLogic Server uses JDBC drivers to provide access to various databases. WebLogic Server comes with a default set of JDBC drivers but third-party JDBC drivers can also be used.

Types of JDBC Drivers
JDBC drivers listed in the WebLogic Remote Console when creating a data source are not necessarily certified for use with WebLogic Server. JDBC drivers are listed as a convenience to help you create connections to many of the database management systems available.
JDBC Driver Support
WebLogic Server provides support for application data access to any database using a JDBC-compliant driver.
JDBC Drivers Installed with WebLogic Server
The Oracle JDBC Thin driver 23ai is installed with Oracle WebLogic Server 14.1.2.0.0. In addition to the Oracle Thin Driver, the mySQL Connector/J 8.0 (mysql-connector-j-8.2.0.jar) JDBC driver, WebLogic-branded DataDirect drivers are also installed with WebLogic Server.
Adding Third-Party JDBC Drivers Not Installed with WebLogic Server
To use third-party JDBC drivers that are not installed with WebLogic Server, you can add them to the DOMAIN_HOME/lib directory.
Globalization Support for the Oracle Thin Driver
For globalization support with the Oracle Thin driver, Oracle supplies the orai18n.jar file.

Parent topic: Configure Database Connectivity

Types of JDBC Drivers

JDBC drivers listed in the WebLogic Remote Console when creating a data source are not necessarily certified for use with WebLogic Server. JDBC drivers are listed as a convenience to help you create connections to many of the database management systems available.

You must install JDBC drivers in order to use them to create database connections in a data source on each server on which the data source is deployed. Drivers are listed in the WebLogic Remote Console with known required configuration options to help you configure a data source. The JDBC drivers in the list are not necessarily installed. Driver installation can include setting system Path, Classpath, and other environment variables. See Adding Third-Party JDBC Drivers Not Installed with WebLogic Server.

When a JDBC driver is updated, configuration requirements may change. The WebLogic Remote Console uses known configuration requirements at the time the WebLogic Server software was released. If configuration options for your JDBC driver have changed, you may need to manually override the configuration options when creating the data source or in the property pages for the data source after it is created.

WebLogic Server provides the following JDBC drivers:

Oracle Thin Drivers

Oracle Thin Driver XA
Oracle Thin Driver non-XA

The following table lists nine Oracle Thin Drivers as they appear in WebLogic Remote Console, a sample of the URL format that is generated from the input provided by the user, and the class name of the driver configured:

Oracle Drivers	URL Format	Description	Driver Class Name
Oracle’s Driver (Thin XA) for Application Continuity; Versions: Any	`jdbc:oracle:thin:@hostname:port/service`	Database is used as service. The service should be available on a single instance for Generic and Multi Data Source.	`oracle.jdbc.replay.OracleXADataSourceImpl`
Oracle’s Driver (Thin XA) for Instance connections; Versions: Any	`jdbc:oracle:thin:@hostname:port:SID`	Database is used as SID, the use of SID is deprecated. Use service name instead of SID in this format.	`oracle.jdbc.xa.client.OracleXADataSource`
Oracle’s Driver (Thin XA) for RAC Service-Instance connections; Versions: Any	`jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=hostname))) (CONNECT_DATA=(SERVICE_NAME=service)(INSTANCE_NAME=instance)))`	Use this format when the service is available on multiple instances and the URL should map to a single instance for Generic and Multi Data Source. A long format URL is generated so that you can specify instance name.	`oracle.jdbc.xa.client.OracleXADataSource`
Oracle’s Driver (Thin XA) for Service connections; Versions: Any	`jdbc:oracle:thin:@//hostname:port/service`	Database is used as service. The service should be available on a single instance for Generic and Multi Data Source.	`oracle.jdbc.xa.client.OracleXADataSource`
Oracle's Driver (Thin XA) for TNS Alias, Driver Extension Application Continuity Connections Versions:Any	`jdbc:oracle:thin:@tnsalias`	Database is used as the tns alias. When specified the driver property oracle.net.tns_admin is set to the location of tnsnames.ora, Oracle wallet or keystore.	`oracle.jdbc.replay.OracleXADataSourceImpl`
Oracle's Driver (Thin XA) for TNS Alias, Driver Extension Connections Versions:Any	`jdbc:oracle:thin:@tnsalias`	Database is used as the tns alias. When specified the driver property oracle.net.tns_admin is set to the location of tnsnames.ora, Oracle wallet or keystore.	`oracle.jdbc.xa.client.OracleXADataSource`
Oracle’s Driver (Thin) for Application Continuity; Versions: Any	`jdbc:oracle:thin:@//hostname:port/service`	Database is used as service. The service should be available on a single instance for Generic and Multi Data Source.	`oracle.jdbc.replay.OracleDataSourceImpl`
Oracle's Driver (Thin) for Consolidated AC/TAC; Versions:21+	`jdbc:oracle:thin:@//hostname:port/service`	Database is used as service. The driver class supports application continuity, is a pooled data source and is only available in Oracle JDBC 21 or later versions.	`oracle.jdbc.datasource.impl.OracleDataSource`
Oracle’s Driver (Thin) for Instance connections; Versions: Any	`jdbc:oracle:thin:@hostname:port:SID`	Database is used as SID, the use of SID is deprecated. Use the service name instead of SID in this format.	`oracle.jdbc.OracleDriver`
Oracle’s Driver (Thin) for pooled instance connections; Versions: Any	`jdbc:oracle:thin:@hostname:port:SID`	Database is used as SID. Use this format to get a pooled data source, this is not a very commonly used format.	`oracle.jdbc.pool.OracleDataSource`
Oracle’s Driver (Thin) for RAC Service-Instance connections; Versions: Any	`jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port))) (CONNECT_DATA=(SERVICE_NAME=service)(INSTANCE_NAME=instance))`	Use this format when the service is available on multiple instances and the URL should map to a single instance for Generic and Multi Data Source. A long format URL is generated so that you can specify instance name.	`oracle.jdbc.OracleDriver`
Oracle’s Driver (Thin) for Service connections; Versions: Any	`jdbc:oracle:thin:@//hostname:port/service`	Database is used as service. The service should be available on a single instance for Generic and Multi Data Source.	`oracle.jdbc.OracleDriver`
Oracle's Driver (Thin) for TNS Alias Connections Versions:Any	`jdbc:oracle:thin:@tnsalias`	Database is used as the tns alias. When specified the driver property oracle.net.tns_admin is set to the location of tnsnames.ora, Oracle wallet or keystore.	`oracle.jdbc.OracleDriver`
Oracle's Driver (Thin) for TNS Alias, Driver Extension Application Continuity Connections Versions:Any	`jdbc:oracle:thin:@tnsalias`	Database is used as the tns alias. When specified the driver property oracle.net.tns_admin is set to the location of tnsnames.ora, Oracle wallet or keystore.	`oracle.jdbc.replay.OracleDataSourceImpl`
Oracle's Driver (Thin) for TNS Alias, Driver Extension Consolidated AC/TAC Connections Versions:21+	`jdbc:oracle:thin:@tnsalias`	Database is used as the tns alias. When specified the driver property oracle.net.tns_admin is set to the location of tnsnames.ora, Oracle wallet or keystore. The driver class supports application continuity, is a pooled data source and is only available in Oracle JDBC 21 or later versions.	`oracle.jdbc.datasource.impl.OracleDataSource`

MySQL (non-XA)
Third-party JDBC drivers

For more information, see Using JDBC Drivers with WebLogic Server.
WebLogic-branded DataDirect drivers: These drivers are available for the following database management systems:
- DB2
- Microsoft SQL Server

All of these drivers are referenced by the weblogic.jar manifest file and do not need to be explicitly defined in a server's classpath.

When deciding which JDBC driver to use to connect to a database, you should try drivers from various vendors in your environment. In general, JDBC driver performance is dependent on many factors, especially the SQL code used in applications and the JDBC driver implementation.

For information about supported JDBC drivers, see Supported Configurations in What's New in Oracle WebLogic Server.

Parent topic: Using JDBC Drivers with WebLogic Server

JDBC Driver Support

WebLogic Server provides support for application data access to any database using a JDBC-compliant driver.

The JDBC-compliant driver needs to meet the following requirements:

The driver must be thread-safe.
The driver must implement standard JDBC transactional calls, such as setAutoCommit() and setTransactionIsolation(), when used in transactional aware environments.
If the driver that does not implement serializable or remote interfaces, it cannot pass objects to an RMI client application.

When WebLogic Server features use a database for internal data storage, database support is more restrictive than for application data access. The following WebLogic Server features require internal data storage:

Container Managed Persistence (CMP)
Rowsets
JMS/JDBC Persistence and use of a WebLogic JDBC Store
JDBC Session Persistence
RDBMS Security Providers
Database Leasing (for singleton services and server migration)
JTA Logging Last Resource (LLR) optimization.

Parent topic: Using JDBC Drivers with WebLogic Server

JDBC Drivers Installed with WebLogic Server

The Oracle JDBC Thin driver 23ai is installed with Oracle WebLogic Server 14.1.2.0.0. In addition to the Oracle Thin Driver, the mySQL Connector/J 8.0 (mysql-connector-j-8.2.0.jar) JDBC driver, WebLogic-branded DataDirect drivers are also installed with WebLogic Server.

The drivers files are named ojdbc11.jar for JDK17 and JDK21.

Note:

See Using WebLogic-branded DataDirect Drivers in Developing JDBC Applications for Oracle WebLogic Server.

These drivers are installed in subdirectories of $ORACLE_HOME/oracle_common/modules. The manifest in the weblogic.jar lists this file so that it is loaded when weblogic.jar is loaded (when the server starts). Therefore, you do not need to add this JDBC driver to your CLASSPATH. If you plan to use a third-party JDBC driver that is not installed with WebLogic Server, you must install the drivers, which includes updating your CLASSPATH with the path to the driver files, and may include updating your PATH with the path to database client files. See Supported Configurations in What's New in Oracle WebLogic Server .

Note:

WebLogic Server includes a version of the Derby DBMS installed with the WebLogic Server examples in the WL_HOME\common\derby directory. Derby is an all-Java DBMS product included in the WebLogic Server distribution solely in support of demonstrating the WebLogic Server examples. For more information about Derby, see http://db.apache.org/derby.

Parent topic: Using JDBC Drivers with WebLogic Server

Adding Third-Party JDBC Drivers Not Installed with WebLogic Server

To use third-party JDBC drivers that are not installed with WebLogic Server, you can add them to the DOMAIN_HOME/lib directory.Here, DOMAIN_HOME represents the directory in which the WebLogic Server domain is configured. The default path is ORACLE_HOME/user_projects/domains.

For more information, see Adding JARs to the Domain /lib Directory in Developing Applications for Oracle WebLogic Server.

Note:

In previous releases, adding a new JDBC driver or updating a JDBC driver where the replacement JAR has a different name than the original JAR required updating the WebLogic Server's classpath to include the location of the JDBC driver classes. This is no longer required.

Using a Third-Party JAR File in DOMAIN_HOME/lib

Using a third-party JAR file in DOMAIN_HOME/lib is only supported for third-party JDBC drivers that are not installed with WebLogic Server. The drivers installed with WebLogic Server are described in JDBC Drivers Installed with WebLogic Server.

When you use a third-party JAR file in the DOMAIN_HOME/lib directory, note the following:

The classloader that gets created is a child of the system classpath classloader in WebLogic Server.
Any classes that are in JARs in this directory are visible only to Jakarta EE applications in the server, such as EAR files.
You can use the WebLogic Remote Console and WLST online to configure and manage the JAR files. (You may also be able to use WLST offline because the data source is not deployed.)
These JAR files do not work when run from a standalone client (such as the t3 RMI client) or standalone applications (such as java utils.Schema).
If there are multiple domain directories involved (that is, multiple machines without a shared file system), the JAR file must be installed in /libin each domain directory.
WebLogic Server use of methods called on third-party drivers (such as TimesTen abort and DB2 setDB2ClientUser) is supported.

Note:

For details on WebLogic Server functionality supported with these JAR files, see Database Interoperability in What's New in Oracle WebLogic Server, and the appropriate version of the Oracle Fusion Middleware Supported System Configurations matrix documentation for specific database driver and DB version certification information.

Data Source Support

Third-party JAR files installed in /lib can be used with the following:

All data source types supported by WebLogic Server system resources including Generic, Multi Data Source, and Active GridLink. The Universal Connection Pool data source does not apply since the UCP JAR is not third-party.
Packaged data sources in an EAR or a WAR.
Jakarta EE 8 data source definition defined in an EAR or WAR.

Although not JDBC methods, using a third-party JAR file in /lib does apply to WebLogic Server data source callbacks like Multi Data Source failover, connection, replay, and harvesting.

Example 3-1 Example of Using a Third-Party JAR File in /lib

The following example shows the files contained in a standalone WAR file named getversion.war. The Derby JAR files are located in WEB-INF/lib or DOMAIN_HOME/lib (or both). The class file is compiled and installed at WEB-INF/classes/demo/GetVersion.class.

<web-app>
  <welcome-file-list>
    <welcome-file>welcome.jsp</welcome-file>
  </welcome-file-list>
  <display-name>GetVersion</display-name>
  <servlet>
    <description></description>
    <display-name>GetVersion</display-name>
    <servlet-name>GetVersion</servlet-name>
    <servlet-class>
       demo.GetVersion
    </servlet-class>
  </servlet>
<!-- Data source description can go in the web.xml descriptor or as an annotation in the java code - see below
  <data-source>
    <name>java:global/DSD</name>
    <class-name>org.apache.derby.jdbc.ClientDataSource</class-name>
    <port-number>1527</port-number>
    <server-name>localhost</server-name>
    <database-name>examples</database-name>
    <transactional>false</transactional>
  </data-source>
-->
</web-app>
 
WEB-INF/weblogic.xml
 
<weblogic-web-app>
  <container-descriptor>
    <prefer-web-inf-classes>true</prefer-web-inf-classes>
  </container-descriptor>
</weblogic-web-app>
 
Java file
 
package demo;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.annotation.sql.DataSourceDefinition;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
 
@DataSourceDefinition(name="java:global/DSD",
className="org.apache.derby.jdbc.ClientDataSource",
portNumber=1527,
serverName="localhost",
databaseName="examples",
transactional=false
)
@WebServlet(urlPatterns = "/GetVersion")
public class GetVersion extends javax.servlet.http.HttpServlet
  implements javax.servlet.Servlet {
  @Resource(lookup = "java:global/DSD")
  private DataSource ds;
 
  public GetVersion() {
    super();
  }
 
  protected void doGet(HttpServletRequest request,
    HttpServletResponse response) throws ServletException, IOException {
    doPost(request, response);
  }
 
  protected void doPost(HttpServletRequest request,
    HttpServletResponse response) throws ServletException, IOException {
    response.setContentType("text/html");
 
    PrintWriter writer = response.getWriter();
    writer.println("<html>");
    writer.println("<head><title>GetVersion</title></head>");
    writer.println("<body>" + doit() +"</body>");
    writer.println("</html>");
    writer.close();
  }
 
  private String doit() {
    String ret = "FAILED";
    Connection conn = null;
    try {
      conn = ds.getConnection();
      ret = "Connection obtained with version= " +
        conn.getMetaData().getDriverVersion();
    } catch(Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (conn != null)
          conn.close();
      } catch (Exception ignore) {}
    }
    return ret;
  }
}

Parent topic: Using JDBC Drivers with WebLogic Server

Globalization Support for the Oracle Thin Driver

For globalization support with the Oracle Thin driver, Oracle supplies the orai18n.jar file.This file replaces nls_charset.zip.

If you use character sets other than US7ASCII, WE8DEC, WE8ISO8859P1 and UTF8 with CHAR and NCHAR data in Oracle object types and collections, you must include orai18n.jar and orai18n-mapping.jar in your CLASSPATH.

The orai18n.jar and orai18n-mapping.jar are included with the WebLogic Server installation in the ORACLE_HOME\oracle_common\modules\oracle.nlsrtl folder. These files are not referenced by the weblogic.jar manifest file, so you must add them to your CLASSPATH before they can be used.

Parent topic: Using JDBC Drivers with WebLogic Server

Using Oracle JDBC Driver Extensions

In 23ai and following versions, the Oracle JDBC Driver can be extended through a Java Service Provider Interface (SPI). The SPI provides the URL, user, password, and JDBC parameters from an external source.

There is an open-source project at open-source project for “Oracle JDBC Driver Extensions” that provides some already-written “providers” that use the SPI to read the parameter from external vaults. There are providers for OCI vault, Azure vault, and Open Telemetry.

Using the vault enables you to remove the credential information from a local schema file for security reasons and store the information in a single location for all applications that use the database. The benefits of using JDBC Extensions is that it centralizes where the database information can be updated with no change to the application or to the Data Source configuration in WebLogic Server. This is beneficial when the application uses many data sources.

Parent topic: Configure Database Connectivity

Using OCI Vault

You need to setup an OCI account to use the OCI vault. Perform the following steps to use the OCI vault:

Setting Up an OCI Account

Open the following URL.
https://www.oracle.com/cloud/sign-in.html
Enter your details as listed below:
- Country/territory
- First name
- Last name
- Email
Verify your email address.
Select Corporate/Individual.

This option is for a work-related account.
Select a Home Region.

Ensure you pick the closest location to avoid any network delay.
Set up a password meeting the criteria.
Set up 2FA using the Oracle Authenticator.
After setting up your OCI account, you must setup configuration for access to your client machine by installing the OCI Command Line Interface (CLI). For more information, see Quickstart.

For example,

Run the following command on OL8:
```
sudo dnf -y install oraclelinux-developer-release-el8 
```
```
sudo dnf install python36-oci-cli
```

Creating Configuration File

Run the following command:
```
oci setup config
```
Use the following default value as the file name:
```
~/.oci/config
```
In the OCI dashboard, click Profile in the top left corner.
Copy the OCID.

This is the user OCID.
Click Profile, select Tenancy and copy the OCID.

This is the Tenancy OCID.
Select the region associated with the account.

A list of regions and associated numbers are available.
Create a new public/private key pair.
The configuration file is created.
Follow the steps in How to Upload the Public Key to upload the public key.

Parent topic: Using Oracle JDBC Driver Extensions

Storing Wallet and DB Connection String

Oracle JDBC Extension stores a URL string, username, encrypted password, and optional JDBC parameters in an OCI secret within an OCI vault. It works on any Oracle database and not only a cloud database. You can use an existing database or create a new OCI database.

Perform the following steps to create a wallet and store it:

Create the database.
Use SQL to create a user with a password and grant CREATE SESSION privilege.
Click on Database Connection, lookup for the <databasename>_tp TNS name, and click on copy to get the URL.

You will need the URL to create the secret.

Note:

There is no mechanized way to create the secret. You do not need to have the wallet and TNS_ADMIN set on the local client.

It is necessary to create a wallet for storing it in a secret.

Create a Vault

In the OCI dashboard, select the menu in the top left corner.
Select Identity & Security > Vault > Create Vault.
Enter a name and create vault.
Click on the vault name to enter the vault.
Create a Master Encryption Key by selecting Create Key.
Enter a name and click Create Key.

Create a Secret for DB Wallet

In the left margin, click Secrets.
Click Create Secret and enter a name.
Click Manual Secret Generation and paste the password enclosed in double quotes into the text box.
Click Secret.
Click the name of the secret.
Click Copy for the OCID.
Create and download database wallet.
After unzipping the wallet, run the following command to get the base-64 string for the wallet:
```
base64 cwallet.sso > output
```
Edit the output to ensure that no spaces are there in a single string.
To create a secret with this string, click Secrets > Create Secret.
Enter a name and click Manual Secret Generation.

Note:
It is critical to change the Secret Type Template to Base64
Copy and paste the base64 output from the cwallet.sso into the text box.

Create a Secret for DB Password

In the left pane, click Secrets.
Click Create Secret and enter a name.
Click Manual Secret Generation.
Select Secret Type Template (Select Plain-Text to enter plain text password).
Enter DB Password in Secret Contents.
Click Create Secret.
Click the name of the secret and save the OCID of DB Password.
Create a Secret for DB Connection Details.
Click Create Secret.
Click the name of the secret and save the OCID.

You can now create your secret that will be referenced by the driver.
In the left margin, click Secrets.
Click Create Secret and enter a name.

Click Manual Secret Generation.

You need to manually create the text and it should be done out of the OCI console. For information on the format, see https://docs.oracle.com/en/database/oracle/oracle-database/23/jajdb/oracle/jdbc/spi/OracleConfigurationProvider.html.

Given below is an example where the host and service name come from the TNS name that you saved from the datasource, the username is the user that you created in the database, the secrets for the password and wallet_location that you created in the vault.


{"connect_descriptor":
"(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=dbhost.oraclecloud.com))(connect_data=(service_name=dbservice.oraclecloud.com))(security=(ssl_server_dn_match=yes)))",
"user": "DEMO1",
"password": {
     "type": "ocivault",
	 "value": "ocid1.vaultsecret.oc1.ca-toronto-
1.amaaaaaadngsnoaajxsjica4vqggkcq2f4jxpillfc6nv3x55fs7btedglka"
},
"wallet_location": {
     "type": "ocivault",
	 "value": "ocid1.vaultsecret.oc1.ca-toronto-
1.amaaaaaadngsnoaaftfj2ll37ex67wru3wqhqsv2q6x2x67epegzl2gvkm6a"

},
"jdbc": { 
  "oracle.jdbc.ReadTimeout": 1006, 
  "defaultRowPrefetch": 20	
}
}

Copy and paste the Json string into the Secret Contents text box for the manual secret.
Click Create Secret.
Click the name of secret that you just created.
Click Copy next to the OCID.

You must use the OCID of the secret to generate the URL of the form jdbc:oracle:thin:@config-ocivault://OCID-FOR-SECRET.

For example,
jdbc:oracle:thin:@config-ocivault://ocid1.vaultsecret.oc1.ca-toronto- 1.amaaaaaadngsnoaabnyfwwxnmz67flenjpy26ikuylyui45n6p3xfx76h3dq
If you need to change the secret value, you must click the name of the secret. Click Create Secret Version to create a new version, enter the text, and click Create Secret Version.

Note:
When you create a new secret version, it does not change the OCID so you do not need to update the OCID wherever it is used.

Parent topic: Using Oracle JDBC Driver Extensions

JDBC Diagnosability

In 23ai, diagnosability and logging are enhanced to improve the user experience.

In previous releases, the debug JAR files are used for logging purposes. These files are indicated with a _g in the file name. For example, ojdbc8_g.jar or ojdbc11_g.jar and must be included in the CLASSPATH. The enhanced diagnosability feature eliminates the need to use the debug JAR files and also the need to switch between the regular JAR files and the debug JAR files.

Parent topic: Configure Database Connectivity

Enabling JDBC Driver Logging

Perform the following steps to enable logging and diagnose failures:

Set the value of the following system properties to true:

-Doracle.jdbc.diagnostic.enableLogging=true

-Doracle.jdbc.diagnostic.enableSensitiveDiagnostics=true

-Doracle.jdbc.diagnostic.permitSensitiveDiagnostics=true

Configure java util logging with -Djava.util.logging.config.file=./logging.config using the following logging file:


oracle.jdbc.handlers =
java.util.logging.FileHandleroracle.jdbc.level = FINESTjava.util.logging.FileHandler.level =
FINESTjava.util.logging.FileHandler.pattern =
%h/jdbc_incident_%u.logjava.util.logging.FileHandler.limit =
50000java.util.logging.FileHandler.count =
10java.util.logging.FileHandler.formatter =
oracle.jdbc.diagnostics.OracleSimpleFormatter


handlers = java.util.logging.ConsoleHandler
oracle.jdbc.level = FINEST
java.util.logging.ConsoleHandler.level = FINEST
java.util.logging.ConsoleHandler.formatter = oracle.jdbc.diagnostics.OracleSimpleFormatter

Set the value of the following system property to true:

-Doracle.ucp.diagnostic.enableLogging=true

Parent topic: JDBC Diagnosability

Diagnosing First Failure

The Diagnose First Failure feature is one of the major features of the enhanced diagnosability.

When this feature is enabled, it diagnosis the first occurrence of a failure and the most critical trace information is captured in the memory. After that memory fills, the oldest trace records are overwritten and are subsequently dumped into the java.util.logging when signaled. One of the following can signal the dumping of the trace records:

Occurrence of an error in the connection Client application code calling an API An MBean

This feature captures only the most critical information which includes the extent of information that provides a reasonable chance of diagnosing the most likely problems. In the public mode, this information is severely restricted so that any sensitive information is omitted. In the sensitive mode, this information includes the entire network conversation.

This feature is enabled by default. You can disable it either by setting the CONNECTION_PROPERTY_ENABLE_DIAGNOSE_FIRST_FAILURE property to FALSE or through the DiagnosticMBeans interface.

JDBC diagnosability feature is enhanced in this release to make it more user-friendly.

Parent topic: JDBC Diagnosability

Configuring JDBC Data Sources

In WebLogic Server, you configure database connectivity by adding JDBC data sources to your WebLogic domain. Configuring data sources requires several steps including choosing a type of data source, creating the data source, configuring connection pools and Oracle database parameters and so on.

Creating a JDBC Data Source
WebLogic JDBC data sources provide database access and database connection management.
Configuring Connection Pool Features
Each JDBC data source has a pool of JDBC connections that are created when the data source is deployed or at server startup. Applications use a connection from the pool then return it when finished using the connection. Connection pooling enhances performance by eliminating the costly task of creating database connections for the application.
Advanced Connection Properties
You can set up advanced connection properties like fatal error codes and use of Edition-Based Redefinition (EBR). You define fatal error codes which indicate the database server with which the data source communicates is no longer accessible on a connection. EBR provides the ability to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.
Configure Oracle Parameters
WebLogic Server provides several attributes that provide improved data source performance when using Oracle drivers.
Configure ONS Client Parameters
ONS client configuration allows the data source to subscribe to and process Oracle FAN events.
Tuning Generic Data Source Connection Pools
You can improve application and system performance by ensuring a proper configuration of the connection pool attributes in JDBC data sources in your WebLogic Server domain.
Generic Data Source Handling for Oracle RAC Outages
It is possible to use a Generic data source with Oracle RAC with some limitations. These limitations complicate transaction processing, monitoring, and graceful handling of RAC outages.
Generic Data Source Handling of Driver-Level Failover
Several database drivers support a feature to define multiple database instances in the URL and failover from one database to the next. It is possible to use a Generic data source with driver-level failover with some limitations. These limitations complicate transaction processing, monitoring, and graceful handling of database instance outages.

Parent topic: Configure Database Connectivity

Creating a JDBC Data Source

WebLogic JDBC data sources provide database access and database connection management.

You can create and manage JDBC data sources using the following management tools:

Oracle WebLogic Remote Console: See Data Sources in Oracle WebLogic Remote Console Online Help.
WebLogic Scripting Tool (WLST): See WLST Online Sample Scripts in Understanding the WebLogic Scripting Tool.
Example:
```
EXAMPLES_HOME\wl_server\examples\src\examples\wlst\online\jdbc_data_source_creation.py
```
where EXAMPLES_HOME represents the directory in which the WebLogic Server code examples are configured.

You can configure data sources in the WebLogic Remote Console.

Configure JDBC Data Source Properties
JDBC data source properties include options that determine the identity of the data source and the way database connection handles the data.
Configure Transaction Options
When you configure a JDBC data source using the WebLogic Remote Console, WebLogic Server automatically selects specific transaction options based on the type of JDBC driver. XA, non-XA, and Global transaction options are supported by WebLogic JDBC data sources.
Configure Connection Properties
Connection Properties allows you to configure the connection between the data source and the DBMS. Typical attributes are the database name, host name, port number, user name, and password.
Configure Testing Options
Test Database Connection allows you to test a database connection before the data source configuration is finalized using a table name or SQL statement.
Target JDBC Data Sources
You can select one or more targets to which to deploy your new JDBC data source. If you don't select a target, the data source will be created but not deployed. You will need to deploy the data source at a later time before getting connections.

Parent topic: Configuring JDBC Data Sources

Configure JDBC Data Source Properties

JDBC data source properties include options that determine the identity of the data source and the way database connection handles the data.

Data Source Names: You can use JDBCA data source name to identify the data source within the WebLogic domain. For system resource data sources, names must be unique among all other JDBC system resources. To avoid naming conflicts, data source names should also be unique among other configuration object names, such as servers, applications, clusters, and JMS queues, topics, and servers. For JDBC application modules packaged in an application, data source names must be unique among JDBC data sources with a similar scope.

The data source name cannot contain the following special characters: @ # $.

Data Source Scope: You can select the scope for the data source and set the scope to Global (at the domain level). See Target Data Sources.

JNDI Names: You can configure a data source so that it binds to the JNDI tree with a single or multiple names. See Developing JNDI Applications for Oracle WebLogic Server.

Database Type: You can select the Database Management System (DBMS) of the database you want to connect. For information about supported databases, see Supported Configurations in What's New in Oracle WebLogic Server.

JDBC Driver: You can select a JDBC database driver that is preferred to create a database connection. You should verify, however, that the URL is as you want it before asking the console to test it. The driver you select must be in the classpath on all servers on which you intend to deploy the data source.

Some but not all JDBC drivers listed in the WebLogic Remote Console are shipped (and/or are already in the classpath) with WebLogic Server. See Types of JDBC Drivers.

All of these drivers are referenced by the weblogic.jar manifest file and do not need to be explicitly defined in a server's classpath.

For information about supported JDBC drivers, see Supported Configurations in What's New in Oracle WebLogic Server.

Parent topic: Creating a JDBC Data Source

Configure Transaction Options

When you configure a JDBC data source using the WebLogic Remote Console, WebLogic Server automatically selects specific transaction options based on the type of JDBC driver. XA, non-XA, and Global transaction options are supported by WebLogic JDBC data sources.

For more information on configuring transaction support for a data source, see JDBC Data Source Transaction Options.

Parent topic: Creating a JDBC Data Source

Configure Connection Properties

Connection Properties allows you to configure the connection between the data source and the DBMS. Typical attributes are the database name, host name, port number, user name, and password.

Note:

You can use a Single Client Access Name (SCAN) address to represent the host name.

If you set the Oracle RAC REMOTE_LISTENER parameter for your data source to SCAN, then the data source connection URL can only use a SCAN address.
If you set the Oracle RAC REMOTE_LISTENER parameter for your data source to List of Node VIPs, then the data source connection URL can only use a list of VIP addresses.
If you set the Oracle RAC REMOTE_LISTENER parameter for your data source to Mix of SCAN and List of Node VIPs, then the data source connection URL can use both SCAN and VIP addresses.

See Introduction to Oracle RAC in Real Application Clusters Administration and Deployment Guide.

Configuring Connection Properties for Oracle BI Server: If you selected Oracle BI Server as your DBMS, configure the additional connection properties on the Connection Properties page as described in Connection String in Oracle Business Intelligence Publisher Administrator's and Developer's Guide.

Parent topic: Creating a JDBC Data Source

Configure Testing Options

Test Database Connection allows you to test a database connection before the data source configuration is finalized using a table name or SQL statement.

If necessary, you can test additional configuration information using the Properties and System Properties attributes.

Parent topic: Creating a JDBC Data Source

Target JDBC Data Sources

You can select one or more targets to which to deploy your new JDBC data source. If you don't select a target, the data source will be created but not deployed. You will need to deploy the data source at a later time before getting connections.

For more information, see Oracle WebLogic Remote Console Online Help and Using JDBC Drivers with WebLogic Server.

Parent topic: Creating a JDBC Data Source

Configuring Connection Pool Features

Each JDBC data source has a pool of JDBC connections that are created when the data source is deployed or at server startup. Applications use a connection from the pool then return it when finished using the connection. Connection pooling enhances performance by eliminating the costly task of creating database connections for the application.

Note:

If a non-dynamic data source attribute is updated, the data source needs to be undeployed or redeployed for the attribute to take effect. To determine whether an attribute is dynamic or non-dynamic, see the MBean reference MBean Reference for Oracle WebLogic Server for the attribute. If the attribute definition contains the Redeploy or Restart required text, then it is a non-dynamic attribute.

See Oracle WebLogic Remote Console Online Help and JDBCConnectionPoolParamsBean in MBean Reference for Oracle WebLogic Server.

Note:

Certain Oracle JDBC extensions, and possibly other non-standard methods available from other drivers may durably alter a connection's behavior in a way that future users of the pooled connection will inherit. WebLogic Server attempts to protect connections against some types of these calls when possible.

The following topics include information about connection pool options for a JDBC data source. Some of these options are dynamically changeable and others are non-dynamic.

Parent topic: Configuring JDBC Data Sources

Enabling JDBC Driver-Level Features

WebLogic JDBC data sources support the javax.sql.ConnectionPoolDataSource interface implemented by JDBC drivers. You can enable driver-level features by adding the property and its value to the Properties attribute in a JDBC data source. Driver-level properties in the Properties attribute are set on the driver's ConnectionPoolDataSource object.

Parent topic: Configuring Connection Pool Features

Enabling Connection-based System Properties

WebLogic JDBC data sources support setting driver properties using the value of system properties. The value of each property is derived at runtime from the named system property. You can configure connection-based system properties using the WebLogic Remote Console by editing the System Properties attribute of your data source configuration.

If a system property value is set, it overrides an encrypted property value, which overrides a normal property value (you can only have one property value for each property name).

A system property value can contain one of the variables listed in Table 3-1. If one or more of these variables is included in the system property, it is substituted with the corresponding value. If a value is not found, no substitution is performed. If none of these variables are found in the system property, then the value is taken as a system property name.

Table 3-1 Variables Supported in System Property Values for JDBC Data Source

Variable	Value Description
`${pid}`	First half (up to @) of `ManagementFactory.getRuntimeMXBean().getName()`
`${machine}`	Second half of `ManagementFactory.getRuntimeMXBean().getName()`
`${user.name}`	Java system property `user.name`
`${os.name}`	System property `os.name`
`${datasourcename}`	Data source name from the JDBC descriptor. It does not contain the partition name.
`${partition}`	Partition name or DOMAIN
`${serverport}`	WebLogic Server server listen port
`${serversslport}`	WebLogic Server server SSL listen port
`${servername}`	WebLogic Server server name
`${domainname}`	WebLogic Server domain name

A sample set of properties is shown in the following example:

<properties>
<property>
  <name>user</name>
  <sys-prop-value>user</sys-prop-value>
</property>
<property>
  <name>v$session.osuser</name>
  <sys-prop-value>${user.name}</sys-prop-value>
</property>
<property>
  <name>v$session.process</name>
  <sys-prop-value>${pid}</sys-prop-value>
</property>
<property>
  <name>v$session.machine</name>
  <sys-prop-value>${machine}</sys-prop-value>
</property>
<property>
  <name>v$session.terminal</name>
  <sys-prop-value>${datasourcename}</sys-prop-value>
</property>
<property>
  <name>v$session.program</name>
  <sys-prop-value>WebLogic ${servername} Partition ${partition}</sys-prop-value>
</property>
</properties>

In this example:

user is set to the value of -Duser=value
v$session values are set as described in Table 3-1

For example, v$session.program running on myserver is set to WebLogic myserver Partition DOMAIN

Note that the values have the following length limitations:

osuser—30
process—24
machine—64
terminal—30
program—48

Parent topic: Configuring Connection Pool Features

Enabling Connection-based Encrypted Properties

WebLogic JDBC data sources support setting driver properties using encrypted values. You can configure connection-based encrypted properties using the WebLogic Remote Console by editing the Encrypted Properties attribute of your data source configuration. See Using Encrypted Connection Properties.

Parent topic: Configuring Connection Pool Features

Initializing Database Connections with SQL Code

When WebLogic Server creates database connections in a data source, the server can automatically run SQL code to initialize the database connection. To enable this feature, enter SQL followed by a space and the SQL code you want to run in the Init SQL attribute in the WebLogic Remote Console. Alternatively, you can specify simply a table name without SQL and the statement SELECT COUNT(*) FROM tablename is used. If you leave this attribute blank (the default), WebLogic Server does not run any code to initialize database connections.

WebLogic Server runs this code whenever it creates a database connection for the data source, which includes at server startup, when expanding the connection pool, and when refreshing a connection.

You can use this feature to set DBMS-specific operational settings that are connection-specific or to ensure that a connection has memory or permissions to perform required actions.

Start the code with SQL followed by a space. An Oracle DBMS example:

SQL alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

The SQL statement is executed using JDBC Statement.execute(). Options that you can set using InitSQL vary by DBMS. See the documentation from your database vendor for supported statements. If you want to execute multiple statements, you may want to create a stored procedure and execute it. The syntax is vendor specific. For example, to execute an Oracle stored procedure:

SQL CALL MYPROCEDURE()

Parent topic: Configuring Connection Pool Features

Advanced Connection Properties

You can set up advanced connection properties like fatal error codes and use of Edition-Based Redefinition (EBR). You define fatal error codes which indicate the database server with which the data source communicates is no longer accessible on a connection. EBR provides the ability to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

Parent topic: Configuring JDBC Data Sources

Define Fatal Error Codes

You can define fatal error codes that indicate that the database server with which the data source communicates is no longer accessible on a connection. The connection is marked invalid and taken out of the pool but the data source is not suspended. These errors include deployment errors that cause a server to fail to boot and connection errors that prevent a connection from being put back in the connection pool.

When specified as the exception code within a SQLException (retrieved by sqlException.getErrorCode()), it indicates that a fatal error has occurred, the connection is no longer good, and it is removed from the connection pool. For Oracle databases the following fatal error codes are predefined within WLS and do not need to be placed in the configuration file:

Error Code	Description
3113	end-of-file on communication channel
3114	not connected to ORACLE
1033	ORACLE initialization or shutdown in progress
1034	ORACLE not available
1089	immediate shutdown in progress - no operations are permitted
1090	shutdown in progress - connection is not permitted
17002	I/O exception

For DB2, the following fatal error codes are predefined: -4498, -4499, -1776, -30108, -30081, -30080, -6036, -1229, -1224, -1035, -1034, -1015, -924, -923, -906, -518, -514, 58004.

The fatal error codes feature can be disabled by defining the data source driver connection property oracle.jdbc.OracleDriver value as false.

Parent topic: Advanced Connection Properties

Using Edition-Based Redefinition

Edition-based redefinition (EBR) provides the ability to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time. It allows a pre-upgrade and post-upgrade view of the data to exist at the same time, providing a hot upgrade capability. You can then specify which view you want for a particular session.

See:

Using Edition-Based Redefinition in Oracle Database Development Guide
Edition-Based Redefinition White Paper at Edition-Based Redefinition Technical Deep Dive

Using EBR with JDBC Connections

There are two approaches to using EBR with JDBC connections:

If you use a database service to connect to the database and an initial session edition was specified for that service, then the initial session edition for the service is your initial session edition on the connection. This approach is recommended for minimal overhead on the connection.

When you create or modify a database service, you can specify its initial session edition. To create or modify a database service, Oracle recommends using the srvctl add service or srvctl modify service command. To specify the default initial session edition of the service, use the -edition option.

Alternatively, you can create or modify a database service with the DBMS_SERVICE.CREATE_SERVICE or DBMS_SERVICE.MODIFY_SERVICE procedure, and specify the default initial session edition of the service with the EDITION attribute.
Changing your session edition after connecting to the database using the SQL statement ALTER SESSION SET EDITION. You can change your session edition to any edition on which you have the USE privilege. Note that changing the edition can require re-generating a significant amount of state on session and database server. Oracle recommends using DBMS_SESSION.RESET_PACKAGE to clean-up some of this state when changing the edition on a session.

Using Edition-based redefinition does not require any new WebLogic Server functionality.

To make use of EBR, your environment needs to consist of an earlier version of the application with a data source that references the earlier EDITION and a later version of the application with a data source that references the later EDITION. When referring to multiple versions of a WebLogic Server application, you should be using WebLogic Server versioned applications in the production redeployment feature. See Developing Applications for Production Redeployment in Developing Applications for Oracle WebLogic Server. By combining Oracle database EBR and WebLogic Server versioned applications, the application can be upgraded with no downtime, making the combination of features more powerful than either feature independently.

You need to run with a versioned database and a versioned application initially so that you can switch versions. To version a WebLogic Server application, simply add the Weblogic-Application-Version property in the MANIFEST.MF file (you can also specify it at deployment time).

Configuring WebLogic Data Sources to Use Editions

The following list describes the different ways you can configure WebLogic data sources to use Oracle database editions.

Packaged Data Source Using a Single Edition—The recommended way to configure the data source is to use a packaged data source descriptor that is stored in the application EAR or WAR file so that everything is self-contained. By doing so, you can use the same name for each data source and you do not need to change the application to use a variable name based on the edition. The data source URL in the descriptor should reference the database service associated with the correct edition. If for some reason you are using a SID instead of a database service (no longer recommended), the alternative is to specify SQL ALTER SESSION SET EDITION = name in the Init SQL parameter in the data source descriptor. This SQL statement is executed for each newly created physical database connection in the data source pool. This approach assumes that a data source references only a single edition of the database and all connections use that edition.

Note the following restrictions when using a packaged data source.
- You cannot use a packaged data source with Logging Last Resource (LLR). You must use a system resource.
- You cannot use an application-scoped packaged data source with EmulateTwoPhaseCommit for the global-transactions-protocol with a versioned application. You must use a global-scoped data source.
  
  Therefore, if you need to use LoggingLastResource or EmulateTwoPhaseCommit, you cannot use this approach. See JDBC Application Module Limitations.
System Resource Data Source Using a Single Edition—You can use a system resource as an alternative to a packaged data source. In this case, each data source must have a unique name and JNDI name. The application needs to be flexible enough to use that name at runtime. For example, you can pass in the data source JNDI name as a system property and the code that looks up the data source in JNDI will use that value.

The disadvantage of using a single edition per data source, whether packaged or as a system resource, is that it requires more database connections. A single edition approach can work when the period during which the old and new editions are running is relatively short. For applications that are using a lot of data sources and/or connections, this is not a viable approach.
System Resource Data Source Using Multiple Editions—An alternative is to have a data source that references multiple editions. The recommended configuration would still use a database service associated with a single edition. However, the connections will be re-associated with different editions during the lifetime of the connection.
Multiple Editions by Setting the Edition for Every Reservation—It is possible for the application to set the database edition every time it gets a connection. There is some overhead associated with making this call each time (round trip to the database server and setting the session) and the application code needs to be modified everywhere that a connection is reserved. If you are using the JDBC Replay Driver, this initialization should be done in the ConnectionInitializationCallback. See Using a Connection Callback.

It's important to optimize for the normal use case instead of optimizing for the (hopefully) short period during which the migration is done to a new edition. This approach doesn't optimize for the normal case where all connections are on the needed edition.
Multiple Editions using Connection Labeling—You can also associate an edition with the connection and try to reserve a connection with the correct edition. The recommended way to tag a connection with a property is to use connection labeling. The application then needs to implement the pieces associated with connection labeling.
- When a connection is reserved, it needs to determine the edition needed in the context.
- A matching method is needed to determine if the property, in this case just the edition, matches.
- A labeling initialization method is needed to make the connection match if it doesn't already match by using SQL ALTER SESSION SET EDITION = name.
There is overhead associated with connection labeling, particularly when exclusively scanning the list of existing connections to find a mach. On the other hand, the normal use case is that every connection matches the current edition so there is no need to look far to find a match. It is only during migration that there will be thrashing between editions and potentially longer searches to find a match (or to determine that there is no match).

Parent topic: Advanced Connection Properties

Configure Oracle Parameters

WebLogic Server provides several attributes that provide improved data source performance when using Oracle drivers.

For detailed information, see Advanced Configurations for Oracle Drivers and Databases.

Parent topic: Configuring JDBC Data Sources

Configure ONS Client Parameters

ONS client configuration allows the data source to subscribe to and process Oracle FAN events.

When configuring the ONS node list, Oracle recommends not specifying a value and allowing auto-ONS to perform the ONS configuration. In some cases, however, it is necessary to explicitly configure the ONS configuration, for example if you need to specify an Oracle Wallet and password, or if you want to explicitly specify the ONS topology.

You can configure an ONS client using the following option:

Configuring an ONS Client using WLST

Parent topic: Configuring JDBC Data Sources

Tuning Generic Data Source Connection Pools

You can improve application and system performance by ensuring a proper configuration of the connection pool attributes in JDBC data sources in your WebLogic Server domain.

For more information, see Tuning Data Source Connection Pools.

Parent topic: Configuring JDBC Data Sources

Generic Data Source Handling for Oracle RAC Outages

It is possible to use a Generic data source with Oracle RAC with some limitations. These limitations complicate transaction processing, monitoring, and graceful handling of RAC outages.

Note:

Oracle recommends using a Multi Data Source (MDS) or Active GridLink (AGL) data source instead of a Generic data source using driver-level failover. See Using Active GridLink Data Sources or Using Multi Data Sources with Oracle RAC.

The following limitations are due to Genetic data sources not being aware of the RAC instances associated with the connections in the pool:

A Generic data source does not have the ability to disable a single instance in the pool that a MDS or AGL data source provides. If one of the RAC instances goes down (planned or unplanned), the data source tests all connections in the pool for the down instance, disabling them individually. In addition to more overhead and application delays, the pool sees multiple failures which cause the entire pool to be disabled. To prevent the pool from being disabled, set the value of Count Of Test Failures Till Flush to 0. See JDBCConnectionPoolParamsBean in MBean Reference for Oracle WebLogic Server.
JTA or global transactions should not be used with this configuration. Because a Generic data source is not aware of the RAC instances, it cannot guarantee transaction affinity. This is a problem if the transaction spans multiple servers or if a failure occurs such that another connection is used to complete the transaction. Since the additional connections required to complete the transaction may not be within the same RAC instance, transaction processing may fail.
It is not possible to monitor the connections based on the RAC instances.

Parent topic: Configuring JDBC Data Sources

Generic Data Source Handling of Driver-Level Failover

Several database drivers support a feature to define multiple database instances in the URL and failover from one database to the next. It is possible to use a Generic data source with driver-level failover with some limitations. These limitations complicate transaction processing, monitoring, and graceful handling of database instance outages.

The following limitations are due to WebLogic Server instances not being aware of the database instances associated with the connections in the pool:

A Generic data source does not have the ability to disable a single instance in the pool that a Multi Data Source provides. If one of the database instances goes down (planned or unplanned), the data source tests all connections in the pool for the down instance, disabling them individually. In addition to more overhead and application delays, the pool sees multiple failures which cause the entire pool to be disabled. To prevent the pool from being disabled, set the value of Count Of Test Failures Till Flush to 0.

For more information, see JDBCConnectionPoolParamsBean in MBean Reference for Oracle WebLogic Server.
JTA or global transactions should not be used with this configuration. Because WebLogic Server is not aware of the database instances, it cannot guarantee transaction affinity. This is a problem if the transaction spans multiple servers or if a failure occurs such that another connection is used to complete the transaction. Since the additional connections required to complete the transaction may not be within the same database instance, transaction processing may fail.
It is not possible to monitor the connections based on the database instances.

Parent topic: Configuring JDBC Data Sources