如何解决 Amazon RDS Oracle 数据库实例使用的存储超出预期的问题?

上次更新时间:2020 年 10 月 28 日

我设置了一个 Amazon Relational Database Service (Amazon RDS) Oracle 数据库实例,但其使用的空间超出了我的预期。为什么会发生这种情况,该怎么办?

简短描述

为了满足数据库功能所需操作的要求,系统可能会显著增加临时表占用的空间。创建的 Amazon RDS Oracle 数据库实例为所有表空间都启用了自动扩展。这意味着每个表空间都会不断增长以容纳更多数据,直到不再需要更多空间或用完分配的存储为止。要解决此问题:

  1. 在表空间中找到分配给数据的空间量。
  2. 查看对存档日志或跟踪文件的空间分配。
  3. 查看对数据泵目录的空间分配。

解决方法

在表空间中找到分配给数据的空间量

1.    确定分配给 Oracle 数据库不同组件的空间分布:

set pages 200
select
'===========================================================' || chr(10) ||
'Total Database Physical Size = ' || round(redolog_size_gb+dbfiles_size_gb+tempfiles_size_gb+ctlfiles_size_gb,2) || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
' Redo Logs Size : ' || round(redolog_size_gb,3) || ' GB' || chr(10) ||
' Data Files Size : ' || round(dbfiles_size_gb,3) || ' GB' || chr(10) ||
' Temp Files Size : ' || round(tempfiles_size_gb,3) || ' GB' || chr(10) ||
' Archive Log Size - Approx only : ' || round(archlog_size_gb,3) || ' GB' || chr(10) ||
' Control Files Size : ' || round(ctlfiles_size_gb,3) || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
' Used Database Size : ' || used_db_size_gb || ' GB' || chr(10) ||
' Free Database Size : ' || free_db_size_gb || ' GB' ||chr(10) ||
' Data Pump Directory Size : ' || dpump_db_size_gb || ' GB' || chr(10) ||
' BDUMP Directory Size : ' || bdump_db_size_gb || ' GB' || chr(10) ||
' ADUMP Directory Size : ' || adump_db_size_gb || ' GB' || chr(10) ||
'===========================================================' || chr(10) ||
'Total Size (including Dump and Log Files) = ' || round(round(redolog_size_gb,2) +round(dbfiles_size_gb,2)+round(tempfiles_size_gb,2)+round(ctlfiles_size_gb,2) +round(adump_db_size_gb,2) +round(dpump_db_size_gb,2)+round(bdump_db_size_gb,2),2) || ' GB' || 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_gb,
(SELECT SUM(bytes) / 1024 / 1024 / 1024 data_size
FROM dba_data_files)
dbfiles_size_gb,
(SELECT nvl(SUM(bytes), 0) / 1024 / 1024 / 1024 temp_size
FROM dba_temp_files)
tempfiles_size_gb,
(SELECT SUM(blocks * block_size / 1024 / 1024 / 1024) size_gb
FROM v$archived_log
WHERE first_time >= SYSDATE - (
(SELECT value
FROM rdsadmin.rds_configuration
WHERE name =
'archivelog retention hours') /
24 ))
archlog_size_gb,
(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024
controlfile_size
FROM v$controlfile)
ctlfiles_size_gb,
round(SUM(used.bytes) / 1024 / 1024 / 1024, 3)
db_size_gb,
round(SUM(used.bytes) / 1024 / 1024 / 1024, 3) - round(
free.f / 1024 / 1024 / 1024)
used_db_size_gb,
round(free.f / 1024 / 1024 / 1024, 3)
free_db_size_gb,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP')))
bdump_db_size_gb,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('ADUMP')))
adump_db_size_gb,
(SELECT round(SUM(filesize) / 1024 / 1024 / 1024, 3)
FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')))
dpump_db_size_gb
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);

如果分配给临时表空间的空间超出预期,则使用以下查询进一步分析数据库上使用的空间。

2.    要查看有关临时表空间使用情况的信息,请在视图 DBA_TEMP_FREE_SPACE 上运行以下查询:

SQL> SELECT * FROM dba_temp_free_space;

3.    要调整临时表空间的大小(例如,调整为 10GB),请根据表空间使用情况查询的输出运行以下查询:

SQL> ALTER TABLESPACE temp RESIZE 10g;

如果表空间分配的大小超过 10GB 的阈值,此命令可能会失败。

4.    如果此命令失败,则运行以下命令来缩小临时表空间上的空间:

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

5.    要查看正在对磁盘执行主动排序并且分配了临时分区的长时间运行的会话,请运行以下查询:

SQL> SELECT * FROM v$sort_usage;

6.    根据输出,您可以终止会话(如果应用程序逻辑和业务允许),然后按照第 3 步操作,尝试重新调整临时表空间的大小。

7.    如果没有终止会话的选项,则创建一个新的临时表空间。然后将新的表空间设置为默认表空间,同时删除旧的临时表空间:

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;

查看为存档日志或跟踪文件分配的空间

1.    检查当前存档日志保留期:

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

2.    通过运行以下查询计算存档日志使用的空间。

注意:X 是 RDS 实例中设置的存档日志保留期。将 X 替换为上一个查询中报告的值。

SELECT sum(blocks*block_size)/1024/1024/1024 FROM v$archived_log WHERE first_time >=sysdate-X/24 AND dest_id=1;

3.    如果为存档日志分配的空间超过预期,则更新保留策略值。然后,您可以允许 Amazon RDS 自动化清除较早的存档日志文件。以下示例将 RDS 实例配置为保留 24 小时的存档日志:

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

有关列出和清除跟踪文件的更多信息,请参阅清除跟踪文件

查看为数据泵目录分配的空间

1.    如果对数据泵目录分配的空间超出预期,请检查可删除的可用 .dmp 文件以释放空间:

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

2.    如果此查询找到 .dmp 文件,您可以运行以下查询并将 file name 替换为相关 .dmp 文件的名称,从而删除这些文件:

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