bashからsqlplusを起動し、Oracleデータベースの出力を変数に取り込む方法(応用篇)

前回の記事の続きです。

CDBではなくPDBに接続して結果を取得したい

前回の記事ではsqlplusから接続する場合の方式についてローカル接続であることを前提としています。しかし、マルチテナント構成のOracleデータベースの場合、ただローカル接続しただけではCDBに対しての情報は取得できてもPDBからの情報は取得できません。

その対策としては別に難しいものは何もなく、ただそのままローカル接続した直後にalter session set container=PDB名を1回実行すれば問題ありません。例えば、以下のような例となります。

PDB=PDB1
SYSDATE=$(sqlplus -silent "/ as sysdba" << EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
alter session set container=${PDB}
select sysdate from dual;
EXIT;
EOF
)
echo ${SYSDATE}

もちろん、リスナー接続が可能であればそれにより直接PDBへ接続が可能です。ただしその場合はOS認証を使用できずパスワード認証が必要になりますので、別途それに対する考慮が必要となるでしょう。

複数のPDBにまとめて処理を行いたい

マルチテナント構成では、複数のPDBが作成されている可能性があります。その場合、それぞれのPDBに対して一通り同じ情報を取得したり、同じ処理を実行したりしたいといった要件もありえます。

以下に紹介する例については、実際に私が経験した内容についての対応方法をまとめたものです。

PDBのSYSAUX表領域の使用量がなぜか増え続ける‥

これは少し以前に実際のOracle19c運用環境で確認されたことですが、マルチテナント構成のPDBのSYSAUX表領域の使用量が時間と共にじわじわと増え続ける、リークのような現象が確認されました。

より詳細に状況を確認すると、増えている領域は自動メンテナンスタスク機能で生成されるSM/ADVISORというオブジェクトでした。本来、デフォルトの設定では30日以上経過したこれらのオブジェクトは自動で削除されるために増え続ける想定は無かったのですが、自動削除の機能が何らかの原因で機能せず、その結果増え続けている状況のようです。

以下のようなSQLをPDBに対して実行して、とても古い日付のオブジェクトが表示される場合は、同じようにSYSAUX表領域の肥大化が発生している可能性があります。

SQL> select TASK_ID,TASK_NAME,EXECUTION_NAME ,execution_start
  2  from dba_advisor_executions
  3  where TASK_NAME='AUTO_STATS_ADVISOR_TASK' order by 4 desc;
 
   TASK_ID TASK_NAME                 EXECUTION_NAME  EXECUTION_START
---------- ------------------------- --------------- -------------------
         6 AUTO_STATS_ADVISOR_TASK   EXEC_169639     2023/03/22 02:00:51 ★本来は30日以上古いオブジェクトは削除される
         6 AUTO_STATS_ADVISOR_TASK   EXEC_169336     2023/03/21 02:01:48
         6 AUTO_STATS_ADVISOR_TASK   EXEC_169059     2023/03/20 02:01:18
         6 AUTO_STATS_ADVISOR_TASK   EXEC_168772     2023/03/19 02:01:36
         6 AUTO_STATS_ADVISOR_TASK   EXEC_168467     2023/03/18 02:01:28
         6 AUTO_STATS_ADVISOR_TASK   EXEC_168190     2023/03/17 02:01:45
         6 AUTO_STATS_ADVISOR_TASK   EXEC_167902     2023/03/16 02:01:33
         6 AUTO_STATS_ADVISOR_TASK   EXEC_167603     2023/03/15 02:01:40
         :
         :

これに対してサポートの情報も当たってみましたが、少なくともその当時はきれいに恒久対処できそうな情報はありませんでした。一応、prvt_advisor.delete_expired_tasksプロシージャを各PDBに対して手動実行することで古いSM/ADVISORオブジェクトは消えてはくれるので、定期的にこのプロシージャを実行することで対処することにしました。

bashからsqlplusを通して無名PL/SQLを実行し、その結果をCSVで受け取る

その対処を実装するに当たり、削除処理を実行するだけでなく処理実行前後のSM/ADVISORのオブジェクトの数を取得しておくことで、どの程度削減効果があったのかも記録しておきたいところです。

そこで、削除処理実行前のオブジェクト数取得、削除処理実行、削除処理実行後のオブジェクト数取得をまとめて行う無名PL/SQLをbashから投入し、結果をCSVでbashの変数で受け取る処理を実装することにしました。

readarray -t PDBs < <(sqlplus -silent "/ as sysdba" << EOF
  SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
  select name from v\$pdbs where name <> 'PDB$SEED' and open_mode = 'READ WRITE';
  exit;
EOF
)

for PDB in ${PDBs[@]}; do
  PLSQL_OUTPUT=$(sqlplus -silent "/ as sysdba" << EOF
    set serveroutput on feedback off heading off echo off;
    alter session set container=${PDB};
    exec dbms_output.enable;
    declare
      v_sysaux_size   number;
      v_be_object_cnt number;
      v_af_object_cnt number;
    begin
      select space_usage_kbytes into v_sysaux_size from v\$sysaux_occupants where occupant_name='SM/ADVISOR';
      select count(*) into v_be_object_cnt from dba_advisor_objects where task_name='AUTO_STATS_ADVISOR_TASK';
      prvt_advisor.delete_expired_tasks;
      select count(*) into v_af_object_cnt from dba_advisor_objects where task_name='AUTO_STATS_ADVISOR_TASK';
      dbms_output.put_line(v_sysaux_size || ',' || v_be_object_cnt || ',' || v_af_object_cnt);
    end;
    /
EOF
)
  echo "Purging old advisor tasks for ${PDB} is done. SM/ADVISOR size: $(echo ${PLSQL_OUTPUT} | cut -f1 -d',') kbytes, AUTO_STATS_ADVISOR_TASK objects: $(echo ${PLSQL_OUTPUT} | cut -f2 -d',') to $(echo ${PLSQL_OUTPUT} | cut -f3 -d',')"
done

作成上のポイントは以下のとおりです。

• まずCDBに接続し、起動している(open_mode='READ_WRITE')が、シードDBではない(name <> 'PDB$SEED')PDBの名前を取得しbashの配列PDBsへ格納する
• dbms_output.put_lineを使用して情報の取得結果をCSVとして出力。この出力を有効にするため、前もってset serveroutput onを実行する
• alter session set containerを実行するたびにdbms_output.enableを実行する
• v\$ビューへのアクセスが必要だが、"$"はbashスクリプトでは変数の接頭辞として処理されてしまうので、\を使用して無効化する

alter session set containerを使用してコンテナを切り替えると、dbms_outputはdisableにリセットされるので、切り替えのたびにdbms_output.enableの実施が必要となります。マルチテナント構成でこのような内容のスクリプトを実施する際には、この点が思うようにdbms_outputを使用した出力が出来ない原因として陥りやすい点となりますので注意が必要です。

さて、実際の結果は以下のようになりました(PDB名は伏せ字にしています)。PDBによってその効果は異なりますが、定期的に実行させることで肥大化の防止に役立てることができました。

Purging old advisor tasks for AAAAAA is done. SM/ADVISOR size: 26304 kbytes, AUTO_STATS_ADVISOR_TASK objects: 852 to 691
Purging old advisor tasks for BBBBBB is done. SM/ADVISOR size: 22720 kbytes, AUTO_STATS_ADVISOR_TASK objects: 14694 to 11528
Purging old advisor tasks for CCCCCC is done. SM/ADVISOR size: 12480 kbytes, AUTO_STATS_ADVISOR_TASK objects: 889 to 721
Purging old advisor tasks for DDDDDD is done. SM/ADVISOR size: 18432 kbytes, AUTO_STATS_ADVISOR_TASK objects: 0 to 0
Purging old advisor tasks for EEEEEE is done. SM/ADVISOR size: 18880 kbytes, AUTO_STATS_ADVISOR_TASK objects: 0 to 0
Purging old advisor tasks for FFFFFF is done. SM/ADVISOR size: 958720 kbytes, AUTO_STATS_ADVISOR_TASK objects: 2233 to 2233
Purging old advisor tasks for HHHHHH is done. SM/ADVISOR size: 953920 kbytes, AUTO_STATS_ADVISOR_TASK objects: 3835806 to 3826279

この処理内容では特にPL/SQLを使う必要は無いのでは？とか、最終的にechoで出力するのならdbms_output.put_lineのところで1回でできるだろう？、とかの突っ込みはあると思いますが、説明の便宜のため少し改変しています。

終わりに

以上、SQL*Plusとbashを組み合わせた活用方法を2️回に分けて見ていきました。
昨今では、bashより強力な処理体系をもつ言語がいくつもあります。そのため昔ながらのbashには非力な印象をもつ人もいるかもしれませんが、それでもreadarrayやcut(とSQL*PlusのCOLSEPなどの変数設定)を組み合わせるとかなり融通が効くようになりますので、まだまだおすすめです。何より、大抵のLinux環境で追加インストールすることなく最初から使える点がとても大きいですね。