Introduction
It is not uncommon for businesses to have policies to retain audit data for several years, but then it ought to follow that that data should be purged at the end of the retention period. A regular purge process should be put in place, otherwise, audit data will accumulate. However, it is all too common for the purge to be deferred, often indefinitely, until it becomes a problem.
I have been working on a system that has built up 12 years of audit data. It was starting to consume significant space in the SYSAUX tablespace, and queries on the standard audit data had become slow. We noticed that Oracle Enterprise Manager was regularly querying this data looking for recent failed logins. This was performing so poorly that it had become a never-ending process.
This company has a stated requirement that audit data be retained for 7 years, but some can be purged after 2 years. However, within the last 7 years, this production database has had different database IDs due to its upgrade history.
Oracle delivers a PL/SQL package to manage audit purges. However, this utility will only purge the current database ID, and it will only purge all of that data by a date. It cannot selectively purge some data by other criteria. Therefore, a custom purge utility that extends that package was required. This post discusses that extension.
We introduced
- Regular weekly purge jobs for all forms of audit, for all DBIDs. They run every weekend when the system is quieter.
- This includes a custom purge job to delete some data after 2 years
General Recommendations
- Standard audit is deprecated from Oracle 19c and will be desupported in a future release. Therefore, Oracle recommends that customers migrate from standard audit to Unified Audit. Oracle provides a Traditional to Unified Audit Syntax Converter - Generate Unified Audit Policies from Current Traditional Audit Configuration (Doc ID 2909718.1). Note, that this will only migrate the audit configuration. The standard audit data that has built up will still need to be purged according to the retention policy until it is all gone.
- Should any future database maintenance/upgrade activity cause the database ID to change, a new purge timestamp will be required for the new database ID, and an additional purge job will be required for the new legacy database ID.
Overview
Standard audit and Unified Audit (introduced in 12c) can be configured to monitor certain operations by database users. Audit data is generally written to tables in the SYSAUX tablespace, but unified audit can be written to XML files outside the database.
- Standard audit data is written to SYS.AUD$. It is exposed via DBA_AUDIT_TRAIL. Oracle does not support partitioning this table, and users who have tried have reported errors.
- Unified audit data is written to AUDSYS.UNIFIED$AUD. It is exposed via UNIFIED_AUDIT_TRAIL. Unified Audit is enabled by default, so if you think you are only using standard audit, you are probably also writing some Unified Audit. This table is interval range partitioned. The default interval is calendar monthly, but can be altered.
Purging Audit
Audit Timestamps
Oracle's delivered audit management package DBMS_AUDIT_MGMT can purge various types of audit. Each audit purge works on an individual database ID, the current database ID by default. An audit purge timestamp is created for each type of audit, and for each DBID to be purged. This is defined as a fixed date and not a retention period. Audit data that is before the date is purged. The audit timestamps must be updated before each purge to maintain the desired retention period.
The first step is to identify the DBIDs. In my case, most of the data is standard audit. I will simply query the number of rows and the range of dates for each DBID in the audit data.
select dbid, count(*), min(timestamp), max(timestamp)
from dba_audit_trail
group by dbid
order by 3
/
DBID COUNT(*) MIN(TIMESTAMP) MAX(TIMESTAMP)
---------- ---------- ------------------- -------------------
1000000002 33606824 11/09/2012 00:06:42 20/08/2016 00:46:22
1000000003 241327475 20/08/2016 03:36:36 13/05/2023 04:47:20
1000000005 23320615 12/05/2023 23:54:18 06/01/2024 00:56:49
1000000006 134188 06/01/2024 01:05:29 04/03/2024 22:24:53
2000000008 282780 05/03/2024 03:39:39 02/05/2024 15:05:41
However, some unified audit is enabled by default, so both types of audit have to be purged.
select dbid, count(*), min(event_timestamp), max(event_timestamp)
from unified_audit_trail
group by dbid
order by 3
/
DBID COUNT(*) MIN(EVENT_TIMESTAMP MAX(EVENT_TIMESTAMP
---------- ---------- ------------------- -------------------
1000000004 28323 21/04/2023 10:09:39 21/04/2023 12:00:51
1000000005 1434181 12/05/2023 22:44:47 06/01/2024 00:56:49
1000000006 1700032 06/01/2024 01:05:29 04/03/2024 22:02:29
2000000008 263038 05/03/2024 03:12:27 07/05/2024 16:39:41
The following PL/SQL creates an audit timestamp for each database ID, for each type of audit. The list of database IDs obtained above was then hard-coded in the following PL/SQL block. Doing this dynamically can involve a long query on the audit data, so it is easier to hard code them.
If a database ID is not specified to DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP, a control record for the current database ID is created. The audit timestamp is set to 7 years ago because our policy is to purge any audit data older than 7 years. Later, a database scheduler job will be created for each timestamp defined.
At the time of writing, it will be several years until the Unified Audit data is old enough to be purged, but if the purge jobs are not created now, nobody will remember to add them later!
declare
k_standard_retention_years CONSTANT INTEGER NOT NULL := 7;
begin
FOR i IN(
with d (dbid) as (
select TO_NUMBER(NULL) from dual
union select 1000000001 from dual
union select 1000000002 from dual
union select 1000000003 from dual
union select 1000000004 from dual
union select 1000000005 from dual
union select 1000000006 from dual
)
select dbid from d
) LOOP
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
,last_archive_time => ADD_MONTHS(sysdate,-12*k_standard_retention_years)
,database_id => i.dbid
);
END LOOP;
FOR i IN(
with d (dbid) as (
select TO_NUMBER(NULL) from dual
union select 1000000004 from dual --2023 unified only
union select 1000000005 from dual --2023-24 --current prod +unified
union select 1000000006 from dual --2024 +unified
--union select 2000000008 from dual --2024 +unified --current PRT
)
select dbid from d
) LOOP
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
,last_archive_time => ADD_MONTHS(sysdate,-12*k_standard_retention_years)
,database_id => i.dbid
);
end loop;
end;
/
As old database IDs are completely purged their last archive timestamps can be removed from the audit purge configuration in the database with DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP and the corresponding database jobs can be dropped.
Should any future database maintenance/migration/upgrade activity cause the database ID to change again, new timestamps must be set up for the new current database ID, and corresponding scheduler purge jobs must be created.
Custom Audit Purge Management Package
A custom package procedure XX_CUSTOM_AUDIT_PURGE has been created. It is available on GitHub. The package is deliberately created to be owned by the AUDSYS user that is used to run the default audit purge jobs. That account is delivered by Oracle and is locked. It should not be unlocked. Therefore, a SYSDBA privileged account must deploy the package.
This package contains various procedures
- INIT_AUDIT_PURGE
- initialises the standard and fine-grained audit purges,
- creates a job resource constraint so the scheduler only runs one purge process at a time, and
- creates a job class to assign the job to a resource plan consumer group so that the purge job runs at low priority.
- UPDATE_AUDIT_PURGE_TS updates the audit purge timestamps to 7 years ago, thus maintaining a rolling 7-year purge.
- CUSTOM_PURGE performs a limited purge of some audit data after 2 years. Exactly which audit actions are to be purged is hard-coded in this procedure.
- CREATE_AUDIT_PURGE_JOBS uses DBMS_AUDIT_MGMT.CREATE_PURGE_JOB to create the various scheduler jobs to run the Oracle-delivered purge procedure DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL for the various audits to be purged.
- However, this procedure alters the jobs to control their scheduler, concurrency and resource manager consumer group.
- CREATE_CUSTOM_PURGE_JOB creates a scheduler job to execute the CUSTOM_PURGE procedure.
- CREATE_UPDATE_AUDIT_PURGE_TS_JOB creates a job on the database scheduler to run UPDATE_AUDIT_PURGE_TS.
Some constants and defaults have been hard-coded in the package including
- The 2-year and 7-year purge rules.
- Resource consumer group of the purge jobs
- Job Class for purge jobs to which resource consumer group is assigned.
- Number of rows to be deleted between commits during custom delete (2-year purge).
The audit purge requirements are unlikely to change, and if they do a code change is required to implement the change that must be installed by the core DBA. I felt that this approach was easier and more secure than creating and controlling access to metadata.
After an initial large purge to clear the backlog, we find that these jobs only run for a few minutes each. By scheduling them to run regularly, we maintain a relatively steady data volume.
Reclaiming Space after Initial Large Purge
The audit purge is no more than a simple delete. Once deleted (and committed) the space freed up can be used by other rows of audit data, but space is not released from the audit tables back to the SYSAUX tablespace.
The only supported way to recover space from the purged audit tables is to rebuild them by moving them to a different tablespace, and if necessary, moving them back using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOC procedure again.
Create a tablespace SYSAUX_AUD. It should be created as a BIGFILE tablespace as SYSAUX is also a BIGFILE tablespace. Otherwise, in our case, it would have needed at least 3 data files (so it could expand to 96G).
The following PL/SQL block creates a database scheduler job for each call to this procedure so that it can run in the background.
- This time the jobs are one-time execution jobs that will be auto-dropped on completion.
- The same resource constraint XX_PURGE_RESOURCE, as used by the other purge jobs, ensures that it will not run concurrently with any of those jobs (nor vice versa).
- If the job start date is not set, the job will run as soon as it is enabled and no other job using the same resource is running.
DECLARE
TYPE t_aud_types IS TABLE OF VARCHAR2(8);
a_aud_types t_aud_types;
l_job_name VARCHAR2(30);
l_tablespace_name VARCHAR2(30) := 'SYSAUX_AUD';
e_job_does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(e_job_does_not_exist, -27475);
BEGIN
a_aud_types := t_aud_types('AUD_STD','UNIFIED') /*FGA_STD deliberately excluded*/;
FOR i IN a_aud_types.FIRST..a_aud_types.LAST
LOOP
l_job_name := 'XX_AUDIT_TRAIL_LOC_'||a_aud_types(i);
BEGIN
dbms_scheduler.drop_job(job_name => l_job_name);
EXCEPTION WHEN e_job_does_not_exist THEN NULL;
END;
dbms_scheduler.create_job
(job_name => l_job_name
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN dbms_audit_mgmt.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_'||a_aud_types(i)||',audit_trail_location_value => '''||l_tablespace_name||'''); END;'
,start_date => SYSTIMESTAMP at time zone ('US/Eastern')
,job_class => 'XX_PURGE_CLASS'
,auto_drop => TRUE
,enabled => FALSE);
DBMS_SCHEDULER.set_resource_constraint --Only one purge job can run at any one time
(object_name => l_job_name
,resource_name => 'AUDSYS.XX_PURGE_RESOURCE'
,units => 1);
dbms_scheduler.enable(l_job_name);
END LOOP;
END;
/
- NB: I specified the time zone for the database job because I am working in a different time zone to the database!
We have found that due to a bug, the unified audit table is not relocated to the new tablespace. It is expected that this will be fixed in version 23ai.
Validate/Reinstate Privileges
Using SET_AUDIT_TRAIL_LOCATION will strip off any explicitly granted privileges on the audit tables. Ensure privileges on the standard audit tables are intact. Otherwise, the custom purge will fail.
column grantee format a8
column owner format a8
column table_name format a20
column privilege format a20
column grantor format a8
select * from dba_tab_privs
where (owner,table_name) IN(('SYS','FGA_LOG$'),('SYS','AUD$'),('AUDSYS','AUD$UNIFIED'))
/
Minimum expected output
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INH
-------- -------- -------------------- -------- -------------------- --- --- --- ------------------------ ---
AUDSYS SYS AUD$ SYS DELETE NO NO NO TABLE NO
AUDSYS SYS AUD$ SYS SELECT NO NO NO TABLE NO
TL;DR
If you collect audit data, then you should create a retention policy and schedule purge jobs to delete it after the retention period expires. It is not unreasonable to keep audit data for several years. The database ID may change in that time due to upgrade or migration. Purge processes are also required for each legacy DBID.
After an initial large purge reclaims space by relocating the Standard Audit table, but remember to reinstate any explicitly granted privileges.
Unified Audit cannot be relocated until a bug is resolved in 23ai.
The performance of purge and query of standard audit will improve after doing this.
@go-faster.co.uk on BlueSky
Posts
