11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT

11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Virag Sharma

In earlier version there is no standard way to change AUDIT tables tablespace.
IN Oracle 11g R2 ( Also included in 11.1.0.7 and 10.2.0.5 ( Need to check)) , you can change audit table (SYS.AUD$ and SYS.FGA_LOG$) tablespace using DBMS_AUDIT_MGMT


Not ONLY you can change audits table tablespace , now you can periodically deleting the audit trail records using CLEAN_AUDIT_TRAIL (new in 11.2 ) Procedure.

So Now it is official , that you can change AUD$ table tablespace and purge :-) .

In Below given example , am trying to change tablespace for AUD$

Checking current tablespace from AUD$

SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$'; TABLESPACE_NAME -------------------------------------------------------------------------------- SYSTEM

Changing Tablespace from SYSTEM to SYSAUX for AUD$

SQL> BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX'); END; / PL/SQL procedure successfully completed.

Checking changed Tablespace

SQL> select TABLESPACE_NAME from dba_segments where SEGMENT_NAME='AUD$'; TABLESPACE_NAME -------------------------------------------------------------------------------- SYSAUX

AUDIT_TRAIL_TYPE: Refers to the database audit trail type. Enter one of the following values:

1. DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
2. DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
3. DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables.
   

AUDIT_TRAIL_LOCATION_VALUE: Specifies the NEW destination tablespace.

Not ONLY you can change audits table tablespace , now you can periodically deleting the audit trail records/xml/.aud files etc using CLEAN_AUDIT_TRAIL Procedure.

STEPS for Purging AUDIT TRAIL

# Check initialization BEGIN IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN dbms_output.put_line('CLEANUP NOT INITIALIZED' ); ELSE dbms_output.put_line('CLEANUP INITIALIZED' ); END IF; END; # Set initialization BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, DEFAULT_CLEANUP_INTERVAL => 6 ); END; # Set Last Audit Time stamp SQL> desc DBA_AUDIT_MGMT_LAST_ARCH_TS Name Null? Type ----------------------------------------- -------- ---------------------------- AUDIT_TRAIL VARCHAR2(20) RAC_INSTANCE NOT NULL NUMBER LAST_ARCHIVE_TS TIMESTAMP(6) WITH TIME ZONE SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS; # Check is Last Audit Time stamp set or not BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, LAST_ARCHIVE_TIME => sysdate -30 <------ want to delete aud file older then 30 days RAC_INSTANCE_NUMBER => 1 ); END; # For non RAC don't use "RAC_INSTANCE_NUMBER =>" # If RAC system having 4 node then run above command 4 time # with RAC_INSTANCE_NUMBER 1 , 2, 3 ,4 # Manual Purge BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, USE_LAST_ARCH_TIMESTAMP => TRUE); END; # # If USE_LAST_ARCH_TIMESTAMP is False , it purge all audit trail # # Here we used DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL => # All audit trail types. This includes the standard database audit trail # (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail, # and XML audit trail. More details are given below #AUDIT_TRAIL_ALL => All audit trail types. This includes the standard database audit trail (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail, and XML audit trail. #AUDIT_TRAIL_AUD_STD => Standard database audit records in the SYS.AUD$ table #AUDIT_TRAIL_DB_STD => Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records #AUDIT_TRAIL_FGA_STD => Standard database fine-grained auditing (FGA) records in the SYS.FGA_LOG$ table #AUDIT_TRAIL_FILES => Both operating system (OS) and XML audit trails #AUDIT_TRAIL_OS => Operating system audit trail. This refers to the audit records stored in operating system files. #AUDIT_TRAIL_XML => XML audit trail. This refers to the audit records stored in XML files.

Refrence

DBMS_AUDIT_MGMT ( Oracle Documentation 11.2 )

More Post on Oracle RDBMS Database 11g R2 ( Release 2 )

Oracle 11g Release 2 (11.2 ) New Features : SCAN - Single Client Access Name
11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT
Oracle Database 11g Release 2 New Features : Edition based redefination