Monday Nov 18, 2013

Navigating Through Diagnostic Data Options

In this new post I will talk about diagnostic data and more importantly what data and source of data are needed to troubleshoot and resolve some specific common problems.

WebLogic Server along with the JVM offers various ways to collect logs, debug traces, diagnostic images, thread dumps, and much more. However, useful data can easily get buried with lots of noise, unneeded data that won't help resolving a particular problem.

Performance Issues

We will start with performance issues such as long running requests or server hangs.  The best way to narrow down where the issue lies is to dump the java threads and parse them through a thread dump analyzer tool such as ThreadLogic.  Looking at the server log files, or enabling some debugging features would be premature since the first step is to identify the thread(s) that are stuck to determine what is being executed and relevant JEE resources. The log could also alert about stuck threads once they have been stuck for over the maximum allowed time (see MaxStruckThreadTime) but unless thread dumps are examined, it won't be possible to determine possible patterns of issues and threads' relationship around locked objects.

Memory Leaks

Another common performance issue involves memory leaks.  If you encounter OutOfMemory java.lang exceptions you should issue a heap dump and analyze it with a tool such as VisualVM or the Eclipse MAT.  See my post on Heap Dumps analysis for more details. Heap dumps are snapshots of memory certain times.  Heap dumps are to the Java process memory (non native) what thread dumps are to Java process threads.  Enabling -verbose:gc is also an effective way to monitor heap usage at runtime.  The output of the GC stats can be redirected to a file so that not to clog the server logs.  This can be done by setting -Xloggc with HotSpot JVM.  It's recommended to include -XX:+PrintGCDetails and -XX:+PrintGCTimeStamps to log time stamps and detailed GC activities as well.  Troubleshooting guide for HotSpot VM details these flags among others useful flags for diagnosis purposes.

Applications, 3rd parties, and configuration issues

The following chart summarizes what could be collected to debug each of the listed WLS component.  This list is by no means exhaustive.  Many more debug flags could be used for these listed components.  This chart contains examples of useful debug options and links to additional resources.

For the following, Redirect stdout and stderr logging should be enabled.  This can be done via the Administration Console in the advanced section of the servers logging tab or at startup in the java command line with -Dweblogic.StdoutDebugEnabled=true.  In addition, the severity level of the log file should be set to debug.

WLS comp. or 3rd party resource

Examples of Debug options and flags

MyOracleSupport  related resources

(login required)

 EJB/Web Serv.

-Dweblogic.webservice.verbose=true

-Dweblogic.webservice.client.verbose=true

To log SOAP requests and response messages

Trace in servers log files 

Troubleshooting EJBs Issues

How to enable Debugging for Weblogic EJB

How To Debug WLS Application Container Problems

Troubleshooting Oracle Web Services 11g

 JMS

-Dweblogic.debug.DebugJMSStore

To log info on load, store events and transaction records into servers log files

See Debugging JMS for more JMS debugging options


Troubleshooting JMS Info Center

 SSL

-Dssl.debug=true

-Dweblogic.security.SSL.verbose=true 

Trace in servers log files 

Troubleshooting SSL Security
Clustering

-Dweblogic.debug.DebugReplication for high level replication info

-Dweblogic.debug.DebugClusterAnnouncements to log info on announcement, StateDump and attributes messages sent or received by multicast

Trace in servers log files  

Debug RJVM or Intra-Cluster Communication Problems
JTA

weblogic.JTAXA & weblogic.JTA2PC for runtime issues, example:

JAVA_OPTIONS="-Dweblogic.Debug=weblogic.JTAXA, weblogic.JTA2PC"

Trace in servers log files  

Investigating Transaction Problems
JDBC

-Dweblogic.debug.DebugJDBCSQL=true

to print information about JDBC methods invoked with their arguments, return values, and thrown exceptions

See JDBC Debugging Scopes under Monitoring JDBC Resources

Trace in servers log files  

Investigating JDBC Issues

JDBC Debugging Scopes

Oracle JDBC Driver (*)

-Doracle.jdbc.Trace=true to enable jdbc logging

Create OracleLog.properties

Trace is file defined in oracle.jdbc.LogFile

Enable Oracle JDBC Driver Debug in WLS
Deployment (Classloader) Set the following options to true to report which classes are getting loaded

-Dweblogic.utils.classloaders.GenericClassLoader.Verbose

-Dweblogic.utils.classloaders.ChangeAwareClassLoader.Verbose

-Dweblogic.utils.classloaders.ClasspathClassFinder

-Dweblogic.utils.classloaders.DefaultFilteringClassLoader.Verbose

-Dweblogic.utils.classloaders.FilteringClassLoader.Verbose

-Dweblogic.utils.classloaders.FilteringClassLoader.ResourceDump

Trace in servers log files

Deployment Problems - Enabling Debug


Proxy plug-in

Debug="ALL" in the proxy configuration file

(iisproxy.ini, httpd.conf or obj.conf)

Trace in file set in WLLogFile defined inside proxy configuration file

Common Diagnostic Process for Proxy Plug-In Problems

(*)  Enabling JDBC Driver debug flags is verbose so it's recommended to set logging properties to appropriate levels such as FINE, SEVERE, FINEST, INFO etc. based upon the debugging requirement.

RDA

Diagnosing and troubleshooting also require a good understanding of the Weblogic domain environment, its configuration, patching level, JVM parameters and also an easy navigation through various logs and configuration files.  For this, RDA is a great diagnosing tool because it collects all this data indiscriminately.  One of the numerous benefits of RDA is that it doesn't require any complex setting and can collect data from each distinctive log file, xml repository and scripts.  So, RDA reports are of great assistance while working directly with Oracle support because that allow the engineer assigned to a service request to quickly understand the environment under which a specific issue is occurring.  This generally helps expedite the resolution of the SR.

WLDF

The Weblogic Diagnostic Framework can be used to collect metrics, setup watch and notifications, and to define instrumentation.  With WLS 12.1.2, you can set the level (Low, Medium or High) of built-in modules that will gather metrics, or disable them completely.  WLDF collects runtime statistics on JDBC, JTA and JVM and more.  You can then use the Monitoring Dashboard to view and navigate through the collected data.  WLDF is not an alternative to debug options.  Debug options activate tracing that is coded as part of WLS whereas WLDF collects and monitors runtime mbeans values and can activate notifications with defined rules.  They are used for different purposes.  

The WLDF built-in configurations (Low, Medium, & High) can be cloned into a new configuration, and used as templates for creating custom WLDF configurations.  The Low volume built-in is enabled by default in production mode.  One new feature with WLS 12.1.2 is Runtime control which gives the ability to activate or deactivate diagnostic system modules dynamically at runtime wihtout making any domain configuration change.

JFR

JFR recording data can also be captured through WLDF diagnostic images based on WLDF watch rules so that JVM runtime system information can be analyzed along with recording of WLS components diagnostic data.  Once extracted from the diagnostic image capture, the JFR file can be analyzed using Java Mission Control.

WLST

WLST diagnostic commands can be used to extract data from the diagnostic archive (event and harvested metric data) in either XML form or CSV, based on the function you use.  You can also dump the diagnostics data from a harvester to a local file.

The following WLST functions allow to capture an image and to download image files from a remote system over WLST:

One major difference between the diagnostic framework and the debug options is that debugs are enabled so that an issue can be reproduced and traced whereas the diagnostic image capture is used as a server-level dump for post-failure analysis in a similar way heap dumps are used post memory leak failures.

Monday Sep 16, 2013

Ensuring high level of performance with WebLogic JDBC

In this post you will find some common best practices aimed at ensuring high levels of performance with WebLogic JDBC.  However, rather than just throwing some tips, I will detail why each recommendation is beneficial to JDBC and Weblogic Server performance.

  • Use Oracle JDBC thin driver (Type 4) rather than OCI driver (Type 2)

The Oracle JDBC thin driver is lightweight (easy to install and administrate), platform-independent (entirely written in Java), and provides slightly higher performance than the JDBC OCI (Oracle Call Interface) driver.  The thin driver does not require any additional software on the client side.  Oracle JDBC FAQ stipulates that the performance benefit with the thin driver is not consistent and that the OCI driver can even deliver better performances in some scenarios.

  • Use PreparedStatements objects rather than Statement 
With PreparedStatements, compiled SQL statements will be kept in cache and only be executed once against the database.  As a result, unnecessary parsing and round-trips to the database will be avoided when the same statement is used later within the same connection.  The statement cache size defines the total number of prepared statement that can be made with a single connection from the Datasource.

  • Close all JDBC resources in a finally Block

This include ResultSet, Connection, Statement and Prepared Statement objects and to avoid potential memory leaks.  The connection.close() won't necessarily automatically clean up all the other objects because the implementation of close() may differ between JDBC drivers.  Also, JDBC objects not properly closed could lead to this error:

java.sql.SQLException: ORA-01000: maximum open cursors exceeded.

If you don't explicitly close Statements and ResultSets right away, cursors may accumulate and exceed the maximum number allowed in your DB before the Connection is closed. 

  • Set Shrink frequency to 0 in production environments

By disabling Shrink frequency you will not allow waits before shrinking a connection pool that has incrementally increased to meet demand.  The Shrink Frequency parameter is used to specify the number of seconds to wait before shrinking a connection pool, given that ShrinkingEnabled is kept at its default value, or set to true.  Weblogic shrinks a connection pool by reducing the number of connections to the greater of either the minimum capacity or the number of connection in use and thus frees up some resources.  In development we can afford to keep the no-longer-used connection active rather than immediately returning them to the pool.  

  • Consider skipping the SQL-query test when Test Connections on Reserve is enabled

When Test Connections on Reserve is enabled (see Advanced Connection Pool Configuration in the console), the Weblogic Server checks a database connection prior to returning the connection to a client to avoid passing an invalid connection in the application.  Since this operation could impact the performance, it's recommended to use Seconds to Trust an Idle Pool Connection (set to 10 seconds by default) that defines how long WebLogic Server will trust the connection and therefore skip the connection test after a connection has been proven valid.

  • Enable Pinned-to-Thread
Disabled by default, this option can improve performance by enabling threads to keep a pooled database connection even after the application closes the logical connection.  This eliminates potential contention between threads getting a database connection.  However, this parameter should be used with great care because the connection pool maximum capacity is ignored when pinned-to-thread is enabled.  A connection no longer used will not return to the pool but will stay linked to the thread, and no shrinking can apply to that pool.
  • Ensure that Maximum Thread Constraint property doesn't exceed the number of database connection

This property (See Environment Work Manager in the console) will set a maximum number of possible concurrent requests. If it exceeds the number of database connection then some threads might end up waiting until existing database connection are made available.


Visit Tuning Data Source Connection Pools and Tuning Data Sources for additional parameters tuning in JDBC data sources and connection pools to improve system performance with Weblogic Server 12.1.2, and Performance Tuning Your JDBC Application for application-specific design and configuration. 

Wednesday Jul 31, 2013

JMS JDBC Store Performance Using Multiple Connections

This article is a bit different than the normal data source articles I have been writing because it's focused on an application use of WebLogic Server (WLS) data sources, although the application is still part of WLS.  Java Messaging Service (JMS) supports the use of either a file store or a JDBC store to store JMS persistent messages (the JDBC store can also be used for Transaction Log information, diagnostics, etc.).  The file store is easier to configure,generates no network traffic, and is generally faster.  However, the JDBC store is popular because most customers have invested in High Availability (HA) solutions, like RAC, Data Guard or Golden Gate,  for their database so using a JDBC store on the database makes HA and migration much easier (for the file store, the disk must be shared or migrated). Some work has been done in recent releases to improve the JDBC store performance and take advantage of RAC clusters.

It's obvious from the JDBC store configuration that JMS uses just a single table in the database.  JMS use of this table is sort of like a queue so there are hot spots at the beginning and end of the table as messages are added and consumed - that might get fixed in WLS in the future but it is a consideration for the current store performance.  JMS since the beginning has been single threaded on a single database connection.  Starting in WLS 10.3.6 (see this link), the store can run with multiple worker threads each with its own connection by setting Worker Count on the JDBC Persistent Store page in the console.  There are no documented restrictions or recommendations about how to set this value  Should we set it to the maximum allowed of 1000 so we get a lot of work done?  Not quite ...

Since we have contention between the connections, using too many connections is not good.  To begin with, there is overhead in managing the work among the threads so if JMS is lightly loaded, it's worse to use multiple connections.  When you have a high load, we found that for one configuration, 8 threads gave the best performance but 4 was almost as good at half the resources using the Oracle Thin driver on an Oracle database (more about database vendors below).  Optimization for queues with multiple connections is  a big win with some gains as high as 200%.  Handling a topic is another ... well, topic.  It's complicated by the fact that a message can go to a single or multiple topics and we want to aggregate acknowledgements to reduce contention and improve performance.  Topic testing saw more modest gains of  around 20%, depending on the test.

How about different data source types? It turns out that when using a RAC cluster and updates are scattered across multiple instances, there is too much overhead in locking and cache fusion across the RAC instances.  That makes it important that all of the reserved connections are on a single RAC instance.   For a generic data source, there is nothing to worry about - you have just one node.  In the case of multi data source (MDS), you can get all connections on a single instance by using the AlgorithmType set to "Failover" (see this link ).   All connections will be reserved on the first configured generic data source within the MDS until a failure occurs, then the failed data source will be marked as suspended and all connections will come from the next generic data source in the MDS.  You don't want to use AlgorithmType set to "Load-Balancing".  In the case of Active GridLink (AGL), it's actually difficult to get connection affinity to a single node and without it, performance can seriously degrade.  Some benchmarks saw performance loss of 50% when using multiple connections on different instances.  For WLS 10.3.6 and 12.1.1, it is not recommended to use AGL with multiple connections.  In WLS 12.1.2, this was fixed so that JMS will reserve all connections on the same instance.  If there is a failure, all of the reserved connections need to be closed, a new connection is reserved using Connection Runtime Load Balancing (RCLB), hopefully on a lightly loaded instance), and then the rest of the connections are reserved on the same instance.  In one benchmark, performance improved by 200% when using multiple connections on the same instance.

How about different database vendor types?  Your performance will vary based on the application and the database.  The discussion above regarding RAC cluster performance is interesting and may have implications for any application that you are moving to a cluster.  Another thing that is specific to the Oracle database is indexing the table for efficient access by multiple connections.  In this case, it is recommended to use a reverse key index for the primary key.  The bytes in the key are reversed such that keys that normally would be grouped because the left-most bytes are the same are now distributed more evenly (imagine using a B-tree to store a bunch of sequential numbers with left padding with 0's, for example). 

Bottom line: this feature may give you a big performance boost but you might want to try it with your application, database, hardware, and vary the worker count.




Monday Jul 22, 2013

Part 2 - 12c Database and WLS - Application continuity

I introduced WLS support for 12c database in this blog link and described one of the prerequisites for Application Continuity (AC) at this blog link.

When an error occurs on a connection, it would be nice to be able to keep processing on another connection - that's what AC does.  When on a single node database, getting another connection means that the database and network are still available, e.g., maybe just a network glitch.  However, in the case of a Real Application Cluster (RAC), chances are good that even if you lost a connection on one instance you can get a connection on another instance for the same database.  For this feature to work correctly, it's necessary to ensure that all of the work that you did on the connection before the error is replayed on the new connection - that's why AC is also called replay. To replay the operations, it's necessary to keep a list of the operations that have been done on the connection so they can be done again.  Of course, when you replay the operations, data might have changed since the last time so it's necessary to keep track of the results to make sure that they match.  So AC may fail if replaying the operations fail.

You can read the WLS documentation, an AC white paper, and the Database documentation to get the details. This article gives an overview of this feature (and a few tidbits that the documentation doesn't cover).

To use this feature, you need to use both the 12c driver and a 12c database.  Note that if you use an 11.2.0.3 driver and/or database, it might appear to work but replay will only happen for read-only transactions (the 11g mode is not supported but we don't have a mechanism to prevent this).

You need to configure a database service to run with AC.  To do that, you need to set the new 12c service attributes FAILOVER_TYPE=TRANSACTION and COMMIT_OUTCOME=true on the server side.

There's not much to turning on AC in WLS - you just use the replay driver "oracle.jdbc.replay.OracleDataSourceImpl" instead of "oracle.jdbc.OracleDriver"  when configuring the data source.  There's no programming in the application needed to use it.  Internal to WLS, when you get a connection we call the API to start collecting the operations and when you close a connection we call the API to clear the operation history.

WLS introduced a labeling callback for applications that use the connection labeling feature.  If this callback is registered, it will be called when a new connection is being initialized before replay.  Even if you aren't using labeling, you might still want to be called and there is a new connection initialization callback that is for replay (labeling callback trumps initialization callback if both exist).

It sounds easy and perfect - what's the catch?   I've already mentioned that you need to get rid of references to concrete classes and use the new Oracle interfaces.  For some applications that might be some significant work.  I've mentioned that if replaying the operations fails or is inconsistent, AC fails.  There are a few other operations that turn off AC - see the documentation for details. One of the big ones is that you can't use replay with XA transactions (at least for now).   Selecting from V$instance or sys_context  or other test traces for test instrumentation needs to be in callouts as the values change when replayed.  If you use sysdate or systimestamp, you need to grant keep date time to your user.

We are also tracking two defects - AC doesn't work with Oracle proxy authentication and it doesn't work with the new DRCP feature. 

There's another more complex topic to consider (not currently in the current WLS documentation).  By default, when a local transaction is completed on the connection, replay ends.  You can keep working on the connection but failure from that point on will return an error to the application. This default is based on the service attribute SESSION_STATE_CONSISTENCY with a value of DYNAMIC.  You can set the value to STATIC if your application does not modify non-transactional session state (NTSS) in the transaction.  I'm not sure how many applications fall into this trap but the safe thing is to default to dynamic.  I'll include such a code fragment below.  A related common problem that people run into is forgetting to disable autocommit, which defaults to true, and the first (implicit) commit turns off replay if SESSION_STATE_CONSISTENCY is set to DYNAMIC.

It's important to know how to turn on debugging so that if a particular sequence doesn't replay, you can understand why.  You simply need to run with the debug driver (ojdbc6_g.jar or ojdbc7_g.jar) and run with -Dweblogic.debug.DebugJDBCReplay (or turn this debug category on in the configuration).

AC won't replay everything and you still need to have some application logic to deal with the failures or return them to the end user.  Also, there's some overhead in time and memory to keep the replay data.  Still, it seems like a great feature for a lot of applications where you don't need to change anything but the driver name and you can avoid showing an error to the end user or simplify some recovery logic.

P.S. Confused about NTSS? So was I.  Examples of non-transactional session state that can change at run-time are ALTER SESSION, PL/SQL global variables, SYS_CONTEXT, and temporary table contents.  Here's an example of a PL/SQL global variable.  Imagine a package with the following body:

 current_order number := null;
 current_line number;
 procedure new_order (customer_id number) is
  current_order := order_seq.nextval;
  insert into orders values (current_order, customer_id);
  current_line := 0;
 end new_order;
 procedure new_line (product_id number, count number) is
  current_line := current_line + 1;
  insert into order_lines values (current_order, current_line,product_id, count);
 end new_line;
end order;

and a psuedo-code sequence in WLS like this:
getConnection()
exec "begin order.new_order(:my_customer_id); end;"
commit;
exec "begin order.new_line(:my_product_id, :my_count); end;"
<DB server failure and failover>
commit;

In this scenario, we won't replay the first transaction, because it's already committed and we'd end up with two orders in the database. But if we don't replay it, we lose the order_id, so the new_line call won't work. So we have to disable replay after the commit.  If you can guarantee that no such scenario exists, you can mark the service session-state as static.



Thursday Jul 11, 2013

Using Oracle JDBC Type Interfaces

One of the hot new features in Oracle database 12c is Application Continuity (AC).  The feature basically will detect that a connection has gone bad and substitute a new one under the covers (I'll talk about it more in another article).  To be able to do that, the application is given a connection wrapper instead of the real connection.  Wrappers or dynamic proxies can only be generated for classes based on interfaces.  The Oracle types, like REF and ARRAY, were originally introduced as concrete classes.   In WLS 10.3.6 and/or the JDBC 11.2.0.3 driver, there are new interfaces for the Oracle types that you will need to use to take advantage of this new AC feature.

First, some history so that you understand the needed API changes.  In the early days of WebLogic data source, any references to vendor proprietary methods were handled by hard-coded references.  Keeping up with adding and removing methods was a significant maintenance problem. At the peak of the insanity, we had over one thousand lines of code that referenced Oracle-proprietary methods and the server could not run without an Oracle jar in the classpath (even for DB2-only shops).  In release 8.1 in March 2003, we introduced wrappers for all JDBC objects such that we dynamically generated proxies that implement all public interface methods of, and delegate to, the underlying vendor object.  The hard-coded references and the maintenance nightmare went away and just as importantly, we could provide debugging information, find leaked connections, automatically close objects when the containing object closed, replace connections that fail testing, etc.  The Oracle types were concrete classes so proxies were generated for these classes implementing the WLS vendor interfaces weblogic.jdbc.vendor.oracle.*.  Applications can cast to the WLS vendor interfaces or use getVendorObj to access the underlying driver object. Later, we added an option to unwrap data types, with a corresponding loss of functionality like no debug information.

Although the focus of this article is Oracle types, the dynamic proxies work for any vendor.   For example, you can cast a DB2 connection to use a proprietary method  ((com.ibm.db2.jcc.DB2Connection)conn).setDB2ClientUser("myname").

Starting with Oracle driver 11.2.0.3, the database team needed wrappers for the new AC feature and introduced new interfaces.  For WebLogic data source users, that's good news - no more unwrapping, the weblogic.jdbc.vendor package is no longer needed, and it's all transparent.  Before you go and change your programs to use the new Oracle proprietary interfaces, the recommended approach is to first see if you can just use standard JDBC API's.  In fact, as part of defining the new interfaces, Oracle proprietary methods were dropped if there was an equivalent standard JDBC API or the method was not considered to add significant value.  This table defines the mapping.  The goal is to get rid of references to the first and second columns and replace them with the third column.

" align="center">Old Oracle types
Deprecated WLS Interface
New interfaces
oracle.sql.ARRAY
weblogic.jdbc.vendor.oracle.OracleArray
oracle.jdbc.OracleArray
oracle.sql.STRUCT
weblogic.jdbc.vendor.oracle.OracleStruct
oracle.jdbc.OracleStruct
oracle.sql.CLOB
weblogic.jdbc.vendor.oracle.OracleThinClob
oracle.jdbc.OracleClob
oracle.sql.BLOB
weblogic.jdbc.vendor.oracle.OracleThinBlob
oracle.jdbc.OracleBlob
oracle.sql.REF
weblogic.jdbc.vendor.oracle.OracleRef
oracle.jdbc.OracleRef

This is a job for a shell hacker!  Much of it can be automated and the compiler can tell you if you are referencing a method that has gone away - then check if the missing method is in the equivalent jdbc.sql interface (e.g., getARRAY() becomes the JDBC standard getArray()).

You can take a look at a sample program that I wrote to demonstrate all of these new interfaces at   https://blogs.oracle.com/WebLogicServer/resource/StephenFeltsFiles/OracleTypes.txt (note that this is actually a ".java" program). It covers programming with all of these Oracle types.  While use of Blob and Clob might be popular, Ref and Struct might not be used as much.  The sample program shows how to create, insert, update, and access each type using both standard and extension methods.  Note that you need to use the Oracle proprietary createOracleArray() instead of the standard createArrayOf(). Although the sample program doesn't use the standard createBlob() or createClob(), these are supported for the Oracle driver.

The API's can be reviewed starting with the 11.2.0.3 version of the Javadoc.  A copy is available at http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/package-summary.html .

This is a first step toward using Application Continuity.  But it's also a good move to remove Oracle API's that will eventually go away and use standard JDBC interfaces and new Oracle interfaces.



Tuesday Jul 02, 2013

Part 1 - 12c Database and WLS - Overview

The download of Oracle 12c database became available on June 25, 2013.  There are some big new features in 12c database and WebLogic Server will take advantage of them. Immediately, we will support using 12c database and drivers with WLS 10.3.6 and 12.1.1.  With WLS 12.1.2, available July 11, 2013 (join the Online Launch Event on July 31st), additional functionality is supported (those rows in the table below with all "No" values will get a "Yes).  The following table maps the Oracle 12c Database features supported with various combinations of currently available WLS releases, 11g and 12c Drivers, and 11g and 12c Databases.

Feature WebLogic Server 10.3.6/12.1.1 with 11g drivers and 11gR2 DB WebLogic Server 10.3.6/12.1.1 with 11g drivers and 12c DB WebLogic Server 10.3.6/12.1.1 with 12c drivers and 11gR2 DB WebLogic Server 10.3.6/12.1.1 with 12c drivers and 12c DB
JDBC replay No No No Yes (Active GridLink only in 10.3.6, add generic in 12.1.1)
Multi Tenant Database No Yes (except set container) No Yes (except set container)
Dynamic switching between Tenants No No No No
Database Resident Connection pooling (DRCP) No No No No
Oracle Notification Service (ONS) auto configuration No No No No
Global Database Services (GDS) No Yes (Active GridLink only) No Yes (Active GridLink only)
JDBC 4.1 (using ojdbc7.jar files & JDK 7) No No Yes Yes

 The My Oracle Support (MOS) document covering this is "WebLogic Server 12.1.1 and 10.3.6 Support for Oracle 12c Database [ID 1564509.1]" at the link https://support.oracle.com/epmos/faces/DocumentDisplay?id=1564509.1.

The following documents are also key references:
12c Oracle Database Developer Guide http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/toc.htm
12c Oracle Database Administrator's Guide http://docs.oracle.com/cd/E16655_01/server.121/e17636/toc.htm .

I plan to write some related blog articles not to duplicate existing product documentation but to introduce the features, provide some examples, and tie together some information to make it easier to understand.

How do you get started with 12c?  The easiest way is to point your data source at a 12c database.  The only change on the WLS side is to update the URL in your data source (assuming that you are not just upgrading your database).  You can continue to use the 11.2.0.3 driver jar files that shipped with WLS 10.3.6 or 12.1.1.  You shouldn't see any changes in your application.  You can take advantage of enhancements on the database side that don't affect the mid-tier.  On the WLS side, you can take advantage of using Global Data Service or connecting to a tenant in a multi-tenant database transparently.

If you want to use the 12c client jar files, it's a bit of work because they aren't shipped with WLS and you can't just drop in ojdbc6.jar as in the old days.  You need to use a matched set of jar files and they need to come before existing jar files in the CLASSPATH.  The MOS article is written from the standpoint that you need to get the jar files directly - download almost 1G and install over 600M footprint to get 15 jar files.  Assuming that you have the database installed and you can get access to the installation (or ask the DBA), you need to copy the 15 jar files to each machine with a WLS installation and get them in your CLASSPATH.  You can play with setting the PRE_CLASSPATH but the more practical approach may be to just update WL_HOME/common/bin/commEnv.sh directly.  There's a change in the transaction completion behavior (read the MOS) so if you think you might run into that, you will want to set -Doracle.jdbc.autoCommitSpecCompliant=false.  Also if you are running with Active GridLink, you must set -Doracle.ucp.PreWLS1212Compatible=true (how's that for telling you that this is fixed in WLS 12.1.2). 

Once you get the configuration out of the way, you can start using the new ojdbc7.jar in place of the ojdbc6.jar to get the new JDBC 4.1 API's.  You can also start using Application Continuity.  This feature is also known as JDBC Replay because when a connection fails you get a new one with all JDBC operations up to the failure point automatically replayed.  As you might expect, there are some limitations but it's an interesting feature.

 Obviously I'm going to focus on the 12c database features that we can leverage in WLS data source.  You will need to read other sources or the product documentation to get all of the new features.

PS Since this was written, WLS 12.1.2 is now available.  See blog entry Oracle WebLogic Server 12.1.2 is available for more information. There is an Online Launch Event on July 31st.

Thursday Jun 20, 2013

JDBC in Java Mission Control

In case you haven't tried them before, JRockit Flight Recorder (JFR) and JRockit Mission Control (JMC) are useful tools to analyze what's happening in the Java Virtual Machine (JVM) and your running application. Basically, you make a record of events and information in a defined time period and then analyze the generated jfr file with JMC.  WebLogic Server 10.3.3 added support in the diagnostics component to work tightly with JMC. Part of that support is analysis of what is happening on the JDBC front and that's what I'm going to focus on here.  If you want some background, read about JFR at http://docs.oracle.com/cd/E15289_01/doc.40/e15070.pdf  and JMC at http://www.oracle.com/technetwork/middleware/jrockit/overview/index-090630.html.

The command name to get started is jrmc.If the JVM browser is not visible, click Window -> Show View -> JVM Browser. Right click your WebLogic Server JVM in the list and select Start Console.  It should look something like the following screen shot.

Now you need to record a sample for analysis.  Right click the JVM connection again and select Start Recording.  Select a timer period like one minute and click start.  For this article, I ran a demo program OTRADE that was used at Oracle Open World 2012.  It has a simple load generator that simulates updating stock values for Oracle stock.  The variety of SQL statements is limited but enough to demonstrate how JFR works.  A window opens to the JFR analysis when the time period is complete.  I'm going to focus the analysis on JDBC events. Select the Events button on the left.  Select Window -> Show View -> Event Types. Unselect Java Application, expand WebLogic, and select JDBC (minimize the Event Type window).  This is what the overview tab looks like with just the JDBC events showing.


The Log tab allows you to drill into individual records.

The Trace tab allows you to see the stack trace where the SQL call was made.  This can be useful if there is a misbehaving SQL statement and you want to know who is calling it.

There are a lot of other interesting areas for analysis that I won't dive into.  You might want to look at Code -> Hot methods to see where time is being spent or Memory -> object statistics to see the big memory users.

It's possible to load some "unsupported" plug-ins. I'd recommend the one for WebLogic.  Select Help, Install Plugins, Expand, WebLogic Tab Pack, Next, Finish, and Install All.  You will need to restart jrmc and select the WebLogic button on the left.


Then select the Database tab.


The default WLDF diagnostic volume is Low.  You can reset it from the administrative console by selecting Environment -> Servers, clicking on the name of your server, selecting Configuration -> General, and changing the Diagnostic Volume value to Medium or High.

If you are aware of what's happening on the JVM front, you will realize that the last version of JRockit is JDK6.  That's fine for WLS 10.3.6, which supports the server running on JDK6 or JDK7, but 12.1.1 moves to JDK7, which is based on HotSpot.  The early versions of  JDK7 on HotSpot don't have the ability to run JFR but that's coming back in Oracle JDK7 with updates greater than or equal to 7u4.  I tested using the latest publicly available update at this time, 7u25, with WLS 10.3.6.  You need to have a Java Advanced or Java Suite license (included in WebLogic Server EE and WebLogic Suite license). You can download it from "All Java SE Downloads on MOS [ID 1439822.1]". Of course, all of the "JRockit" names now become "Java".  JRMC is now JMC (JFR remains the same).  The JFR component is now gated behind switches and needs to be actively turned on.  Also, HotSpot still needs to set PermGen (that isn't fixed until JDK8).  So your command line will be something like

java -Xmx1024m -XX:MaxPermSize=350m -XX:+UnlockCommercialFeatures -XX:+FlightRecorder   weblogic.Server

This article is a bit long but I've only scratched the surface on how to use this tool to analyze your WebLogic data source usage among the many other WLS components.  I realize the pictures in this format of the blog appear small - use whatever mechanism your browser has to zoom in on them - the detail is available.

P.S. The JFR recording data is included in WebLogic Diagnostic Framework (WLDF) Diagnostic Images, and is also included in DFW incident reports. Those mechanisms can be used to capture JFR recording data as well.  See Using WLDF with Oracle JRockit Flight Recorder and Configuring and Capturing Diagnostic Images.


Friday Jun 07, 2013

Using try-with-resources with JDBC objects

There is a new language construct that is introduced in JDK7 that applies to JDBC.  It’s called try-with-resource.  There is a tutorial at http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html.
It allows you to automatically close an object when it is no longer needed.  Theoretically, the object must extend java.lang.AutoCloseable.  In JDK7, that list includes java.sql.CallableStatement, Connection, PreparedStatement, Statement, ResultSet, and *RowSet.  The following code

Statement stmt = null;
try {
  stmt = con.createStatement();
} catch (Exception ignore) {
} finally {
  if (stmt != null) stmt.close()
}
becomes a shorter version using the new syntax
try (Statement stmt = con.createStatement()) {
} catch (Exception ignore) {
}

Here’s what a larger example looks like that I embedded in a WebLogic servlet for testing.  Note that there are two resources in the first try-with-resource, separated by a semi-colon.

private String doit() {
  String table2 = "test222";
  String dropsql = "drop table " + table2 + "";
  String createsql = "create table " + table2 + " ( col1 int, col2 int )";
  String insertsql = "insert into " + table2 + " values (1,2)";
  String selectsql = "select col1, col2 from " + table2 + "";
  try {
    ds = getDS();
  } catch(Exception e) {
    return("failed to get datasource");
  }
  try (Connection conn = ds.getConnection();
    Statement stmt = conn.createStatement()) {
    try {
       stmt.execute(dropsql);
   } catch (Exception ignore) {} // ignore if table not dropped
   stmt.execute(createsql);
   stmt.execute(insertsql);
   try (ResultSet rs = stmt.executeQuery(selectsql)) {
     rs.next();
   } catch (Exception e2) {
     e2.printStackTrace();
     return("failed");
   }
 } catch(Exception e) {
   e.printStackTrace();
   return("failed");
 }
 return "DONE";
}

I’m not sure whether or not I like the programming paradigm – you can decide that for yourself.

How do I know that the connection, statement, and result set get closed?  I can turn on JDBCSQL debugging in the server and look at the server log output (this is a good trick to use to see what is happening under the covers).  Here is a stripped down version of the output.

 Connection@1 CreateStatement() 
 Connection@1 CreateStatement returns StatementWrapper@2 
 StatementWrapper@2 execute(drop table test222) 
 StatementWrapper@2 execute(drop table test222) throws java.sql.SQLSyntaxErrorException: table or view does not exist
 StatementWrapper@2 execute(create table test222 ( col1 int, col2 int )) 
 StatementWrapper@2 execute returns false 
 StatementWrapper@2 execute(insert into test222 values (1,2)) 
 StatementWrapper@2 execute returns false 
 StatementWrapper@2 executeQuery(select col1, col2 from test222) 
 StatementWrapper@2 executeQuery returns ResultSetImpl@3 
 ResultSetImpl@3 next() 
 ResultSetImpl@3 next returns true 
 ResultSetImpl@3 close() 
 ResultSetImpl@3 close returns 
 StatementWrapper@2 close() 
 StatementWrapper@2 close returns 
 Connection@1 close() 
 Connection@1 close returns

You might be thinking that there are not a lot of JDBC 4.1 compliant drivers out there and that’s true (the Oracle thin driver will have an ojdbc7.jar in version 12c, when it is released).  However, an object like java.sql.Statement implements AutoClosable so when running on JDK7 the driver Statement also is assumed to implement AutoClosable.  The object just needs to have a close() method so all of these JDBC objects have met the criteria since the initial specification.  WebLogic Server started supporting JDK7 in version 10.3.6.  Testing with all drivers that are shipped with WebLogic server, plus some drivers from various vendors, show that this JDK7 language feature works just fine with pre-JDBC 4.1 drivers. 

Note that this trick doesn't work for new JDK7 methods - you will get an AbstractMethodError.

So if you like this programming paradigm, start using it now with WebLogic Server 10.3.6 or later.

Wednesday Apr 24, 2013

Getting Connections with Active GridLink

As a follow-up to an earlier blog, someone asked "Can you please tell us more about gravitationShrinkFrequencySeconds and DRCP ?"   DRCP support isn’t shipping yet (wait until the next version of the Database and of WLS) so I can’t talk about it.

I think that people understand how connections are allocated in generic data sources and multi data sources (MDS).   Let me talk generally about how connections are allocated in Active GridLink (AGL). With the former (generic and MDS), connection allocation is pretty much dependent on WLS.  With AGL, it is also very dependent on the database configuration and runtime statistics.  The runtime information is provided to WLS via Oracle Notification Service (ONS) in the form of up and down events and Runtime Load Balancing events.

Connections are added to the pool initially based on the configured initial capacity. Connect time load balancing based on the listener(s) is used to spread the connections across the instances in the RAC cluster. For that to work correctly, you must either specify a Single Client Access Name (SCAN) address or use LOAD_BALANCE=ON for multiple non-SCAN addresses.  If you have multiple addresses and you forget to use LOAD_BALANCE=on, then all of the connections will end up on the first RAC instance – not what you want.  Connection load balancing is intended to give out connections based on load on the available RAC instances.  It’s not perfect.  If you have two instances and they are evenly loaded, there should be approximately 50% of the connections on each instance (but don’t enter a bug if they split up 49/51).  There a general problem with load balancing that the statistics can lag reality.  If you have big swings in demand on the two instances, then you can end up creating a connection on the more heavily loaded instance.

If you go to get a connection from the pool, runtime load balancing is used to pick the instance.  That’s again based on the load on the various instances and the information is updated by default every 30 seconds.  If a connection is available on the desired instance, then you have a hit on the pool and you get the available connection.

If you go to get a connection from the pool and one doesn’t exist on the desired instance, then you have a miss.  In this case, a connection is added to the pool on demand based on connection load balancing.  That is, if you go to reserve a connection and there isn’t one available in the pool, then a new one is created and it is done using connection load balancing as described above.  There’s an assumption here that the connect time and runtime load balancing match up.

If you are running within an XA transaction, then that takes priority in determining the connection that you get.  There are restrictions on XA support within a RAC cluster such that all processing for an XA transaction branch must take place on one instance in the cluster (the restrictions are complicated so the one-instance limitation is the only safe approach).  Additionally, performance is significantly better using a single instance so you want that to happen anyway.  The first time that you get a connection, it’s via the mechanisms described above (runtime load balancing or connection load balancing).  After that, any additional requests for connections within the same XA transaction will get a connection from the same instance.  If you can’t get a connection with “XA affinity,” the application will get an exception.

Similar to XA affinity, if you are running within a Web session, you will get affinity for multiple connections created by the same session.  Unlike XA affinity, “Session Affinity” is not mandatory.  If you can’t get a connection to the same instance, it will go to another instance (but there will be a performance penalty).

When you take down a RAC instance, it generates a “planned down event”.  Any unused connections for that instance are released immediately and connections in use are released when returned to the pool.  If a RAC instance fails, it generates an “unplanned down event.”  In that case, all connections for that instance are destroyed immediately.

When a RAC instance becomes available, either a new instance or an instance is restarted, it generates an “up event.”  When that occurs, connections are proactively created on the new instance.

The pool can get filled up with connections to the “wrong” instance (heavily loaded) if load changes over time.  To help that situation, we release unused connections based on runtime load balancing information.  When gravitation shrinking occurs, one unused connection is destroyed on a heavily loaded instance.  The default period for gravitation shrinking is 30 seconds but you can control it by setting the system property “weblogic.jdbc.gravitationShrinkFrequencySeconds” to an integer value.  We don’t actively create a connection at this point.  We wait until there is demand for a new connection and then the connection load balancing will kick in.

Finally, normal shrinking happens if not disabled.  When this occurs, half of the unused connections down to minimum capacity are destroyed.  The algorithm currently just takes the first set of connections on the list without regard to load balancing (it’s random with respect to instances).  The default period is 900 seconds and you can configure this using ShrinkFrequencySeconds.

There are possible improvements that could be made with respect to pool misses, and gravitational and normal shrinking.  And the database team is working on improving the load balancing done on their end. Still, it works pretty well with instances dynamically coming and going and the load changing over time.

Monday Oct 15, 2012

Data Source Security Part 4

So far, I have covered Client Identity and Oracle Proxy Session features, with WLS or database credentials.  This article will cover one more feature, Identify-based pooling.  Then, there is one more topic to cover - how these options play with transactions.

Identity-based Connection Pooling

An identity based pool creates a heterogeneous pool of connections.  This allows applications to use a JDBC connection with a specific DBMS credential by pooling physical connections with different DBMS credentials.  The DBMS credential is based on either the WebLogic user mapped to a database user or the database user directly, based on the “use database credentials” setting as described earlier. Using this feature enabled with “use database credentials” enabled seems to be what is proposed in the JDBC standard, basically a heterogeneous pool with users specified by getConnection(user, password).


The allocation of connections is more complex if Enable Identity Based Connection Pooling attribute is enabled on the data source.  When an application requests a database connection, the WebLogic Server instance selects an existing physical connection or creates a new physical connection with requested DBMS identity.


The following section provides information on how heterogeneous connections are created:
1. At connection pool initialization, the physical JDBC connections based on the configured or default “initial capacity” are created with the configured default DBMS credential of the data source.
2. An application tries to get a connection from a data source.
3a. If “use database credentials” is not enabled, the user specified in getConnection is mapped to a DBMS credential, as described earlier.  If the credential map doesn’t have a matching user, the default DBMS credential is used from the datasource descriptor.
3b. If “use database credentials” is enabled, the user and password specified in getConnection are used directly.
4. The connection pool is searched for a connection with a matching DBMS credential.
5. If a match is found, the connection is reserved and returned to the application.
6. If no match is found, a connection is created or reused based on the maximum capacity of the pool:
- If the maximum capacity has not been reached, a new connection is created with the DBMS credential, reserved, and returned to the application.
- If the pool has reached maximum capacity, based on the least recently used (LRU) algorithm, a physical connection is selected from the pool and destroyed. A new connection is created with the DBMS credential, reserved, and returned to the application.

It should be clear that finding a matching connection is more expensive than a homogeneous pool.  Destroying a connection and getting a new one is very expensive.  If you can use a normal homogeneous pool or one of the light-weight options (client identity or an Oracle proxy connection), those should be used instead of identity based pooling.

Regardless of how physical connections are created, each physical connection in the pool has its own DBMS credential information maintained by the pool. Once a physical connection is reserved by the pool, it does not change its DBMS credential even if the current thread changes its WebLogic user credential and continues to use the same connection.

To configure this feature, select Enable Identity Based Connection Pooling.  See http://docs.oracle.com/cd/E24329_01/apirefs.1211/e24401/taskhelp/jdbc/jdbc_datasources/EnableIdentityBasedConnectionPooling.html  "Enable identity-based connection pooling for a JDBC data source" in Oracle WebLogic Server Administration Console Help.

You must make the following changes to use Logging Last Resource (LLR) transaction optimization with Identity-based Pooling to get around the problem that multiple users will be accessing the associated transaction table.
- You must configure a custom schema for LLR using a fully qualified LLR table name. All LLR connections will then use the named schema rather than the default schema when accessing the LLR transaction table. 
- Use database specific administration tools to grant permission to access the named LLR table to all users that could access this table via a global transaction. By default, the LLR table is created during boot by the user configured for the connection in the data source. In most cases, the database will only allow access to this user and not allow access to mapped users.


Connections within Transactions

Now that we have covered the behavior of all of these various options, it’s time to discuss the exception to all of the rules.  When you get a connection within a transaction, it is associated with the transaction context on a particular WLS instance.


When getting a connection with a data source configured with non-XA LLR or 1PC (using the JTS driver) with global transactions, the first connection obtained within the transaction is returned on subsequent connection requests regardless of the values of username/password specified and independent of the associated proxy user session, if any. The connection must be shared among all users of the connection when using LLR or 1PC.


For XA data sources, the first connection obtained within the global transaction is returned on subsequent connection requests within the application server, regardless of the values of username/password specified and independent of the associated proxy user session, if any.  The connection must be shared among all users of the connection within a global transaction within the application server/JVM.


Friday Oct 12, 2012

Data Source Security Part 3

In part one, I introduced the security features and talked about the default behavior.  In part two, I defined the two major approaches to security credentials: directly using database credentials and mapping WLS user credentials to database credentials.  Now it's time to get down to a couple of the security options (each of which can use database credentials or WLS credentials).

Set Client Identifier on Connection

When "Set Client Identifier" is enabled on the data source, a client property is associated with the connection.  The underlying SQL user remains unchanged for the life of the connection but the client value can change.  This information can be used for accounting, auditing, or debugging.  The client property is based on either the WebLogic user mapped to a database user using the credential map or is the database user parameter directly from the getConnection() method, based on the “use database credentials” setting described earlier.

To enable this feature, select “Set Client ID On Connection” in the Console.  See "Enable Set Client ID On Connection for a JDBC data source" http://docs.oracle.com/cd/E24329_01/apirefs.1211/e24401/taskhelp/jdbc/jdbc_datasources/EnableCredentialMapping.html in Oracle WebLogic Server Administration Console Help.

The Set Client Identifier feature is only available for use with the Oracle thin driver and the IBM DB2 driver, based on the following interfaces.

For pre-Oracle 12c, oracle.jdbc.driver.OracleConnection.setClientIdentifier(client) is used.  See http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#i1009003 for more information about how to use this for auditing and debugging.   You can get the value using getClientIdentifier()  from the driver.  To get back the value from the database as part of a SQL query, use a statement like the following.
“select sys_context('USERENV','CLIENT_IDENTIFIER') from DUAL”.

Starting in Oracle 12c, java.sql.Connection.setClientInfo(“OCSID.CLIENTID", client) is used.  This is a JDBC standard API, although the property values are proprietary.  A problem with setClientIdentifier usage is that there are pieces of the Oracle technology stack that set and depend on this value.  If application code also sets this value, it can cause problems. This has been addressed with setClientInfo by making use of this method a privileged operation. A well-managed container can restrict the Java security policy grants to specific namespaces and code bases, and protect the container from out-of-control user code. When running with the Java security manager, permission must be granted in the Java security policy file for
permission "oracle.jdbc.OracleSQLPermission" "clientInfo.OCSID.CLIENTID";
Using the name “OCSID.CLIENTID" allows for upward compatible use of

“select sys_context('USERENV','CLIENT_IDENTIFIER') from DUAL”

or use the JDBC standard API java.sql.getClientInfo(“OCSID.CLIENTID") to retrieve the value.

This value in the Oracle USERENV context can be used to drive the Oracle Virtual Private Database (VPD) feature to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.  See Using Oracle Virtual Private Database to Control Data Access http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm for more information about VPD.  Using this data source feature means that no programming is needed on the WLS side to set this context; it is set and cleared by the WLS data source code.

For the IBM DB2 driver, com.ibm.db2.jcc.DB2Connection.setDB2ClientUser(client) is used for older releases (prior to version 9.5).  This specifies the current client user name for the connection. Note that the current client user name can change during a connection (unlike the user).  This value is also available in the CURRENT CLIENT_USERID special register.  You can select it using a statement like

“select CURRENT CLIENT_USERID from SYSIBM.SYSTABLES”.

When running the IBM DB2 driver with JDBC 4.0 (starting with version 9.5), java.sql.Connection.setClientInfo(“ClientUser”, client) is used.  You can retrieve the value using java.sql.Connection.getClientInfo(“ClientUser”) instead of the DB2 proprietary API (even if set setDB2ClientUser()). 

Oracle Proxy Session

Oracle proxy authentication allows one JDBC connection to act as a proxy for multiple (serial) light-weight user connections to an Oracle database with the thin driver.  You can configure a WebLogic data source to allow a client to connect to a database through an application server as a proxy user. The client authenticates with the application server and the application server authenticates with the Oracle database. This allows the client's user name to be maintained on the connection with the database.

Use the following steps to configure proxy authentication on a connection to an Oracle database.
1. If you have not yet done so, create the necessary database users.
2. On the Oracle database, provide CONNECT THROUGH privileges. For example:
SQL> ALTER USER connectionuser GRANT CONNECT THROUGH dbuser;
where “connectionuser” is the name of the application user to be authenticated and “dbuser” is an Oracle database user.
3. Create a generic or GridLink data source and set the user to the value of dbuser.
4a. To use WLS credentials, create an entry in the credential map that maps the value of wlsuser to the value of dbuser, as described earlier.  
4b. To use database credentials, enable “Use Database Credentials”, as described earlier.
5. Enable Oracle Proxy Authentication, see "Configure Oracle parameters" in Oracle WebLogic Server Administration Console Help.
6. Log on to a WebLogic Server instance using the value of wlsuser or dbuser.
6. Get a connection using getConnection(username, password).  The credentials are based on either the WebLogic user that is mapped to a database user or the database user directly, based on the “use database credentials” setting. 

You can see the current user and proxy user by executing:

“select user, sys_context('USERENV','PROXY_USER') from DUAL".

Note: getConnection fails if “Use Database Credentials” is not enabled and the value of the user/password is not valid for a WebLogic Server user.  Conversely, it fails if “Use Database Credentials” is enabled and the value of the user/password is not valid for a database user.

A proxy session is opened on the connection based on the user each time a connection request is made on the pool. The proxy session is closed when the connection is returned to the pool.  Opening or closing a proxy session has the following impact on JDBC objects.
- Closes any existing statements (including result sets) from the original connection.
- Clears the WebLogic Server statement cache.
- Clears the client identifier, if set.
-The WebLogic Server test statement for a connection is recreated for every proxy session.

These behaviors may impact applications that share a connection across instances and expect some state to be associated with the connection.

Oracle proxy session is also implicitly enabled when use-database-credentials is enabled and getConnection(user, password) is called,starting in WLS Release 10.3.6.  Remember that this only works when using the Oracle thin driver.

To summarize, the definition of oracle-proxy-session is as follows.
- If proxy authentication is enabled and identity based pooling is also enabled, it is an error.
- If a user is specified on getConnection() and identity-based-connection-pooling-enabled is false, then oracle-proxy-session is treated as true implicitly (it can also be explicitly true).
- If a user is specified on getConnection() and identity-based-connection-pooling-enabled is true, then oracle-proxy-session is treated as false.


Tuesday Oct 09, 2012

Data Source Security Part 2

In Part 1, I introduced the default security behavior and listed the various options available to change that behavior.  One of the key topics to understand is the difference between directly using database user and password values versus mapping from WLS user and password to the associated database values.   The direct use of database credentials is relatively new to WLS, based on customer feedback.  Some of the trade-offs are covered in this article.

Credential Mapping vs. Database Credentials

Each WLS data source has a credential map that is a mechanism used to map a key, in this case a WLS user, to security credentials (user and password).  By default, when a user and password are specified when getting a connection, they are treated as credentials for a WLS user, validated, and are converted to a database user and password using a credential map associated with the data source.  If a matching entry is not found in the credential map for the data source, then the user and password associated with the data source definition are used.  Because of this defaulting mechanism, you should be careful what permissions are granted to the default user.  Alternatively, you can define an invalid default user to ensure that no one can accidentally get through (in this case, you would need to set the initial capacity for the pool to zero so that the pool is populated only by valid users).

To create an entry in the credential map:

1) First create a WLS user.  In the administration console, go to Security realms, select your realm (e.g., myrealm), select Users, and select New. 

2) Second, create the mapping.  In the administration console, go to Services, select Data sources, select your data source name, select Security, select Credentials, and select New.  See http://docs.oracle.com/cd/E24329_01/apirefs.1211/e24401/taskhelp/jdbc/jdbc_datasources/ConfigureCredentialMappingForADataSource.html for more information.

The advantages of using the credential mapping are that:

1) You don’t hard-code the database user/password into a program or need to prompt for it in addition to the WLS user/password and

2) It provides a layer of abstraction between WLS security and database settings such that many WLS identities can be mapped to a smaller set of DB identities, thereby only requiring middle-tier configuration updates when WLS users are added/removed.

You can cut down the number of users that have access to a data source to reduce the user maintenance overhead.  For example, suppose that a servlet has the one pre-defined, special WLS user/password for data source access, hard-wired in its code in a getConnection(user, password) call.  Every WebLogic user can reap the specific DBMS access coded into the servlet, but none has to have general access to the data source.  For instance, there may be a ‘Sales’ DBMS which needs to be protected from unauthorized eyes, but it contains some day-to-day data that everyone needs. The Sales data source is configured with restricted access and a servlet is built that hard-wires the specific data source access credentials in its connection request.  It uses that connection to deliver only the generally needed day-to-day information to any caller. The servlet cannot reveal any other data, and no WebLogic user can get any other access to the data source.  This is the approach that many large applications take and is the reasoning behind the default mapping behavior in WLS.

The disadvantages of using the credential map are that:

1) It is difficult to manage (create, update, delete) with a large number of users; it is possible to use WLST scripts or a custom JMX client utility to manage credential map entries.

2) You can’t share a credential map between data sources so they must be duplicated.

Some applications prefer not to use the credential map.  Instead, the credentials passed to getConnection(user, password) should be treated as database credentials and used to authenticate with the database for the connection, avoiding going through the credential map.  This is enabled by setting the “use-database-credentials” to true.  See http://docs.oracle.com/cd/E24329_01/apirefs.1211/e24401/taskhelp/jdbc/jdbc_datasources/ConfigureOracleParameters.html "Configure Oracle parameters" in Oracle WebLogic Server Administration Console Help.

Use Database Credentials is not currently supported for Multi Data Source configurations.  When enabled, it turns off credential mapping on Generic and Active GridLink data sources for the following attributes:

1. identity-based-connection-pooling-enabled (this interaction is available by patch in 10.3.6.0).

2. oracle-proxy-session (this interaction is first available in 10.3.6.0).

3. set client identifier (this interaction is available by patch in 10.3.6.0).  Note that in the data source schema, the set client identifier feature is poorly named “credential-mapping-enabled”.  The documentation and the console refer to it as Set Client Identifier.

To review the behavior of credential mapping and using database credentials:

- If using the credential map, there needs to be a mapping for each WLS user to database user for those users that will have access to the database; otherwise the default user for the data source will be used.  If you always specify a user/password when getting a connection, you only need credential map entries for those specific users.

- If using database credentials without specifying a user/password, the default user and password in the data source descriptor are always used.  If you specify a user/password when getting a connection, that user will be used for the credentials.  WLS users are not involved at all in the data source connection process.

Monday Oct 08, 2012

Data Source Security Part 1

I’ve written a couple of articles on how to store data source security credentials using the Oracle wallet.  I plan to write a few articles on the various types of security available to WebLogic Server (WLS) data sources.  There are more options than you might think!

There have been several enhancements in this area in WLS 10.3.6.  There are a couple of more enhancements planned for release WLS 12.1.2 that I will include here for completeness.  This isn’t intended as a teaser.  If you call your Oracle support person, you can get them now as minor patches to WLS 10.3.6.  

The current security documentation is scattered in a few places, has a few incorrect statements, and is missing a few topics.  It also seems that the knowledge of how to apply some of these features isn’t written down.  The goal of these articles is to talk about WLS data source security in a unified way and to introduce some approaches to using the available features. 

Introduction to WebLogic Data Source Security Options

By default, you define a single database user and password for a data source.  You can store it in the data source descriptor or make use of the Oracle wallet.  This is a very simple and efficient approach to security.  All of the connections in the connection pool are owned by this user and there is no special processing when a connection is given out.  That is, it’s a homogeneous connection pool and any request can get any connection from a security perspective (there are other aspects like affinity).  Regardless of the end user of the application, all connections in the pool use the same security credentials to access the DBMS.   No additional information is needed when you get a connection because it’s all available from the data source descriptor (or wallet).

java.sql.Connection conn =  mydatasource.getConnection();

Note: You can enter the password as a name-value pair in the Properties field (this not permitted for production environments) or you can enter it in the Password field of the data source descriptor. The value in the Password field overrides any password value defined in the Properties passed to the JDBC Driver when creating physical database connections. It is recommended that you use the Password attribute in place of the password property in the properties string because the Password value is encrypted in the configuration file (stored as the password-encrypted attribute in the jdbc-driver-params tag in the module file) and is hidden in the administration console.  The Properties and Password fields are located on the administration console Data Source creation wizard or Data Source Configuration tab.

The JDBC API can also be used to programmatically specify a database user name and password as in the following. 

java.sql.Connection conn = mydatasource.getConnection(“user”, “password”);

According to the JDBC specification, it’s supposed to take a database user and associated password but different vendors implement this differently.  WLS, by default, treats this as an application server user and password.  The pair is authenticated to see if it’s a valid user and that user is used for WLS security permission checks.  By default, the user is then mapped to a database user and password using the data source credential mapper, so this API sort of follows the specification but database credentials are one-step removed from the application code.  More details and the rationale are described later.

While the default approach is simple, it does mean that only one database user is doing all of the work.  You can’t figure out who actually did the update and you can’t restrict SQL operations by who is running the operation, at least at the database level.   Any type of per-user logic will need to be in the application code instead of having the database do it. 

There are various WLS data source features that can be configured to provide some per-user information about the operations to the database.

WebLogic Data Source Security Options

This table describes the features available for WebLogic data sources to configure database security credentials and a brief description.  It also captures information about the compatibility of these features with one another.

Feature Description Can be used with Can’t be used with

User authentication (default)

Default getConnection(user, password) behavior – validate the input and use the user/password in the descriptor. Set client identifier Identity pooling, Use database credentials
Use database credentials Instead of using the credential mapper, use the supplied user and password directly. Set client identifier, Proxy session, Identity pooling User authentication, Multi Data Source
Set Client Identifier Set a client identifier property associated with the connection (Oracle and DB2 only). Everything
Proxy Session Set a light-weight proxy user associated with the connection (Oracle-only). Set client identifier, Use database credentials Identity pooling
Identity pooling Heterogeneous pool of connections owned by specified users. Set client identifier, Use database credentials Proxy session, User authentication, Labeling, Multi-datasource, Active GridLink

Note that all of these features are available with both XA and non-XA drivers.

Currently, the Proxy Session and Use Database Credentials options are on the Oracle tab of the Data Source Configuration tab of the administration console (even though the Use Database Credentials feature is not just for Oracle databases – oops).  The rest of the features are on the Identity tab of the Data Source Configuration tab in the administration console (plan on seeing them all in one place in the future).

The subsequent articles will describe these features in more detail.  Keep referring back to this table to see the big picture.

Wednesday Sep 19, 2012

WebLogic JDBC Use of Oracle Wallet for SSL

Introduction

Secure Sockets Layer (SSL) can be used to secure the connection between the middle tier “client”, WebLogic Server (WLS) in this case, and the Oracle database server.  Data between WLS and database can be encrypted.  The server can be authenticated so you have proof that the database can be trusted by validating a certificate from the server.  The client can be authenticated so that the database only accepts connections from clients that it trusts.

Similar to the discussion in an earlier article about using the Oracle wallet for database credentials, the Oracle wallet can also be used with SSL to store the keys and certificates.  By using it correctly, clear text passwords can be eliminated from the JDBC configuration and client/server configuration can be simplified by sharing the wallet across multiple datasources.
There is a very good Oracle Technical White Paper on using SSL with the Oracle thin driver at
http://www.oracle.com/technetwork/database/enterprise-edition/wp-oracle-jdbc-thin-ssl-130128.pdf [LINK1].  The link http://www.oracle.com/technetwork/middleware/weblogic/index-087556.html [LINK2] describes how to use WebLogic Server with Oracle JDBC Driver SSL.

The information in this article is a guide on what steps need to be taken in the variety of available options; use the links above for details.

SSL from the driver to the database server is basically turned on by specifying a protocol of “tcps” in the URL.  However, there is a fair amount of setup needed.  Also remember that there is an overhead in performance.

Creating the wallets

The common use cases are
1. “data encryption and server-only authentication”, requiring just a trust store, or
2. “data encryption and authentication of both tiers” (client and server), requiring a trust store and a key store.

It is recommended to use the auto-login wallet type so that clear text passwords are not needed in the datasource configuration to open the wallet.  The store type for an auto-login wallet is “SSO” (Single Sign On), not “JKS” or “PKCS12” as in [LINK2].  The file name is “cwallet.sso”.

Wallets are created using the orapki tool.  They need to be created based on the usage (encryption and/or authentication).  This is discussed in detail in [LINK1] in Appendix B or in the Advanced Security Administrator’s Guide of the Database documentation.

Database Server Configuration

It is necessary to update the sqlnet.ora and listener.ora files with the directory location of the wallet using WALLET_LOCATION.  These files also indicate whether or not SSL_CLIENT_AUTHENTICATION is being used (true or false).

The Oracle Listener must also be configured to use the TCPS protocol.  The recommended port is 2484.
LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcps)(HOST=servername)(PORT=2484)))

WebLogic Server Classpath

The WebLogic Server CLASSPATH must have three additional security files.
The files that need to be added to the WLS CLASSPATH are

  • $MW_HOME/modules/com.oracle.osdt_cert_1.0.0.0.jar
  • $MW_HOME/modules/com.oracle.osdt_core_1.0.0.0.jar
  • $MW_HOME/modules/com.oracle.oraclepki_1.0.0.0.jar

One way to do this is to add them to PRE_CLASSPATH environment variable for use with the standard WebLogic scripts.

Setting the Oracle Security Provider

It’s necessary to enable the Oracle PKI provider on the client side.  This can either be done statically by updating the java.security file under the JRE or dynamically by setting it in a WLS startup class using

java.security.Security.insertProviderAt(new oracle.security.pki.OraclePKIProvider (), 3);

See the full example of the startup class in [LINK2].

Datasource Configuration

When creating a WLS datasource, set the PROTOCOL in the URL to tcps as in the following.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=host)(PORT=port))(CONNECT_DATA=(SERVICE_NAME=myservice)))

For encryption and server authentication, use the datasource connection properties:
- javax.net.ssl.trustStore=location of wallet file on the client
- javax.net.ssl.trustStoreType=”SSO”

For client authentication, use the datasource connection properties:
- javax.net.ssl.keyStore=location of wallet file on the client
- javax.net.ssl.keyStoreType=”SSO”

Note that the driver connection properties for the wallet require a file name, not a directory name.

Active GridLink ONS over SSL

For completeness, there is another SSL usage for WLS datasources.  The communication with the Oracle Notification Service (ONS) for load balancing information and node up/down events can use SSL also.

Create an auto-login wallet and use the wallet on the client and server.  The following is a sample sequence to create a test wallet for use with ONS.

orapki wallet create -wallet ons -auto_login -pwd ONS_Wallet
orapki wallet add -wallet ons -dn "CN=ons_test,C=US" -keysize 1024 -self_signed -validity 9999 -pwd ONS_Wallet
orapki wallet export -wallet ons -dn "CN=ons_test,C=US" -cert ons/cert.txt -pwd ONS_Wallet

On the database server side, it’s necessary to define the walletfile directory in the file $CRS_HOME/opmn/conf/ons.config and run onsctl stop/start.

When configuring an Active GridLink datasource, the connection to the ONS must be defined.  In addition to the host and port, the wallet file directory must be specified.  By not giving a password, a SSO wallet is assumed.

Summary

To use SSL with the Oracle thin driver without any clear text passwords, use an SSO Oracle Wallet.  SSL support in the Oracle thin driver is available starting in 10g Release 2.

About

The official blog for Oracle WebLogic Server fans and followers!

Stay Connected

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
5
6
7
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today