Skip Headers
Oracle® Database Security Guide
12c Release 1 (12.1)

E48135-10
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

23 Administering the Audit Trail

This chapter contains:

Managing the Unified Audit Trail

This section contains:

When Are Audit Records Created?

Auditing is always enabled. Oracle Database generates an audit record during or after the execution phase of the SQL statement that is being audited. Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is run.

You can control the frequency when audit trail records are written to disk. If the write mode is set to queued, then the audit records are written periodically to the SGA and not to disk immediately. If you want to write the audit records immediately to disk, then you can set it to immediate-write mode. Otherwise, there is a minimum flush threshold configured by default which checks if the queues were flushed more than 3 seconds before and accordingly flushes the audit records in the SGA queue. However, owing to the database activity, the flush may not happen every 3 seconds and could take longer.

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.

See Also:

Activities That Are Mandatorily Audited

The UNIFIED_AUDIT_TRAIL data dictionary view captures activities from administrative users such as SYSDBA, SYSBACKUP, and SYSKM. 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 configuration changes that are made to Oracle Database Vault

How Do Cursors Affect Auditing?

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.

Writing the Unified Audit Trail Records to the AUDSYS Schema

This section contains:

About Writing Unified Audit Trail Records to AUDSYS

By default, Oracle Database writes audit records to system global area (SGA) queues and then periodically writes them to the AUDSYS schema audit table in the SYSAUX tablespace, rather than immediately writing them to that table. This design greatly improves the performance of the audit trail processes and the database as a whole.

In the event of an instance crash or during SHUTDOWN ABORT operations, there is a chance that some audit records may be lost. If this is a concern, then you can configure the audit trail to immediately write audit records to the AUDSYS schema audit table. The following modes are available:

  • Immediate-write mode. This setting writes all audit records to the audit trail immediately. However, be aware that database performance may be affected.

  • Queued-write mode. This setting, which is the default write mode, queues the audit records in memory to be written periodically to the AUDSYS schema audit table. To set the size of the SGA, set the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter. The default size is 1 MB, and you can enter a range of 1 through 30.

See Also:

Oracle Database Reference for more information about the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter

Setting the Write Mode for Unified Audit Trail Records

In a multitenant environment, the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure applies to the current pluggable database (PDB) only. If the database is read-only, then DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY sets the value directly in the SGA.

To set the write mode for unified audit trail records:

  1. Log in to SQL*Plus as a user who has been granted the AUDIT_ADMIN role.

    For example:

    sqlplus audit_admin
    Enter password: password
    
  2. In a multitenant environment, connect to the appropriate PDB.

    For example:

    CONNECT audit_admin@hrpdb
    Enter password: password
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  3. Set the AUDIT_TRAIL_MODE property of the DBMS_AUDIT_MGMT package, as follows:

    • To use immediate-write mode, run the following procedure:

      BEGIN
       DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
      END;
      /
      
    • To use queued-write mode, run the following procedure:

      BEGIN
       DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
        DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);
      END;
      /
      

The settings take effect on subsequent database sessions.

Manually Flushing Audit Records to the Audit Trail in Queued-Write Mode

If you choose to use queued-write mode, then you can manually writing records to the unified audit trail in the following environment. Be aware that there is a minimum flush threshold that is configured by default. It checks if the queues were flushed more than 3 seconds before and accordingly flushes the audit records in the SGA queue. However, owing to the database activity, the flush may not happen every 3 seconds and could take longer.

Writing Records to Disk for the Current Database Instance

To manually write records to disk for the current database instance or the current Oracle Real Application Clusters (Oracle RAC instance, run either of the following procedures.

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(DBMS_AUDIT_MGMT.FLUSH_CURRENT_INSTANCE);

Writing Records to Disk Across an Oracle RAC Environment

The following procedure, designed for Oracle RAC environments, flushes the audit records across all Oracle RAC instances:

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(DBMS_AUDIT_MGMT.FLUSH_ALL_INSTANCES);

Writing Records to Disk in a Multitenant Environment

In a multitenant environment, the following procedure writes the audit trail records to disk for the current PDB:

BEGIN  
 DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(
  CONTAINER  => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

The following procedure writes the audit trail records to disk across all PDBs in the multitenant environment:

BEGIN  
 DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL(
  CONTAINER  => DBMS_AUDIT_MGMT.CONTAINER_ALL);
END;
/

Moving Operating System Audit Records into the Unified Audit Trail

When the database is not writable (such as during database mounts), if the database is closed, or if it is read-only, then Oracle Database writes the audit records to external files in the $ORACLE_BASE/audit/$ORACLE_SID directory. You can load the files into the database by running the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure. Be aware that if you are moving a large number of operating system audit records to the unified audit trail, performance may be affected.

To move the audit records in these files to the AUDSYS schema audit table when the database is writable:

  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 PDB in which you want to move the audit trail records to the unified audit trail.

    For example:

    CONNECT aud_admin@hrpdb
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Ensure that the database is open and writable.

    For a non-CDB architecture, to find the databases that are open and writable, query the V$DATABASE view.

    For example:

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

    In a multitenant environment, you can query the V$PDBS view to find information about PDBs associated with the current instance.

    For example:

    SELECT NAME, OPEN_MODE FROM V$PDBS;
    
    NAME            OPEN_MODE  
    --------------- ---------- 
    HRPDB           READ WRITE 
    
  3. Run the DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES procedure.

    EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;
    

The audit records are loaded into the AUDSYS schema audit table immediately, and then deleted from the $ORACLE_BASE/audit/$ORACLE_SID directory.

Disabling Unified Auditing

If you must disable unified auditing, then follow this procedure:

  1. Disable any unified audit policies that are currently enabled.

    This step prevents the database from going into mixed mode auditing after you complete this procedure. See "About Mixed Mode Auditing" for more information about mixed mode auditing.

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

    2. Query the POLICY_NAME and ENABLED_OPT columns of the AUDIT_UNIFIED_ENABLED_POLICIES data dictionary view to find unified audit policies that are enabled.

    3. Run the NOAUDIT POLICY statement to disable each enabled policy.

      See "Disabling Unified Audit Policies" for more information.

  2. Connect as user SYS with the SYSOPER privilege.

    CONNECT sys as sysoper
    Enter password: password
    

    In a multitenant environment, this command connects you to the root.

  3. Shut down the database.

    For example:

    SHUTDOWN IMMEDIATE
    

    In a multitenant environment, this command shuts down all PDBs in the CDB.

  4. Depending on your platform, do the following:

    • UNIX systems: Run the following commands:

      cd $ORACLE_HOME/rdbms/lib
      make -f ins_rdbms.mk uniaud_off ioracle
      
    • Windows systems: Rename the %ORACLE_HOME%/bin/orauniaud12.dll file to %ORACLE_HOME%/bin/orauniaud12.dll.dbl.

    In a multitenant environment, these actions disable unified auditing in all PDBs in the CDB.

  5. In SQL*Plus, restart the database.

    STARTUP
    

    In a multitenant environment, this command restarts all PDBs in the CDB.

Archiving the Audit Trail

This section contains:

Archiving the Traditional Operating System Audit Trail

To archive the traditional operating system audit trail from an upgraded database, use your platform-specific operating system tools to create an archive of the traditional operating system audit files.

To archive the traditional operating system audit files, create tape or disc backups. You can create a compressed file of the audit files, and then store it on tapes or discs. Consult your operating system documentation for more information.

Afterwards, you should purge (delete) the traditional operating system audit records both to free audit trail space and to facilitate audit trail management.

Archiving the Unified and Traditional Database Audit Trails

You should periodically archive and then purge the audit trail to prevent it from growing too large. Archiving and purging both frees audit trail space and facilitates the purging of the database audit trail.

After you complete the archive, you can purge the database audit trail contents. See "Purging Audit Trail Records" for more information.

To archive the unified, traditional standard, and traditional fine-grained audit records, you can copy the relevant records to a normal database table. For example:

INSERT INTO table SELECT ... FROM UNIFIED_AUDIT_TRAIL ...;
INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...; 

Purging Audit Trail Records

This section contains:

About Purging Audit Trail Records

Note:

This feature has been updated in Oracle Database 12c Release 1 (12.1.0.2), to include support for purging audit trail records in both read-write and read-only databases.

You should periodically archive and then delete (purge) audit trail records. This section describes a variety of ways that you can use to 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, in most cases, 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.

Note:

Oracle Database audits all deletions from the audit trail, without exception.

See Also:

Selecting an Audit Trail Purge Method

Table 23-1 provides a road map for selecting an audit trail purge method.

Table 23-1 Selecting an Audit Trail Purge Method

What Do You Want to Purge? About This Type of Purge Method

All audit records, or audit records created before a specified timestamp, on a regularly scheduled basis

You can schedule a purge operation to occur an specific times. For example, you can schedule it for every Saturday at 2 a.m.

General steps:

  1. If necessary, tune online and archive redo log sizes to accommodate the additional records generated during the audit table purge process.

  2. Plan a timestamp and archive strategy.

  3. Optionally, set an archive timestamp for the audit records.

  4. Create and schedule the purge job.

See "Scheduling an Automatic Purge Job for the Audit Trail" for more information.

All audit records, or records that were created before a specified timestamp, when you want it to run at a specified time

You can manually purge the audit records right away in a one-time operation, rather than creating a purge schedule.

General steps:

  1. If necessary, tune online and archive redo log sizes to accommodate the additional records generated during the audit table purge process.

  2. Plan a timestamp and archive strategy.

  3. Optionally, set an archive timestamp for the audit records.

  4. Run the purge operation.

See "Manually Purging the Audit Trail" for more information.


Scheduling an Automatic Purge Job for the Audit Trail

This section contains:

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. The individual audit records created before the timestamp can be purged.

Be aware that purging the audit trail, particularly a large one, can take a while to complete. Consider scheduling the purge job so that it runs during a time when the database is not busy.

You can create multiple purge jobs for different audit trail types, so long as they do not conflict. For example, you can create a purge job for the standard audit trail table and then the fine-grained audit trail table. However, you cannot then create a purge job for both or all types, that is, by using the DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD or DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL property. In addition, be aware that the jobs created by the DBMS_SCHEDULER PL/SQL package do not execute on a read-only database. An automatic purge job created with DBMS_AUDIT_MGMT uses the DBMS_SCHEDULER package to schedule the tasks. Therefore, these jobs cannot run on a database or PDB that is open in read-only mode.

Step 1: If Necessary, Tune Online and Archive Redo Log Sizes

The purge process may generate additional redo logs. Before you run this process, you may need to tune online and archive redo log sizes to accommodate the additional records generated during the audit table purge process. For more information about tuning log files, see Oracle Database Administrator's Guide.

In a unified auditing environment, the purge process does not generate as many redo logs as in a mixed mode auditing environment, so if you have migrated to unified auditing, then you may want to bypass this step.

Step 2: Plan a Timestamp and Archive Strategy

You must record the timestamp of the audit records before you can archive them. You can check the timestamp date by querying the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view. Later on, when the purge takes place, Oracle Database purges only the audit trail records that were created before the date of this timestamp. See "Step 3: Optionally, Set an Archive Timestamp for Audit Records" for more information.

After you have timestamped the records, you are ready to archive them. See "Archiving the Audit Trail" for more information.

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.

You can set a timestamp when the last audit record was archived. Setting an archive timestamp provides the point of cleanup to the purge infrastructure. If you are setting a timestamp for a read-only database, then you can use the DBMS_AUDIT.MGMT.GET_LAST_ARCHIVE_TIMESTAMP function to find the last archive timestamp that was configured for the instance on which it was run. For a read-write database, you can query the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view.

To find the last archive timestamps for the unified audit trail, you can query the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view. After you set the timestamp, all audit records in the audit trail that indicate a time earlier than that timestamp are purged when you run the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure. If you want to clear the archive timestamp setting, see "Clearing the Archive Timestamp Setting".

If you are using Oracle Database Real Application Clusters, then use Network Time Protocol (NTP) to synchronize the time on each computer where you have installed an Oracle Database instance. For example, suppose you set the time for one Oracle RAC instance node at 11:00:00 a.m. and then set the next Oracle RAC instance node at 11:00:05. As a result, the two nodes have inconsistent times. You can use Network Time Protocol (NTP) to synchronize the times for these Oracle RAC instance nodes.

To set the timestamp for the purge job:

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

    In a multitenant environment, log into either the root or the PDB in which you want to schedule the purge job. In most cases, you may want to schedule the purge job on individual PDBs.

    For example, to log into a PDB called hrpdb:

    CONNECT aud_admin@hrpdb
    Enter password: password
    Connected.
    
  2. Run the DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP PL/SQL procedure to set the timestamp.

    For example:

    BEGIN
      DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
       AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
       LAST_ARCHIVE_TIME    =>  '12-OCT-2013 06:30:00.00',
       RAC_INSTANCE_NUMBER  =>  1,
       CONTAINER            => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
    END;
    /
    

    In this example:

    • AUDIT_TRAIL_TYPE: Specifies the audit trail type. DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED sets it for the unified audit trail.

      For upgraded databases that still have traditional audit data from previous releases:

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Traditional standard audit trail table, AUD$. (This setting does not apply to read-only databases.)

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Traditional fine-grained audit trail table, FGA_LOG$. (This setting does not apply to read-only databases.)

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Traditional operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Traditional operating system audit trail files.

    • LAST_ARCHIVE_TIME: Specifies the timestamp in YYYY-MM-DD HH:MI:SS.FF UTC (Coordinated Universal Time) format for AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_AUD_STD, and AUDIT_TRAIL_FGA_STD, and in the Local Time Zone for AUDIT_TRAIL_OS and AUDIT_TRAIL_XML.

    • RAC_INSTANCE_NUMBER: Specifies the instance number for an Oracle RAC installation. If you specified the DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD or DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD audit trail types, then you can omit the RAC_INSTANCE_NUMBER argument. This is because there is only one AUD$ or FGA_LOG$ table, even for an Oracle RAC installation. If you specified DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED in an Oracle RAC environment, then you must specify an Oracle RAC instance number. The default is 0, which is used for single-instance database installations. You can find the instance number by issuing the SHOW PARAMETER INSTANCE_NUMBER command in SQL*Plus.

    • CONTAINER: Applies the timestamp to a multitenant environment. DBMS_AUDIT_MGMT.CONTAINER_CURRENT specifies the current PDB; DBMS_AUDIT_MGMT.CONTAINER_ALL applies to all PDBs in the multitenant environment.

      Note that you can set CONTAINER to DBMS_MGMT.CONTAINER_ALL only from the root, and DBMS_MGMT.CONTAINER_CURRENT only from a PDB.

    Typically, after you set the timestamp, you can use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure to remove the audit records that were created before the timestamp date.

Step 4: Create and Schedule the Purge Job

Create and schedule the purge job by running the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB PL/SQL procedure.

For example:

CONNECT aud_admin@hrpdb
Enter password: password
Connected.

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
   AUDIT_TRAIL_PURGE_INTERVAL  => 12,
   AUDIT_TRAIL_PURGE_NAME      => 'Audit_Trail_PJ',
   USE_LAST_ARCH_TIMESTAMP     => TRUE,
   CONTAINER                   => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

In this example:

  • AUDIT_TRAIL_TYPE: Specifies the audit trail type. DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED sets it for the unified audit trail.

    For upgraded databases that still have audit data from previous releases:

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$. (This setting does not apply to read-only databases.)

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$. (This setting does not apply to read-only databases.)

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables. (This setting does not apply to read-only databases.)

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files.

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types. (This setting does not apply to read-only databases.)

  • AUDIT_TRAIL_PURGE_INTERVAL: Specifies the hourly interval for this purge job to run. The timing begins when you run the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure, in this case, 12 hours after you run this procedure. Later on, if you want to update this value, run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL procedure.

  • USE_LAST_ARCH_TIMESTAMP: Enter either of the following settings:

    • TRUE: Deletes audit records created before the last archive timestamp. To check the last recorded timestamp, query the LAST_ARCHIVE_TS column of the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view for read-write databases and the DBMS_AUDIT_MGMT.GET_LAST_ARCHIVE_TIMESTAMP function for read-only databases. The default value is TRUE. Oracle recommends that you set USE_LAST_ARCH_TIMESTAMP to TRUE.

    • FALSE: Deletes all audit records without considering last archive timestamp. Be careful about using this setting, in case you inadvertently delete audit records that should have been deleted.

  • CONTAINER: In a multitenant environment, defines where to create the purge job. If you set CONTAINER to DBMS_AUDIT_MGMT.CONTAINER_CURRENT, then it is available, visible, and managed only from the current PDB. The DBMS_AUDIT_MGMT.CONTAINER_ALL setting creates the job in the root. This defines the job as a global job, which runs according to the defined job schedule. When the job is invoked, it cleans up audit trails in all the PDBs in the multitenant environment. If you create the job in the root, then it is visible only in the root. Hence, you can enable, disable, and drop it from the root only.

Manually Purging the Audit Trail

This section contains:

About Manually Purging the Audit Trail

You can manually purge the audit trail right away, without scheduling a purge job. Similar to a purge job, you can purge audit trail records that were created before an archive timestamp date or all the records in the audit trail. Only the current audit directory is cleaned up when you run this procedure.

For upgraded databases that may still have audit trails from earlier releases, note the following about the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure:

  • On Microsoft Windows, because the DBMS_AUDIT_MGMT package does not support cleanup of Windows Event Viewer, setting the AUDIT_TRAIL_TYPE property to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS has no effect. This is because operating system audit records on Windows are written to Windows Event Viewer. The DBMS_AUDIT_MGMT package does not support this type of cleanup operation.

  • On UNIX platforms, if you had set the AUDIT_SYSLOG_LEVEL initialization parameter, then Oracle Database writes the operating system log files to syslog files. (Be aware that when you configure the use of syslog files, the messages are sent to the syslog daemon process. The syslog daemon process does not return an acknowledgement to Oracle Database indicating a committed write to the syslog files.) If you set the AUDIT_TRAIL_TYPE property to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, then the procedure only removes .aud files under audit directory (This directory is specified by the AUDIT_FILE_DEST initialization parameter).

Procedure for Manually Purging the Audit Trail

  1. Follow these steps under "Scheduling an Automatic Purge Job for the Audit Trail":

  2. If you are using a multitenant environment, then connect to the database in which you created the purge job.

    If you created the purge job in the root, then you must log into the root. If you created the purge job in a specific PDB, then log into that PDB.

    For example:

    CONNECT aud_admin@hrpdb
    Enter password: password
    Connected.
    
  3. Purge the audit trail records by running the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure.

    For example:

    BEGIN
      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;
    /
    

    In this example:

    • AUDIT_TRAIL_TYPE: Specifies the audit trail type. DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED sets it for the unified audit trail.

      For upgraded databases that still have audit data from previous releases:

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$. (This setting does not apply to read-only databases.)

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$. (This setting does not apply to read-only databases.)

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables. (This setting does not apply to read-only databases)

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML Operating system audit trail files.

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files.

      • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types. (This setting does not apply to read-only databases.)

    • USE_LAST_ARCH_TIMESTAMP: Enter either of the following settings:

      • TRUE: Deletes audit records created before the last archive timestamp. To set the archive timestamp, see "Step 3: Optionally, Set an Archive Timestamp for Audit Records". The default (and recommended) value is TRUE. Oracle recommends that you set USE_LAST_ARCH_TIMESTAMP to TRUE.

      • FALSE: Deletes all audit records without considering last archive timestamp. Be careful about using this setting, in case you inadvertently delete audit records that should have been deleted.

    • CONTAINER: Applies the cleansing to a multitenant environment. DBMS_AUDIT_MGMT.CONTAINER_CURRENT specifies the local PDB; DBMS_AUDIT_MGMT.CONTAINER_ALL applies to all databases.

Other Audit Trail Purge Operations

This section contains:

Enabling or Disabling an Audit Trail Purge Job

To enable or disable an audit trail purge job, use the DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS PL/SQL procedure.

In a multitenant environment, where you run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS procedure depends on the location of the purge job, which is determined by the CONTAINER parameter of the DBMS_MGMT.CREATE_PURGE_JOB procedure. If you had set CONTAINER to CONTAINER_ALL (to create the purge job in the root), then you must run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS procedure from the root. If you had set CONTAINER to CONTAINER_CURRENT, then you must run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS procedure from the PDB in which it was created.

For example, assuming that you had created the purge job in a the hrpdb PDB:

CONNECT aud_admin@hrpdb
Enter password: password
Connected.

BEGIN
 DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
  AUDIT_TRAIL_PURGE_NAME      => 'Audit_Trail_PJ',
  AUDIT_TRAIL_STATUS_VALUE    => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

In this example:

  • AUDIT_TRAIL_PURGE_NAME: Specifies a purge job called Audit_Trail_PJ. To find existing purge jobs, query the JOB_NAME and JOB_STATUS columns of the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.

  • AUDIT_TRAIL_STATUS_VALUE: Enter one of the following properties:

    • DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE: Enables the specified purge job.

    • DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE: Disables the specified 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. The interval setting that is used in the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure takes precedence over this setting.

In a multitenant environment, where you run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL procedure depends on the location of the purge job, which is determined by the CONTAINER parameter of the DBMS_MGMT.CREATE_PURGE_JOB procedure. If you had set CONTAINER to CONTAINER_ALL, then the purge job exists in the root, so you must run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS procedure from the root. If you had set CONTAINER to CONTAINER_CURRENT, then you must run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL procedure from the PDB in which it was created.

For example, assuming that you had created the purge job in the hrpdb PDB:

CONNECT aud_admin@hrpdb
Enter password: password
Connected.

BEGIN
 DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
  AUDIT_TRAIL_PURGE_NAME       => 'Audit_Trail_PJ',
  AUDIT_TRAIL_INTERVAL_VALUE   => 24);
END;
/

In this example:

  • AUDIT_TRAIL_PURGE_NAME: Specifies the name of the audit trail purge job. To find a list of existing purge jobs, query the JOB_NAME and JOB_STATUS columns of the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.

  • AUDIT_TRAIL_INTERVAL_VALUE: Updates the default hourly interval set by the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure. Enter a value between 1 and 999. The timing begins when you run the purge job.

Deleting an Audit Trail Purge Job

To delete an audit trail purge job, use the DBMS_AUDIT_MGMT.DROP_PURGE_JOB PL/SQL procedure. To find existing purge jobs, query the JOB_NAME and JOB_STATUS columns of the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view.

In a multitenant environment, where you run the DBMS_AUDIT_MGMT.DROP_PURGE_JOB procedure depends on the location of the purge job, which is determined by the CONTAINER parameter of the DBMS_MGMT.CREATE_PURGE_JOB procedure. If you had set CONTAINER to CONTAINER_ALL, then the purge job exists in the root, so you must run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS procedure from the root. If you had set CONTAINER to CONTAINER_CURRENT, then you must run the DBMS_AUDIT_MGMT.DROP_PURGE_JOB_INTERVAL procedure from the PDB in which it was created.

For example, assuming that you had created the purge job in the hrpdb PDB:

CONNECT aud_admin@hrpdb
Enter password: password
Connected.

BEGIN
 DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
  AUDIT_TRAIL_PURGE_NAME  => 'Audit_Trail_PJ');
END;
/

Clearing the Archive Timestamp Setting

To clear the archive timestamp setting, use the DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP PL/SQL procedure. You can find a history of audit trail log cleanup by querying the DBA_AUDIT_MGMT_CLEAN_EVENTS data dictionary view if the database is read-write. For read-only databases, you can find this history in the alert log of the database instance.

For example, assuming that you had created the purge job in the hrpdb PDB:

CONNECT aud_admin@hrpdb
Enter password: password
Connected.

BEGIN
  DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   CONTAINER            =>  DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

In this example:

  • AUDIT_TRAIL_TYPE is set for the unified audit trail. If the AUDIT_TRAIL_TYPE property is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS or DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, then you cannot set RAC_INSTANCE_NUMBER to 0. You can omit the RAC_INSTANCE_NUMBER setting if you set AUDIT_TRAIL_TYPE to DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED.

  • CONTAINER: Applies the timestamp to a multitenant environment. DBMS_AUDIT_MGMT.CONTAINER_CURRENT specifies the local PDB; DBMS_AUDIT_MGMT.CONTAINER_ALL applies to all databases.

Example: Directly Calling a Unified Audit Trail Purge Operation

The pseudo code in Example 23-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.

Example 23-1 Directly Calling a Database Audit Trail Purge Operation

-- 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 */ 
/

Note:

If you want to modify this example for other audit trail types, be aware that additional steps may be required. For more information, see the Oracle Database 11g Release 2 (11.2) version of Oracle Database Security Guide, which is available from the following documentation library:

http://www.oracle.com/pls/db112/homepage

Audit Trail Management Data Dictionary Views

Table 23-1 lists data dictionary views that provide information about audit trail management settings.

Table 23-2 Views That Display Information about Audit Trail Management Settings

View Description

DBA_AUDIT_MGMT_CLEAN_EVENTS

Displays the history of purge events of the traditional (that is, non-unified) audit trails. Periodically, as a user who has been granted the AUDIT_ADMIN role, you should delete the contents of this view so that it does not grow too large. For example:

DELETE FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;

This view applies to read-write databases only. For read-only databases, a history of purge events is in the alert log.

DBA_AUDIT_MGMT_CLEANUP_JOBS

Displays the currently configured audit trail purge jobs

DBA_AUDIT_MGMT_CONFIG_PARAMS

Displays the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package

DBA_AUDIT_MGMT_LAST_ARCH_TS

Displays the last archive timestamps that have set for audit trail purges