Audittrail in Oracle applications

What is AuditTrail?AuditTrail is a way of keeping track of changes made to important data in Oracle Application tables. AuditTrail keeps a history of the following three questions:
1. What changed
2. Who changed it
3. When did the change take place
This can be done on a row by row basis or on individual columns of a  table.

How to Setup AuditTrail

Step 1. Logon to the Applications as the sysadmin user and select the System Administrator responsibility.
Step 2. Define auditgroups: It is required to group the tables that you need to audit since auditing is enabled on an auditgroups basis.
Navigation: Security -> AuditTrail -> Groups
Step 3. Define audit installations: In order to be able to audit across application installations, you must specify the Oracle ID’s whose tables you wish to audit. At this point, you will need to specify the list of columns from the table you want audited.
Navigation: Security -> AuditTrail -> Install
Step 4. Run the “AuditTrail Update Tables” report from the submit request form to enable auditing.
Navigation: Requests -> Run -> select a single request

What does the “AuditTrail Update Tables” report do?
Creates shadow tables with a name of the first 26 characters of the audit table and a “_A” suffix. The columns will have the same datatypes as those of the audited table. The program also creates views _AC# and _AV# on the shadow table to facilitate the access of data. The “#” in the view name represents a number, and can be any number depending on how many views are needed to access the data.
The _AC# view contains the current state of the data at the time of query from the join of the shadow table and the table audited.The _AV# view contains the same data as the shadow table plus the current value of the rows. Basically, the _AC# view contains the current status of the audited table with the unchanged columns .Querying the _AC# and _AV# views will also enable you to monitor who changed the data on the original table, by whom, and when the changes were made. As you can see from the above examples, the
shadow table keeps track of the original data on the table prior to the change.

How to stop audit trail?
In situations where you want to stop auditing, you must set the audit group state to either “Disable-prepare for archive” or “Disable-Interrupt Audit” and run the “AuditTrail Update Tables” report.
Disable-prepare for archive: Copies all the current values in the audited table into the shadow table
and disables auditing triggers and thus there is no more auditing.
Disable-Interrupt Audit: Modifies the triggers to save the final row into the shadow table for each modified row in the audited table. Further modification of the same row in the audited table are not
If you are interested in cleaning up the shadow table, set the audit group to “Disable-Purge table” and run the “AuditTrail Update Table” report to drop the auditing triggers and views and to delete the rows in the shadow table.

Leave a Reply