RDS for OracleのStatspackでデフォルト表領域を任意の場所へ変更する

こんにちは。 ご機嫌いかがでしょうか。 "No human labor is no human error" が大好きな吉井です。

今回は RDS for Oracle に関する記事を投稿します。 以前はあるオラクル製品の導入をメインとするコンサルをしていたので、Oracle DB もよく触っていたのですが、 今はほとんど触らないので色々な事を思い出しながら検証してみました。

Statspack はご存知の方も多いかと思います。 RDS for Oracle にもオプションで Statspack を追加可能です。 オプショングループで指定しておくと自動でインストールしてくれます。 PERFSTAT スキーマが作成されるのですが、デフォルト表領域が SYSAUX となっています。 DBA によっては、”好ましくない”と感じる方もいらっしゃるかもしれません。

本エントリでは PERFSTAT のデフォルト表領域を変更する手順を検証します。

前提

今回の検証は以下の環境で実施しました。

RDS for Oracle エンジン: Oracle Standard Edition Two 12.2.0.1.ru-2018-10.rur-2018-10.r1 Client OS: Amazon Linux 2 Oracle Instant Client: 12.2.0.1.0-1.x86_64

RDS オプショングループに STATSPACK を追加してからデータベースを作成してください。

手順

まずはマスターユーザー(RDS 作成時の作ったもの)でインスタンスへログインします。 PERFSTAT というユーザーが存在するはずです。

SQL> select username from dba_users where username = 'PERFSTAT';
USERNAME
--------
PERFSTAT

PERFSTAT は初期ではロック状態なのでアンロックします。

SQL> ALTER USER perfstat IDENTIFIED BY <new_password> ACCOUNT UNLOCK;

PERFSTAT ユーザーがオーナーのテーブル達の表領域は SYSAUX です。

SQL> select table_name, tablespace_name from dba_tables where owner = 'PERFSTAT';
TABLE_NAME                 TABLESPACE_NAME
-----------                ----------------
STATS$DATABASE_INSTANCE    SYSAUX
(略)

新しく表領域を作ります。 下の例では「STATSPACK」としています。 表領域名や自動拡張、マックスサイズ等はご自身の環境に合わせて調整ください。

SQL> CREATE TABLESPACE STATSPACK
  DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Tablespace created.

表領域が作成されていることの確認です。

SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files;
FILE_NAME   TABLESPACE_NAME
----------  -----------------
/rdsdbdata/db/ORCL_A/datafile/o1_mf_statspac_g5b9cqkq_.dbf    STATSPACK
(略)

PERFSTAT ユーザーのデフォルト表領域を変更します。

SQL> alter user perfstat default tablespace STATSPACK;
User altered.

新しい表領域に対するクォータを設定します。

SQL> alter user perfstat QUOTA UNLIMITED ON STATSPACK;
User altered.

PERFSTAT がオーナーになっているテーブルを新しい表領域へ移動します。 alter table .. move tablespace を実行したいのですが、手で書いていると面倒なので SQL で表示させます。

SQL> select  'alter table ' || table_name || ' move tablespace STATSPACK;'  from dba_tables where owner = 'PERFSTAT';

上の SQL で表示させた結果をコピペで実行します。 その結果を確認しているのが以下です。 表領域が変わっていることが確認できます。

SQL> select table_name, tablespace_name from dba_tables where owner = 'PERFSTAT';
TABLE_NAME                 TABLESPACE_NAME
-----------                ----------------
STATS$DATABASE_INSTANCE    STATSPACK
(略)

索引も移動させておきます。 alter index .. rebuild tablespace を実行します。 これも面倒なので SQL 表示＆コピペで実行です。

SQL> select 'alter index perfstat.' || index_name || ' rebuild tablespace STATSPACK;' from dba_indexes where OWNER = 'PERFSTAT';

perfstat でログインし直します。 試しにスナップショットを作ってみます。

SQL> execute statspack.snap(i_snap_level => 7)
PL/SQL procedure successfully completed.

スナップショットが出来ていることを確認をします。

SQL> select snap_id, snap_time from stats$snapshot order by 1;
   SNAP_ID SNAP_TIME
---------- ---------
         1 02-FEB-19

レポートを作成するために、もう一度スナップショットを作成させます。

SQL> execute statspack.snap(i_snap_level => 7)
PL/SQL procedure successfully completed.

Statspack レポートを作成します。

SQL> exec RDSADMIN.RDS_RUN_SPREPORT(1,2);

レポートはマネジメントコンソールから対象のデータベースを選択して ログを検索すると見つかります。 ダウンロードしてテキストエディタなどで開いてください。

さいごに

オンプレで Statspack をインストールする際には、専用の表領域を使うことが多いと思います。 RDS を使う場合でもデータベース運用は変えたくないと判断しても不思議ではありません。 本エントリで紹介した手順を参考にするかどうかは、DBA と相談して様々なリスクを考慮したうえで判断して頂ければと存じます。

参考

RDSでのstatspack