How to Store Oracle 10g Log Files in XML

Logging is a top priority for any project. Current versions of most popular databases have excellent logging capabilities, but it’s not always easy to get the logged data in a format that you can work with.

Oracle 10g gives you the capability to store logs in three ways:

1. Store operational logs in the System Table, which you can find in the Data Dictionary. The table is called SYS.AUD$.
2. Write operational logs to the system’s Logging System.
3. Write operational logs to flat files in the system directories, as many conventional logging mechanisms do.

Each method has both advantages and disadvantages. This tip concentrates on the third option, saving log information to flat files, in XML format.

XML is the chosen standard here because it’s easy for external systems to interoperate with, it requires no dependence on the live database, and Oracle includes support for querying XML-formatted log files.

Follow these steps to enable the XML-formatted flat-file logging option in an Oracle 10g installation:

1. Set the log file directory by setting the audit_file_dest parameter. Note that the path must be a valid OS directory.

audit_file_dest='loggingpath'

2. Set the Audit_Trial parameter to XML:

audit_trail=xml

You need to restart the Oracle instance for logging to take effect.