How do I avoid SYSTEM errors when I use Amazon RDS for Oracle?

Short description

The ORA-01653 error is caused because there is a lack of free space on the SYS.AUD table in the SYSTEM tablespace. The error message includes the table name that's unable to grow and the tablespace name that's present. By default, SYS.AUD$ and FGA_LOG$ tables are stored in SYSTEM. With the default audit tablespace settings, there is no space for SYS.AUD$ or FGA_LOG$ to grow in the SYSTEM tablespace. The error message "ORA-01653" appears with the respective table name because the tablespace is full. For more information about the audit actions that can cause tablespace full, see What is auditing? on the Oracle website.

Note: By default AUDIT_SYS_OPERATIONS is activated on Amazon RDS for Oracle.

Resolution

Migrate the AUDIT tables to a dedicated tablespace

Audit tables grow indefinitely until either a manual or automated purge routine is completed. If delete statements are used to purge the records, then free space is reclaimed only to the affected segment. Delete statements can cause fragmentation issues.

To control the size of your Amazon RDS DB instance's audit tables, migrate AUDIT tables from the SYSTEM tablespace to a different tablespace. To migrate audit tables to a user-defined custom tablespace, package DBMS_AUDIT_MGMT in your Amazon RDS for Oracle instance. For more information, see Subprogram groups on the Oracle website.

To migrate the table, complete the following steps:

1. To create a new tablespace, run the following command. For more information, see CREATE TABLESPACE on the Oracle website.

   CREATE TABLESPACE <name>;

2. To migrate your AUD$ table to the new tablespace, run the following command:

   BEGINSYS.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. To migrate your FGA_LOG$ table to the new tablespace, run the following command:

   BEGINSYS.DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => '<name>');END;/

   Note: The size of your table affects the migration and takes time to complete. If you have large or frequently accessed tables, then you might receive deadlock errors. To resolve deadlock errors, temporarily set DB_AUDIT_TRAIL to NONE, restart the database, and then retry the operations. You can revert DB_AUDIT_TRAIL after the process completes.

Follow best practices for audit tables

Complete the following steps to maintain manageable audit tables:

1. Mitigate the space that's consumed by the audit tables.
2. Audit the minimum set of pertinent actions to minimize the occurrence of the "ORA-01653" error.
3. Archive and purge audit trail records on a regular basis.

For more information, see Audits of typical database activity on the Oracle website.

Turn on AUTOEXTEND for the SYSTEM tablespace

For Amazon RDS DB instances that run Oracle, AUTOEXTEND for the SYSTEM tablespace is set to ON by default. If you turn off AUTOEXTEND manually, then your database's ability to free additional space for the SYSTEM tablespace might be affected. Run the following ALTER TABLESPACE command to activate this setting for the SYSTEM tablespace:

ALTER TABLESPACE SYSTEM AUTOEXTEND ON;

For more information, see Creating and sizing tablespaces.

Note: SYSTEM tablespace is a bigfile tablespace in Amazon RDS for Oracle instances. The default value of MAXSIZE is unlimited. For example, because the custom maxsize value is not specified in the previous command, the system tablespace can grow up to 32 TB. For more information see, Physical database limits on the Oracle website. Defragmentation of SYSTEM tablespace isn't possible. Perform regular space checks and appropriate audit purge routines to be sure that the SYSTEM tablespace doesn't grow significantly large.

Set MAXSIZE for the SYSTEM tablespace to a larger value

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

ALTER TABLESPACE SYSTEM AUTOEXTEND ON MAXSIZE 50G;

Related information

How do I truncate the sys.aud$ table on my Amazon RDS DB instance that is running Oracle?

Performing common database tasks for Oracle DB instances

Performing common log-related tasks for Oracle DB instances

Oracle database log files on the Oracle website