Move Oracle DB Audit Trails To A New Tablespace

There are times when it is necessary to move Oracle DB audit trails to a new table space.  Auditing is the monitoring and recording of selected user database actions. In standard auditing, you use initialization parameters and the AUDIT and NOAUDIT SQL statements to audit SQL statements, privileges, and schema objects, and network and multitier activities.

There are also activities that Oracle Database always audits, regardless of whether auditing is enabled. These activities are administrative privilege connections, database startups, and database shutdowns.

Another type of auditing is fine-grained auditing. Fine-grained auditing enables you to audit at the most granular level, data access, and actions based on content, using Boolean measurement, such as value > 1000.  Fine-grained auditing can be used to audit activities based on access to or changes in a column. You can create security policies to trigger auditing when someone accesses or alters specified elements in an Oracle database, including the contents within a specified object.

Oracle Database records audit activities in audit records. Audit records provide information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.

When you use standard auditing, Oracle Database writes the audit records to either to DBA_AUDIT_TRAIL (the SYS.AUD$ table and the SYS. FGA_LOG$ table), the operating system audit trail, or to the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.  In addition, the actions performed by administrators are recorded in the syslog audit trail when the AUDIT_SYSLOG_LEVEL initialization parameter is set.

Auditing is configured through the setting of the AUDIT_TRAIL initialization parameter.  The value of this parameter is set to a value of “DB” by default.  This enables database auditing and directs standard audit records to the database audit trail (SYS.AUD$), except for records that are always written to the operating system audit trail.  Setting the parameter value to “DB, EXTENDED” performs all actions of the AUDIT_TRAIL=DB setting and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.

Since the audit trail tables are owned by the SYS schema, by default these tables are located in the SYSTEM tablespace.  As you might imagine, enabling standard or fine-grained auditing would cause a significant growth in the SYSTEM tablespace.  Most shops do not desire that type of activity to happen in the SYSTEM tablespace.  The solution is to move the audit trail tables to their own tablespace.  This can be easily accomplished with the DBMS_AUDIT_MGMT package.

Oracle introduced this level of auditing with the Audit Vault database feature in Oracle 11g Release 2.  As part of this feature, Oracle included the DBMS_AUDIT_MGMT package. Its features include the purge of audit records and can be used to move the audit tables AUD$ and FGA_LOG$ to another tablespace.  Currently, the use of the DBMS_AUDIT_MGMT is not supported for pre 11gR2(11.2.0.1) instance which is not an Audit Vault source database.

To move the Oracle Audit Trail to a new a new tablespace using the DBMS_AUDIT_MGMT package, follow these steps:

  • Check the current tablespace of the audit trail tables.

SQL> SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name IN (‘AUD, ‘FGA_LOG)
ORDER BY table_name; 

OWNER           TABLE_NAME      TABLESPACE_NAME

————— ————— ——————————

SYS             AUD$            SYSTEM

SYS             FGA_LOG$        SYSTEM

2 rows selected.

SQL> SELECT owner, index_name, index_type, table_owner, table_name, tablespace_name
FROM dba_indexes
WHERE table_name IN (‘AUD, ‘FGA_LOG)
ORDER BY table_name;

OWNER    INDEX_NAME                  INDEX_TYPE   TABLE_OWNER  TABLE_NAME  TABLESPACE_NAME

——– ————————— ———— ———— ———– —————–

SYS      SYS_IL0000000501C00041$$    LOB          SYS          AUD$        SYSTEM

SYS      SYS_IL0000000501C00040$$    LOB          SYS          AUD$        SYSTEM

SYS      SYS_IL0000000511C00028$$    LOB          SYS          FGA_LOG$    SYSTEM

SYS      SYS_IL0000000511C00013$$    LOB          SYS          FGA_LOG$    SYSTEM

4 rows selected.

  • Create a new tablespace and size it large enough to hold the audit data:

SQL> CREATE tablespace “SYSAUD” datafile size 1G autoextend on;

Tablespace created.

  • Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.

— Move the SYS.AUD$ table

SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘SYSAUD’);

PL/SQL procedure successfully completed.

— Move the SYS.FGA_LOG$ table

SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => ‘SYSAUD’);

PL/SQL procedure successfully completed.

  • Check whether the tables were moved successfully.

SQL> SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name IN (‘AUD, ‘FGA_LOG)
ORDER BY table_name; 

OWNER           TABLE_NAME      TABLESPACE_NAME

————— ————— ——————————

SYS             AUD$            SYSAUD

SYS             FGA_LOG$        SYSAUD

2 rows selected.

SQL> SELECT owner, index_name, index_type, table_owner, table_name, tablespace_name
FROM dba_indexes
WHERE table_name IN (‘AUD, ‘FGA_LOG)
ORDER BY table_name;

OWNER    INDEX_NAME                  INDEX_TYPE   TABLE_OWNER  TABLE_NAME  TABLESPACE_NAME

——– ————————— ———— ———— ———– —————–

SYS      SYS_IL0000000501C00041$$    LOB          SYS          AUD$        SYSAUD

SYS      SYS_IL0000000501C00040$$    LOB          SYS          AUD$        SYSAUD

SYS      SYS_IL0000000511C00028$$    LOB          SYS          FGA_LOG$    SYSAUD

SYS      SYS_IL0000000511C00013$$    LOB          SYS          FGA_LOG$    SYSAUD

4 rows selected.

The DBMS_AUDIT_MGMT package can be used to maintain all audit data, whether it is at the OS level or the database level.  See the Database PL/SQL Packages and Types Reference for Oracle 11g R2 / Oracle 12c for more information.

2018-01-18T20:34:36+00:00 January 18th, 2018|Database|Comments Off on Move Oracle DB Audit Trails To A New Tablespace