Storing XML application logs in the database

Hi,

 

I'm looking for some advice on how to best enable the automated upload
of application logs to the database.  The applications will log to a
text file, with the leading columns of each log entry being well defined
in CSV format, and the log data in XML-like format with varying data.
Each log file will have a unique name that includes the host name.  When
the logs rotate, they will need to be uploaded to the database for
queries, historical analysis, etc.   I'm trying to come up with an easy,
automated way to upload the log data from the application servers.  The
app is using Instant Client, so SQL*Loader on the application servers is
out.   The developers will need to search on the XML-like log data, so I
don't want to just store it in a CLOB.  We're on 11gR1 and will be
moving to 11gR2 shortly.  There is no need to maintain the original
formatting of the log file.

 

What I'd really like to do is shred the log into a set of relational
tables, but the XML-like field is to be essentially free form data.

 

I'm toying with the idea of using XDB to allow the app servers to upload
the log files via FTP or HTTP and using an XML schema to parse the
incoming files into object/relational tables.  I think this would also
require that the log files be fully XML, which shouldn't be a problem.
This would be easiest for the developers, but I've no experience with
XDB or XML in the database, so I wonder if I'm trying to be too clever
for my own good. 

 

My fallback plan would be to FTP the log files to the database server
and just have a shell script use SQL*Loader to populate regular
relational tables with the XML data stored as XMLType in
object/relational tables.   

 

My questions are:

Would I be better off storing the XML field as object/relational or as
binary XMLType?  

Is the XML handling in the database as efficient as relational tables or
does it add a lot of overhead?

Am I overlooking an easier way to do this?  

Any other recommendations?  

 

Thanks

 

Warren Puziewicz

Database Architect

Realtime Worlds, Ltd

152 West Marketgait

Dundee DD1 1NJ

+44 (0) 01382 202821

 


____________________________________________________________________
DISCLAIMER

This message and any attachments contain privileged and confidential 
information intended for the use of the addressee named above. If you are not 
the intended recipient of this message, you are hereby notified that any use, 
dissemination, distribution or reproduction of this message is prohibited. 
Please note that we cannot guarantee that this message or any attachment is 
virus free or that it has not been intercepted and amended. The views of the 
author may not necessarily reflect those of Realtime Worlds Ltd.

 

Realtime Worlds Ltd is registered in Scotland, number 225628. Registered 
Office: 152 West Marketgait, Dundee, DD1 1NJ.

Other related posts: