Oracle service bus error logging in report provider

Oracle service bus error logging in report provider

Gepubliceerd: Categorie: 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:

  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
Over auteur Michel Schildmeijer

Having started in the pharmacy sector, Michel transitioned to IT in 1996, where he worked 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 he worked as a support analyst in the financial sector 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 recognized as an official Oracle Speaker. Since a few years, he is also an active contributor to the OpenSource community and solutions regarding containerization and DevOps. Furthermore, he is a well-known expert in several communities. He speaks regularly at events in the EMEA region and worldwide, such as KubeCon, Continuous Delivery Summit, Oracle OpenWorld, Groundbreakers Developers Tours and several other tech conferences. Read more at: and

Meer posts van Michel Schildmeijer
Reactie plaatsen