How To Move The DB Audit Trails To A New Tablespace

xychong123發表於2016-12-15
AI

In this Document

Goal
Fix
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 20-FEB-2013***

GOAL

How to move the database audit trail tables(  SYS.AUD$ & SYS.FGA_LOG$) to another tablespace using DBMS_AUDIT_MGMT?

FIX

1) Check the current tablespace of the audit trail tables: 

CONN / AS SYSDBA

SQL> SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; 2

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           SYSAUX
FGA_LOG$                       SYSAUX


2) Check the current size of two tables: 

select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');

SEGMENT_NAME         SIZE_IN_MEGABYTES
-------------------  -----------------------------
AUD$                 12
FGA_LOG$             .0625





3) Create a new tablespace and make sure that it's size is large enough for the tables that will be moved: 

SQL>create tablespace audit_tbs datafile '/u01/app/oracle/oradata/d1v11202/audit_tbs1.dbf' size 100M autoextend on; 

Tablespace created.




4) Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.


SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.



4)  Check  whether the tables were moved successfully: 

SQL> 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_TBS
FGA_LOG$                 AUDIT_TBS



NOTE :
=======
The requirement for the introduction of this package came from Audit Vault. Currently it is not supported to use the DBMS_AUDIT_MGMT package on a pre 11gR2(11.2.0.1) instance which is not an Audit Vault source database. Its features include the purge of audit records and can be used to move the audit tables AUD$ and FGA_LOG$ to another tablespace.

Audit Trails with dbms_audit_mgmt, which has been officially introduced/supported in Oracle 11g R2.

REFERENCES

NOTE:1299033.1 - Master Note For Oracle Database Auditing
NOTE:731908.1 - New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information
NOTE:804624.1 - Known Issues When Using: DBMS_AUDIT_MGMT

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20747382/viewspace-2130683/，如需轉載，請註明出處，否則將追究法律責任。

相關文章