Automated Audit_Trail Purging

[wlourens]

Hi

I implemented the the impressive steps in article: (http://www.oracle-base.com/articles/11g ... -11gr2.php) to automate the Audit_Trail Purging for all my audit_trails.

I now refer to the note at the bottom: "If purge jobs use the last archived timestamp and you do not manually move this timestamp forward, the job will run and have nothing to purge."

We are managing a large number of databases, and I was hoping for a way to automatically clear the audit trails every xx days. It will be a tedious process to have to set the last archived timestamp manually everytime. I do not need to archive audit records, I only need them to be purged after every xx (example 90) days.

Will this be possible to automate with the oracle packages; can the "last archived timestamp" automatically be set to DATE - 90 days to allow for Automatic audit trail purging?

Thanks

[Tim...]

Hi.

Sure thing. The DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP procedure can be scheduled to run and set the date automatically. I've added an example of this to the bottom on this section of the article.

http://www.oracle-base.com/articles/11g ... ed_purging

Cheers

Tim...

[wlourens]

Thanks, Awesome stuff !! ! !!

[Tim...]

[wlourens]

Hi Tim,

After I implemented the "audit_last_archive_time" job as below:

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, TRUNC(SYSTIMESTAMP)-120); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Automatically set audit last archive time.');
END;

/

The following error showed up in my alert.log:

######################################
### ###
### xxxxxxxx - XXX ###
### ###
######################################
ORA-12012: error on auto execute of job "SYS"."AUDIT_LAST_ARCHIVE_TIME"
ORA-46250: Invalid value for argument 'AUDIT_TRAIL_TYPE'
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2159

It seems to be related to "DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL" that this oracle Release 11.2.0.2.0 is not accepting as a parameter:

SQL> BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, --Supposed to be all audit trail types.
last_archive_time => SYSTIMESTAMP-120);
END;
/ 2 3 4 5 6
BEGIN
*
ERROR at line 1:
ORA-46250: Invalid value for argument 'AUDIT_TRAIL_TYPE'
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2159
ORA-06512: at line 2

If I set the AUDIT_TRAIL_TYPES separately, it works:

SQL> BEGIN
2 DBMS_AUDIT_MGMT.set_last_archive_timestamp(
3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, --Standard database audit records in the SYS.AUD$ table
4 last_archive_time => SYSTIMESTAMP-120);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
2 3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, --(FGA) records in the SYS.FGA_LOG$ table
4 last_archive_time => SYSTIMESTAMP-120);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, --Operating system audit trail
3 4 last_archive_time => SYSTIMESTAMP-120);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_AUDIT_MGMT.set_last_archive_timestamp(
3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, --XML audit trail
4 last_archive_time => SYSTIMESTAMP-120);
END;
5 6 /

PL/SQL procedure successfully completed.

Seems like we will might need separate jobs for different Audit Trail Types, if AUDIT_TRAIL_ALL is not accepted?

[wlourens]

http://docs.oracle.com/cd/E11882_01/app ... m#BABBHHGC

SET_LAST_ARCHIVE_TIMESTAMP Procedure

The following audit_trail_type values for valid for this procedure:

AUDIT_TRAIL_AUD_STD
AUDIT_TRAIL_FGA_STD
AUDIT_TRAIL_OS
AUDIT_TRAIL_XML

[Tim...]

Hi.

Sorry. My bad. I saw it was a valid constant and assumed it would do all at once. Bad assumption.

You can write a single job that contains multiple calls for all the audit trails in it, so it still gets done as a single job.

Cheers

Tim...

[wlourens]

Thanks!

Please remember to also update the website: http://www.oracle-base.com/articles/11g ... -11gr2.php

I guess I could build one job for both set_last_archive_timestamp and then purging

[Tim...]

Hi.

That is corrected. It now defines a single job for all 4 audit trails.

Cheers

Tim...

[wlourens]

!!

I now define a single job for all 4 audit trials and purging

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-120);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-120);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-120);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-120);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_ALL, use_last_arch_timestamp => TRUE);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Automatically set audit last archive time and then purge all audit trails.');
END;
/

[Tim...]

You probably want to remove the autojob if you created it. Leaving it it won't cause any problems, but it is unnecessary.

Cheers

Tim...