Move audit Trail table to created-user Tablespace with Oracle database 11g & 12c

This article explains how to move the audit table from the default tablespace to a user-created tablespace with Oracle Database 11g and 12c.

Plan:

1. Intorduction:
2. Move audit table on Oracle database 11g:
3. Move audit table on Oracle database 12c:
4. conclusion

1. Introduction:

Database auditing helps meet your database security and compliance requirements. Audit records are written to database tables, operating system (OS) files, or XML files depending on the AUDIT_TRAIL initialization parameter setting, for more information with Oracle database 11g and Oracle database 12c.

2. Move audit table to user-created tablespace on Oracle database 11g:

After the administrator has enabled database auditing (with the AUDIT_TRAIL parameter) and specified auditing options, the database begins collecting audit information

The parameter values can be:

• NONE: Disables collection of audit records
• DB: Enables auditing with records stored in the database
• DB,EXTENDED: Populates SQLBIND and SQLTEXT columns
• XML: Enables auditing with records stored in XML format OS files
• XML,EXTENDED: Includes SQLBIND and SQLTEXT information
• OS: Enables auditing with records stored in the OS audit trail

 

While the AUDIT_TRAIL parameter equal to DB or DB,EXTENDED then all audit are recorded on the table SYS.AUD$ and the Faine Grained Audit are recorded on the table SYS.FGA_LOG$ on the tablespace SYSTEM.

To minimize the effect on performance in the SYSTEM tablespace, we can move the databaseaudit trail tables (SYS.AUD$ and SYS.FGA_LOG$) from the SYSTEM tablespace to a user-created tablespace by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

a. Check the Size of AUD$ table:

SQL> select SEGMENT_NAME,TABLESPACE_NAME,BLOCKS,BYTES/1024/1024 "Size Mb" from dba_segments where  SEGMENT_NAME = 'AUD$';

b. Creation of a tablespace to be the future audit tables tablespace destination:

Creation of a tablespace TBS_AUDIT:

c. Move SYS.AUD$ table t the tablepsace TBS_AUDIT:

SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,AUDIT_TRAIL_LOCATION_VALUE => 'TBS_AUDIT') ;

d. Check the NEW location of AUD$ table:

Note: The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure has the following parameters:

• AUDIT_TRAIL_TYPE: Specifies the database audit trail type. Valid values are:

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Indicates the standard audit trail table (SYS.AUD$)

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Indicates the fine-grained audit trail table (SYS.FGA_LOG$)

– DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Indicates the standard and finegrained audit trail tables

• AUDIT_TRAIL_LOCATION_VALUE: Specifies the destination tablespace

Note: Before executing the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure, you should ensure that the destination tablespace has adequate space to accommodate the audit trail tables.

3. Move audit tables on Oracle database 12c:

a. Check if the Unified audit trail is activated or not:

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
----------------------------------------------------------------
TRUE

b.Check the location of the Objects segments of the SCHEMA AUDSYS:

All Oracle Unified audit Trail are stored on the schema AUDSYS on the tablespace SYSAUX.

SQL> select SEGMENT_NAME,SEGMENT_SUBTYPE,SEGMENT_TYPE,BYTES/1024/1024,TABLESPACE_NAME  from dba_segments where OWNER = 'AUDSYS';

c. Creation of a policy to audit any action on HR.EMPLOYEES (to check the size of AUDSYS objects)

d. Move SYS.AUD$ table to the tablepsace TBS_AUDIT12c (with user SYS):

SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,AUDIT_TRAIL_LOCATION_VALUE => 'TBS_AUDIT12c');

e. Check the new Location:

After the move to the new tablespace, we can get a duplicate row with the same segments name but with the new tablespace.

SQL> select SEGMENT_NAME,SEGMENT_SUBTYPE,SEGMENT_TYPE,BYTES/1024/1024,TABLESPACE_NAME  from dba_segments where OWNER = 'AUDSYS' order by 1;

f. Create a Test of audit;

g. Purge the audit to the tables from memory (with SYS user):

h. Check the size again of the Objects:

SQL> select SEGMENT_NAME,SEGMENT_SUBTYPE,SEGMENT_TYPE,BYTES/1024/1024,TABLESPACE_NAME  from dba_segments where OWNER = 'AUDSYS' order by 1;

And again:

Note: The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure has the following parameters:

• AUDIT_TRAIL_TYPE: Specifies the database audit trail type. Valid values are:

– DBMS_AUDIT_MGMT. AUDIT_TRAIL_UNIFIED: Indicates the Audit trail unified 12c option

• AUDIT_TRAIL_LOCATION_VALUE: Specifies the destination tablespace

 

Note: Before executing the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure, you should ensure that the destination tablespace has adequate space to accommodate the audit trail tables.

 

4. Conclusion:

This article shows an example to move audit table from original tablespace to a user-created tablespace within Oracle database 11g and 12c.