11g Feature: Audit Trail Purging, No more custom scripts

Dear All,

Prior to 11g, in order to purge the audit trails we have to write a custom script and run in a cron or control M scheduler job or a DBMS_SCHEDULER, where in

From 11g 11.1.0.7 onwards Oracle provides a package DBMS_AUDIT_MGMT to manage the audit trails.

Additionally, This package can also be deployed in 10.2.0.3 databases via patch as mentioned in note 731908.1.

Example:-

Automated Audit trail Purging

The CREATE_PURGE_JOB procedure allows you to schedule a job to call the CLEAN_AUDIT_TRAIL procedure. When creating a purge job you can specify 4 parameters.

AUDIT_TRAIL_TYPE: The audit trail to be purged by the scheduled job (Constants) refere to above link for constants.

AUDIT_TRAIL_PURGE_INTERVAL: The interval in hours between purges.

AUDIT_TRAIL_PURGE_NAME: A name for the purge job.

USE_LAST_ARCH_TIMESTAMP: Set to FALSE to purge all records/files, or TRUE to only purge records/files older than the timestamp specified for the audit trail.

The following code schedules a purge of all audit trails every 24 hours. The resulting job is visible in the DBA_SCHEDULER_JOBS view.

BEGIN

DBMS_AUDIT_MGMT.create_purge_job(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL /* clears off OS/AUD$/xml trails */,

audit_trail_purge_interval => 24 /* hours */,

audit_trail_purge_name => 'PURGE_ALL_AUDIT_TRAILS',

use_last_arch_timestamp => TRUE);

END;

/

SQL> SELECT job_action FROM dba_scheduler_jobs WHERE job_name = 'PURGE_ALL_AUDIT_TRAILS';

JOB_ACTION

--------------------------------------------------------------------------------

BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE); END;

SQL>

Now moving your Audit table tablespace becomes easy. Moving the Database Audit Trail to a Different Tablespace

BEGIN

DBMS_AUDIT_MGMT.set_audit_trail_location(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => 'AUDIT_AUX'); -- tablespace name

END;

/

PL/SQL procedure successfully completed.

Note:- AUD_STD, AUD_FGA are standard values

SQL> -- Check locations.

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$')

ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

AUD$ AUDIT_AUX

FGA_LOG$ SYSTEM

The full blown package reference is here which is core package of Database Vault Management. However in order to use this , Database Vault license is required ? 🙁

http://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm

-Thanks

Geek DBA