What is Audit Trail?
Audit Trail (Oracle EBS Auditing) is a way of keeping track of changes made to important data in Oracle Application tables. Audit Trail 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 Audit Trail(Oracle EBS Auditing)
Step 1. Logon to the Applications as the sysadmin user and select the System Administrator responsibility.
Step 2: Make sure AuditTrail:Activate profile option is set to true
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.
Navigation: Security -> Audit Trail -> Install
Step 4: Define Audit tables and desired columns: choose the table you need to audit . At this point, you will need to specify the list of columns from the table you want audited
Navigation: Security -> Audit Trail -> Tables
Step 5. Define audit groups: It is required to group the tables that you need to audit since auditing is enabled on an audit groups basis.
Navigation: Security -> Audit Trail -> Groups
Step 6. Run the “Audit Trail Update Tables” report from the submit request form to enable auditing.
Navigation: Requests -> Run -> select a single request
What does the “Audit Trail 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 “Audit Trail 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 recorded.
- 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.
Recommended Table to Audit
ALR_ALERTS
FND_AUDIT_COLUMNS
FND_AUDIT_GROUPS
FND_AUDIT_SCHEMAS
FND_AUDIT_TABLES
FND_CONCURRENT_PROGRAMS
FND_DATA_GROUPS
FND_DATA_GROUP_UNITS
FND_ENABLED_PLSQL
FND_FLEX_VALIDATION
FND_FORM
FND_FORM_FUNCTIONS
FND_GRANTS
FND_MENUS
FND_MENU_ENTIRES
FND_ORACLE_USERID
FND_PROFILE_OPTIONS
FND_PROFILE_OPTION_VALUES
FND_REQUEST_GROUPS
FND_REQUEST_GROUP_UNITS
FND_RESP_FUNCTIONS
FND_USER_RESP_GROUPS
Audit table Reporting
Audit Trail reports are not provided with Oracle E-Business Suite. You may write audit reports as needed using SQL. Audit Trail provides the views of your shadow tables to make audit reporting easier; you can write your reports to use these views.
Hope you like this article on Oracle EBS Auditing. Please do provide the feedback
Related Articles
FNDCPASS : FNDCPASS & AFPASSWD is the utility used to change the apps schema ,Oracle EBS schema and user password in Oracle EBS all versions
Guest User password in 11i/R12 : Check out how to troubleshoot Guest User password in 11i/R12, how to change the guest user password and how to check it
oracle apps dba interview questions : You should not miss these 60 awesome oracle apps dba interview questions.Must read to succeed in interviews and jobs.Download also available
APPLSYSPUB schema : applsyspub schema is the public schema in Oracle EBS which is used first while connecting to Oracle Forms and Oracle OAF pages.
oracle apps queries : This page contains the very useful and practical Top 30 Useful oracle apps queries for APPS DBA to help in day to day administration activities
how to enable trace for a running concurrent request : How to enable trace in Self service page, Oracle forms, Concurrent Program, running concurrent Program
https://docs.oracle.com/cd/E26401_01/doc.122/e22952/T156458T663757.htm