Coherence Extend client from Oracle 11g RDBMS

Given coherence JAR file isn't that large I have always wanted a coherence client to run from the database itself by connecting to extend proxy client. Here is how I did this and what is required on the Oracle DB side to ensure you can run java code within the database to connect to a coherence extend proxy client.

Note: This was done with Oracle 11g r2 (11.2.0.1) RDBMS. The JVM in the DB is a 1.5 JVM version. So you won't be able to use JDK 1.6 for this.

1. First we create a new schema called COHERENCE in our database. The script to create this user is as follows.

drop user coherence cascade;

create user coherence identified by coherence
default tablespace users
temporary tablespace temp;

grant dba to coherence;

execute dbms_java.grant_permission('COHERENCE','SYS:java.util.PropertyPermission','*', 'read,write');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','getClassLoader','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','createClassLoader','');

execute dbms_java.grant_permission('COHERENCE','SYS:java.net.SocketPermission','*','listen,connect,resolve');

execute dbms_java.grant_permission('COHERENCE','SYS:java.util.PropertyPermission','*','read,write');

execute dbms_java.grant_permission('COHERENCE','SYS:java.lang.RuntimePermission','setFactory','');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'localhost:8088', 'listen,resolve' );

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/tangosol-coherence-override-dev.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/tangosol-coherence-override.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.io.FilePermission', '/custom-mbeans.xml', 'read');

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'papicell-au.au.oracle.com:9099', 'connect,accept,resolve');

execute dbms_java.grant_permission('COHERENCE', 'SYS:java.security.SecurityPermission', 'getDomainCombiner', '' );

prompt
prompt COHERENCE user ready to roll
prompt
prompt all done..

As you can see I granted DBA to the user coherence only because I wanted to get this up and running quickly but in theory the DBA role should not be required. Also all those privileges are required in order to create a socket connection to the extend proxy client.

2. Load the following 3 JAR files into the database using the new user coherence. In order for the database to load/resolve coherence.jar it will require the log4j and sleepycat JAR files. I tried to avoid loading them as coherence outside of the database runs fine with just coherence.jar but inside the database it needed to compile/resolve the class files which meant it needed those 2 JAR files to do that. To me it's not a big issue as there quite small JAR files as well.

loadjava -u coherence/coherence -r -v -f -s -grant public log4j-1.2.15.jar

loadjava -u coherence/coherence -r -v -f -s -grant public je.jar

loadjava -u coherence/coherence -r -v -f -s -grant public coherence.jar

Note: There will be some errors resolving some of the classes but the main ones needed will resolve/compile fine so just ignore the errors. In fact a query as follow should show most of classes as VALID.

COHERENCE@linux11gr2> @check

STATUS    COUNT(*)
------- ----------
INVALID         11
VALID         3200


Java Name                                                    STATUS
------------------------------------------------------------ ----------
com/tangosol/util/internal/SunMiscCounter                    INVALID
com/tangosol/util/internal/SunMiscCounter$1                  INVALID
com/sleepycat/persist/model/ClassEnhancerTask                INVALID
org/apache/log4j/jmx/Agent                                   INVALID
com/tangosol/run/jca/CacheAdapter                            INVALID
com/tangosol/run/jca/CacheAdapter$CacheConnectionSpec        INVALID
com/tangosol/engarde/websphere/SecurityHelper                INVALID
com/tangosol/engarde/StubManagerHookup                       INVALID
com/tangosol/engarde/StubManager                             INVALID
com/tangosol/engarde/EjbRouter                               INVALID
com/tangosol/engarde/DebugStubManagerHookup                  INVALID

11 rows selected.

COHERENCE@linux11gr2> l
  1  select dbms_java.longname(object_name) "Java Name", status
  2  from user_objects
  3  where object_type like '%JAVA%'
  4* and status = 'INVALID'

3.Now we have a proxy client running on "papicell-au.au.oracle.com:9099" which is storage disabled but we have other cluster nodes which are storage enabled. In this setup the extend proxy client  is the node the database client will connect to and hence why we set it up with this privilege.

execute dbms_java.grant_permission( 'COHERENCE', 'SYS:java.net.SocketPermission', 'papicell-au.au.oracle.com:9099', 'connect,accept,resolve');

4. Now we are ready to actually create a Java Stored procedure which will connect to the extend proxy client and put/get data off the cache. The code for this is as follows and must be loaded into the database.

import com.tangosol.net.CacheFactory;
import com.tangosol.net.NamedCache;

public class CoherenceClient
{
  public CoherenceClient()
  {
  }
  
  public static void getData ()
  {
    System.setProperty("tangosol.coherence.cacheconfig", "jserver:/resource/schema/COHERENCE/client-cache-config.xml");
    System.setProperty("tangosol.coherence.distributed.localstorage", "false");
    
    NamedCache cache = CacheFactory.getCache("test");

    System.out.println("Key 1 = " + cache.get(1));
    
    CacheFactory.shutdown();
  }
  
  public static void putData ()
  {
    System.setProperty("tangosol.coherence.cacheconfig", "jserver:/resource/schema/COHERENCE/client-cache-config.xml");
    System.setProperty("tangosol.coherence.distributed.localstorage", "false");
    
    NamedCache cache = CacheFactory.getCache("test");

    cache.put(1, "Pas Apicella");
    
    CacheFactory.shutdown();   
  }
} 

5. The client cache config file which needs to be loaded into the database with the Java Class is as follows. You can see it connects to "papicell-au.au.oracle.com:9099" which is our extend proxy client host:port. This file is called "client-cache-config.xml" which is loaded into the database itself.

<?xml version="1.0"?>
<?xml version="1.0"?>

<!DOCTYPE cache-config SYSTEM "cache-config.dtd">

<cache-config>
  <caching-scheme-mapping>
    <cache-mapping>
      <cache-name>*</cache-name>
      <scheme-name>remote</scheme-name>
    </cache-mapping>
  </caching-scheme-mapping>
  <caching-schemes>
    <remote-cache-scheme>
      <scheme-name>remote</scheme-name>
      <initiator-config>
        <tcp-initiator>
          <remote-addresses>
            <socket-address>
              <address>papicell-au.au.oracle.com</address>
              <port>9099</port>
              <reusable>true</reusable>
            </socket-address>
          </remote-addresses>
        </tcp-initiator>
      </initiator-config>
    </remote-cache-scheme>
  </caching-schemes>
</cache-config>

6. The Java Stored procedure was created in JDeveloper 10g given we are using JDK 1.5 that made it easier to deploy from JDeveloper itself. The project is as follows. In this setup we simply exposed the Java Stored procedure static methods as 2 PLSQL procedures, but it would make sense to use a PLSQL package here rather then stand alone procedures.

7. You will see we reference the client cache config file as follows in our Java Stored procedure which is how it's done in the Oracle JVM.

System.setProperty("tangosol.coherence.cacheconfig", 

                               "jserver:/resource/schema/COHERENCE/client-cache-config.xml");

8. Finally we are ready to invoke our Java Stored Procedure which was loaded as follows. By creating a Java Stored procedure we are making it available in SQL terms. This means any client can invoke the coherence client , meaning even a PLSQL client if it wanted to or a .NET client. You simple call the stored procedures like any other procedures in the database. Basically when we test this we will use SQL*Plus to verify it works.

JDEV Log window which shows a successful deployment.

Invoking loadjava on connection 'coherence-11gr2' with arguments:
-order -resolve -thin
Loadjava finished.
Executing SQL Statement:
CREATE OR REPLACE PROCEDURE getData AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'CoherenceClient.getData()';
Success.
Executing SQL Statement:
CREATE OR REPLACE PROCEDURE putData AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'CoherenceClient.putData()';
Success.
Publishing finished.
----  Stored procedure deployment finished.  ----

9. Now we can write a very basic SQL file as follows which will put data onto the coherence cache and then retrieve it calling our 2 stored procedures.

set serveroutput on
call dbms_java.set_output(100000);

exec putData;
exec getData;

SQL*PLus Output

COHERENCE@linux11gr2> @run

Call completed.

2010-08-04 14:15:19.750/0.077 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
configuration from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence.xml"
2010-08-04 14:15:19.755/0.082 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
overrides from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence-override-dev.xml"
2010-08-04 14:15:19.756/0.083 Oracle Coherence 3.5.3/465 (thread=Root Thread, member=n/a): Optional configuration
override "/tangosol-coherence-override.xml" is not specified
2010-08-04 14:15:19.758/0.085 Oracle Coherence 3.5.3/465 (thread=Root Thread, 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-08-04 14:15:19.876/0.203 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded cache
configuration from "jserver:/resource/schema/COHERENCE/client-cache-config.xml"
2010-08-04 14:15:19.975/0.302 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Started:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STARTED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}
2010-08-04 14:15:19.976/0.303 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Opening Socket
connection to 10.187.80.135:9099
2010-08-04 14:15:19.979/0.306 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Connected to
10.187.80.135:9099
2010-08-04 14:15:20.013/0.340 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Stopped:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STOPPED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}

PL/SQL procedure successfully completed.

2010-08-04 14:15:20.031/0.358 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
configuration from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence.xml"
2010-08-04 14:15:20.034/0.361 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded operational
overrides from resource "jserver:/resource/schema/COHERENCE/tangosol-coherence-override-dev.xml"
2010-08-04 14:15:20.035/0.362 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Optional
configuration override "/tangosol-coherence-override.xml" is not specified
2010-08-04 14:15:20.036/0.363 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, 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-08-04 14:15:20.047/0.374 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Loaded cache
configuration from "jserver:/resource/schema/COHERENCE/client-cache-config.xml"
2010-08-04 14:15:20.062/0.389 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Started:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STARTED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}
2010-08-04 14:15:20.063/0.390 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Opening Socket
connection to 10.187.80.135:9099
2010-08-04 14:15:20.065/0.392 Oracle Coherence GE 3.5.3/465 (thread=Root Thread, member=n/a): Connected to
10.187.80.135:9099
Key 1 = Pas Apicella
2010-08-04 14:15:20.082/0.409 Oracle Coherence GE 3.5.3/465 (thread=RemoteCache:TcpInitiator, member=n/a): Stopped:
TcpInitiator{Name=RemoteCache:TcpInitiator, State=(SERVICE_STOPPED), ThreadCount=0, Codec=Codec(Format=POF),
PingInterval=0, PingTimeout=0, RequestTimeout=0, ConnectTimeout=0,
RemoteAddresses=[papicell-au.au.oracle.com/10.187.80.135:9099], KeepAliveEnabled=true, TcpDelayEnabled=false,
ReceiveBufferSize=0, SendBufferSize=0, LingerTimeout=-1}

PL/SQL procedure successfully completed.

COHERENCE@linux11gr2>

In this example we turn on debug output on the database side using DBMS_JAVA package to get console messages displayed when invoking the Java Stored procedure to verify indeed coherence is being used and what cache config file we are using. The fact that we now can access the coherence cluster from the database in SQL makes this worth the effort.

I also make sure I disconnect from the cluster once done in this simple example using "CacheFactory.shutdown();" in my Java Stored Procedures, assuming that database clients just need to read/put data from the cache and then there done.