Sunday May 01, 2016

Using SQLXML Data Type with Application Continuity

When I first wrote an article about changing Oracle concrete classes to interfaces to work with Application Continuity (AC) (https://blogs.oracle.com/WebLogicServer/entry/using_oracle_jdbc_type_interfaces), I left out one type. oracle.sql.OPAQUE is replaced with oracle.jdbc.OracleOpaque. There isn’t a lot that you can do with this opaque type. While the original class had a lot of conversion methods, the new Oracle type interfaces have only methods that are considered significant or not available with standard JDBC API’s. The new interface only has a method to get the value as an Object and two meta information methods to get meta data and type name. Unlike the other Oracle type interfaces (oracle.jdbc.OracleStruct extends java.sql.Struct and oracle.jdbc.OracleArray extends java.sql.Array), oracle.jdbc.OracleOpaque does not extend a JDBC interface

There is one related very common use case that needs to be changed to work with AC. Early uses of SQLXML made use of the following XDB API.

SQLXML sqlXml = oracle.xdb.XMLType.createXML(
((oracle.jdbc.OracleResultSet)resultSet).getOPAQUE("issue"));

oracle.xdb.XMLType extends oracle.sql.OPAQUE and its use will disable AC replay. This must be replaced with the standard JDBC API

SQLXML sqlXml = resultSet.getSQLXML("issue");

If you try to do a “new oracle.xdb.XMLType(connection, string)” when running with the replay datasource, you will get a ClassCastException. Since XMLType doesn’t work with the replay datasource and the oracle.xdb package uses XMLType extensively, this package is no longer usable for AC replay.

The API’s for SQLXML are documented at https://docs.oracle.com/javase/7/docs/api/java/sql/SQLXML.html. The javadoc shows API’s to work with DOM, SAX, StAX, XLST, and XPath.

Take a look at the sample program at https://blogs.oracle.com/WebLogicServer/resource/StephenFeltsFiles/XmlSample.txt

The sample uses StAX to store the information and DOM to get it. By default, it uses the replay datasource and it does not use XDB.

You can run with replay debugging by doing something like the following. Create a file named /tmp/config.txt that has the following text.

java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter
handlers = java.util.logging.FileHandler
java.util.logging.FileHandler.pattern = /tmp/replay.log
oracle.jdbc.internal.replay.level = FINEST

Change your WLS CLASSPATH (or one with the Oracle client jar files) to put ojdbc7_g.jar at the front (to replace ojdbc7.jar) and add the current directory.

Compile the program (after renaming .txt to .java) and run it using

java -Djava.util.logging.config.file=/tmp/config.txt XmlSample

The output replay log is in /tmp/replay.log. With the defaults in the sample program, you won’t see replay disabled in the log. If you change the program to set useXdb to true, you will see that replay is disabled. The log will have “DISABLE REPLAY in preForMethodWithConcreteClass(getOPAQUE)” and “Entering disableReplayInternal”.

This sample can be used to test other sequences of operations to see if they are safe for replay.

Alternatively, you can use orachk to do a static analysis of the class. See https://blogs.oracle.com/WebLogicServer/entry/using_orachk_to_clean_up for more information. If you run orachk on this program, you will get this failure.

FAILED - [XmlSample][[MethodCall] desc= (Ljava/lang/String;)Loracle/sql/OPAQUE; method name=getOPAQUE, lineno=105]

Thursday Apr 28, 2016

Testing WLS and ONS Configuration

Introduction

Oracle Notification Service (ONS) is installed and configured as part of the Oracle Clusterware installation. All nodes participating in the cluster are automatically registered with the ONS during Oracle Clusterware installation. The configuration file is located on each node in $ORACLE_HOME/opmn/conf/ons.config. See the Oracle documentation for further information. This article focuses on the client side.

Oracle RAC Fast Application Notification (FAN) events are available starting in database 11.2. This is the minimum database release required for WLS Active GridLink. FAN events are notifications sent by a cluster running Oracle RAC to inform the subscribers about the configuration changes within the cluster. The supported FAN events are service up, service down, node down, and load balancing advisories (LBA).

fanWatcher Program

You can optionally test your ONS configuration independent of running WLS. This tests the connection from the ONS client to the ONS server but not configuration of your RAC services. See https://blogs.oracle.com/WebLogicServer/entry/fanwatcher_sample_program for details to get, compile, and run the fanWatcher program. I’m assuming that you have WLS 10.3.6 or later installed and you have your CLASSPATH set appropriately. You would run the test program using something like

java fanWatcher "nodes=rac1:6200,rac2:6200" database/event/service

If you are using the database 12.1.0.2 client jar files, you can handle more complex configurations with multiple clusters, for example DataGuard, with something like

java fanWatcher "nodes.1=site1.rac1:6200,site1.rac2:6200
nodes.2=site2.rac1:6200,site2.rac2:6200" database/event/service

Note that a newline is used to separate multiple node lists. You can also test with a wallet file and password, if the ONS server is configured to use SSL communications.

Once this program is running, you should minimally see occasional LBA notifications. If you start or stop a service, you should see an associated event.

Auto ONS

It’s possible to run without specifying the ONS information using a feature call auto-ONS. The auto-ONS feature cannot be used if you are running with

- an 11g driver or 11g database. Auto-ONS depends on protocol flowing between the driver and the database server and this feature was added in 12c.

- pre-WLS 12.1.3. Auto-ONS is supported starting in WLS 12.1.3.

- an Oracle wallet with SSL communications. Configuration of the wallet requires also configuring the ONS information.

- complicated ONS topology. In general, auto-ONS can figure out what you need but there are cases where you need to specify it precisely. In WLS 12.2.1, an extension of the ONS configuration allows for specifying the exact topology using a property node list. See http://docs.oracle.com/middleware/1221/wls/JDBCA/gridlink_datasources.htm#BABGJBIC for more information.

If you have some configurations that use an 11g driver or database and some that run with 12c driver/database, you may want to just specify the ONS information all of the time instead of using the auto-ONS simplification. The fanWatcher link above indicates how to test fanWatcher using auto-ONS.

WLS ONS Configuration and Testing

The next step is to ensure that you have end-to-end configuration running. That includes the database service for which events will be generated to the AGL datasource that processes the events for the corresponding service.

On the server side, the database service must be configured RCLB enabled. RCLB is enabled for a service if the service GOAL (NOT CLB_GOAL) is set to either SERVICE_TIME or THROUGHPUT. See the Oracle documentation for further information on using srvctl to set this when creating the service.

On the WLS side, the key pieces are the URL and the ONS configuration.

The URL is configured using a long format with this service name specified. The URL can use an Oracle Single Client Access Name (SCAN) address, for example,

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=scanname)(PORT=scanport))(CONNECT_DATA=(SERVICE_NAME=myservice)))

or multiple non-SCAN addresses with LOAD_BALANCE=on, for example,

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myservice)))

Defining the URL is a complex topic - see the Oracle documentation for more information.

As described above, the ONS configuration can be implicit using auto-ONS or explicit. The trade-offs and restrictions are also described above. The format of the explicit ONS information is described at http://docs.oracle.com/middleware/1221/wls/JDBCA/gridlink_datasources.htm#BABGJBIC.

If you create the datasource using the administration console with explicit ONS configuration, there is a button to click on to test the ONS configuration. This tests doing a simple handshake with the ONS server.

Of course, the first real test of your ONS configuration with WLS is deploying the datasource, either when starting the server or when targeting the datasource on a running server.

In the administration console, you can look at the AGL runtime monitoring page for ONS, especially if using auto-ONS, to see the ONS configuration.

You can look at the page for instances and check the affinity flag and instance weight attributes that are updated on LBA events. If you stop a service using something like

srvctl stop service -db beadev -i beadev2 -s otrade

that should also show up on this page with the weight and capacity going to 0.


If you look at the server log (for example servers/myserver/logs/myserver.log) you should see a message tracking the outage like the following.

…. <Info> <JDBC> … <Data source JDBC Data Source-0 for service otrade received a service down event for instance [beadev2].>

If you want to see more information like the LBA events, you can enable the JDBCRAC debugging using –Dweblogic.debug.DebugJDBCRAC=true. For example,

... <JDBCRAC> ... lbaEventOccurred() event=service=otrade, database=beadev, event=VERSION=1.0 database=beadev service=otrade { {instance=beadev1 percent=50 flag=GOOD aff=FALSE}{instance=beadev2 percent=50 flag=UNKNOWN aff=FALSE} }

There will be a lot of debug output with this setting so it is not recommended for production.

Wednesday Apr 27, 2016

Migrating from Generic Data Source to Active GridLink

Earlier, I wrote an article about how to migrate from a Multi Data source (MDS) for RAC connectivity to Active GridLink (AGL). This is needed to move from the older datasource technology to the newer technology, both supporting Oracle RAC. The information is now in the public documentation set at http://docs.oracle.com/middleware/1221/wls/JDBCA/gridlink_datasources.htm#JDBCA690.

There are also many customers that are growing up from a standalone database to an Oracle RAC cluster. In this case, it’s a migration from a GENERIC datasource to an AGL datasource. This migration is pretty simple.

No changes should be required to your applications.  A standard application looks up the datasource in JNDI and uses it to get connections.  The JNDI name won’t change.

The only changes necessary should be to your configuration and the necessary information is generally provided by your database administrator.   The information needed is the new URL and optionally the configuration of Oracle Notification Service (ONS) on the RAC cluster. The latter is only needed if you are running with

- an 11g driver or 11g database. Auto-ONS depends on protocol flowing between the driver and the database server and this feature was added in 12c.

- pre-WLS 12.1.3. Auto-ONS is supported starting in WLS 12.1.3.

- an Oracle wallet with SSL communications. Configuration of the wallet requires also configuring the ONS information.

- complicated ONS topology. In general, auto-ONS can figure out what you need but there are cases where you need to specify it precisely. In WLS 12.2.1, an extension of the ONS configuration allows for specifying the exact topology using a property node list. See http://docs.oracle.com/middleware/1221/wls/JDBCA/gridlink_datasources.htm#BABGJBIC for more information.

The URL and ONS attributes are configurable but not dynamic. That means that the datasource will need to be shutdown and restarted after the change. The simplest way to do this is to untarget the datasource, make the changes, and then re-target the datasource.

The recommended approach to migrate from a GENERIC to AGL datasource is to use WLST. The URL must be changed in the JDBCDriverParams object. The new JDBCOracleParams object (it generally doesn’t exist for a GENERIC datasource) needs to have FAN enabled set to true and optionally set the ONS information.

The following is a sample WLST script with the new values hard-coded. You could parameterize it and make it more flexible in handling multiple datasources. If you are using an Oracle wallet for ONS, that needs to be added to the JDBCOracleParams object as well.

# java weblogic.WLST file.py
import sys, socket, os
hostname = socket.gethostname()
datasource="JDBC Data Source-0"
connect("weblogic","welcome1","t3://"+hostname+":7001")
edit()
startEdit()
cd("/JDBCSystemResources/" + datasource )
targets=get("Targets")
set("Targets",jarray.array([], ObjectName))
save()
activate()
startEdit()
cd("/JDBCSystemResources/" + datasource + "/JDBCResource/" +
datasource + "/JDBCDriverParams/" + datasource )
set("Url","jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(" +
"ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521)))" +
"(CONNECT_DATA=(SERVICE_NAME=otrade)))")
cd("/JDBCSystemResources/" + datasource + "/JDBCResource/" +
datasource + "/JDBCOracleParams/" + datasource )
set("FanEnabled","true")
set("OnsNodeList","dbhost:6200")
#cd("/JDBCSystemResources/" + datasource + "/JDBCResource/" +
# datasource )
#set("DatasourceType", "AGL")
save()
activate()
startEdit()
cd("/JDBCSystemResources/" + datasource )
set("Targets", targets)
save()
activate()

Starting in WLS 12.2.1, there is an explicit datasource type at the JDBCResource level. If that is set to GENERIC, you must re-set it using set("DatasourceType", "AGL"). In the script above, uncomment the lines to set it.

In the administrative console, the database type is read-only and there is no mechanism to change the database type. You can try to get around this by setting the URL, FAN Enabled box, and ONS information. However, in 12.2.1 there is no way to re-set the Datasource Type in the console and that value overrides all others.

Tuesday Dec 08, 2015

Monitoring FAN Events

fanWatcher is a sample program to print the Oracle Notification Service (ONS) Fast Application Notification (FAN) event information. These events provide information regarding load balancing, and service and instance up and down events. This information is automatically processed by WebLogic Server Active GridLink and UCP on the mid-tier. For more information about FAN events, see this link.  The program described here is an enhancement of the earlier program described in that white paper  This program can be modified to work as desired to monitor events and help diagnose problems with configuration. The code is available this link (rename it from .txt to .java).

To run this Java application, you need to be set up to run a JDK and you need ons.jar and ojdbcN.jar in the CLASSPATH. The CLASSPATH is set differently depending on whether you are running on the database server or on the mid-tier with WebLogic Server or UCP. Make sure to use the correct path separator for CLASSPATH on your platform (';' for Windows, ':' otherwise).

The general format for the command line is

java fanWatcher config_type [eventtype … ]

Event Type Subscription

The event type sets up the subscriber to only return limited events. You can run without specifying the event type to see what types of events are returned. When you specify an event name on the command line, the program sets up the subscriber to have a simple match on the event. If the specified pattern occurs anywhere in a notification's header, then the comparison statement evaluates true. The most basic pattern match is an empty string (not null), which matches all notifications. The pattern is enclosed in double quotes (required) and prefixed with “%” to be case insensitive.

Event processing is more complete than shown in this sample. The subscription string is generally composed of one or more comparison statements, each logically related to another with the boolean operators '|' for an OR relationship or '&' for an AND relationship. Parentheses are used to group these comparison statements, and the '!' operator placed before an opening parenthesis negates the evaluated value within.

Each individual comparison statement must be enclosed within double quotes ('"'), and can take one of two basic forms: "pattern" or "name=value". A "pattern" is a simple string match of the notification header: if the specified "pattern" occurs anywhere in a notification's header, then the comparison statement evaluates true. The most basic pattern match is an empty string (not NULL) which matches all notifications.

The "name=value" format compares the ONS notification header or property name with the name against the specified value, and if the values match, then the comparison statement evaluates true. If the specified header or property name does not exist in the notification the comparison statement evaluates false. A comparison statement will be interpreted as a case insensitive when a percent character ('%') is placed before the opening quote. Note that for "name=value" comparison statements, only the value is treated as case insensitive with this option: the name lookup will always be case sensitive. A comparison statement will be interpreted as a regular expression when a dollar sign character ('$') is placed before the opening quote. Standard POSIX regular expressions are supported. To specify a regular expression that is also case insensitive, place the dollar sign and percent sign together and in that order ("$%") before the opening quote.

A special case subscription string composed of only the exclamation point character ('!') signifies that the subscription will not match any notifications.

You might want to modify the event to select on a specific service by using something like

%"eventType=database/event/servicemetrics/<serviceName> "

Running with Database Server 10.2 or later

This approach runs on the database server and connects directly to the local ONS daemon available in the Grid Infrastructure cluster. The FANwatcher utility must be run as a user that has privilege to access the $CRS_HOME/opmn/conf/ons.config, which is used by the ons daemon to start and accessed by this program. The configuration type on the command line is set to “crs”.

# CRS_HOME should be set for your Grid infrastructure
echo $CRS_HOME
CRS_HOME=/mypath/scratch/12.1.0/grid/
CLASSPATH="$CRS_HOME/jdbc/lib/ojdbc6.jar:$CRS_HOME/opmn/lib/ons.jar:."
export CLASSPATH
javac fanWatcher.java
java -Doracle.ons.oraclehome=$CRS_HOME fanWatcher crs

Running with WLS 10.3.6 or later using an explicit node list

There are two ways to run in a client environment – with an explicit node list and using auto-ONS. It’s necessary to have ojdbcN.jar and ons.jar that are available when configured for WLS. If you are set up to run with UCP directly, these should also be in your CLASSPATH.

In the first approach, it will work with Oracle driver and database 11 and later (SCAN support came in later versions of Oracle including the 11.2.0.3 jar files that shipped with WLS 10.3.6).

# Set the WLS environment using wlserver*/server/bin/setWLSEnv
CLASSPATH="$CLASSPATH:." # add local directory for sample program
export CLASSPATH
javac fanWatcher.java
java fanWatcher "nodes=rac1:6200,rac2:6200" database/event/service

The node list is a string of one or more values of the form name=value separated by a newline character (\n).

There are two supported formats for the node list.

The first format is available for all versions of ONS. The following names may be specified.

nodes – This is required. The format is one or more host:port pairs separated by a comma.

walletfile – Oracle wallet file used for SSL communication with the ONS server.

walletpassword – Password to open the Oracle wallet file.

The second format is available starting in database 12.2.0.2. It supports more complicated topologies with multiple clusters and node lists. It has the following names.

nodes.id—this value is a list of nodes representing a unique topology of remote ONS servers. id specifies a unique identifier for the node list. Duplicate entries are ignored. The list of nodes configured in any list must not include any nodes configured in any other list for the same client or duplicate notifications will be sent and delivered. The list format is a comma separated list of ONS daemon listen addresses and ports pairs separated by colon.

maxconnections.id— this value specifies the maximum number of concurrent connections maintained with the ONS servers. id specifies the node list to which this parameter applies. The default is 3.

active.id If true, the list is active and connections are automatically established to the configured number of ONS servers. If false, the list is inactive and is only be used as a fail over list in the event that no connections for an active list can be established. An inactive list can only serve as a fail over for one active list at a time, and once a single connection is re-established on the active list, the fail-over list reverts to being inactive. Note that only notifications published by the client after a list has failed over are sent to the fail over list. id specifies the node list to which this parameter applies. The default is true.

remotetimeout —The timeout period, in milliseconds, for a connection to each remote server. If the remote server has not responded within this timeout period, the connection is closed. The default is 30 seconds.

The walletfile and walletpassword may also be specified (note that there is one walletfile for all ONS servers). The nodes attribute cannot be combined with name.id attributes.

Running with WLS using auto-ONS

Auto-ONS is available starting in Database 12.1.0.1. Before that, no information is available. Auto-ONS only works with RAC configurations; it does not work with an Oracle Restart environment.  Since the first version of WLS that ships with Database 12.1 is WLS 12.1.3, this approach will only work with upgraded database jar files on versions of WLS earlier than 12.1.3. Auto-ONS works by getting a connection to the database to query the ONS information from the server. For this program to work, a user, password, and URL are required. For the sample program, the values are assumed to be in the environment (to avoid putting them on the command line). If you want, you can change the program to prompt for them or hard-code the values into the java code.

# Set the WLS environment using wlserver*/server/bin/setWLSEnv
# Set the credentials in the environment. If you don't like doing this,
# hard-code them into the java program
password=mypassword
url='jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=\
(ADDRESS=(PROTOCOL=TCP)(HOST=rac1)(PORT=1521))\
(ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=1521)))\
(CONNECT_DATA=(SERVICE_NAME=otrade)))'
user=myuser
export password url user
CLASSPATH="$CLASSPATH:."
export CLASSPATH
javac fanWatcher.java
java fanWatcher autoons

fanWatcher Output

The output looks like the following. You can modify the program to change the output as desired. In this short output capture, there is a metric event and an event caused by stopping the service on one of the instances.

** Event Header **
Notification Type: database/event/servicemetrics/otrade
Delivery Time: Fri Dec 04 20:08:10 EST 2015
Creation Time: Fri Dec 04 20:08:10 EST 2015
Generating Node: rac1
Event payload:
VERSION=1.0 database=dev service=otrade { {instance=inst2 percent=50 flag=U
NKNOWN aff=FALSE}{instance=inst1 percent=50 flag=UNKNOWN aff=FALSE} } timestam
p=2015-12-04 17:08:03

** Event Header **
Notification Type: database/event/service
Delivery Time: Fri Dec 04 20:08:20 EST 2015
Creation Time: Fri Dec 04 20:08:20 EST 2015
Generating Node: rac1
Event payload:
VERSION=1.0 event_type=SERVICEMEMBER service=otrade instance=inst2 database=dev db_domain= host=rac2 status=down reason=USER timestamp=2015-12-04 17:

Thursday Nov 19, 2015

WLS Replay Statistics

Starting in the 12.1.0.2 Oracle thin driver, the replay driver has statistics related to replay. This is useful to understand how many connections are being replayed. It should be completely transparent to the application so you won’t know if connection replays are occurring unless you check.

The statistics are available on a per connection basis or on a datasource basis. However, connections on a WLS datasource don’t share a driver-level datasource object so the latter isn’t useful in WLS. WLS 12.2.1 provides another mechanism to get the statistics at the datasource level.

The following code sample shows how to print out the available statistics for an individual connection using the oracle.jdbc.replay.ReplayableConnection interface, which exposes the method to get a oracle.jdbc.replay.ReplayStatistics object. See https://docs.oracle.com/database/121/JAJDB/oracle/jdbc/replay/ReplayStatistics.html for a description of the statistics values.

if (conn instanceof ReplayableConnection) {
  ReplayableConnection rc = ((ReplayableConnection)conn);
  ReplayStatistics rs = rc.getReplayStatistics(
    ReplayableConnection.StatisticsReportType.FOR_CURRENT_CONNECTION);
  System.out.println("Individual Statistics");
  System.out.println("TotalCalls="+rs.getTotalCalls());
  System.out.println("TotalCompletedRequests="+rs.getTotalCompletedRequests());
  System.out.println("FailedReplayCount="+rs.getFailedReplayCount());
  System.out.println("TotalRequests="+rs.getTotalRequests());
  System.out.println("TotalCallsTriggeringReplay="+rs.getTotalCallsTriggeringReplay());
  System.out.println("TotalReplayAttempts="+rs.getTotalReplayAttempts());
  System.out.println("TotalProtectedCalls="+rs.getTotalProtectedCalls());
  System.out.println("SuccessfulReplayCount="+rs.getSuccessfulReplayCount());
  System.out.println("TotalCallsAffectedByOutages="+rs.getTotalCallsAffectedByOutages()); 
  System.out.println("TotalCallsAffectedByOutagesDuringReplay="+  
      rs.getTotalCallsAffectedByOutagesDuringReplay());  
  System.out.println("ReplayDisablingCount="+rs.getReplayDisablingCount());
}

Besides a getReplayStatistics() method, there is also a clearReplayStatistics() method.

To provide for a consolidated view of all of the connections associated with a WLS datasource, the information is available via a new operation on the associated runtime MBean. You need to look-up the WLS MBean server, get the JDBC service, then search for the datasource name in the list of JDBC datasource runtime MBeans, and get the JDBCReplayStatisticsRuntimeMBean. This value will be null if the datasource is not using a replay driver, if the driver is earlier than 12.1.0.2, or if it’s not a Generic or AGL datasource. To use the replay information, you need to first call the refreshStatistics() operation that sets the MBean values, aggregating the values for all connections on the datasource. Then you can call the operations on the MBean to get the statistics values, as in the following sample code. Note that there is also a clearStatistics() operation to clear the statistics on all connections on the datasource. The following code shows an example of how to print the aggregated statistics from the data source.

public void printReplayStats(String dsName) throws Exception {
  MBeanServer server = getMBeanServer();
  ObjectName[] dsRTs = getJdbcDataSourceRuntimeMBeans(server);
  for (ObjectName dsRT : dsRTs) {
    String name = (String)server.getAttribute(dsRT, "Name");
    if (name.equals(dsName)) {
      ObjectName mb =(ObjectName)server.getAttribute(dsRT,  
        "JDBCReplayStatisticsRuntimeMBean");
      server.invoke(mb,"refreshStatistics", null, null);
      MBeanAttributeInfo[] attributes = server.getMBeanInfo(mb).getAttributes();
      System.out.println("Roll-up");
      for (int i = 0; i <attributes.length; i++) {
        if(attributes[i].getType().equals("java.lang.Long")) {
          System.out.println(attributes[i].getName()+"="+
            (Long)server.getAttribute(mb, attributes[i].getName()));
        }
      }
    }
  }
}

MBeanServer getMBeanServer() throws Exception {
  InitialContext ctx = new InitialContext();
  MBeanServer server = (MBeanServer)ctx.lookup("java:comp/env/jmx/runtime");
  return server;
}
ObjectName[] getJdbcDataSourceRuntimeMBeans(MBeanServer server) 
  throws Exception {
  ObjectName service = new ObjectName( "com.bea:Name=RuntimeService,Type=\
weblogic.management.mbeanservers.runtime.RuntimeServiceMBean");
  ObjectName serverRT = (ObjectName)server.getAttribute(service,  "ServerRuntime");
  ObjectName jdbcRT = (ObjectName)server.getAttribute(serverRT,  "JDBCServiceRuntime");
  ObjectName[] dsRTs = (ObjectName[])server.getAttribute(jdbcRT,
    "JDBCDataSourceRuntimeMBeans");
  return dsRTs;
}

Now run an application that gets a connection, does some work, kills the session, replays, then gets a second connection and does the same thing. Each connection successfully replays once. That means that the individual statistics show a single replay and the aggregated statistics will show two replays. Here is what the output might look like.

Individual Statistics
TotalCalls=35
TotalCompletedRequests=0
FailedReplayCount=0
TotalRequests=1
TotalCallsTriggeringReplay=1
TotalReplayAttempts=1
TotalProtectedCalls=19
SuccessfulReplayCount=1
TotalCallsAffectedByOutages=1
TotalCallsAffectedByOutagesDuringReplay=0
ReplayDisablingCount=0

Roll-up
TotalCalls=83
TotalCompletedRequests=2
FailedReplayCount=0
TotalRequests=4
TotalCallsTriggeringReplay=2
TotalReplayAttempts=2
TotalProtectedCalls=45
SuccessfulReplayCount=2
TotalCallsAffectedByOutages=2
TotalCallsAffectedByOutagesDuringReplay=0
ReplayDisablingCount=0

Looking carefully at the numbers, you can see that the individual count was done before the connections were closed (TotalCompletedRequests=0) and the roll-up was done after both connections were closed. 

You can also use WLST to get the statistics values for the datasource. The statistics are not visible in the administration console or FMWC in WLS 12.2.1.

Thursday Nov 12, 2015

WLS UCP Datasource

WebLogic Server (WLS) 12.2.1 introduces a new datasource type that uses the Oracle Universal Connection Pool (UCP) as an alternative connection pool.  The UCP datasource allows for configuration, deployment, and monitoring of the UCP connection pool as part of the WLS domain.  It is certified with the Oracle Thin driver (simple, XA, and replay drivers). 

The product documentation is at http://docs.oracle.com/middleware/1221/wls/JDBCA/ucp_datasources.htm#JDBCA746 .  The goal of this article  is not to reproduce that information but to summarize the feature and provide some additional information and screen shots for configuring the datasource.

A UCP data source is defined using a jdbc-data-source descriptor as a system resource.  With respect to multi-tenancy, these system resources can be defined at the domain, partition, resource group template, or resource group level. 

The configuration  for a UCP data source is pretty simple with the standard datasource parameters.  You can  name it, give it a URL, user, password and JNDI name.  Most of the detailed configuration and tuning comes in the form of UCP connection properties.  The administrator can configure values for any setters supported by oracle.ucp.jdbc.PoolDataSourceImpl except LogWriter  (see oracle.ucp.PoolDaaSourceImpl) by just removing the "set" from the attribute name (the names are case insensitive).  For example,

ConnectionHarvestMaxCount=3


Table 8-2 in the documentation lists all of the UCP attributes that are currently supported, based on the 12.1.0.2 UCP jar that ships with WLS 12.2.1.

There is some built-in validation of the (common sense) combinations of driver and connection factory:

Driver

Factory (ConnectionFactoryClassName)

oracle.ucp.jdbc.PoolDataSourceImpl (default)

oracle.jdbc.pool.OracleDataSource

oracle.ucp.jdbc.PoolXADataSourceImpl

oracle.jdbc.xa.client.OracleXADataSource

oracle.ucp.jdbc.PoolDataSourceImpl

oracle.jdbc.replay.OracleDataSourceImpl

To simplify the configuration, if the "driver-name" is not specified, it will default to oracle.ucp.jdbc.PoolDataSourceImpl  and the ConnectionFactoryClassName connection property defaults to the corresponding entry from the above table.

Example 8.1 in the product documentation gives a complete example of creating a UCP data source using WLST.   WLST usage is very common for application configuration these days.

Monitoring is available via the weblogic.management.runtime.JDBCUCPDataSourceRuntimeMBean.  This MBean extends JDBCDataSourceRuntimeMBean so that it can be returned with the list of other JDBC MBeans from the JDBC service for tools like the administration console or your WLST script.  For a UCP data source, the state and the following attributes are set: CurrCapacity, ActiveConnectionsCurrentCount, NumAvailable, ReserveRequestCount, ActiveConnectionsAverageCount, CurrCapacityHighCount, ConnectionsTotalCount, NumUnavailable, and WaitingForConnectionSuccessTotal.

The administration console and FMWC make it easy to create, update, and monitor UCP datasources.

The following images are from the administration console. For the creation path, there is a drop-down that lists the data source types; UCP is one of the choices.  The resulting data source descriptor datasource-type set to "UCP". 

http://aseng-wiki.us.oracle.com/asengwiki/download/attachments/643530931/ucp1.jpg?version=3&modificationDate=1443495917000&api=v2

The first step is to specify the JDBC Data Source Properties that determine the identity of the data source. They include the datasource names, the scope (Global or Multi Tenant Partition, Resource Group, or Resource Group Template) and the JNDI names. 

http://aseng-wiki.us.oracle.com/asengwiki/download/attachments/643530931/ucp2.jpg?version=3&modificationDate=1443495999000&api=v2

The next page handles the user name and password, URL, and additional connection properties. Additional connection properties are used to configure the UCP connection pool. There are two ways to provide the connection properties for a UCP data source in the console. On the Connection Properties page, all of the available connection properties for the UCP driver are displayed so that you only need to enter the property value.  On the next page for Test Database Connection, you can enter a propertyName=value directly into the Properties text box.  Any values entered on the previous Connection Properties page will already appear in the text box.  This page can be used to test the specified values including the connection properties.

http://aseng-wiki.us.oracle.com/asengwiki/download/attachments/643530931/ucp5.jpg?version=1&modificationDate=1443496172000&api=v2

The Test Database Connection page allows you to enter free-form values for properties and test a database connection before the data source configuration is finalized. If necessary, you can provide additional configuration information using the Properties, System Properties, and Encrypted Properties attributes.

http://aseng-wiki.us.oracle.com/asengwiki/download/attachments/643530931/ucp3.jpg?version=1&modificationDate=1404361954000&api=v2

The final step is to target the data source. You can select one or more targets to which to deploy your new UCP 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 you can get a connection in the application.

For editing the data source, minimal tabs and attributes are exposed to configure, target, and monitor this data source type. 

http://aseng-wiki.us.oracle.com/asengwiki/download/attachments/643530931/ucp4.jpg?version=1&modificationDate=1404361954000&api=v2

The capabilities in FMWC are similar to the administrative console but with a different look and feel.

If you select JDBC Data Sources from the WebLogic Domain drop-down, you will see a list of existing data sources with their associated data source type, scope, and if applicable RG, RGT and Partition.