Auditing is always enabled. Oracle Database generates audit records during or after the execution phase of the audited SQL statements.

Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is run.

To improve read performance of the unified audit trail, the unified audit records are written immediately to disk to an internal relational table in the AUDSYS schema. In the previous release, the unified audit records were written to SecureFile LOBs. If you had migrated to unified auditing in Oracle Database 12c release 1 (12.1), then you can manually transfer the unified audit records from the SecureFile LOBS to this internal table. If the version of the database that you are using supports partitioned tables, then this internal table is a partitioned table. In this case, you can modify the partition interval of the table by using the DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL procedure. The partitioned version of this table is based on the EVENT_TIMESTAMP timestamp as a partition key with a default partition interval of one month. If the database version does not support partitioning, then the internal table is a regular, non-partitioned table.

The generation and insertion of an audit trail record is independent of the user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options from unified audit policies that are in effect at the time a database user connects to the database remain in effect for the duration of the session. When the session is already active, setting or changing statement or privilege unified audit options does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become immediately effective for current sessions.

By default, audit trail records are written to the AUDSYS schema in the SYSAUX tablespace. You can designate a different tablespace, including one that is encrypted, by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

Certain security sensitive database activities are always audited and such audit configuration cannot be disabled.

The UNIFIED_AUDIT_TRAIL data dictionary view captures activities from administrative users such as SYSDBA, SYSBACKUP, and SYSKM. You do not need to audit the unified audit trail. The unified audit trail resides in a read-only table in the AUDSYS schema. Hence, DMLs are not permitted on the unified audit trail views. Even DML and DDL operations on the underlying dictionary tables from AUDSYS schema are not permitted.

The SYSTEM_PRIVILEGE_USED column shows the type of administrative privilege that was used for the activity.

The following audit-related activities, such as modifications to audit policies, are mandatorily audited:

• CREATE AUDIT POLICY

• ALTER AUDIT POLICY

• DROP AUDIT POLICY

• AUDIT

• NOAUDIT

• EXECUTE of the DBMS_FGA PL/SQL package

• EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package

• ALTER TABLE attempts on the AUDSYS audit trail table (remember that this table cannot be altered)

• Top level statements by the administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, until the database opens. When the database opens, Oracle Database audits these users using the audit configurations in the system—not just the ones that were applied using the BY clause in the AUDIT statement, for example, but those that were applied for all users when AUDIT statement does not have a BY clause or when the EXCEPT clause was used and these users were not excluded.

• All user-issued DML statements on the SYS.AUD$ and SYS.FGA_LOG$ dictionary tables

• Any attempts to modify the data or metadata of the unified audit internal table. SELECT statements on this table are not audited by default or mandatorily.

• All configuration changes that are made to Oracle Database Vault

• Access to sensitive columns in the optimizer dictionary tables. Be aware that internal access to these table columns by the DBMS_STATS package does not generate mandatory audit records. The optimizer dictionary tables are as follows:

  Optimizer Dictionary Table	Columns
  SYS.HIST_HEAD$	minimum, maximum, lowval, hival
  SYS.HISTGRM$	endpoint, epvalue_raw
  SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY	minimum, maximum, lowval, hival
  SYS.WRI$_OPSTAT_HISTGRM_HISTORY	endpoint, epvalue_raw

For each execution of an auditable operation within a cursor, Oracle Database inserts one audit record into the audit trail.

Events that cause cursors to be reused include the following:

• An application, such as Oracle Forms, holding a cursor open for reuse

• Subsequent execution of a cursor using new bind variables

• Statements executed within PL/SQL loops where the PL/SQL engine optimizes the statements to reuse a single cursor

Auditing is not affected by whether or not a cursor is shared. Each user creates her or his own audit trail records on first execution of the cursor.

Oracle Database automatically writes audit records to an internal relational table in the AUDSYS schema.

In Oracle Database 12c release 1 (12.1), you had the option of queuing the audit records in memory (queued-write mode) and be written periodically to the AUDSYS schema audit table. However, starting with Oracle Database 12c release 2 (12.2), immediate-write mode and queued-write mode are deprecated. The parameters that controlled them (UNIFIED_AUDIT_SGA_QUEUE_SIZE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE, and DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE), while still viewable, no longer have any functionality.

The new functionality of having audit records always written to a relational table in the AUDSYS schema prevents the risk of audit records being lost in the event of an instance crash or during a SHUTDOWN ABORT operation. The new functionality also improves the performance of the audit trail and the database as a whole.

If you have upgraded from Oracle Database 12c release 1 (12.1) and migrated to unified auditing in that release, then Oracle recommends that you use the DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure to transfer the audit records as generated in the previous release to the AUDSYS audit internal table. Oracle Database Upgrade Guide provides information about transferring unified audit records after an upgrade.

Audit records that have been written to the spillover audit files can be moved to the unified audit trail database table.

1. Log into the database instance as a user who has been granted the AUDIT_ADMIN role.

   For example:

   CONNECT aud_admin
   Enter password: password
   Connected.
   

   In a multitenant environment, log into the CDB root with the AUDIT_ADMIN role. Before you can upgrade to the current release or Oracle Database, you must execute the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure from the CDB root to avoid losing operating system spillover files during the upgrade process.

   For example:

   CONNECT c##aud_admin
   Enter password: password
   Connected.
   

2. Ensure that the database is open and writable.

   For a non-CDB architecture, to find if the database is open and writable, query the V$DATABASE view.

   For example, in a CDB environment:

   SELECT NAME, OPEN_MODE FROM V$DATABASE;
   
   NAME            OPEN_MODE  
   --------------- ---------- 
   HRPDB           READ WRITE 
   

   In a multitenant environment, you can run the show pdbs command to find information about PDBs associated with the current instance.

3. Run the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure.

   EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;
   

4. In a multitenant environment, if you want to load individual PDB audit records, then log in to each PDB and run the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure again.

You can disable unified auditing.

You can create an archive of the traditional operating system audit files after you have upgraded Oracle Database.

You should periodically archive and then purge the audit trail to prevent it from growing too large.

You can use a variety of ways to purge audit trail records.

You should periodically archive and then delete (purge) audit trail records. You can purge a subset of audit trail records or create a purge job that performs at a specified time interval. Oracle Database either purges the audit trail records that were created before the archive timestamp, or it purges all audit trail records. You can purge audit trail records in both read-write and read-only databases.

The purge process takes into account not just the unified audit trail, but audit trails from earlier releases of Oracle Database. For example, if you have migrated an upgraded database that still has operating system or XML audit records, then you can use the procedures in this section to archive and purge them.

To perform the audit trail purge tasks, you use the DBMS_AUDIT_MGMT PL/SQL package. You must have the AUDIT_ADMIN role before you can use the DBMS_AUDIT_MGMT package. Oracle Database mandatorily audits all executions of the DBMS_AUDIT_MGMT PL/SQL package procedures.

If you have Oracle Audit Vault and Database Firewall installed, the audit trail purge process differs from the procedures described in this manual. For example, Oracle Audit Vault archives the audit trail for you.

You can perform the purge on a regularly scheduled basis or at a specified times.

• Purging the Audit Trail on a Regularly Scheduled Basis
  You can purge all audit records, or audit records that were created before a specified timestamp, on a regularly scheduled basis.
• Manually Purging the Audit Trail at a Specific Time
  You can manually purge the audit records right away in a one-time operation, rather than creating a purge schedule.

Scheduling an automatic purge job requires planning beforehand, such as tuning the online and archive redo log sizes.

• About Scheduling an Automatic Purge Job
  You can purge the entire audit trail, or only a portion of the audit trail that was created before a timestamp.
• Step 1: If Necessary, Tune Online and Archive Redo Log Sizes
  The purge process may generate additional redo logs.
• Step 2: Plan a Timestamp and Archive Strategy
  You must record the timestamp of the audit records before you can archive them.
• Step 3: Optionally, Set an Archive Timestamp for Audit Records
  If you want to delete all of the audit trail, then you can bypass this step.
• Step 4: Create and Schedule the Purge Job
  You can use the DBMS_AUDIT_MGMT PL/SQL package to create and schedule the purge job.

You can use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to manually purge the audit trail.

• About Manually Purging the Audit Trail
  You can manually purge the audit trail right away, without scheduling a purge job.
• Using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL to Manually Purge the Audit Trail
  After you complete preparatory steps, you can use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to manually purge the audit trail.

Other kinds of audit trail purge include enabling or disabling the audit trail purge job or setting the default audit trail purge job interval.

• Enabling or Disabling an Audit Trail Purge Job
  The DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS procedure enables or disables an audit trail purge job.
• Setting the Default Audit Trail Purge Job Interval for a Specified Purge Job
  You can set a default purge operation interval, in hours, that must pass before the next purge job operation takes place.
• Deleting an Audit Trail Purge Job
  You can delete existing audit trail purge jobs.
• Clearing the Archive Timestamp Setting
  The DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP procedure can clear the archive timestamp setting.

You can create a customized archive procedure to directly call a unified audit trail purge operation.

The pseudo code in Example 26-1 creates a database audit trail purge operation that the user calls by invoking the DBMS_ADUIT.CLEAN_AUDIT_TRAIL procedure for the unified audit trail.

The purge operation deletes records that were created before the last archived timestamp by using a loop. The loop archives the audit records, calculates which audit records were archived and uses the SetCleanUpAuditTrail call to set the last archive timestamp, and then calls the CLEAN_AUDIT_TRAIL procedure. In this example, major steps are in bold typeface.

-- 1. Set the last archive timestamp:
PROCEDURE SetCleanUpAuditTrail()
 BEGIN
  CALL FindLastArchivedTimestamp(AUD$);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   LAST_ARCHIVE_TIME         => '23-AUG-2013 12:00:00',
   CONTAINER                 => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
 END;
/
-- 2. Run a customized archive procedure to purge the audit trail records:
BEGIN
  CALL MakeAuditSettings();
  LOOP (/* How long to loop*/)
    -- Invoke function for audit record archival
    CALL DoUnifiedAuditRecordArchival();
 
    CALL SetCleanUpAuditTrail(); 
    IF(/* Clean up is needed immediately */)
      DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
       AUDIT_TRAIL_TYPE        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
       USE_LAST_ARCH_TIMESTAMP => TRUE,
       CONTAINER               => DBMS_AUDIT_MGMT.CONTAINER_CURRENT );
    END IF
  END LOOP /*LOOP*/
END; /* PROCEDURE */ 
/