How do I avoid "ORA-01653: unable to extend table SYS.AUD$ or FGA_LOG$ in tablespace SYSTEM" errors when using Amazon RDS for Oracle?

Last updated: 2019-04-18

I enabled the Oracle audit feature on my Amazon Relational Database Service (Amazon RDS) database to log events on the database audit tables. But the SYSTEM tablespace is growing more than I expected, or I received the following error message:

"ORA-01653: unable to extend table..." for SYS.AUD$ or FGA_LOG$ in the tablespace SYSTEM."

How do I resolve this error?

Short Description

The ORA-01653 error is caused by a lack of free space on the SYS.AUD table in the SYSTEM tablespace, which is where the Oracle audit tables AUD$ and FGA_LOG$ are defined by default. The error occurs when the table attempts to auto-extend to accommodate additional data, but the table is prevented from expanding. For more information about the auditing actions that can cause full tablespace, see the Oracle documentation for Configuring and Administering Auditing.

Note: AUDIT_SYS_OPERATIONS is enabled by default on Amazon RDS for Oracle, and it cannot be disabled.

Resolution

To resolve this issue, consider one or more of the following solutions:

Migrate the AUDIT tables to a dedicated tablespace

Audit tables grow indefinitely until either a manual or automated purging routine using DELETE statements cleans up the records. Because free space is reclaimed only to the affected segment, purging tables in the SYSTEM tablespace results in no functional change to the size of the AUD$ and FGA_LOG$ tables, which can cause fragmentation issues.

Migrating AUDIT tables from the SYSTEM tablespace to a different tablespace allows you more control over the size of your DB instance's audit tables. You can migrate the tables yourself, or if you're running Oracle 11g R2 on your RDS instance, you can use the DBMS_AUDIT_MGMT package.

To migrate the table manually, follow these steps:

1.    Create a new tablespace by running a command similar to the following:

CREATE TABLESPACE <name>;

Note: Creating a tablespace with AUTOEXTENSIBLE segment space management (enabled by default for new tablespaces) allows you to easily resize the table later.

2.    Migrate your AUD$ table to the new tablespace by running commands similar to the following:

BEGIN
SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => '<name>');
END;
/

3.    Migrate your FGA_LOG$ table to the new tablespace by running commands similar to the following:

BEGIN
SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'BIGFILETEST');
END;
/

Note: Depending on the size of your table, this operation can take time to complete. If you have large or frequently accessed tables, you might also encounter deadlock errors. To resolve deadlock errors, temporarily set DB_AUDIT_TRAIL to NONE, restart the database, and try the operations again. You can revert DB_AUDIT_TRAIL after the process is completed.

Follow best practices for auditing tables

To maintain manageable audit tables, mitigate the space consumed by the audit tables. Also, minimize the occurrence of the ORA-01653 error by auditing the minimum set of pertinent actions. Finally, archive and purge audit trail records often. For more information, see Guidelines for Auditing Typical Database Activity in the Oracle Database documentation.

Turn AUTOEXTEND on for the SYSTEM tablespace

For DB instances that run Oracle, AUTOEXTEND for the SYSTEM tablespace is set to ON by default. If you turn AUTOEXTEND off manually, then your database's ability to provision additional space for the SYSTEM tablespace can be affected. You can enable this setting for the SYSTEM tablespace by running the ALTER TABLESPACE command:

ALTER TABLESPACE SYSTEM AUTOEXTEND ON;

For more information, see Oracle's documentation for Changing Datafile Size.

Set MAXSIZE for the SYSTEM tablespace to a larger value

If the SYSTEM tablespace settings for MAXBYTES or MAXBLOCKS are set too low, set MAXSIZE for the SYSTEM tablespace to a larger value.

You can also remove limits on the maximum size of the SYSTEM tablespace by running a command similar to the following:

ALTER TABLESPACE SYSTEM AUTOEXTEND ON MAXSIZE UNLIMITED;