Oracle service bus error logging in report providerPublished on: 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.