Storing XML application logs in the database
Date: Wed, 27 Jan 2010 09:58:06 -0000
Message-ID: <7FC245ECC3E43243B415E1610AD617FDA768EA_at_mail1.dundee.realtimeworlds.com>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 27 2010 - 03:58:06 CST