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

上次更新时间:2020 年 2 月 6 日

我在设置一个 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.    如果分配给临时表空间的空间超出预期,请使用以下查询进一步分析数据库上使用的空间。

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

SQL> SELECT * FROM dba_temp_free_space;

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

SQL> ALTER TABLESPACE temp RESIZE 10g;

5.    如果表空间分配的大小超过 10 GB 的阈值,此命令可能会失败。如果该命令失败,可以通过运行以下命令来缩小临时表空间上的空间:

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

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

SQL> SELECT * FROM v$sort_usage;

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

8.    如果不取消会话,则可以创建一个新的临时表空间,将新表空间设置为默认值,并删除旧的临时表空间:

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 sum(blocks*block_size)/1024/1024/1024 FROM v$archived_log WHERE next_time >=sysdate-X/24 AND dest_id=1;

2.    如果对存档日志或跟踪文件分配的空间超出预期,请使用以下查询,根据存档日志和跟踪文件进一步分析空间。

3.    查看当前存档日志和跟踪文件保留设置:

SET SERVEROUTPUT ON;
EXEC rdsadmin.rdsadmin_util.show_configuration;

4.    更新保留策略值,并允许 Amazon RDS 自动清除较旧的存档日志文件或跟踪文件。以下示例将重做日志保留 24 小时的时间:

SQL> begin
SQL> rdsadmin.rdsadmin_util.set_configuration(
SQL> name => 'archivelog retention hours',
SQL> value => '24');
SQL> end;
SQL> /
SQL> commit;
# Set the tracefile retention to 24 hours:
SQL> exec rdsadmin.rdsadmin_util.set_configuration('tracefile retention',1440);
SQL> commit;

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

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

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

2.    如果此查询找到 .dmp 文件,您可以通过运行以下查询并替换文件名来删除它们:

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