Listener Log Data Mining With SQL

DZone 's Guide to

Listener Log Data Mining With SQL

In this article, we discuss how to data mine your listener logs by taking advantage of XML facilities.

· Database Zone ·
Free Resource

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections, etc., all of which might be useful, particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilize the power of SQL to mine the data.

Plain Text

It is easy to create an external table to simply grab each line of the log file and return it as a row from a table. We can see that below, simply returning a single column called LINE.


You may also like: Data Mining Process: Cross-Industry Standard Process for Data Mining.

While I have achieved something by having this data exposed as a table, there are still some challenges to face. It now becomes our job to extract the metadata from each line. For example, to appropriately timestamp each row, I need to look for date formatted strings and use some analytics to carry that date down through subsequent rows to assign them to the rest of the data. In the example below, I’ve done that with LAST_VALUE and given an example of how we could extract the HOST metadata


That kind of SQL manipulation is cool and fun, but it is also going to get complex fast! Here is perhaps an alternative way of tackling the problem. As well as the "trace" folder, the listener logs are also written as XML to the "alert" folder. Looking at the log.xml file in this folder, we can see the same listener log information in XML format.


Rather than using an external table, perhaps I can load that data as XML because then much of the metadata is already appropriately tagged for me. No more complex SQL!


So far so good. I now have my listener XML log loaded as a single CLOB into a table. But what happens when I try to manipulate that data using XMLTYPE.


Hmmm…not so good. Initially, I suspected that maybe the log files were not valid XML. So, I extracted just a single MSG row from the file and tried to convert that to XMLTYPE.


So, a single row is fine, but what happens if I try to repeat that with the same row duplicated? I know that the row is valid XML because I just proved that above. But, look at what happens.


This is an expected result because the XMLTYPE conversion is expecting a single XML document. Hence, in order to mimic that with multiple MSG tags, I need to enclose the entire clob in a single XML tag. I’ll use LISTENER.


Now, finally, the clob can be treated as an XMLTYPE without any issues.



So, there you go. Data mining your listener logs by taking advantage of the XML facilities. I bet you never thought you’d hear a database guy singing the praises of XML.

Further Reading

database ,sql ,data mining ,xml ,server logs ,data analysis ,tutorial

Published at DZone with permission of Connor McDonald , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}