Scheduling an Automatic Purge Job for the Audit Trail

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 purge older audit records in an audit trail that was created before a specific time period.

Be aware that purging the audit trail, particularly a large one, can take a while to complete. Oracle recommends that you schedule the purge job at a time when the database is not busy. If the audit trail is considerably large, then the purge process can take a while to complete.

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.

Note: In addition, be aware that the jobs created by the DBMS_SCHEDULER PL/SQL package do not run 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: Ensure Online and Archive redo Log Sizes Are Tuned Appropriately

The purge process may generate additional redo logs.

You may consider skipping the step if you have turned off traditional auditing in the upgraded instance.

Ensure that the online and archive redo log sizes 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.

Related Topics

Step 2: 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 must record the timestamp of the audit records before you can archive them. You can set a timestamp for 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. Optionally, you can clear the archive timestamp setting.

If you are using Real Application Clusters, then use Network Time Protocol (NTP) to synchronize the time on each computer where you have installed an Oracle AI 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.

  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. Fnd the timestamp date, by querying the DBA_AUDIT_MGMT_LAST_ARCH_TS data dictionary view.

    The last archived timestamp is set automatically if you are using Oracle Audit Vault and Database Firewall or Oracle Data Safe after the audit record is collected. Later on, when the purge takes place, Oracle AI 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.

  3. 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:

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.

Related Topics

Step 3: Create and Schedule the Purge Job

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: