How to move Audit and FGA Log Tables in 11g R2 +

The DBMS_AUDIT_MGMT package is installed by default in Oracle 11.2, and in patch sets 10.2.0.5 and 11.1.0.7 but has also been made available as a separately install-able patch for other versions. Move AUD$ and FGA_LOG to TOOLS  SQL>; BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'TOOLS'); END; / -- Previous verions use DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, to avoid some internal  issues.  SQL>; BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'TOOLS'); END; / Ensure the index are valid after this  And Grant GRANT DELETE ON SYS.AUD$ TO DELETE_CATALOG_ROLE; GRANT SELECT ON SYS.AUD$ TO SYSTEM; More if you are interested....  Auditing Features by DBMS_AUDIT_MGMT in Oracle Database 11g Release 2 Oracle 11g R2  eases the pain of  audit trail using the DBMS_AUDIT_MGMT package. Following are noteworthy  Moving the DB Audit Trail to a Non System Tablespace Controlling the Size and Age of the OS Audit Trail data  Purging Audit Trails data  Timestamp Management Manual and Automated Purge  This package is  also provided versions down to 10g Release 2.  See Oracle Support Note 731908.1. The SET_AUDIT_TRAIL_LOCATION procedure is to alter the location of the standard and/or fine-grained database audit trail. IAlteration of the OS audit trail is not curretnly possible.  The procedure accepts two parameters. AUDIT_TRAIL_TYPE: The type of audit trail DB/FGA/or btoh  AUDIT_TRAIL_LOCATION_VALUE: Target Tablespace  The AUDIT_TRAIL_TYPE parameter takes following values . DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$). DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$). DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails. See it in work :-  CONN / AS SYSDBA SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; TABLE_NAME                     TABLESPACE_NAME ------------------------------ ------------------------------ AUD$                           SYSTEM FGA_LOG$                       SYSTEM SQL> Now Move that to Tools  BEGIN   DBMS_AUDIT_MGMT.set_audit_trail_location(     audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,     audit_trail_location_value => 'TOOLS'); END; / PL/SQL procedure successfully completed. SQL> -->  Check now  SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; TABLE_NAME                     TABLESPACE_NAME ------------------------------ ------------------------------ AUD$                           TOOLS FGA_LOG$                       SYSTEM SQL> Next try  the fine-grained audit trail. BEGIN   DBMS_AUDIT_MGMT.set_audit_trail_location(     audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,     audit_trail_location_value => 'TOOLS'); END; / PL/SQL procedure successfully completed. 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$                           TOOLS FGA_LOG$                       TOOLS SQL> Next step do all it in one step  BEGIN   DBMS_AUDIT_MGMT.set_audit_trail_location(     audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,     audit_trail_location_value => 'SYSAUX'); END; / PL/SQL procedure successfully completed. SQL> Check now .  SELECT table_name, tablespace_name FROM   dba_tables WHERE  table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; TABLE_NAME                     TABLESPACE_NAME ------------------------------ ------------------------------ AUD$                           SYSAUX FGA_LOG$                       SYSAUX SQL> The time taken is directly propotional to the size of data currently in the audit trail tables, and the resources available on your system. The Size and Age of the OS Audit Trail The SET_AUDIT_TRAIL_PROPERTY procedure allows you to set the maximum size and/or age of the OS audit trail files.  The procedure accepts three parameters. AUDIT_TRAIL_TYPE: The type of audit trail to be modified (AUDIT_TRAIL_OS, AUDIT_TRAIL_XML or AUDIT_TRAIL_FILES). AUDIT_TRAIL_PROPERTY: The name of the property to be set (OS_FILE_MAX_SIZE or OS_FILE_MAX_AGE). AUDIT_TRAIL_PROPERTY_VALUE: The required value for the property. To check the current settings query the DBA_AUDIT_MGMT_CONFIG_PARAMS view. COLUMN parameter_name FORMAT A30 COLUMN parameter_value FORMAT A20 COLUMN audit_trail FORMAT A20 SELECT * FROM   dba_audit_mgmt_config_params WHERE  parameter_name LIKE 'AUDIT FILE MAX%'; PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL ------------------------------ -------------------- -------------------- AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL Defaluts setting are set for files will grow to a maximum of 102400 kilobytes, or files older than 5 days,  at which point a new file will be created.  How to change ti to maximum size of OS audit files to 15,000b. BEGIN   DBMS_AUDIT_MGMT.set_audit_trail_property(     audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,     audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,     audit_trail_property_value => 15000); END; / (Better leave it at default, for readability :)  bigger the file , you will have trouble opening , considering various OS in the organization. )  SELECT * FROM   dba_audit_mgmt_config_params WHERE  parameter_name LIKE 'AUDIT FILE MAX%'; PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL ------------------------------ -------------------- -------------------- AUDIT FILE MAX SIZE            15000                OS AUDIT TRAIL AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL SQL> Now change the age to the Maximum age of XML audit files to 10 days. BEGIN   DBMS_AUDIT_MGMT.set_audit_trail_property(     audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,     audit_trail_property       => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,     audit_trail_property_value => 10); END; / SELECT *  FROM   dba_audit_mgmt_config_params WHERE  parameter_name LIKE 'AUDIT FILE MAX%'; PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL ------------------------------ -------------------- -------------------- AUDIT FILE MAX SIZE            15000                OS AUDIT TRAIL AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL AUDIT FILE MAX AGE             10                   XML AUDIT TRAIL SQL> The CLEAR_AUDIT_TRAIL_PROPERTY procedure can be used to reset them to the default values or remove all restrictions  the USE_DEFAULT_VALUES parameter value to FALSE removes the restrictions  (lethal size )  so better use TRUE,  setting it to TRUE for factory reset.  BEGIN   DBMS_AUDIT_MGMT.clear_audit_trail_property(    audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,    audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,    use_default_values   => TRUE ); END; / SELECT * FROM   dba_audit_mgmt_config_params WHERE  parameter_name LIKE 'AUDIT FILE MAX%'; PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL ------------------------------ -------------------- -------------------- AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL AUDIT FILE MAX AGE             10                   XML AUDIT TRAIL SQL> Max age restriction is ok to do so that you dotn have too many files.   BEGIN   DBMS_AUDIT_MGMT.clear_audit_trail_property(    audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES,    audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,    use_default_values   => FALSE ); END; / SELECT * FROM   dba_audit_mgmt_config_params WHERE  parameter_name LIKE 'AUDIT FILE MAX%'; PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL ------------------------------ -------------------- -------------------- AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL AUDIT FILE MAX AGE             NOT SET              OS AUDIT TRAIL AUDIT FILE MAX AGE             NOT SET              XML AUDIT TRAIL SQL> SQL> Clean up or Purging Audit Trail Records You can manually delete records from the AUD$ and FGA_LOG$ tables and manually delete OS audit files from the file system, always   With DBMS_AUDIT_MGMT package comes with  safer features for this purpose  If  Oracle Audit Vault is used ,  then that should be used  manage your audit trail.  To be continued .....