AWSのRDSの一時表領域が肥大化して止まったので小さくする

# AWSのRDSの一時表領域が肥大化して止まったので小さくする

## 事象
![FireShot Capture 003 - RDS · AWS Console - ap-northeast-1.console.aws.amazon.com.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/486809/620d0dff-f631-172e-3ab5-c06edc84075e.png)

ある時間よりAWSで動いているRDSのOracleインスタンスのストレージ（容量100GB、空き70GB）が急激に減少、枯渇しました。
CloudWatchでストレージ容量の監視をおこなっていたのでアラートが気がつきました。
DBへの新規接続もままならない状態だったので、とりあえずストレージを10G追加したところそのまま安定。
原因の調査と解決にむけて作業を開始することにしました:runner:

## 原因の調査

同じインスタンスに複数のユーザーがはいっている構成だったので各ユーザーのデータの使用量を調べます。
**いったい犯人はどいつや……？**

```sql
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くらいしか使ってない。
誰かがめちゃくちゃデータつっこんだ可能性は消えました。（よかった）

次は表領域ごとにみてみます。
とは言ってもユーザーごとに見てたいして使ってないので念のため。

```sql
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しか使ってない。

次は一時表領域のサイズを調べます。

```sql
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**さんだと言うことがわかりましたので対処を始めましょう。


>あとでわかったことですが、新人さんがスーパーなクエリーを実行したっぽいとのこと。ソートとかで使い切ったのではないかと思われます:relaxed:

## Shrinkをためしてみる（失敗）

下記の記事を参考にShrinkのコマンドを確かめてみます。
たしかOracle 11gからの機能だった気がする。

[想定よりも多くのストレージを使用している Amazon RDS Oracle DB インスタンスの問題を解決する方法を教えてください。](https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-oracle-storage-optimization/)



```sql
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`系がダメっぽい。


```sql
-- こちらが古き良き手法の手順です。一時表領域のファイルを作り直してロテートする。
-- 一時表領域のファイルのパスとサイズを調べる
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
```

残念であります！
無念であります！

:sob: :sob: :sob:


## 新しく一時表領域を作成して肥大化した一時表領域を削除する（成功）


[こちら](https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-oracle-storage-optimization/)にこんな記述が。
>セッションを強制終了することを選択しない場合は、他の手順を検討します。たとえば、別のデフォルトの temp2 テーブルスペースの作成、元の一時テーブルスペースのドロップ、必要なパラメーターを使用した新しいデフォルトの一時テーブルスペースの再作成、temp2 テーブルスペースのドロップなどが考えられます。

なるほどナス。んじゃそれやってみましょう。

デフォルトの一時表領域を変更するには

```sql
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 新しい一時表領域名;
```

みたいなの打たないといけません。
あれ、Amazon RDSのOracleちゃんは`ALTER DATABASE`系は使えないんじゃ？
と思っていたらDBの管理タスクを行うパッケージがあるそうで。

[Oracle DB インスタンスの一般的な DBA データベースタスク](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Database.html)

その中の使うのはコレ。
[デフォルトの一時テーブルスペースの設定](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Database.html#Appendix.Oracle.CommonDBATasks.SettingDefTempTablespace)

じゃあやっていきましょう。まずは作成するところから。

```sql
-- まずは変更先の新しい一時表領域を作成する
create temporary tablespace temp01;
```

これは普通にできる。
次はパッケージのプロシージャを使って一時表領域を変更します。

```sql
-- デフォルトのテーブルスペースを変更する (現在はTEMP。 TEMP01にする)
exec rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp01');
```

できたできた。

```sql
-- 確認
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します :smiling_imp:

```sql
-- 使ってるやつを探す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するには例のパッケージを使います。
[セッションの強制終了](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html#Appendix.Oracle.CommonDBATasks.KillingSession)

```sql
-- 数値のところは上で調べたやつね！（まだ打たないでください）
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|

はなしてくれた:relaxed:

もしかして今ならRESIZEできるのでは？
とりあえずサイズ確認。

|表領域名|GB|
|---|---|
|TEMP|10|
|TEMP01|0.1|

**な　ん　で　や　！**

なんでいきなり小さくなっとるん！

![FireShot Capture 004 - RDS · AWS Console - ap-northeast-1.console.aws.amazon.com.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/486809/2bd2dac7-31bd-8cd0-1d2e-5e493c9f6592.png)

当然ストレージの空き領域もガッツリ復活しました。
作業途中で作成したりしていらなくなった一時表領域を削除するときはこちら。

```sql
-- 大きくなってしまった一時表領域を削除
-- 表領域ごと消すのでバックアップをとるなどして慎重にやりましょう。
DROP TABLESPACE いらない一時表領域名 INCLUDING CONTENTS AND DATAFILES;
```

## まとめ

最初から再起動すればよかったんじゃないか説がありますが、色々調べたので残しておきます。
Amazon RDS上のOracleの一時表領域がうまく開放できない場合は**rdsdbユーザー**の機嫌をうまくとりましょう。
（あと、失敗した新人は決していじめないように！というかケアするように！）

おつかれさまでした！ :beer: :beer: :beer:

## 参考
[Oracleの一時表領域がディスク圧迫した際の復旧手順](https://qiita.com/mikoski01/items/646bb221cdc3ef2a054a)