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>