Start Journaling Internal Tables

Oracle keeps an audit trail of all data changes in some of its internal metadata tables in shadow journaling tables. However, you must explicitly turn this feature on. Journaling tables help to satisfy regulatory requirements.

Set AuditTrail:Activate Profile to Yes

Set the AuditTrail:Activate profile to Yes at the Oracle LSH Application level.

  1. Log on as sysadmin; see Log on as sysadmin for details.
  2. Click the System Administrator responsibility link. A new column of links appears.
  3. Under Profile in the right-hand column, click System.

    If you receive a message asking if you want to install an applet, do so.

    The Find System Profile Values window appears.

  4. In the Display section, uncheck Site and check Application.
  5. In the Application field, enter%life% to bring up Oracle Life Sciences Data Hub.
  6. In the Profile field, enter AuditTrail:Activate in the Profile text box at the bottom of the window.
  7. Click Find. The system returns you to the System Profile Values window with AuditTrail:Activate displayed in the Profile Option Name column and Oracle Life Sciences Data Hub displayed in the Application column.
  8. Select Yes as the value for the column Application and click the Save icon or save from the File menu (File > Save).

Define the Audit Installation

To start journaling, do the following:

  1. Log on as sysadmin; see Log on as sysadmin for details.

  2. Click the System Administrator responsibility link. A new column of links appears.

  3. In the new column of links, scroll down to Security: Audit Trail. In this section, click Install.

    If you receive a message asking if you want to install an applet, do so.

    The Audit Installations window appears.

  4. Click the Search icon (flashlight/torch) in the toolbar. The Find Audit Installations pop-up appears.

    Note:

    If the icon is inactive, the wrong window is in focus. If necessary, retrieve the Audit Installations window:

    1. In the Navigator - System Administrator window, Functions tab, expand the Security node.

    2. Expand the Audit Trail node.

    3. Click Install.The Audit Installations window appears.

    Then click the Search icon in the toolbar.

  5. Click in the Oracle Username field to display the ellipsis (...), then click the ellipsis to display the list of values. Find and select CDR.

  6. Click Find. The system returns you to the Audit Installation window with CDR displayed in the Oracle Username column

  7. Select the Audit Enabled checkbox and click Save.

Prevent Error

For the most current information, see My Oracle Support article 727208.1, AuditTrail Update Tables fails on View AP_SYSTEM_PARAMETERS_ALL_AC1.

To follow Step 1 in the article, do the following:

  1. Log in to Oracle LSH as sysadmin.

  2. Navigate to System Administrator, then Security, then Audit Trail, and then Groups.

  3. Confirm anything necessary to get the application to run and a form appears.

  4. Press F11.

  5. In the Audit Group line enter SQLAP.AP_SYSTEM_PARAMETERS_ALL and press Ctrl+F11. The Audit Group SQLAP.AP_SYSTEM_PARAMETERS_ALL appears.

  6. Proceed with Step 2 in the MOS article.

Step 2 in the article is:

  1. Set the Group state to "Disable - Purge Table" for AP_SYSTEM_PARAMETERS_ALL. This option Disable - Purge Table Drops the auditing triggers and views and deletes all data from the shadow table.
  2. Be sure to Save before exiting.

Run the Audit Trail Update Tables Concurrent Program

After you have defined CDR as the audit installation, run the Audit Trail Concurrent Program to enable auditing.

  1. From the View menu, select Requests. The Find Requests pop-up appears.

  2. Click Submit a New Request. The Submit a New Request pop-up appears.

  3. Select Single Request and click OK. The Submit Request pop-up appears.

  4. From the Name drop-down list, select AuditTrail Update Tables and click OK. The system returns you to the Submit Request window with AuditTrail Update Tables displayed in the Name field.

  5. Click Submit. A window pops up with the Request ID and asks if you want to submit another request.

  6. Make a note of the Request ID and click No.

Monitor the Job: To monitor the job's progress:

  1. Click Find and use the Request ID to search for the job.
  2. Click Refresh Data periodically to update the execution phase and status of the job displayed on screen.
  3. When the phase is Complete, click the View Log. Always check the log file.

    The log file contains a record of queries to update the FND tables, creating shadow tables, giving grants, creating triggers and procedures. At the end it should say: Concurrent Request completed successfully or, if the job did not complete successfully, an error message giving the reason for the failure.