How to obtain an Oracle 10g audit trail in XML format

Oracle databases have always had the ability to audit operation types against the database and to keep audit trails in a system table. This table is named Sys.aud$ and is located in the data directory. In some operating systems, you can also write audit records for the operating system's own event log subsystem.

Oracle 10g adds a third option: the ability to write files in an operating system directory. These files can be in XML format.

Auditing with XML rather than sys.aud$ has the following benefits:

• XML files can be easily processed by tools other than the database, filter out useful events, combine with audit logs from other systems, and format HTML displays.
• Operating system files provide more robust security than sys.aud$ tables, especially if you want to prevent database administrators from viewing or modifying audit trails.
• Even if you close the database instance, you can continue to use audit logs that are kept outside the database, but you can still query them from the database through a new view, V$xml_audit_trail.

To activate the XML file auditing function, you need to set two initialization parameters. First, use the following code to determine the directory where the audit file is written:

Audit_file_dest= ' DirectoryPath '

Note that this is not a directory object, such as data Pump, but rather the actual pathname of the operating system directory.

Then, set the Audit_trail parameter:

Audit_trail=xml

Finally, restart the Oracle instance. The Audit_trail parameter is static and needs to be restarted for Oracle to take effect. However, if you use the deferred parameter, you can change the Audit_file_dest parameter dynamically by using the ALTER SYSTEM command:

ALTER SYSTEM SET audit_file_dest= ' DirectoryPath ' DEFERRED

Exiting the session will save the log in its original location, but the new session will be logged to the destination.