予想よりも多くのストレージを使用している Amazon RDS Oracle DB インスタンスに関する問題を解決する方法を教えてください。

最終更新日: 2020 年 10 月 28 日

Amazon Relational Database Service (Amazon RDS) Oracle DB インスタンスをセットアップしましたが、予想よりも多くのスペースを使用します。こうなった理由は何ですか? 私にできることは何ですか?

簡単な説明

一時テーブルスペースは、データベース関数に必要なオペレーションに対応するために大幅に増加する可能性があります。Amazon RDS Oracle DB インスタンスは、すべてのテーブルスペースに対して自動拡張を有効にして作成されます。つまり、ストレージが不要になるか、割り当てられたストレージが使用されるまで、より多くのデータを収容できることを意味します。この問題を解決する方法:

テーブルスペース内のデータに割り当てられたスペースの量を調べます。
アーカイブログまたはトレースファイルのスペース割り当てを確認します。
データポンプディレクトリのスペース割り当てを確認します。

解決方法

テーブルスペース内のデータに割り当てられたスペースの量を調べる

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. 一時テーブルスペースのサイズを変更するには (10 GB など)、テーブルスペース使用クエリの出力に基づいて次のクエリを実行します。

SQL> ALTER TABLESPACE temp RESIZE 10g;

このコマンドは、テーブルスペースが 10 GB のしきい値を超える範囲を割り当てた場合に失敗することがあります。

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 の自動化により、古いアーカイブログファイルを消去することができます。次の例では、24 時間のアーカイブログを保持するように RDS インスタンスを設定します。

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 ファイルが見つかった場合は、次のクエリを実行してファイル名を .dmp ファイルの名前に置き換えて削除します。

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