Using Coherence*Web With the Monolithic Session Model

With the release of WLS 10.3.3 we now get the Coherence*Web Library deployed by default in our domains when we add configured them to use Oracle Enterprise Manger as described here. With that here are the steps to ensure your WAR projects use C*Web. In this demo we switch to the Monolithic model which is not the default Session model.

Note: This is a WAR based demo.

1. Create a weblogic.xml file in the WEB-INF directory of your web based project which references the C*web library

<?xml version = '1.0' encoding = 'windows-1252'?>
<weblogic-web-app
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.bea.com/ns/weblogic/weblogic-web-app http://www.bea.com/ns/weblogic/weblogic-web-app/1.0/weblogic-web-app.xsd"
xmlns="http://www.bea.com/ns/weblogic/weblogic-web-app">
 <library-ref>
  <library-name>coherence-web-spi</library-name>
  <specification-version>1.0.0.0</specification-version>
  <implementation-version>1.0.0.0</implementation-version>
  <exact-match>false</exact-match>
 </library-ref>       
</weblogic-web-app>

2. Edit the web.xml of your web based project to switch to Monolithic model.

<context-param>
  <param-name>coherence-sessioncollection-class</param-name>
  <param-value>com.tangosol.coherence.servlet.MonolithicHttpSessionCollection</param-value>
</context-param>

3. Finally ensure you place coherence.jar into your WEB-INF/lib directory

The following show what your project would look like in JDeveloper 11.1.1.3





















4. Once deployed we will see that we have swicthed to the new Session Model which is shown in the managed server log file at startup of the container or at the point when the application is started.


2010-05-13 07:11:37.680/37.754 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Loaded operational configuration from resource "zip:/home/oracle/product/11gR3/user_projects/domains/cweb_dom/servers/lemon/tmp/_WL_user/cohwebmonolithicmodel/5z88cr/war/WEB-INF/lib/coherence.jar!/tangosol-coherence.xml"
2010-05-13 07:11:37.687/37.761 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Loaded operational overrides from resource "zip:/home/oracle/product/11gR3/user_projects/domains/cweb_dom/servers/lemon/tmp/_WL_user/cohwebmonolithicmodel/5z88cr/war/WEB-INF/lib/coherence.jar!/tangosol-coherence-override-dev.xml"
2010-05-13 07:11:37.689/37.763 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Optional configuration override "/tangosol-coherence-override.xml" is not specified
2010-05-13 07:11:37.695/37.769 Oracle Coherence 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Optional configuration override "/custom-mbeans.xml" is not specified

Oracle Coherence Version 3.5.3/465
Grid Edition: Development mode
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

2010-05-13 07:11:37.853/37.927 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): Loaded cache configuration from "file:/home/oracle/product/11gR3/user_projects/domains/cweb_dom/servers/lemon/tmp/_WL_user/coherence-web-spi/bky8eh/WEB-INF/classes/session-cache-config.xml"
2010-05-13 07:11:38.607/38.681 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=n/a): UnicastUdpSocket failed to set receive buffer size to 1428 packets (2096304 bytes); actual size is 92 packets (135168 bytes). Consult your OS documentation regarding increasing the maximum socket buffer size. Proceeding with the actual value may cause sub-optimal performance.
2010-05-13 07:11:38.925/38.999 Oracle Coherence GE 3.5.3/465 (thread=Cluster, member=n/a): Service Cluster joined the cluster with senior service member n/a
2010-05-13 07:11:42.158/42.232 Oracle Coherence GE 3.5.3/465 (thread=Cluster, member=n/a): Created a new cluster "cluster:0xDDEB" with Member(Id=1, Timestamp=2010-05-13 07:11:38.628, Address=10.187.81.36:8088, MachineId=57380, Location=site:au.oracle.com,machine:wayne-p2,process:16376, Role=WeblogicServer, Edition=Grid Edition, Mode=Development, CpuCount=2, SocketCount=2) UID=0x0ABB5124000001288E5BC584E0241F98
2010-05-13 07:11:42.204/42.278 Oracle Coherence GE 3.5.3/465 (thread=Invocation:Management, member=1): Service Management joined the cluster with senior service member 1
2010-05-13 07:11:43.622/43.696 Oracle Coherence GE 3.5.3/465 (thread=DistributedCache:DistributedSessions, member=1): Service DistributedSessions joined the cluster with senior service member 1
2010-05-13 07:11:43.683/43.757 Oracle Coherence GE 3.5.3/465 (thread=Invocation:SessionOwnership, member=1): Service SessionOwnership joined the cluster with senior service member 1
2010-05-13 07:11:43.709/43.783 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=1): Configured session model "MonolithicHttpSessionCollection":
Clustered Session Cache Name=session-storage
Local Session Cache Name=local-session-storage
Local Session Attribute Cache Name=local-attribute-storage
Death Certificate Cache Name=session-death-certificates
SessionDistributionController Class Name=
AttributeScopeController Class Name=com.tangosol.coherence.servlet.AbstractHttpSessionCollection$ApplicationScopeController
Maximum Session Inactive Seconds=3600
Session ID Character Length=52
Member Session Locking Enforced=true
Application Session Locking Enforced=true
Thread Session Locking Enforced=false
Assume Session Locality for Reaping=false
Strict "Servlet Specification" Exception Handling=true
Sticky Session Ownership=true
Sticky Session Ownership Service Name=SessionOwnership
2010-05-13 07:11:43.725/43.799 Oracle Coherence GE 3.5.3/465 (thread=[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)', member=1): Registering HttpSessionManager(ServetContext=ServletContextWrapper (2.5)
SessionHelper!=null
MajorVersion=2
MinorVersion=5
Clustered=false
ServletContext (wrapped)=weblogic.servlet.internal.session.CoherenceWebServletContextWrapper@1f4d4bd
AttributeMap=null
Oracle Coherence 3.5.3/465) using object name "type=HttpSessionManager,nodeId=1,appId=cohwebmonolithicmodel!cohwebmonolithicmodel.war"



Also to start a coherence server which is storage enabled for the session management you would do it as follows. By default the WLS Coherence node is storage disabled. This is a unix script example.

export JAVA_HOME=/home/oracle/product/11gR3/jdk160_18
export COHERENCE_HOME=/home/oracle/product/coherence/353/coherence
export COH_OPTS="-server -classpath $COHERENCE_HOME/lib/coherence.jar:$COHERENCE_HOME/lib/coherence-web-spi.war"
export COH_OPTS="$COH_OPTS -Dtangosol.coherence.cacheconfig=/WEB-INF/classes/session-cache-config.xml
-Dtangosol.coherence.distributed.localstorage=true -Dtangosol.coherence.management.remote=true
-Dtangosol.coherence.session.localstorage=true"

java $COH_OPTS -Xms512m -Xmx512m com.tangosol.net.DefaultCacheServer

JDeveloper 11g - ADF Deployment using a Data Source defined in WLS itself

Typically when deploying ADF applications I will use a data source config defined at the WLS level and targeted to my managed server or cluster. I then deploy my ADF application from JDeveloper to that managed server or cluster however there are a few things to be aware of prior to deployment to ensure your using the correct Data Source. By default you won't be using the WLS defined Data Source if you not aware of a few things.

Note: For those who prefer to use a separate Data Source per application then out of the box you don't need to follow the steps below as it will do that for you by default. Also for our model ADFBC project we are assuming that it's configurtion is set to use "JDBC DataSource".

When your ready to deploy

1. Select "Applications -> Application Properties"
2. Click on "Deployment"

By default the "Auto generate and synchronize weblogic-jdbc.xml Descriptors during deployemnt" will be checked.

3. Uncheck that option we don't want to do that here as we already have a Data Source created in WLS.

4. Verify indeed that the weblogic-jdbc.xml is not part of the archive by selecting "Application -> Deploy -> .. to ear file"

Note: This show we no longer will bundle a weblogic-jdbc.xml file.

 
D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\deploy>jar -tvf ADFDemo_application1.ear
4305851 Thu May 06 09:45:32 EST 2010 ADFDemo_ViewController_webapp1.war
  221 Thu May 06 09:45:34 EST 2010 META-INF/adfm.xml
  495 Thu May 06 09:45:34 EST 2010 META-INF/application.xml
 3933 Thu May 06 09:04:04 EST 2010 META-INF/cwallet.sso
  949 Thu May 06 09:04:02 EST 2010 META-INF/jps-config.xml
 1559 Thu May 06 09:03:20 EST 2010 META-INF/weblogic-application.xml
  745 Thu May 06 09:04:02 EST 2010 adf/META-INF/adf-config.xml
 1253 Thu May 06 09:04:04 EST 2010 adf/META-INF/connections.xml
  831 Thu May 06 09:05:10 EST 2010 adf/model/common/bc4j.xcfg
  273 Thu May 06 09:45:34 EST 2010 lib/adf-loc.jar

D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\deploy>

5. Finally it's worth checking that indeed you not using a bundled data source so that you are actually defining the required properties for your data source in WLS console for the correct one.

Installing ADF runtime into FMW 11gR1 patchset 2

If you wish to use ADF with FMW 11gR1 pathset 2 (11.1.1.3) you will need to install it as follows into your stand alone FMW home.

Note: Assuming you already have Weblogic 10.3.3 installed.

1. Navigate to this web page which is where we need to download some files from.

http://www.oracle.com/technology/software/products/middleware/htdocs/fmw_11_download.html

2. Download and install "Application Development Runtime (11.1.1.2.0)" into your stand alone Weblogic 10.3.3

3. Now download and install "Application Development Runtime (11.1.1.3.0)" into your stand alone Weblogic 10.3.3.

4. Now run config.sh to create a new domain for ADF applications. I normally select "Oracle Enterprise Manager" which will then include the JRF required for ADF, as shown below.
















5. Then in EM itself apply the JRF template to the managed servers which will host ADF applications developed in JDeveloper 11.1.1.3.

- http://{server}:{port}/em

- Click the + symbol for "Weblogic Domain".
- Click on the + symbol for your domain.
- Select the managed server you wish to enable ADF applications for.
- Click on the button "Apply JRF Template".

6. Finally deploy an ADF app from JDeveloper 11.1.1.3 to verify the setup.

[09:22:08 AM] ---- Deployment started. ----
[09:22:08 AM] Target platform is (Weblogic 10.3).
[09:22:08 AM] Retrieving existing application information
[09:22:09 AM] Running dependency analysis...
[09:22:09 AM] Building...
[09:22:19 AM] Deploying 2 profiles...
[09:22:23 AM] Wrote Web Application Module to D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\ViewController\deploy\ADFDemo_ViewController_webapp1.war
[09:22:25 AM] Wrote Enterprise Application Module to D:\jdev\jdevprod\11gr3\jdeveloper\jdev\mywork\ADFDemo\deploy\ADFDemo_application1.ear
[09:22:26 AM] Deploying Application...
[09:22:29 AM] [Deployer:149191]Operation 'deploy' on application 'ADFDemo_application1' is initializing on 'apple'
[09:22:36 AM] [Deployer:149192]Operation 'deploy' on application 'ADFDemo_application1' is in progress on 'apple'
[09:22:47 AM] [Deployer:149194]Operation 'deploy' on application 'ADFDemo_application1' has succeeded on 'apple'
[09:22:47 AM] Application Deployed Successfully.
[09:22:47 AM] The following URL context root(s) were defined and can be used as a starting point to test your application:
[09:22:47 AM] http://10.187.81.36:7003/adfdemo
[09:22:47 AM] Elapsed time for deployment: 39 seconds
[09:22:47 AM] ---- Deployment finished. ----

WITH Clause with Oracle JDBC

Went to a 2 day tuning course with Dan Hotka and he introduced me to the WITH SQL clause.

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table

So I gave this a go and can see this helping out with LONG complicated queries , no need to add the same subquery over and over again, just use WITH AS. In short I tested this with Oracle JDBC , I knew it would work but just wanted to be sure.

SQL

  
SCOTT@linux11gr2> l
  1   with dept_a as (select /*+ materialize */ deptno from dept)
  2      select distinct e.deptno
  3      from dept_a d, emp e
  4      where e.deptno = d.deptno
  5      union
  6*     select deptno from dept_a d2

JDBC Code

  
  public void run() throws SQLException
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    
    String SQL_WITH = 
      " with dept_a as (select /*+ materialize */ deptno from dept) " + 
      "    select distinct e.deptno " + 
      "    from dept_a d, emp e " + 
      "    where e.deptno = d.deptno " + 
      "    union " + 
      "    select d2.deptno from dept_a d2";
    
    try
    {
      conn = getConnection();
      stmt = conn.createStatement();
      rset = stmt.executeQuery(SQL_WITH);
      while (rset.next())
      {
        System.out.println(rset.getString(1));
      }
    }
    finally
    {
      if (rset != null)
        rset.close(); 

      if (stmt != null)
        stmt.close(); 
      
      if (conn != null)
        conn.close();   
      
      if (ods != null)
        ods.close();
    }
  }

Coherence: Adding my Own POF Serializer for java.sql.Date

The standard serializers that come with POF unfortunately don't cover all standard Java classes. So in this case I have a domain model based on an oracle RDBMS table which was using a DATE column. in Oracle JDBC then we would call getDate() from the ResultSet Object to return a java.sql.Date(). Thanks to some help internally I was able to add my own POF Serializer as shown below.

1. Create a POF Serializer class as follows.

package pas.au.coherence.query.server;

import com.tangosol.io.pof.PofReader;
import com.tangosol.io.pof.PofSerializer;
import com.tangosol.io.pof.PofWriter;

import java.io.IOException;

public class SQLDateSerializer implements PofSerializer
{
   public void serialize(PofWriter pofWriter, Object o) throws IOException
   {
     java.sql.Date d = (java.sql.Date)o;
     pofWriter.writeLong(1, d.getTime());
     pofWriter.writeRemainder(null);
   }

   public Object deserialize(PofReader pofReader) throws IOException
   {
     java.sql.Date result = new java.sql.Date(pofReader.readLong(1));
     pofReader.readRemainder();
     return result;
   }

}

2. Update my POF config file to add this serializer.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE pof-config PUBLIC "pof-config" "pof-config.dtd" >
<pof-config>
<user-type-list>
  <include>coherence-pof-config.xml</include>
  <user-type>
    <type-id>1000</type-id>
    <class-name>pas.au.coherence.query.server.AllDBObject</class-name>
  </user-type>
  <user-type>
    <type-id>1001</type-id>
    <class-name>java.sql.Date</class-name>
    <serializer>
      <class-name>pas.au.coherence.query.server.SQLDateSerializer</class-name>
      <init-params/>
    </serializer>
  </user-type>
</user-type-list>
</pof-config>

3. My domain object would then use read/write object to trigger the use of this serializer

 
package pas.au.coherence.query.server;


import com.tangosol.io.pof.PofReader;
import com.tangosol.io.pof.PofWriter;
import com.tangosol.io.pof.PortableObject;

import java.io.IOException;

import java.math.BigDecimal;

import java.sql.Date;

public class AllDBObject implements PortableObject
{
 private String owner;
 private String objectName;
 private String subObjectName;
 private BigDecimal objectId;
 private BigDecimal dataObjectId;
 private String objectType;
 private Date created;
 private Date lastDDLTime;
 private String timestamp;
 private String status;
 private String temporary;
 private String generated;
 private String secondary;
 private BigDecimal namespace;
 private String editionName;
  
 public AllDBObject()
 {
 }

 public AllDBObject(String owner, String objectName, String subObjectName,
                    BigDecimal objectId, BigDecimal dataObjectId,
                    String objectType, Date created, Date lastDDLTime,
                    String timestamp, String status, String temporary,
                    String generated, String secondary,
                    BigDecimal namespace, String editionName)
 {
   super();
   this.owner = owner;
   this.objectName = objectName;
   this.subObjectName = subObjectName;
   this.objectId = objectId;
   this.dataObjectId = dataObjectId;
   this.objectType = objectType;
   this.created = created;
   this.lastDDLTime = lastDDLTime;
   this.timestamp = timestamp;
   this.status = status;
   this.temporary = temporary;
   this.generated = generated;
   this.secondary = secondary;
   this.namespace = namespace;
   this.editionName = editionName;
 }

 public void setOwner(String owner)
 {
   this.owner = owner;
 }

 public String getOwner()
 {
   return owner;
 }

 public void setObjectName(String objectName)
 {
   this.objectName = objectName;
 }

 public String getObjectName()
 {
   return objectName;
 }

 public void setSubObjectName(String subObjectName)
 {
   this.subObjectName = subObjectName;
 }

 public String getSubObjectName()
 {
   return subObjectName;
 }

 public void setObjectId(BigDecimal objectId)
 {
   this.objectId = objectId;
 }

 public BigDecimal getObjectId()
 {
   return objectId;
 }

 public void setDataObjectId(BigDecimal dataObjectId)
 {
   this.dataObjectId = dataObjectId;
 }

 public BigDecimal getDataObjectId()
 {
   return dataObjectId;
 }

 public void setObjectType(String objectType)
 {
   this.objectType = objectType;
 }

 public String getObjectType()
 {
   return objectType;
 }

 public void setCreated(Date created)
 {
   this.created = created;
 }

 public Date getCreated()
 {
   return created;
 }

 public void setLastDDLTime(Date lastDDLTime)
 {
   this.lastDDLTime = lastDDLTime;
 }

 public Date getLastDDLTime()
 {
   return lastDDLTime;
 }

 public void setTimestamp(String timestamp)
 {
   this.timestamp = timestamp;
 }

 public String getTimestamp()
 {
   return timestamp;
 }

 public void setStatus(String status)
 {
   this.status = status;
 }

 public String getStatus()
 {
   return status;
 }

 public void setTemporary(String temporary)
 {
   this.temporary = temporary;
 }

 public String getTemporary()
 {
   return temporary;
 }

 public void setGenerated(String generated)
 {
   this.generated = generated;
 }

 public String getGenerated()
 {
   return generated;
 }

 public void setSecondary(String secondary)
 {
   this.secondary = secondary;
 }

 public String getSecondary()
 {
   return secondary;
 }

 public void setNamespace(BigDecimal namespace)
 {
   this.namespace = namespace;
 }

 public BigDecimal getNamespace()
 {
   return namespace;
 }

 public void setEditionName(String editionName)
 {
   this.editionName = editionName;
 }

 public String getEditionName()
 {
   return editionName;
 }

 public void readExternal(PofReader in) throws IOException
 {
   this.owner = in.readString(0);
   this.objectName = in.readString(1);
   this.subObjectName = in.readString(2);
   this.objectId = in.readBigDecimal(3);
   this.dataObjectId = in.readBigDecimal(4);
   this.objectType = in.readString(5);
   this.created = (Date) in.readObject(6);
   this.lastDDLTime = (Date) in.readObject(7);
   this.timestamp = in.readString(8);
   this.status = in.readString(9);
   this.temporary = in.readString(10);
   this.generated = in.readString(11);
   this.secondary = in.readString(12);
   this.namespace = in.readBigDecimal(13);
   this.editionName = in.readString(14);
 }

 public void writeExternal(PofWriter out) throws IOException
 {
   out.writeString(0, this.owner);
   out.writeString(1, this.objectName);
   out.writeString(2, this.subObjectName);
   out.writeBigDecimal(3, this.objectId);
   out.writeBigDecimal(4, this.dataObjectId);
   out.writeString(5, this.objectType);
   out.writeObject(6, this.created);
   out.writeObject(7, this.lastDDLTime);
   out.writeString(8, this.timestamp);
   out.writeString(9, this.status);
   out.writeString(10, this.temporary);
   out.writeString(11, this.generated);
   out.writeString(12, this.secondary);
   out.writeBigDecimal(13, this.namespace);
   out.writeString(14, this.editionName);
 }

 @Override
 public String toString()
 {
   return "AllDbObject - owner: " + this.owner +
          " ,objectName: " + this.objectName +
          " ,subObjectName: " + this.subObjectName +
          " ,objectId: " + this.objectId +
          " ,dataObjectId: " + this.dataObjectId +
          " ,objectType: " + this.objectType +
          " ,created: " + this.created +
          " ,lastDDLTime: " + this.lastDDLTime +
          " ,timestamp: " + this.timestamp +
          " ,status: " + this.status +
          " ,temporrary: " + this.temporary +
          " ,generated: " + this.generated +
          " ,secondary: " + this.secondary +
          " ,namespace: " + this.namespace +
          " ,editionName: " + this.editionName;
 }

 @Override
 public boolean equals(Object object)
 {
   if (this == object)
   {
     return true;
   }
   if (!(object instanceof AllDBObject))
   {
     return false;
   }
   final AllDBObject other = (AllDBObject) object;
   if (!(owner == null ? other.owner == null : owner.equals(other.owner)))
   {
     return false;
   }
   if (!(objectName == null ? other.objectName == null : objectName.equals(other.objectName)))
   {
     return false;
   }
   if (!(subObjectName == null ? other.subObjectName == null : subObjectName.equals(other.subObjectName)))
   {
     return false;
   }
   if (!(objectId == null ? other.objectId == null : objectId.equals(other.objectId)))
   {
     return false;
   }
   if (!(dataObjectId == null ? other.dataObjectId == null : dataObjectId.equals(other.dataObjectId)))
   {
     return false;
   }
   if (!(objectType == null ? other.objectType == null : objectType.equals(other.objectType)))
   {
     return false;
   }
   if (!(created == null ? other.created == null : created.equals(other.created)))
   {
     return false;
   }
   if (!(lastDDLTime == null ? other.lastDDLTime == null : lastDDLTime.equals(other.lastDDLTime)))
   {
     return false;
   }
   if (!(timestamp == null ? other.timestamp == null : timestamp.equals(other.timestamp)))
   {
     return false;
   }
   if (!(status == null ? other.status == null : status.equals(other.status)))
   {
     return false;
   }
   if (!(temporary == null ? other.temporary == null : temporary.equals(other.temporary)))
   {
     return false;
   }
   if (!(generated == null ? other.generated == null : generated.equals(other.generated)))
   {
     return false;
   }
   if (!(secondary == null ? other.secondary == null : secondary.equals(other.secondary)))
   {
     return false;
   }
   if (!(namespace == null ? other.namespace == null : namespace.equals(other.namespace)))
   {
     return false;
   }
   if (!(editionName == null ? other.editionName == null : editionName.equals(other.editionName)))
   {
     return false;
   }
   return true;
 }

 @Override
 public int hashCode()
 {
   final int PRIME = 37;
   int result = 1;
   result = PRIME * result + ((owner == null) ? 0 : owner.hashCode());
   result = PRIME * result + ((objectName == null) ? 0 : objectName.hashCode());
   result = PRIME * result + ((subObjectName == null) ? 0 : subObjectName.hashCode());
   result = PRIME * result + ((objectId == null) ? 0 : objectId.hashCode());
   result = PRIME * result + ((dataObjectId == null) ? 0 : dataObjectId.hashCode());
   result = PRIME * result + ((objectType == null) ? 0 : objectType.hashCode());
   result = PRIME * result + ((created == null) ? 0 : created.hashCode());
   result = PRIME * result + ((lastDDLTime == null) ? 0 : lastDDLTime.hashCode());
   result = PRIME * result + ((timestamp == null) ? 0 : timestamp.hashCode());
   result = PRIME * result + ((status == null) ? 0 : status.hashCode());
   result = PRIME * result + ((temporary == null) ? 0 : temporary.hashCode());
   result = PRIME * result + ((generated == null) ? 0 : generated.hashCode());
   result = PRIME * result + ((secondary == null) ? 0 : secondary.hashCode());
   result = PRIME * result + ((namespace == null) ? 0 : namespace.hashCode());
   result = PRIME * result + ((editionName == null) ? 0 : editionName.hashCode());
   return result;
 }
}

Oracle Coherence / POF (Portable Object Format) with JDeveloper 11g

Now I am constantly creating coherence projects in JDeveloper 11g as my IDE it has a useful source code wizard. The wizard is "Source -> Generate equals() and hashCode() methods". Given I am more often then not using POF (Portable Object Format) then this option saves me a lot of time. The "Source -> Generate Constructor from fields" also comes in very handy for Coherence projects as well.

From memory JDeveloper 10g didn't have these source code wizard's.

POF is described in the link below. Using POF has many advantages ranging from performance benefits to language independence.

http://coherence.oracle.com/display/COH35UG/The+Portable+Object+Format

Diagnosability Management with the 11g JDBC Driver

The JDBC diagnosability management feature introduces an MBean, oracle.jdbc.driver.OracleDiagnosabilityMBean. This MBean provides means to enable and disable JDBC logging. You problematically enable it as shown bythe method "enableOracleLogging()"from the code below.

1. First ensure you are using the debug version of the Orace JDBC driver. Given I was using JDK 1.6 I used ojdbc6_g.jar

2. Ensure you register the JDBC driver prior to enabling logging

DriverManager.registerDriver(new OracleDriver());

3. Full Code demo as follows:

package pas.au.jdbc.logging;

import java.lang.management.ManagementFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.util.logging.FileHandler;
import java.util.logging.Handler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;

import javax.management.Attribute;
import javax.management.MBeanServer;
import javax.management.ObjectName;

import oracle.jdbc.OracleDriver;


public class Test
{
  public Test()
  {
  }

  public void run () throws SQLException
  {
    DriverManager.registerDriver(new OracleDriver());
    enableOracleLogging();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    
    try
    {
      conn = getConnection();
      System.out.println("Connection retrieved..");

      stmt = conn.createStatement();
      rset = stmt.executeQuery("select empno from emp");
      while (rset.next())
      {
        System.out.println(rset.getInt(1));
      }
    }
    catch (SQLException sqle)
    {
      sqle.printStackTrace();
    }
    finally
    {
      if (rset != null)
      {
        rset.close();
      }
      
      if (stmt != null)
      {
        stmt.close();
      }
      
      if (conn != null)
      {
        conn.close();
      }
    }
    
    
    System.out.println("Connection closed");
  }
  
  public static Connection getConnection() throws SQLException
  {
    String username = "scott";
    String password = "tiger";
    String thinConn = 
       "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g";
    
    Connection conn =
      DriverManager.getConnection(thinConn, username, password);
    conn.setAutoCommit(false);
    return conn;
  }

  private static void enableOracleLogging() 
  {
    try 
    {
      Handler fh = new FileHandler("./oracle_jdbc_log.log");
      fh.setLevel(Level.ALL);
      fh.setFormatter(new SimpleFormatter());
      Logger.getLogger("").addHandler(fh);
      Logger.getLogger("").setLevel(Level.ALL);
  
      MBeanServer mbs = ManagementFactory.getPlatformMBeanServer();
      String loader = Thread.currentThread().getContextClassLoader().toString().
          replaceAll("[,=:\"]+", "");
      ObjectName pattern = 
          new ObjectName("com.oracle.jdbc:type=diagnosability,name=" + 
                         loader );      
      ObjectName diag = ((ObjectName[])(mbs.queryNames(pattern, null).
          toArray(new ObjectName[0])))[0];

      mbs.setAttribute(diag, new Attribute("LoggingEnabled", true));
      System.out.println("LoggingEnabled = " + 
          mbs.getAttribute(diag, "LoggingEnabled"));
    }
    catch(Exception e) 
    {
      e.printStackTrace();  
    }
  }
   
  public static void main(String[] args) throws Exception
  {
    Test test = new Test();
    test.run();
  }
}

4. Sample Output as follows, most of it ommitted due to the size of the log file given we asked for everything to be logged.

Apr 2, 2010 10:09:30 AM DefaultMBeanServerInterceptor setAttribute
FINER: Object= com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e, attribute=LoggingEnabled
Apr 2, 2010 10:09:30 AM Repository retrieve
FINER: name=com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e
Apr 2, 2010 10:09:30 AM DefaultMBeanServerInterceptor getAttribute
FINER: Attribute= LoggingEnabled, obj= com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e
Apr 2, 2010 10:09:30 AM Repository retrieve
FINER: name=com.oracle.jdbc:type=diagnosability,name=sun.misc.Launcher$AppClassLoader@47858e
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver connect
TRACE_1: Public Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g", {user=scott, password=tiger}
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver oracleDriverExtensionTypeFromURL
TRACE_16: Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g"
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver oracleDriverExtensionTypeFromURL
TRACE_16: return: 0
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.OracleDriver oracleDriverExtensionTypeFromURL
TRACE_16: Exit
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection
TRACE_16: Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g", {user=scott, password=tiger}, oracle.jdbc.driver.T4CDriverExtension@cb6009
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection readConnectionProperties
TRACE_16: Enter: "jdbc:oracle:thin:@beast.au.oracle.com:1521/linux11g", {user=scott, password=tiger}
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: Enter: "oracle.jdbc.RetainV9LongBindBehavior", null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection$1 run
TRACE_16: return: null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: return: null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: Exit
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection getSystemProperty
TRACE_16: Enter: "oracle.jdbc.database", null
Apr 2, 2010 10:09:30 AM oracle.jdbc.driver.PhysicalConnection$1 run

Discover/Monitor a Coherence Cluster from Oracle Enterprise Manager

I have wanted to do this for a while since it was added to OEM. This demo is using Coherence 3.5 with OEM 10.2.0.5. The steps are documented here.

http://download.oracle.com/docs/cd/B16240_01/doc/doc.102/e14631/emcgs.htm
Oracle® Enterprise Manager Getting Started Guide for Oracle Coherence
10g Release 5 (10.2.0.5)
Part Number E14631-02

Note: This demo is done within ANT , you can easily convert the ANT tasks to a J2SE client without to much issue.

1. Initially we have to start a Management Coherence Node using the Bulk Management MBeans shipped with OEM. Copy the files below to where your coherence node will run from.

$ORACLE_HOME/sysman/jlib/coherenceEMIntg.jar
$ORACLE_HOME/modules/bulkoperationsmbean_11.1.1.jar

2. In your ANT build.xml file define the following classpath which will include coherence.jar and the 2 jar files from OEM obtained at step #1

<path id="j2ee.classpath">
<pathelement path="${oracle.coherence.home}/lib/coherence.jar"/>
<pathelement path="./lib/coherenceEMIntg.jar"/>
<pathelement path="./lib/bulkoperationsmbean_11.1.1.jar"/>
</path>

3. Start a Management Coherence Node with a target as follows.

<property name="jmxclass.name" value="oracle.sysman.integration.coherence.EMIntegrationServer"/>
...
<target name="run-jmx" depends="compile" description="Run the JMX enabled cache server">
<echo message="Starting JMX enabled cache server with jvm args : ${jvmargs}"/>
<java classname="${jmxclass.name}" fork="true">
 <sysproperty key="tangosol.coherence.management" value="all"/>
 <sysproperty key="tangosol.coherence.management.remote" value="true"/>
 <sysproperty key="com.sun.management.jmxremote.port" value="10001"/>
 <sysproperty key="com.sun.management.jmxremote.authenticate" value="false"/>
 <sysproperty key="com.sun.management.jmxremote.ssl" value="false"/>
 <jvmarg line="${jvmargs}"/>
 <classpath>
   <path refid="j2ee.classpath"/>
 </classpath>
</java>
</target>

4. Start one or more default cache servers using an ANT task as follows.

<property name="class.name" value="com.tangosol.net.DefaultCacheServer"/>
...
<target name="run-default" depends="compile" description="Run the cache server">
<echo message="Starting cache server with jvm args : ${jvmargs}"/>
<java classname="${class.name}" fork="true">
 <jvmarg line="${jvmargs}"/>
 <classpath>
   <path refid="j2ee.classpath"/>
 </classpath>
</java>
</target>

5. In OEM setup a target to the Management Coherence Node using connect details as follows

Note: It's assumed the Management Coherence Node is running on host "papicell-au.au.oracle.com" and the port we used is 10001. Also all other fields are left blank.

Machine Name: papicell-au.au.oracle.com
JMX Remote Port: 10001
Service URL: service:jmx:rmi:///jndi/rmi://papicell-au.au.oracle.com:10001/jmxrmi
Communication Protocol: rmi
Service Name: jmxrmi
Bulk Operations MBean: Coherence:type=BulkOperations

Note: I deliberately have set com.sun.management.jmxremote.authenticate=false to disable authentication here.

Thats it now you can monitor the cache's from OEM and get other JMX stats available through OEM. I believe the OEM agent will communicate directly with Management Coherence Node here.

Finally the build.properties for ANT is defined as follows. These are used in the ANT targets and references above.

# oracle.coherence.home
#
oracle.coherence.home=D:/jdev/coherence/352/coherence

# jvmargs
#
# JVM args to pass into the command at runtime to set heap size etc

jvmargs=-server -showversion -Xms512m -Xmx512m

Tuning a Oracle JDBC Variable In List Query

I recently showed how to create a variable in list for a JDBC query as follows. The problem with this demo is it won't be a very effecient query as the table grows as it will most likely do a full table scan.

Variable in list with Oracle JDBC and RDBMS
http://theblasfrompas.blogspot.com/2008/02/variable-in-list-with-oracle-jdbc-and.html

To show this lets trace the SQL to verify the path of a query against a table as follows. This table has a PRIMARY KEY defined on the ID column which will be what we drive our in list query from. The query we are running is defined as follows:

select loadall_type(id, message_type, message) as "Data"
from   loadall_table
where  id in
(SELECT * FROM TABLE
  (CAST(loadall_pkg.in_number_list('4, 67, 88, 1001') as message_id_nt)));

Table Definition

create table loadall_table
(id number,
message_type varchar2(1),
message varchar2(100),
CONSTRAINT loadall_table_PK PRIMARY KEY (id))
/

So by turning autotrace on for the in list query we can see a full table scan needs to be performed to retrieve 4 rows from a table with 100,000 rows. Sure that won't take long but if the table grows to 10 million rows for example then a full table scan is what we want to avoid.

SCOTT@linux11g> set autotrace on
SCOTT@linux11g> select loadall_type(id, message_type, message) as "Data"
2  from   loadall_table
3  where  id in
4    (SELECT * FROM TABLE
5      (CAST(loadall_pkg.in_number_list('4, 67, 88, 1001') as message_id_nt)));

Data(MESSAGE_ID, MESSAGE_TYPE, MESSAGE)
------------------------------------------------------------------------------------------------------

LOADALL_TYPE(4, 'M', 'Message at 4')
LOADALL_TYPE(67, 'M', 'Message at 67')
LOADALL_TYPE(88, 'M', 'Message at 88')
LOADALL_TYPE(1001, 'M', 'Message at 1001')


Execution Plan
----------------------------------------------------------
Plan hash value: 3431305811

-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |     1 |    25 |   167   (2)| 00:00:03 |
|*  1 |  HASH JOIN RIGHT SEMI              |                |     1 |    25 |   167   (2)| 00:00:03 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| IN_NUMBER_LIST |       |       |            |          |
|   3 |   TABLE ACCESS FULL                | LOADALL_TABLE  |   100K|  2246K|   137   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ID"=VALUE(KOKBF$))


Statistics
----------------------------------------------------------
 6  recursive calls
 0  db block gets
487  consistent gets
 0  physical reads
 0  redo size
4188  bytes sent via SQL*Net to client
1148  bytes received via SQL*Net from client
12  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
 4  rows processed

So if we can assume only a small amount of rows will ever be queried using the in list then a query as follows would be a lot more efficient to return the same data.

SCOTT@linux11g> select /*+ cardinality(plist 2) */ loadall_type(id, message_type, message) as "Data"
2  from   loadall_table,
3         (SELECT * FROM TABLE
4           (CAST(loadall_pkg.in_number_list('4, 67, 88, 1001') as message_id_nt))) plist
5  where  id  = plist.column_value;

Data(MESSAGE_ID, MESSAGE_TYPE, MESSAGE)
------------------------------------------------------------------------------------------------------

LOADALL_TYPE(4, 'M', 'Message at 4')
LOADALL_TYPE(67, 'M', 'Message at 67')
LOADALL_TYPE(88, 'M', 'Message at 88')
LOADALL_TYPE(1001, 'M', 'Message at 1001')


Execution Plan
----------------------------------------------------------
Plan hash value: 113937112

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     2 |    50 |    31   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                       |                |       |       |            |          |
|   2 |   NESTED LOOPS                      |                |     2 |    50 |    31   (0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| IN_NUMBER_LIST |       |       |            |          |
|*  4 |    INDEX UNIQUE SCAN                | SYS_C0050957   |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID       | LOADALL_TABLE  |     1 |    23 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("ID"=VALUE(KOKBF$))


Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
 16  consistent gets
  0  physical reads
  0  redo size
1151  bytes sent via SQL*Net to client
428  bytes received via SQL*Net from client
  3  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  4  rows processed

Be careful using this technique if your in list query contains more then 5% of the table as the newer query will force the use of the index and hence avoid a full table scan. If you know that a very small part of the table is returned and the records are not known until runtime a query as follows would work well for large tables, where the original query would not be as efficient.

We did two things here to tune this query.

1. Used a hint for force the use of an index

/*+ cardinality(plist 2) */

2. Altered the query to no longer use IN list. New Query as follows now.

select /*+ cardinality(plist 2) */
     loadall_type(id, message_type, message) as "Data"
from
loadall_table,
(SELECT * FROM TABLE
 (CAST(loadall_pkg.in_number_list('4, 67, 88, 1001') as message_id_nt))) plist
where  id  = plist.column_value;

Universal Connection Pool (UCP) with OC4J 10.1.3.x

I setup a native OC4J 10.1.3.x Data Source using the Oracle UCP and allthough I didn't really do much more then that it did work. Personally I would stick with ICC in OC4J 10.1.3.x and the default pool implementation but if you have a pressing need to use UCP this would work.

Note: This demo is based on using stand alone OC4J so the container in this case is known as "home"

1. Ensure that the container or stand alone OC4J is using the 11.1.0.7 JDBC driver. I created a shared library as follows which automatically use the latest version. For some reason the 10.1.0.5 default driver won't work here.

Name: oracle.jdbc
Version: 11.1.0.7

2. I wanted to test my data source from asconsole so to do that I needed to edit the $ORACLE_HOME\j2ee\home\application-deployments\ascontrol\orion-application.xml file of the deployed ascontrol application, and comment out the line that removes the importing of the global.libraries.xml file, as shown below.

<imported-shared-libraries>
<!--
<remove-inherited name="global.libraries"/>
-->
<import-shared-library name="oracle.xml.security"/>
</imported-shared-libraries>

3. Create a native Data Source as follows in your data-sources file.

<native-data-source
name="jdbc/UcpNativeDS"
jndi-name="jdbc/UcpNativeDS"
description="UCP Native DataSource"
data-source-class="oracle.ucp.jdbc.PoolDataSourceImpl"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@beast.au.oracle.com:1522:linux10g">
      <property name="connectionFactoryClassName"
                      value="oracle.jdbc.pool.OracleDataSource"/>
      <property name="connectionPoolName" value="TestPool"/>
      <property name="initialPoolSize" value="1"/>
      <property name="maxPoolSize" value="20"/>
</native-data-source>

4. Copy ucp.jar , version 11.1.0.7, into $ORACLE_HOME/j2ee/home/applib of the container.

5. Stop/Start OC4J

6. Test the Data Source from asconsole as follows, which should then work for J2EE applications as well.

OC4J 10.1.3.x - Accessing a Data Source remotely without oc4jadmin user

I previously showed how one would remotely access JNDI object's which include EJB's , Data Sources etc as shown in this post below.

http://theblasfrompas.blogspot.com/2008/07/enable-remote-clients-to-access-oas.html

Typically internally I use oc4jadmin who has access to everything and of course it has no issues. However in the real world 2 things are just about certain.

1. A Data Source would most likely be created at the container level for applications to share allthough it's not uncommon for them to be created at the application level.

2. The OAS administrator would almost certainly provide a separate user for JNDI access.

With such a setup the user created won't be able to access container specific JNDI objects such as data sources as most likely the OAS administrator won't make the user given part of the "oc4j-administrators" group. In this case the same principals as the blog entry apply however you won't find a container orion-application.xml.

So assuming you have a Data Source created in the HOME container you would need to alter the file below and then stop/start the instance to allow users to access container specific JNDI objects such as a Data Source. This demo assumes your user is part of the group called "users" as per the blog entry above.

$ORACLE_HOME/j2ee/home/config/application.xml

<namespace-access>
  <read-access>
    <namespace-resource root="">
      <security-role-mapping>
              <group name="oc4j-administrators" />
              <group name="ascontrol_admin" />
              <group name="users" />
      </security-role-mapping>
    </namespace-resource>
  </read-access>
  <write-access>
      <namespace-resource root="">
        <security-role-mapping>
                <group name="oc4j-administrators" />
                <group name="ascontrol_admin" />
                <group name="users" />
        </security-role-mapping>
      </namespace-resource>
  </write-access>
</namespace-access>

Accessing a Data Source Remotely in FMW 11g (11.1.1.2.0) from JDeveloper 11g (11.1.1.2.0)

I needed to access a data source remotely on FMW 11g server from JDeveloper 11g. I found that to do this I needed to follow these steps.

Note: This was done using 11.1.1.2 which is the latest version of FMW 11g and JDeveloper 11g

1. Edit setDomainEnv script to ensure you set this property to true. By default it's false.

WLS_JDBC_REMOTE_ENABLED="-Dweblogic.jdbc.remoteEnabled=true"

2. Re-start your server to pick up the change done at step #1 above.

3. In your project add the following libraries to allow remote access and the required JDBC driver library.

• Weblogic 10.3 Remote-Client
• Oracle JDBC

4. From JDeveloper access your data source remotely with code as follows making sure you use your connection details and the correct JNDI name for the data source.

package pas.au.remote.wls11g;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.Date;
import java.util.Hashtable;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.DataSource;

public class RemoteDataSourceAccess
{
 private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
 public RemoteDataSourceAccess()
 {
 }

 public void run ()
 {
   logger.log(Level.INFO, "Started RemoteDataSourceAccess at " + new Date());
  
   InitialContext ctx = null;

   try
   {
     ctx = getInitialContext();
     DataSource ds = (DataSource) ctx.lookup("jdbc/scottDS");
     Connection conn = (Connection) ds.getConnection();
     logger.log(Level.INFO, "Got connection : " + conn);
    
     logger.log(Level.INFO, "Auto Commit = " + conn.getAutoCommit());
   }
   catch (NamingException e)
   {
     logger.log(Level.SEVERE, "Error occurred", e);
     System.exit(1);
   }
   catch (SQLException e)
   {
     logger.log(Level.SEVERE, "SQLException occurred", e);
     System.exit(1);
   }

   logger.log(Level.INFO, "Ended RemoteDataSourceAccess at " + new Date());
 }

 public static InitialContext getInitialContext() throws NamingException
 {
   String JNDI_FACTORY="weblogic.jndi.WLInitialContextFactory";
   String url = "t3://wayne-p2.au.oracle.com:7003";
   String username = "weblogic";
   String password = "welcome1";
  
   Hashtable<String,String> env = new Hashtable<String,String>();
   env.put(Context.SECURITY_PRINCIPAL, username);
   env.put(Context.SECURITY_CREDENTIALS, password);
   env.put(Context.INITIAL_CONTEXT_FACTORY, JNDI_FACTORY);
   env.put(Context.PROVIDER_URL, url);
   return new InitialContext(env);
 }
   
 public static void main(String[] args)
 {
  
   RemoteDataSourceAccess test = new RemoteDataSourceAccess();
   test.run();
 }
}

5. Output as follows.

25/02/2010 9:22:56 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Started RemoteDataSourceAccess at Thu Feb 25 09:22:56 EST 2010
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Got connection : weblogic.jdbc.rmi.SerialConnection_weblogic_jdbc_rmi_internal_ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_1032_WLStub@1
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Auto Commit = true
25/02/2010 9:23:03 AM pas.au.remote.wls11g.RemoteDataSourceAccess run
INFO: Ended RemoteDataSourceAccess at Thu Feb 25 09:23:03 EST 2010

Getting detailed logging for Universal Connection Pool (UCP) / Fast Connection Failover (FCF) Testing

While testing UCP/FCF I was having some issues with the RAC cluster setup. One useful way to determine exactly what is happening or not happening from the FAN events sent from RAC cluster to the pool was to setup logging as described below.

1. Create a properties file as follows.

# This is the sample logging properties file that configures
# loggers of some classes (FCF-related ones) to produce detailed logging
# (all levels) and just SEVERE and WARNING level messages for the rest
# of loggers. Console output via UCPFormatter (ODL-like messages).

handlers = java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level = ALL
java.util.logging.ConsoleHandler.formatter = oracle.ucp.util.logging.UCPFormatter

.level = WARNING

oracle.ucp.common.FailoverEventHandlerThreadBase.level = ALL

oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread.level = ALL
oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent.level = ALL
oracle.ucp.jdbc.oracle.ONSOracleFailoverEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfo.level = ALL
oracle.ucp.jdbc.oracle.OracleDatabaseInstanceInfoList.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventNotification.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverEventSubscriber.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverHandler.level = ALL
oracle.ucp.jdbc.oracle.OracleFailoverablePooledConnection.level = ALL
oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OraclePooledConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleXAConnectionConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.level = ALL
oracle.ucp.jdbc.oracle.OracleUniversalPooledConnection.level = ALL

2. Add the JVM command line option as follows - -Djava.util.logging.config.file=ucp_fcf_log.properties.

Then you should see some detailed output as follows. Very useful information in diagnosing UCP/FCF issues with your pool. Just showing small snippet as this is detailed output.

[java] 2010-02-24T10:44:16.734+1100 UCP FINE seq-103,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread.run event
triggered: Service name: orcl.apemrac.au.oracle.com, Instance name: orcl1, Unique name: orcl, Host name: apemrac1, Status: down,
Cardinality: 0, Reason: user, Event type: database/event/service
[java] 2010-02-24T10:44:16.734+1100 UCP FINEST seq-104,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread.run che
ck for events
[java] 2010-02-24T10:44:16.734+1100 UCP FINEST seq-105,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setEventType
eventType: database/event/service
[java] 2010-02-24T10:44:16.750+1100 UCP FINEST seq-106,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.validateEvent
Type eventType: database/event/service
[java] 2010-02-24T10:44:16.750+1100 UCP FINEST seq-107,thread-11 oracle.ucp.jdbc.oracle.ONSDatabaseFailoverEvent. event
Type: database/event/service, eventBody: VERSION=1.0 service=HASERVICE.apemrac.au.oracle.com instance=orcl1 database=orcl host=ape
mrac1 status=down reason=failure
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-108,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setServiceNam
e serviceName: HASERVICE.apemrac.au.oracle.com
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-109,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setInstanceNa
me instanceName: orcl1
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-110,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setDbUniqueNa
me dbUniqueName: orcl
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-111,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setHostName h
ostName: apemrac1
[java] 2010-02-24T10:44:16.765+1100 UCP FINEST seq-112,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setStatus sta
tus: down
[java] 2010-02-24T10:44:16.781+1100 UCP FINEST seq-113,thread-11 oracle.ucp.jdbc.oracle.OracleFailoverEventImpl.setReason rea
son: failure
[java] 2010-02-24T10:44:16.781+1100 UCP FINEST seq-114,thread-11 oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.handleFailov
erEvent failover event: Service name: HASERVICE.apemrac.au.oracle.com, Instance name: orcl1, Unique name: orcl, Host name: apemrac
1, Status: down, Cardinality: 0, Reason: failure, Event type: database/event/service
[java] 2010-02-24T10:44:16.796+1100 UCP FINEST seq-115,thread-11 oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.handleFailov
erEvent service name HASERVICE.apemrac.au.oracle.com in event does not match that in the pool: haservice.apemrac.au.oracle.com. No
FCF attempt.

Universal Connection Pool (UCP) / Fast Connection Failover (FCF)

I wanted to verify a UCP and how it works with FCF. I had previously used ICC/FCF which UCP/FCF now replaces. Development confirmed it is pretty much the same as explained below.

The basic usage model for FCF in UCP is the same as back in ICC. The user gets a Connection, performs JDBC operations on it, gets an exception from a RAC failure, reconnects and recovers if necessary. The main difference from ICC usage is the isValid() API that UCP provides, so that users do not need to check hard-coded exception error codes when deciding whether to reconnect.

So in short code as follows can now be added avoiding the need to check for error codes to determine if indeed a connection needs to reconnect.

public void run () throws SQLException
{
 Connection conn = null;

 try
 {
   counter++;
   conn = pool.getConnection();
   getInstanceDetails(conn, counter);
   pool.displayDetails();
 }
 catch (SQLException sqle)
 {
   // The recommended way to check connection usability after a
   // RAC-down event triggers UCP FCF actions.
   if (conn == null || !((ValidConnection) conn).isValid())
   {
     logger.log
      (Level.INFO,
       "** FCFTest : Connection retry necessary : " + sqle.getMessage());
 
     // Use UCP's FCF-specific statistics to verify the pool's
     // FCF actions.
     OracleJDBCConnectionPoolStatistics stats =
       (OracleJDBCConnectionPoolStatistics) pool.getStats();

     logger.log
      (Level.INFO,
       "** FCFTest : " + stats.getFCFProcessingInfo());
   }
   else
   {
     logger.log
      (Level.SEVERE,
       "** FCFTest : Exception occurred ->");
     sqle.printStackTrace();
   }
 }
 finally
 {
   try
   {
     pool.returnConnection(conn);     
   }
   catch (SQLException se)
   {
     // not much we can do here
     logger.log
      (Level.INFO,
       "** FCFTest : Exception detected when closing connection ");
     se.printStackTrace();
   }
 }
}