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:
- DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$.
- DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$.
- 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
Posts
