Amazon RDS for Oracle の監視について

マツナミです。今回はRDS for Oracleの監視について簡単に確認したいと思います。
オンプレでOracle Databaseの監視をしようとした場合、以下のような項目を監視することが多いのではないかと思います。

Oracleインスタンス プロセス監視
Oracleログ監視
Oracle表領域使用率監視

上記以外にも監視のミドルウェアを入れたりして、キャッシュのヒット率などを監視しているシステムもあるかと思いますが、まずは基本的なところを見ていきます。
ちなみに、本来はEnterprise ManagerをEC2に入れて監視するのがベストな気はしますが、監視のためだけにそこそこ大きなサイズのEC2を立てるのかというのはプロジェクト次第だと思いますので、とりあえず今回はEMを使うことは考えてません。

Oracleインスタンス プロセス監視

インスタンスの監視については、RDSのイベントサブスクリプションを使うことでこの用途を満たせそうです。
経験上、Unix系OSではバックグラウンドプロセスを個別に監視することが多いですが、イベントサブスクリプションを使った場合はインスタンス単位での監視になります。
イベントサブスクリプションはプロセスが停止したときだけでなく、例えばバックアップが完了したときなどに指定したメールアドレスにメールを送信することができるのでその点は便利ですね。

Oracleログ監視

ログ監視の対象となるのはアラートログ上のエラーメッセージ(ORA-)を監視文言とするのが基本で、場合によってはリスナーログも対象になるかと思います。
RDSではホストOSにログインできない関係上、ログファイルを直接参照することはできませんが、代わりにSQLでALERTLOGやLISTENERLOGを参照したり、CLIなどでログを取得することができます。

SQLでALERTLOGを参照する前に、まずはALERTLOG、LISTENERLOGの実態がどうなっているか確認してみましょう。

SQL> select owner, object_name, object_type from dba_objects where object_name in ('ALERTLOG','LISTENERLOG');

OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ ------------------------------
PUBLIC               ALERTLOG                       SYNONYM
PUBLIC               LISTENERLOG                    SYNONYM

SQL>

ALERTLOG、LISTENERLOGはPUBLICスキーマのシノニムだということがわかります。 ではこのシノニムはどこを参照しているのでしょうか。

SQL> select do.owner, do.object_type, do.object_name
  2  from dba_objects do, dba_synonyms ds
  3  where do.owner=ds.table_owner
  4  and do.object_name=ds.table_name
  5  and ds.synonym_name in ('ALERTLOG','LISTENERLOG');

OWNER                OBJECT_TYPE                    OBJECT_NAME
-------------------- ------------------------------ ------------------------------
SYS                  VIEW                           RDS_X$DBGALERTEXT
SYS                  VIEW                           RDS_X$DIAG_ALERT_EXT

SQL>

参照先はSYSスキーマのRDS_X$…というビューだということがわかりました。 これらビューが参照しているテーブルがどこかをさらに確認すると

SQL> set long 10000;
SQL> set head off;
SQL> select text from dba_views
  2  where view_name in ('RDS_X$DBGALERTEXT','RDS_X$DIAG_ALERT_EXT');

select "ADDR","INDX","INST_ID","ORIGINATING_TIMESTAMP","NORMALIZED_TIMESTAMP","O
RGANIZATION_ID","COMPONENT_ID","HOST_ID","HOST_ADDRESS","MESSAGE_TYPE","MESSAGE_
LEVEL","MESSAGE_ID","MESSAGE_GROUP","CLIENT_ID","MODULE_ID","PROCESS_ID","THREAD
_ID","USER_ID","INSTANCE_ID","DETAILED_LOCATION","PROBLEM_KEY","UPSTREAM_COMP_ID
","DOWNSTREAM_COMP_ID","EXECUTION_CONTEXT_ID","EXECUTION_CONTEXT_SEQUENCE","ERRO
R_INSTANCE_ID","ERROR_INSTANCE_SEQUENCE","VERSION","MESSAGE_TEXT","MESSAGE_ARGUM
ENTS","SUPPLEMENTAL_ATTRIBUTES","SUPPLEMENTAL_DETAILS","PARTITION","RECORD_ID" f
rom sys.x$DBGALERTEXT

select "ADDR","INDX","INST_ID","ADR_PATH_IDX","ADR_HOME","ORIGINATING_TIMESTAMP"
,"NORMALIZED_TIMESTAMP","ORGANIZATION_ID","COMPONENT_ID","HOST_ID","HOST_ADDRESS
","MESSAGE_TYPE","MESSAGE_LEVEL","MESSAGE_ID","MESSAGE_GROUP","CLIENT_ID","MODUL
E_ID","PROCESS_ID","THREAD_ID","USER_ID","INSTANCE_ID","DETAILED_LOCATION","UPST
REAM_COMP_ID","DOWNSTREAM_COMP_ID","EXECUTION_CONTEXT_ID","EXECUTION_CONTEXT_SEQ
UENCE","ERROR_INSTANCE_ID","ERROR_INSTANCE_SEQUENCE","MESSAGE_TEXT","MESSAGE_ARG
UMENTS","SUPPLEMENTAL_ATTRIBUTES","SUPPLEMENTAL_DETAILS","PARTITION","RECORD_ID"
,"FILENAME","PROBLEM_KEY","VERSION"
  from sys.X$DIAG_ALERT_EXT
 where component_id like '%tnslsnr%'
 order by ORIGINATING_TIMESTAMP

SQL>

SYSスキーマのx$DBGALERTEXT、X$DIAG_ALERT_EXTだとわかります。
これはAWS環境固有のオブジェクトではなく、オンプレ環境でも作成されるオブジェクトです。
実体はADR下のalertフォルダ内の log.xml のようです。
(オンプレ環境でlog.xmlのエントリーを手動で1つ消すと上記テーブルの件数が1減りました)
ここから例えばアラートログに "ORA-" が出力された場合、検知するような構成を考えると、以下のようなSQLを定期的に実行することになります。

select message_text from sys.x$dbgalertext
where originating_timestamp > (sysdate - 1/24)
and message_text like '%ORA-%';

上記SQLでは直近1時間以内のORA-エラーをSELECTしています。
監視製品を利用してログ監視を行う場合、前回どこまで読んだか保持していたりするので、それと同じレベルで監視しようとすると作りこみは必要そうです。
また、ALERTLOG、LISTENERLOGともに10MBごとにローテーションされる仕様となっているため、ローテーションされたときに正常に検知できるようにするための検討も併せて必要になります。
CLIでファイルを取得して内容を確認するのとどちらが楽か、難しいところです。

表領域使用率監視

マスター用ユーザにはDBAロールが付与されているので、ディクショナリビューへのアクセスは無問題です。
まず、おなじみのdba_data_filesとdba_free_spaceを使った使用率計算です。(こちらのページSQLを参考にさせていただきました)

SQL> select tablespace_name
  2        ,sum(bytes)/1024/1024 bytes_mb
  3        ,sum(bytes-nvl(c3,0))/1024 used_kb,sum(nvl(c3,0))/1024 free_kb
  4        ,round((sum(bytes-nvl(c3,0))/1024)/(sum(bytes)/1024)*100,2) "ts_used_%"
  5    from dba_data_files a,
  6        (select tablespace_name c1,file_id c2,sum(bytes) c3
  7           from dba_free_space
  8          group by tablespace_name,file_id) b
  9   where a.tablespace_name=b.c1(+)
 10     and a.file_id=b.c2(+)
 11   group by tablespace_name
 12  order by 1;

TABLESPACE_NAME        BYTES_MB    USED_KB    FREE_KB  ts_used_%
-------------------- ---------- ---------- ---------- ----------
RDSADMIN                      1        384        640       37.5
SYSAUX                      400     306624     102976      74.86
SYSTEM                      300     295488      11712      96.19
TESTTBS                     100       1024     101376          1
UNDO_T1                     210      12736     202304       5.92
USERS                      2048       1024    2096128        .05

SQL>

ただ、この方式だと現在のデータファイルのサイズと使用率をベースにしているため、自動拡張されたときの最大サイズに対するパーセンテージは取得できません。

SQL> select
  2    tablespace_name,
  3    tablespace_size*8192/1024/1024 BYTES_MB,
  4    round(used_percent,2) "ts_used_%"
  5  from
  6    dba_tablespace_usage_metrics
  7  order by 1;

TABLESPACE_NAME        BYTES_MB  ts_used_%
-------------------- ---------- ----------
RDSADMIN             26186.1406          0
SYSAUX               26585.1406       1.13
SYSTEM               26485.1406       1.09
TEMP                 26285.1641          0
TESTTBS              26285.1406          0
UNDO_T1              26395.1641          0

ここで、dba_tablespace_usage_metricsのtablespace_sizeはどうやらファイルシステムの残り容量をもとに算出されるようです。
ためしに1つの表領域を10GB拡張すると以下のようになります。

SQL> alter tablespace USERS resize 12288M;

表領域が変更されました。

SQL> select
  2    tablespace_name,
  3    tablespace_size*8192/1024/1024 BYTES_MB,
  4    round(used_percent,2) "ts_used_%"
  5  from
  6    dba_tablespace_usage_metrics
  7  order by 1;

TABLESPACE_NAME         BYTES_MB  ts_used_%
-------------------- ----------- ----------
RDSADMIN              15945.7266          0
SYSAUX                16344.7266       1.85
SYSTEM                16244.7266       1.78
TEMP                  16044.7266          0
TESTTBS               16044.7266        .01
UNDO_T1               16154.7266        .02

7行が選択されました。

SQL>

表領域のサイズがすべて10GB程度下がりました。 まぁファイルサイズの容量を見るなら何もSQLで確認する必要はないので、表領域サイズを固定したときに前者のようなSQLを使うことを検討すれば良さそうです。

まとめ

Oracle固有の監視について簡単に確認みました。
最近だと、MySQL系のRDSについてはCloudWatchでログ監視できるようになったという公式ブログの案内がありましたが、Oracleでもこういうことができるようになったら便利になりますね。