シェルスクリプトからSQLPLUSでOracleデータベースにアクセスする

プログラミング言語からデータベースにアクセスする際は、ドライバをインストールしたり何かと面倒ですが、Linux環境にSQLPLUSが入っていればシェルから手軽に起動し、データベースにアクセスすることができます。

今回はシェルを使いSQLPLUSからDBにアクセスする方法について学んでいきます。

事前準備
シェルスクリプトの作成
動作確認
参考書籍

事前準備

今回はCentOS7上にインストールしたOracle 12c R2を使い、その際に一緒にインストールされたSQLPLUSを使います。

シェルスクリプトの内容としては、

CDB上のリソース情報（プロセス数一覧）を取得する
リソース情報をログファイルに出力する
cronから1分周期で実行する
エラー時は別途エラーファイルに出力し、異常終了とする

のようなものを作成していきます。

シェルスクリプトの作成

それではスクリプトを作成していきます。

なお、SQLPLUSを単体でインストールした場合、cronから呼び出す際に共有ライブラリのパスが変わっている場合があるので、その際は別途exportが必要となります。

# check_db_resource.sh
#!/bin/bash

export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/dbhome_1
export ORACLE_SID=orcl
export LANG=ja_JP.UTF-8
export NLS_LANG=Japanese_Japan.AL32UTF8
USER="sys"
PASS="oracle"
BASEDIR="/home/oracle"
DBLOGFILE="$BASEDIR/dbprocesses.log"
DBERRORFILE="$BASEDIR/dberror.log"
DB_CONNECT="${USER}/${PASS} as sysdba"
SQLPLUS="/opt/app/oracle/product/12.2.0.1/dbhome_1/bin/sqlplus"
HEAD="datetime,current_utilization,max_utilization,initial_allocation,limit_value"
ERRHEAD="datetime,error_message"
NOW=`date +"%Y%m%d-%H:%M:%S"`

RESULT=`$SQLPLUS -s $DB_CONNECT <<EOF
WHENEVER SQLERROR EXIT 1
SET HEAD OFF
SET TRIMS ON
SET TERMOUT OFF
SET FEEDBACK OFF
SET ECHO OFF

SELECT CURRENT_UTILIZATION || ',' || 
MAX_UTILIZATION  || ',' ||
TRIM(INITIAL_ALLOCATION)  || ',' ||
TRIM(LIMIT_VALUE)
FROM V\\$RESOURCE_LIMIT
WHERE RESOURCE_NAME = 'processes';

exit
EOF`

if [ $? -eq 1 ]; then
  if [ ! -f "$DBERRORFILE" ]; then
    echo $ERRHEAD > $DBERRORFILE
  fi
  ERRMSG=`echo "$RESULT" | awk ' /ORA-/ {print }'`
  echo "$NOW,$ERRMSG" >> $DBERRORFILE
  exit 1
fi

if [ ! -f "$DBLOGFILE" ]; then
  echo $HEAD > $DBLOGFILE
fi
RESULT=`echo $RESULT | sed -e "s/[\r\n]\+//g"`
echo "$NOW,$RESULT" >> $DBLOGFILE

動作確認

それでは、上記スクリプトをcronに追加して実行させます。

 # dbprocesses.log
 datetime,current_utilization,max_utilization,initial_allocation,limit_value
 20200718-23:07:01,51,66,300,300
 20200718-23:08:01,51,66,300,300

エラーが出た場合も以下に出力されていることが確認できます。

 # dberror.log
 datetime,error_message
 20200718-23:03:01,ORA-12162: TNS:net service name is incorrectly specified
 20200718-23:11:01,ORA-00942: 表またはビューが存在しません。