Why is my Amazon RDS for Oracle DB instance using more storage than expected?

7 minute read
0

I have an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance that uses more space than I expect it to.

Short description

Various components can use the underlying storage for Amazon RDS for Oracle instances. These components include tablespaces, archive logs, log files, online redo log files, and data pump files.

To manage storage growth in your instance, identify how much storage space your components use:

1.    Find the amount of space that's allocated to data in all tablespaces, including temporary tablespaces.

2.    Check the space allocation for archive logs or trace files.

3.    Check the space allocation for the data pump directory.

Note: The allocated storage space for an RDS instance represents the data volume. When you create an instance, Amazon RDS maps the allocated storage to the data volume. This process also uses a small percentage of raw disk space to create the filesystem on top of the physical storage volume.

Resolution

Find the amount of space allocated to data in the tablespaces

To determine the spread of the space that's allocated to different components of the Oracle database, use the following query:

set pages 200
select
'===========================================================' || chr(10) ||
'Total Database Physical Size = ' || round(redolog_size_gib+dbfiles_size_gib+tempfiles_size_gib+ctlfiles_size_gib,2) || ' GiB' || chr(10) ||
'===========================================================' || chr(10) ||
' Redo Logs Size : ' || round(redolog_size_gib,3) || ' GiB' || chr(10) ||
' Data Files Size : ' || round(dbfiles_size_gib,3) || ' GiB' || chr(10) ||
' Temp Files Size : ' || round(tempfiles_size_gib,3) || ' GiB' || chr(10) ||
' Archive Log Size - Approx only : ' || round(archlog_size_gib,3) || ' GiB' || chr(10) ||
' Control Files Size : ' || round(ctlfiles_size_gib,3) || ' GiB' || chr(10) ||
'===========================================================' || chr(10) ||
' Used Database Size : ' || used_db_size_gib || ' GiB' || chr(10) ||
' Free Database Size : ' || free_db_size_gib || ' GiB' ||chr(10) ||
' Data Pump Directory Size : ' || dpump_db_size_gib || ' GiB' || chr(10) ||
' BDUMP Directory Size : ' || bdump_db_size_gib || ' GiB' || chr(10) ||
' ADUMP Directory Size : ' || adump_db_size_gib || ' GiB' || chr(10) ||
'===========================================================' || chr(10) ||
'Total Size (including Dump and Log Files) = ' || round(round(redolog_size_gib,2) +round(dbfiles_size_gib,2)+round(tempfiles_size_gib,2)+round(ctlfiles_size_gib,2) +round(adump_db_size_gib,2) +round(dpump_db_size_gib,2)+round(bdump_db_size_gib,2),2) || ' GiB' || chr(10) ||
'===========================================================' as summary
FROM (SELECT sys_context('USERENV', 'DB_NAME')
db_name,
(SELECT SUM(bytes) / 1024 / 1024 / 1024 redo_size
FROM v$log)
redolog_size_gib,
(SELECT SUM(bytes) / 1024 / 1024 / 1024 data_size
FROM dba_data_files)
dbfiles_size_gib,
(SELECT nvl(SUM(bytes), 0) / 1024 / 1024 / 1024 temp_size
FROM dba_temp_files)
tempfiles_size_gib,
(SELECT SUM(blocks * block_size / 1024 / 1024 / 1024) size_gib
FROM v$archived_log
WHERE first_time >= SYSDATE - (
(SELECT value
FROM rdsadmin.rds_configuration
WHERE name =
'archivelog retention hours') /
24 ))
archlog_size_gib,
(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024
controlfile_size
FROM v$controlfile)
ctlfiles_size_gib,
round(SUM(used.bytes) / 1024 / 1024 / 1024, 3)
db_size_gib,
round(SUM(used.bytes) / 1024 / 1024 / 1024, 3) - round(
free.f / 1024 / 1024 / 1024)
used_db_size_gib,
round(free.f / 1024 / 1024 / 1024, 3)
free_db_size_gib,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP')))
bdump_db_size_gib,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('ADUMP')))
adump_db_size_gib,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')))
dpump_db_size_gib
FROM (SELECT bytes
FROM v$datafile
UNION ALL
SELECT bytes
FROM v$tempfile) used,
(SELECT SUM(bytes) AS f
FROM dba_free_space) free
GROUP BY free.f);

By default, Amazon RDS for Oracle DB instances turn on auto-extend for all tablespaces. This includes data tablespaces, UNDO tablespaces, and temporary tablespaces. This means that each tablespace grows to accommodate more data. This feature continues until you no longer need more storage or you use all allocated storage space.

Resize tablespaces

Data tablespace and UNDO tablespace

To resize data and UNDO tablespaces, see How do I resize the tablespace for my Amazon RDS for Oracle DB instance?

Temporary tablespace

1.    To view information about temporary tablespace usage, run the following query on the view DBA_TEMP_FREE_SPACE:

SQL> SELECT * FROM dba_temp_free_space;

2.    To resize the temporary tablespace (for example, to 10 GB), run the following query based on the output of the tablespace usage query:

SQL> ALTER TABLESPACE temp RESIZE 10g;

If the allocated tablespace extends beyond the 10 GB threshold, then this command might fail.

3.    If the command fails, then shrink space on the temporary tablespace:

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

4.    Check for long-running sessions that perform active sorting to disk and have temporary segments allocated. To do this, run the following query:

SQL> SELECT * FROM v$sort_usage;

5.    If the application logic and business allow you to end the session, then end the session. Then, resize the temporary tablespace again, as shown in step 2.

6.    If you can't end your sessions, then create a new temporary tablespace. Then, set the new tablespace as the default and drop the old temporary tablespace:

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
SQL> create temporary tablespace temp2;
SQL> exec rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp2');
<wait for a few minutes and verify if the default temporary tablespace for all users have been updated>
SQL> set pages 2000
SQL> column username for a30
SQL> select username, TEMPORARY_TABLESPACE from dba_users;
SQL> drop tablespace temp including contents and datafiles;

Check the space allocation for archive logs or trace files

1.    Check the current archive log retention:

SQL> SELECT value FROM rdsadmin.rds_configuration WHERE name ='archivelog retention hours';

In Amazon RDS for Oracle instances, archive log retention is set to 0 by default. This means that after archivelogs upload to Amazon S3, Amazon RDS automatically deletes them from the underlying host. If you need to use archivelogs with products like Oracle LogMiner or GoldenGate, then increase archivelog retention.

2.    Calculate the space that archivelogs use on the underlying host. First, create an archivelog directory:

SQL> EXEC rdsadmin.rdsadmin_master_util.create_archivelog_dir;

Then, identify the exact usage of archivelog on an RDS instance:

SQL> SELECT sum(FILESIZE)/1024/1024/1024 archivelog_usage_GiB FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR'));

3.    If the allocated space for archive logs is more than expected, then update the retention policy value. Then, allow Amazon RDS automation to clear older archive log files. The following example configures the RDS for Oracle instance to retain 24 hours of archive logs:

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

For more information about listing and purging trace files, see Purging trace files.

Check the space allocation for the data pump directory

1.    If the allocated space of the data pump directory is more than expected, then find the .dmp files that can be removed:

SQL> SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) ORDER by mtime;

2.    If this query finds .dmp files, then delete them with the following query. Replace the file name with the name of the .dmp files:

SQL> EXEC utl_file.fremove('DATA_PUMP_DIR','[file name]');

Related information

Working with storage for Amazon RDS DB instances

Terminating a session

Monitoring metrics in an Amazon RDS instance

Amazon RDS DB instance running out of storage