You can purge all audit records, or audit records that were created before a specified timestamp, on a regularly scheduled basis.

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.

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

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.

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.

The purge process may generate additional redo logs.

• If necessary, tune online and archive redo log sizes to accommodate the additional records generated during the audit table purge process.
  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.

You must record the timestamp of the audit records before you can archive them.

• To find the timestamp date, query 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 archive timestamp.
  After you have timestamped the records, you are ready to archive them.

If you want to delete all of the audit trail, then you can bypass this step.

1. As a user who has been granted the AUDIT_ADMIN role, log into the 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 is used for the traditional standard audit trail table, AUD$. (This setting does not apply to read-only databases.)

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD is used for the traditional fine-grained audit trail table, FGA_LOG$. (This setting does not apply to read-only databases.)

     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS is used for the 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 is used for the 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. Do not enter a future system date or timestamp (for example, SYSDATE + 1, or a date in the future) for this value.

   • RAC_INSTANCE_NUMBER specifies the instance number for an Oracle RAC installation. This setting is not relevant for single instance databases. 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. The default is NULL. You can find the instance number for the current instance by issuing the SHOW PARAMETER INSTANCE_NUMBER command in SQL*Plus.

   • CONTAINER applies the timestamp to either the current PDB or to all PDBs. 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.

You can use the DBMS_AUDIT_MGMT PL/SQL package to 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 is used for the standard audit trail table, AUD$. (This setting does not apply to read-only databases.)

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

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD is used for both standard and fine-grained audit trail tables. (This setting does not apply to read-only databases.)

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS is used for the 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 is used for the XML operating system audit trail files.

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES is used for both operating system and XML audit trail files.

    • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL is used for 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 accepts 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 not have been deleted.

  • CONTAINER defines where to create the purge job in the multienant environment. 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.

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).

After you complete preparatory steps, you can use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to manually purge the audit trail.

1. Perform the following scheduling tasks:
   • If necessary, tune the online and archive redo log sizes.
   • Plan a timestamp and archive strategy.
   • Optionally, set an archive timestamp for the audit records.
2. Connect to the root or to the PDB 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. 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 either the current PDB or to all PDBs. DBMS_AUDIT_MGMT.CONTAINER_CURRENT specifies the current PDB; DBMS_AUDIT_MGMT.CONTAINER_ALL applies to all PDBs.

The DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS procedure enables or disables 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.
  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 accepts either 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.

You can set a default purge operation interval, in hours, that must pass before the next purge job operation takes place.

• To set the default audit trail purge job interval for a specific purge job, run the DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL procedure.
  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.

  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.

You can delete existing audit trail purge jobs.

• To delete an audit trail purge job, use the DBMS_AUDIT_MGMT.DROP_PURGE_JOB PL/SQL procedure.
  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;
  /

  Where you run the DBMS_AUDIT_MGMT.DROP_PURGE_JOB procedure in the multitenant environment 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.

The DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP procedure can clear the archive timestamp setting.

• To clear the archive timestamp setting, use the DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP PL/SQL procedure to specify the audit trail type.
  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 specifies where to perform the purge. DBMS_AUDIT_MGMT.CONTAINER_CURRENT specifies the local PDB; DBMS_AUDIT_MGMT.CONTAINER_ALL applies to all containers in the CDB environment.