Automated Audit_Trail Purging

All posts relating to Oracle database administration.

Moderator: Tim...

Automated Audit_Trail Purging

Postby wlourens » Sat Mar 09, 2013 9:28 pm

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
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Automated Audit_Trail Purging

Postby Tim... » Sun Mar 10, 2013 9:39 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Automated Audit_Trail Purging

Postby wlourens » Sun Mar 10, 2013 12:18 pm

Thanks, Awesome stuff !! :D ! 8) !!
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Automated Audit_Trail Purging

Postby Tim... » Sun Mar 10, 2013 1:04 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Automated Audit_Trail Purging

Postby wlourens » Tue Mar 12, 2013 10:25 am

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
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Automated Audit_Trail Purging

Postby wlourens » Tue Mar 12, 2013 11:07 am

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



;)
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Automated Audit_Trail Purging

Postby Tim... » Tue Mar 12, 2013 12:17 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Automated Audit_Trail Purging

Postby wlourens » Wed Mar 13, 2013 1:04 pm

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 8) :)
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Automated Audit_Trail Purging

Postby Tim... » Wed Mar 13, 2013 2:14 pm

Hi.

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

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Automated Audit_Trail Purging

Postby wlourens » Thu Mar 14, 2013 9:56 am

8) !!

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

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;
/
wlourens
Member
 
Posts: 12
Joined: Sat Mar 09, 2013 8:58 pm

Re: Automated Audit_Trail Purging

Postby Tim... » Thu Mar 14, 2013 10:27 am

:)

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 5 guests