AWSのRDSの一時表領域が肥大化して止まったので小さくする
事象
ある時間よりAWSで動いているRDSのOracleインスタンスのストレージ（容量100GB、空き70GB）が急激に減少、枯渇しました。
CloudWatchでストレージ容量の監視をおこなっていたのでアラートが気がつきました。
DBへの新規接続もままならない状態だったので、とりあえずストレージを10G追加したところそのまま安定。
原因の調査と解決にむけて作業を開始することにしました
原因の調査
同じインスタンスに複数のユーザーがはいっている構成だったので各ユーザーのデータの使用量を調べます。
いったい犯人はどいつや……？
SELECT
ds.owner AS "オーナー",
ds.tablespace_name AS "表領域名",
to_char(round(SUM(ds.bytes) / 1024 / 1024 / 1024, 2), 'FM99999990.00') AS "使用容量(GB)"
FROM
dba_segments ds
GROUP BY
ds.owner,
ds.tablespace_name
ORDER BY
3 DESC
結果
| オーナー | 表領域名 | 使用容量(GB) |
|---|---|---|
| User1 | USERS | 5.21 |
| User2 | USERS | 1.94 |
| User3 | USERS | 1.74 |
| User4 | USERS | 1.47 |
| User5 | USERS | 1.17 |
| User6 | USERS | 1.00 |
| SYS | SYSTEM | 0.45 |
(以下省略)
うん、全然使ってないですね！
ストレージは100GBになっていてユーザーデータは13GBくらいしか使ってない。
誰かがめちゃくちゃデータつっこんだ可能性は消えました。（よかった）
次は表領域ごとにみてみます。
とは言ってもユーザーごとに見てたいして使ってないので念のため。
SELECT
ddf.tablespace_name AS "表領域",
round(SUM(ddf.bytes) / 1024 / 1024 / 1024, 2) AS "ファイル容量(GB)",
round(SUM(ddf.bytes - sum_bytes) / 1024 / 1024 / 1024, 2) AS "使用容量(GB)",
round(SUM(sum_bytes) / 1024 / 1024 / 1024, 2) AS "空き容量(GB)",
round((SUM(ddf.bytes - sum_bytes)) / (SUM(ddf.bytes)) * 100, 2) AS "使用率(%)"
FROM
dba_data_files ddf,
(
SELECT
tablespace_name,
file_id,
nvl(SUM(bytes), 0) sum_bytes
FROM
dba_free_space
GROUP BY
tablespace_name,
file_id
) dfs
WHERE
ddf.tablespace_name = dfs.tablespace_name
AND ddf.file_id = dfs.file_id
GROUP BY
ddf.tablespace_name
ORDER BY
ddf.tablespace_name
結果
| 表領域 | ファイル容量(GB) | 使用容量(GB) | 空き容量(GB) | 使用率(%) |
|---|---|---|---|---|
| RDSADMIN | 0 | 0 | 0 | 93.8 |
| SYSAUX | 0.59 | 0.55 | 0.05 | 92.2 |
| SYSTEM | 0.5 | 0.47 | 0.03 | 93.9 |
| UNDO_T1 | 10.9 | 0.04 | 10.86 | 0.4 |
| USERS | 13.34 | 12.71 | 0.64 | 95.2 |
UNDOもデカすぎるということもないっぽい。
USERSも13GBしか使ってない。
次は一時表領域のサイズを調べます。
SELECT
tablespace_name AS "表領域名",
round(SUM(bytes) / 1024 / 1024 / 1024, 2) AS "GB"
FROM
dba_temp_files
GROUP BY
tablespace_name
ORDER BY
tablespace_name
結果
| 表領域名 | GB |
|---|---|
| TEMP | 71.12 |
お ま え か ！
いくらなんでもデカすぎます。
犯人が一時表領域のTEMPさんだと言うことがわかりましたので対処を始めましょう。
あとでわかったことですが、新人さんがスーパーなクエリーを実行したっぽいとのこと。ソートとかで使い切ったのではないかと思われます
Shrinkをためしてみる（失敗）
下記の記事を参考にShrinkのコマンドを確かめてみます。
たしかOracle 11gからの機能だった気がする。
想定よりも多くのストレージを使用している Amazon RDS Oracle DB インスタンスの問題を解決する方法を教えてください。
ALTER TABLESPACE temp RESIZE 10g; --失敗
ALTER TABLESPACE temp SHRINK SPACE KEEP 15g; --失敗
ALTER TABLESPACE temp SHRINK SPACE KEEP 70g; --失敗
結果は失敗。なんでや。
ORA-03297: ファイルには、リクエストしたRESIZE値を超える使用中のデータが含まれています。
ファイル移動でやる 〜古き良き手法〜 （失敗）
ダメっぽいので他の手法を試してみようと思います。
とりあえず古き良き手法から。
先に結果をいいますが失敗です。
Amazon RDSのOracleちゃんはファイル追加とかできないっぽい。
というかALTER DATABASE系がダメっぽい。
-- こちらが古き良き手法の手順です。一時表領域のファイルを作り直してロテートする。
-- 一時表領域のファイルのパスとサイズを調べる
SELECT
dtf.tablespace_name,
dtf.file_name,
trunc(dtf.bytes / 1024 / 1024 / 1024, 2) AS gb
FROM
dba_temp_files dtf
ORDER BY
dtf.tablespace_name,
dtf.file_name;
-- 新しく一つ作成（ここで失敗する）
ALTER TABLESPACE TEMP ADD TEMPFILE '/rdsdbdata/db/ESI_A/datafile/temp_extra01.dbf' SIZE 5G AUTOEXTEND OFF;
-- 圧縮したいやつをオフラインにする
ALTER DATABASE TEMPFILE '/rdsdbdata/db/ESI_A/datafile/o1_mf_temp_ct5nf4lj_.tmp' OFFLINE;
-- 圧縮したいやつを一旦削除する
ALTER DATABASE TEMPFILE '/rdsdbdata/db/ESI_A/datafile/o1_mf_temp_ct5nf4lj_.tmp' DROP INCLUDING DATAFILES
-- 再作成
ALTER TABLESPACE TEMP ADD TEMPFILE '/rdsdbdata/db/ESI_A/datafile/o1_mf_temp_ct5nf4lj_.tmp' SIZE 2048M AUTOEXTEND ON
-- 一時的に作成したものをオフラインにする
ALTER DATABASE TEMPFILE '/tmp/tempxx.dbf' OFFLINE
-- 一時的に作成したものを削除する
ALTER DATABASE TEMPFILE '/tmp/tempxx.dbf' DROP INCLUDING DATAFILES
残念であります！
無念であります！
新しく一時表領域を作成して肥大化した一時表領域を削除する（成功）
こちらにこんな記述が。
セッションを強制終了することを選択しない場合は、他の手順を検討します。たとえば、別のデフォルトの temp2 テーブルスペースの作成、元の一時テーブルスペースのドロップ、必要なパラメーターを使用した新しいデフォルトの一時テーブルスペースの再作成、temp2 テーブルスペースのドロップなどが考えられます。
なるほどナス。んじゃそれやってみましょう。
デフォルトの一時表領域を変更するには
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 新しい一時表領域名;
みたいなの打たないといけません。
あれ、Amazon RDSのOracleちゃんはALTER DATABASE系は使えないんじゃ？
と思っていたらDBの管理タスクを行うパッケージがあるそうで。
Oracle DB インスタンスの一般的な DBA データベースタスク
その中の使うのはコレ。
デフォルトの一時テーブルスペースの設定
じゃあやっていきましょう。まずは作成するところから。
-- まずは変更先の新しい一時表領域を作成する
create temporary tablespace temp01;
これは普通にできる。
次はパッケージのプロシージャを使って一時表領域を変更します。
-- デフォルトのテーブルスペースを変更する (現在はTEMP。 TEMP01にする)
exec rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp01');
できたできた。
-- 確認
SELECT
*
FROM
database_properties
WHERE
property_name = 'DEFAULT_TEMP_TABLESPACE';
| PROPERTY_NAME | PROPERTY_VALUE | DESCRIPTION |
|---|---|---|
| DEFAULT_TEMP_TABLESPACE | TEMP01 | Name of default temporary tablespace |
OK。
削除する一時表領域TEMPを使っているDBのセッションがあると削除できない（というか返ってこない）ので、
セッションを探して存在していたらKILLします 
-- 使ってるやつを探すSQL
SELECT
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
FROM
v$session a,
v$sort_usage b
WHERE
a.saddr = b.session_addr
ORDER BY
b.tablespace;
結果は省略して書きますが使ってる方がいらっしゃいました。
| TABLESPACE | SID | SERIAL# | OSUSER | STATUS |
|---|---|---|---|---|
| TEMP | 316 | 39582 | rdsdb | ACTIVE |
RDSのセッションをKILLするには例のパッケージを使います。
セッションの強制終了
-- 数値のところは上で調べたやつね！（まだ打たないでください）
begin
rdsadmin.rdsadmin_util.kill(
sid => 316,
serial => 39582);
end;
/
さようならセッション君！とこしえにーっ！
……といいたいところなんだけどUSERが rdsdb とかいって、めっちゃAmazon RDSのシステムユーザーっぽいよね。
下手にKILLするのもなんか怖いのでとりあえずDBの再起動を試すことにします。
デフォルトの一時表領域をTEMP01にしてあるので、再起動すればそっちを掴んでくれると信じて。
再起動後。
つかんでたのを離してくれたか確認。
| TABLESPACE | SID | SERIAL# | OSUSER | STATUS |
|---|---|---|---|---|
| TEMP01 | 316 | 46599 | rdsdb | ACTIVE |
はなしてくれた
もしかして今ならRESIZEできるのでは？
とりあえずサイズ確認。
| 表領域名 | GB |
|---|---|
| TEMP | 10 |
| TEMP01 | 0.1 |
な ん で や ！
なんでいきなり小さくなっとるん！
当然ストレージの空き領域もガッツリ復活しました。
作業途中で作成したりしていらなくなった一時表領域を削除するときはこちら。
-- 大きくなってしまった一時表領域を削除
-- 表領域ごと消すのでバックアップをとるなどして慎重にやりましょう。
DROP TABLESPACE いらない一時表領域名 INCLUDING CONTENTS AND DATAFILES;
まとめ
最初から再起動すればよかったんじゃないか説がありますが、色々調べたので残しておきます。
Amazon RDS上のOracleの一時表領域がうまく開放できない場合はrdsdbユーザーの機嫌をうまくとりましょう。
（あと、失敗した新人は決していじめないように！というかケアするように！）
おつかれさまでした！ 