Back to overview

Oracle service bus error logging in report provider

Published on: 14 December 2015 Author: Michel Schildmeijer 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:

SELECT LOCALHOST_TIMESTAMP, HOST_NAME, STATE, 
INBOUND_SERVICE_NAME, INBOUND_SERVICE_URI, INBOUND_OPERATION, 
OUTBOUND_SERVICE_NAME, OUTBOUND_SERVICE_URI, OUTBOUND_OPERATION, 
ERROR_CODE, ERROR_REASON, ERROR_DETAILS 
FROM WLI_QS_REPORT_ATTRIBUTE 
where ERROR_CODE is not NULL 
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.

About the author Michel Schildmeijer

Michel started his career as a medical officer in the Royal Dutch Airforce, with a focus on pharma. After the air force, he continued in pharma, followed by time working in clinical pharmacology. While there, he transitioned to IT by learning UNIX and MUMPS, and developed a system for managing patients’ medical records. As his career developed, his responsibility shifted from a deep technical perspective to a more visionary role. At the end of 2011, Michel authored a book on WebLogic Administration for beginners. He joined Qualogy in April 2012 where he expanded his repertoire significantly, serving a wide range of customers with his knowledge about Java Application Servers, Middleware and Application Integration. He also increased his multiple-industry knowledge in his role as Solutions or IT architect by working for customers in a range of sectors, including financials, telecom, public transportation and government organizations. In 2012, he received the IT Industry-recognized title of Oracle ACE for being an ambassador and community leader in his area of expertise. In 2019, this was enhanced to Oracle ACE Director. Michel is asked regularly to speak about technology and the impact of innovation at national and international conferences such as KubeCon, Oracle OpenWorld, Groundbreakers Developers Tours and others. He contributes actively to the OpenSource community and solutions regarding containerization, CI/CD and DevOps. In October 2021, Michel took an exciting step in his career by becoming Business Unit Manager for Qualogy Managed Services. While he still has a finger on the pulse in terms of technology, he is expanding his scope by combining his experience with the new insights gained in his new position. Read more via blog: https://bit.ly/3fAxrqf | Medium: https://mschildmeijer.medium.com/ | Books: Oracle Weblogic Server 11gR1 PS2: Administration Essentials: https://bit.ly/3IhALmf | Oracle WebLogic Server 12c: First Look1: https://bit.ly/31olbpj