Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

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

Comments are closed.