How do I manage the storage in my Amazon RDS for Oracle DB instance?

7 minute read

I want to use best practices to manage the storage of my Amazon Relational Database Service (Amazon RDS) for Oracle database (DB) instance.

Short description

When your RDS DB instance enters storage-full, your instance's login freezes, database growth stops, and you receive the following error message:

"ORA-27061: waiting for async I/Os failed
Error: Linux-x86_64 Error: 28: No space left on device"

To manage your free storage so that it doesn't reach storage-full, use the following best practices.

Resolution

Monitor the FreeStorageSpace metric

If the FreeStorageSpace metric quickly drops and your DB instance uses more than 90% of its allocated storage, then the database encounters low storage events. The RDS event IDs for low storage are RDS-EVENT-0007 and RDS-EVENT-0089.

To monitor the FreeStorageSpace metric and get notified when the storage space reaches its quota, Create Amazon CloudWatch alarms. For more information, see Monitoring metrics in an Amazon RDS instance.

To prevent this issue, you can turn on auto scaling to automatically add storage space.

Review any autoextend tablespaces

The default tablespace type for RDS for Oracle DB instances is bigfile with AUTOEXTEND turned on. The maximum size of bigfile tablespaces is 16 TiB. The tablespace increases to either the maximum quota for the tablespace or the allocated storage for your DB instance. If your allocated storage is full, then your instance switches to storage-full, and your tablespaces can't extend. For more information, see Autoextend tablespace on the Oracle website.

It's a best practice to plan a tablespace extension based on your application's needs. To avoid storage-full issues before a bulk load, resize tablespaces or allocate more space. If you scale your storage, then DB instance performance usually isn't affected and no outages occur. After you modify the storage size, your DB instance is in the storage-optimization state.

Note: Before you modify your DB instance, see Working with storage for Amazon RDS DB instances.

Check the Oracle file size quotas for Amazon RDS

The maximum size of a single file in your RDS for Oracle DB instances is 16 TiB.

If you resize a data file in a bigfile tablespace beyond this quota, then you receive an error message that's similar to the following one:

"ORA-01237: cannot extend datafile 6
ORA-01110: data file 6: '/rdsdbdata/db/mydir/datafile/myfile.dbf'ORA-27059:
could not reduce file sizeLinux-x86_64 Error: 27:
File too largeAdditional information: 2"

Resolve the "Storage quota exceeded" error

If you reach your storage quota and try to create a new RDS DB instance, then you get an error message that's similar to the following one:

"Storage quota exceeded by <x>; requested size: <x>"

To resolve this issue, request a quota increase. Or, delete unused backups to immediately free up space in your AWS account.

Manage large trace files that occupy the BDUMP directory

Your database occasionally creates large trace files in the BDUMP directory. Trace files use disk space and contribute to storage-full occurrences on your DB instances. By default, Amazon RDS clears trace files and log files that are older than 7 days.

To view your current trace file retention period, run the following command:

EXEC rdsadmin.rdsadmin_util.show_configuration;

To set a new trace file retention period, run the following command:

EXEC rdsadmin.rdsadmin_util.set_configuration('tracefile retention',1440);
commit;

Note: Replace 1440 with the number of minutes you want to retain your trace files for. For more information about how to manage your trace files, see RDS for Oracle database log files.

To sort and remove large trace files, complete the following steps:

To sort the files in the BDUMP directory by size, run a command that's similar to the following one:

select FILENAME, FILESIZE/1024/1024/1024"GB",MTIME from TABLE
  (rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) order by 3 desc

Identify the files that you don't use.
Run the following fremove dbms package to delete large files in trace file retention:
```
exec UTL_FILE.FREMOVE ('BDUMP',' <TRACEFILENAME');
```
Note: Replace TRACEFILENAME with the name of the trace file that you don't use.

Manage large Data Pump Directory that uses the DATA_PUMP_DIR directory

If you use Oracle Data Pump to move your data, then you might copy large dumpfiles that decrease your free storage. To resolve this issue, integrate RDS for Oracle with Amazon Elastic File System (Amazon EFS) so that you don't need to use DATA_PUMP_DIR for database refreshes.

Check your archive log retention

Amazon RDS backs up archive logs to internal storage based on the backup retention for point-in-time recovery (PITR) purposes. Based on archive log retention settings for RDS for Oracle, archive logs that you locally store in RDS storage contribute to allocated storage usage.

To retain archived redo logs, run rdsadmin.rdsadmon_util.set_configuration with the following parameters:

begin
    rdsadmin.rdsadmin_util.set_configuration(
        name  => 'archivelog retention hours',
        value => '24');
end;
/
commit;

Note: Replace 24 with the number of hours that you want to retain archived redo logs.

To check how much space your DB instance used during a specific period of time, run the following query:

SELECT SUM(BLOCKS * BLOCK_SIZE) bytes
  FROM V$ARCHIVED_LOG
 WHERE FIRST_TIME >= SYSDATE-(hours/24) AND DEST_ID=1;

Note: Replace hours with the number of hours that you want to query.

To view retained archive logs, run the following commands to create a directory object as ARCHIVELOG_DIR:

EXEC rdsadmin.rdsadmin_master_util.create_archivelog_dir;

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR'));

Check your database for fragmented objects

Fragmentation causes the database to slow and uses an unnecessary amount of storage space. To identify the fragmented objects that use the most space, run a command that's similar to the following one:

SELECT
owner,
table_name,
ROUND((blocks * 8 / 1024), 2) "size (mb)",
ROUND((num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (mb)",
ROUND((blocks * 8 / 1024) - (num_rows * avg_row_len / 1024 / 1024), 2) "wasted_space (mb)"
FROM
dba_tables
WHERE
owner = UPPER('&schema_name')
AND blocks > 0
AND (blocks * 8 / 1024) > (num_rows * avg_row_len / 1024 / 1024)
ORDER BY 5 DESC;

To remove fragmentation, use export and import, recreate tables, or move tables to a new tablespace.

Note: After you empty and move fragmented objects to a new tablespace and drop the fragmented tablespace, the free storage metric updates.

Use AWR Explorer to analyze growth trends on your DB instance

With Oracle Enterprise edition, you can use AWR Explorer to query your historical information and analyze the growth trends of your database. To check the growth of your database schema for each object type over a specific number of days, run the following command:

title "Total Disk Used by Object Type"  select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Space (M)",  
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"  
from  
dba_hist_snapshot sn,  
dba_hist_seg_stat a,  
dba_objects b,  
dba_segments c  
where end_interval_time > trunc(sysdate) - &days_back  
and sn.snap_id = a.snap_id  
and b.object_id = a.obj#  
and b.owner = c.owner  
and b.object_name = c.segment_name  
and space_used_delta > 0  
and c.owner = '&schema_name'  
group by rollup(segment_type);