Oracle service bus error logging in report provider

Oracle service bus error logging in report provider

Published on: Category: Oracle

Oracle Service Bus Monitoring can be approached in various ways. You could choose to use the OSB Alert and diagnostic framework with pipeline and SLA alerts, or you could log your exceptions in the JMS Reporting Provider. Messages and errorcodes end up in a database table from the OSB repository. These tables are – as you might know -WLI_QS_REPORT* tables.

At this certain customer, we use the Enterprise Manager 12c Cloud control as the central monitoring system for all our Oracle Fusion Middleware. Unfortunately, constructing an OSB Monitoring Template doesn’t include a facility to monitor the reporting provider. I solved this by using a metric extension, a great feature within EM 12c, and finally I added this extension to the monitoring template.

Creating a metric extension

My startpoint is a simple SQL Script to query the OSB WLI table:

  1. SELECT LOCALHOST_TIMESTAMP, HOST_NAME, STATE,
  2. INBOUND_SERVICE_NAME, INBOUND_SERVICE_URI, INBOUND_OPERATION,
  3. OUTBOUND_SERVICE_NAME, OUTBOUND_SERVICE_URI, OUTBOUND_OPERATION,
  4. ERROR_CODE, ERROR_REASON, ERROR_DETAILS
  5. FROM WLI_QS_REPORT_ATTRIBUTE
  6. where ERROR_CODE is not NULL
  7. and LOCALHOST_TIMESTAMP > trunc(sysdate-1);

I am no SQL Guru, but this worked for me, but feel free to tune this to get the best out of it. I used this script for my metric extension.

In EM 12C, select Montoring -> Metric Extensions -> Create New

In the next screen you can enter the SQL Script or upload it to your EM. There are a lot more options I did not use at this time:

Next you need to specify all the columns from the query, and specify one as the key column. I chose the LOCALHOST_TIMESTAMP ( Timestamp of OSB host) as the key value. I did not specify any alert, I will do that one later.

For Credentials I used the ‘Use Default Monitoring Credentials’, but beware: give this user (usually called dbsnmp) select permissions on the WLI_QS_REPORT_ATTRIBUTE table of the OSB Repository schema.

After that you can add the OSB database and test the metric extension:

Then you’re finished. You deploy this extension to your OSB targets.

Select the metric extension and set this to a Deployable Draft (in the dropdown list of the Actions Menu). Then publish the extension following the same method.

Now you can deploy the extension, either to individual targets, your OSB databases, or to your companies Service Bus template, through the Actions Menu of the extension.

If you look at your OSB database metrics, you can see the metric is added.

Michel Schildmeijer
About the author Michel Schildmeijer

Having made his start in the pharmacy sector, Michel transitioned to IT in 1996, working on a UNIX TTY terminal-based system and the MUMPS language. He currently works as a solutions architect at Qualogy, with a focus on middleware, application integration and service-oriented architecture. His passion for middleware started in 2000 when working as a support analyst for a financial institute with BEA WebLogic and Tuxedo. Michel is an expert on the WebLogic platform. He serves customers in his role as architect and advises them in all aspects of their IT landscape. He became an Oracle ACE in 2012 and wrote two books about WebLogic: Oracle WebLogic Server 11gR1 PS2: Administration Essentials and Oracle WebLogic Server 12c: First Look. He is a well-known speaker at national and international conferences and is recognised as an official Oracle Speaker. Read his blog: https://community.oracle.com/blogs/mnemonic

More posts by Michel Schildmeijer
Comments
Reply