リアルタイムでサーバの全プロセスを出力するテーブルを作ってみる(Oracle)

###はじめに

外部テーブル定義のプリプロセッサ句利用して、データベースサーバ上の全プロセスをリアルタイムで出力するテーブルを作ってみます。ちなみにプリプロセッサ句は、11gR2で追加された機能です。

###テーブル出力例

テーブルの出力は以下のようになります。なんとなく見た目すごく便利そうなテーブルですが、データベース内でプロセスを見る必要性を感じないのでたぶん役に立たないでしょう(^^)。なんかに使えますかね？

```
SQL> SELECT * FROM PROCESS_LIST;

HOST                PID USERNAME     CPUTIME      CMD
------------ ---------- ------------ ------------ ----------------------------------------
dbserver40         2476 oracle       00:00:00     mdb_w002_-MGMTDB
dbserver40         2534 root         00:00:07     [kworker/25:1H]
dbserver40         2693 root         00:00:00     /usr/sbin/oddjobd -n -p /var/run/oddjobd.pid -t 300
dbserver40         2695 root         00:08:42     /usr/bin/python -Es /usr/sbin/tuned -l -P
dbserver40         2697 rpcuser      00:00:00     /usr/sbin/rpc.statd
dbserver40         2698 root         00:03:43     /opt/puppetlabs/puppet/bin/ruby /opt/puppetlabs/puppet/bin/puppet agent --no-dae
dbserver40         2699 root         00:00:21     /usr/sbin/sshd -D
dbserver40         2708 rpc          00:00:10     /sbin/rpcbind -w
dbserver40         2715 root         03:34:36     /usr/sbin/tg3sd
dbserver40         2746 root         00:00:00     [nfsiod]
dbserver40         2753 root         00:00:00     [lockd]
dbserver40         2786 root         00:00:00     /usr/sbin/rpc.mountd
dbserver40         2794 root         00:00:11     /usr/sbin/crond -n
...
...
```

###作り方

ま、遊びなんでいいんです。では、作り方です。

まずは、RDBMSユーザでデータベースサーバにログインして新しいディレクトリをつくり、そこにプロセスをカンマ区切りで出力するシェルスクリプトを作ります。RACの場合は、スクリプトを共有ディレクトリに置くか、コピーして全ノードから見えるようにします。スクリプトはリスナープロセスオーナで実行されるので、ファイルのパーミッションは、リスナーオーナ(環境に応じてGridまたはRDBMSユーザ)が実行できるようにしておきます[^1]。

[^1]: 外部プロシジャコールを使用しているためと思われます。ただしCREDENTIAL(12c)は使えないようです。


また、スクリプトはPATHが全く設定されていない状態で実行されるため、コマンドに絶対パスを使用するかスクリプト内でPATH変数を設定します。

```shell:
$ mkdir /shared/ext/bin
$ cat <<EOF > /shared/ext/bin/process_list.sh
#/bin/sh
/bin/ps -e -opid,user,time,cmd | /bin/sed -rn 's/^ *([0-9]+) *(\w+) *([0-9:-]+) *([^,]{1,80}).*$/'\$(/bin/hostname -s)',\1,\2,\3,\4/p'
EOF
$ chmod 775 /shared/ext/bin/process_list.sh
```

次に、データベースにログインし、先程のシェルスクリプトのディレクトリのディレクトリオブジェクトを作成しREAD、WRITE、EXECUTE権限をユーザに許可します（ログディレクトリとスクリプトスクリプトを別にする場合は、ログディレクトにはREAD,WRITE、スクリプト用ディレクトリには、READ,EXECUTE）。

```sql:ディレクトリの設定
CREATE DIRECTORY EXT_BIN as '/shared/ext/bin';
GRANT READ,WRITE,EXECUTE ON DIRECTORY EXT_BIN TO <user>;
```

さらに、先程のスクリプトをプリプロセッサとして指定した外部テーブルを作ります。LOCATIONには、本来は外部テーブルのデータソースとなるファイルを指定しますが、ここではデータソースファイルがありませんので、ダミーとしてシェルスクリプトを指定しています。これは読めるファイルであればなんでも構いません。

```sql:外部テープルの作成
CREATE TABLE process_list
  (
    host     VARCHAR2(12),
    pid      NUMBER,
    username VARCHAR2(12),
    cputime  VARCHAR2(12),
    cmd      VARCHAR2(80)
  )
  organization external
  (
    type oracle_loader DEFAULT directory EXT_BIN 
    access parameters (
         records delimited BY newline
         LOGFILE 'process_list.log'
         BADFILE 'process_list.bad'
         preprocessor EXT_BIN:'process_list.sh'
         fields terminated BY ",")
  location ('process_list.sh')
  )
  reject limit unlimited;
```

これで完了です。テーブルを覗いてみます。テーブルを参照するたびにシェルスクリプトが実行され最新のプロセスリストが出力さることを確認してください。

```
SQL> SELECT * FROM PROCESS_LIST WHERE USERNAME = 'oracle';

HOST                PID USERNAME     CPUTIME      CMD
------------ ---------- ------------ ------------ ----------------------------------------
dbserver40        16365 oracle       02:03:01     /u01/app/12.1.0.2/grid/bin/oraagent.bin
dbserver40        16378 oracle       01:13:07     /u01/app/12.1.0.2/grid/bin/mdnsd.bin
dbserver40        16381 oracle       03:04:36     /u01/app/12.1.0.2/grid/bin/evmd.bin
dbserver40        16394 oracle       01:11:44     /u01/app/12.1.0.2/grid/bin/gpnpd.bin
...
...
```

###RACデータベース

データベースがRACであれば、全ノードでPSコマンドを実行する必要があります。これには、以下のどちらかの方法を用います。

- 上記シェルスクリプト内で全ノードに対しSSH経由でPSをそれぞれ実行する。
- それぞれのノードに接続するDB LINKを経由して外部テーブルを参照するVIEWを作る。

ここでは、後者の方法でグローバル参照のビューを作ってみます。やり方はいろいろあると思いますが、以下ではVIP経由でローカルリスナーに接続しています。これで特定のインスタンスに接続しつつも一部ノードダウン時にタイムアウトになりません。また、VIPフェイルオーバー時のためにホストの条件をつけています。

```sql:全サーバプロセス
CREATE DATABASE LINK NODE1 CONNECT TO <user> IDENTIFIED BY <pass>
USING '<node1 VIP>:<port>/<service_name>';
CREATE DATABASE LINK NODE2 CONNECT TO <user> IDENTIFIED BY <pass>
USING '<node2 VIP>:<port>/<service_name>';

CREATE VIEW PROCESS_LIST_GV AS
SELECT * FROM PROCESS_LIST@NODE1 where host = 'dbserver40'
UNION ALL
SELECT * FROM PROCESS_LIST@NODE2 where host = 'dbserver41'
;
```

```
SQL> SELECT host, COUNT(*) FROM PROCESS_LIST_GV GROUP BY host;

HOST                 COUNT(*)
------------ ----------------
dbserver41                954
dbserver40              1,063
```

###おわりに

本来、プリプロセッサ句はデータファイルを事前処理してオラクルが外部テーブルとして読み込めるようにするためのものです。これにより、行ごとにフォーマットされていないログのようなファイル(alert.logが代表格[^2])をCSVに変換したり、圧縮データファイルを展開したり、またはリモートサーバーにあるファイルをダウンロートしてから、外部テーブルとして見せることが出来るようになります。うまく使えばいい感じな機能ではありますね。

[^2]: alert.logには既にx$dbgalertextが用意されれてますけどね。


以上です。