Statspackレポートを定期的（1日1回）に自動で作成する方法

OracleのStatspackレポートを、自動で1日1回、1時間間隔のレポート23個を定期処理で作成したいなぁと思いましたのでシェルを作成してみました。

全く改善させていないショボいスクリプトで申し訳ないですが、せっかくなので共有してみたいと思います。（同じようなことをしたい人がきっといるはずだと思いますので‥）

改善案などありましたらぜひアドバイスいただけると嬉しいです。

1 要件
2 構成
3 仕組み・スクリプト

要件

要件として、下記のような状況の中で自動作成を目指しました。

1時間間隔（自動）でスナップショットを作成
前日のレポートを作成
レポートは1時間間隔の24時間分におけるStatspackレポート23個
OS oracleユーザーのCronで実施
インスタント名はORCL

構成

構成は下記のような感じです。ディレクトリ（tmp）の中にスナップショットリストのファイルを一時的に作成します。

get_sptext.sh　➡　本体
[ディレクトリ（sql）] REPORT1.sql　➡　スナップショットリストを取得するSQLファイル
[ディレクトリ（sql）]REPORT2.sql　➡　Statspackレポートを作成するSQLファイル
[ディレクトリ（tmp）]

仕組み・スクリプト

REPORT1.sql で前日のスナップショットリストを取得してきます。取得したリストをもとにぐるぐるまわしてStatspackレポートを作成します。

本体となるシェルスクリプト（get_sptext.sh）は下記の通りです。

#!/bin/sh
#-----------------------------------------------------------------------------
#
# Statspackレポートの定期作成
#
#-----------------------------------------------------------------------------

#ORACLE環境変数
export ORACLE_HOME=/opt/oracle/app/oracle/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=/opt/oracle/app/oracle/product/11.2.0/dbhome_1/lib:
export NLS_LANG=japanese_japan.UTF8
export ORACLE_SID=ORCL
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

#Oracle BIN
ORACLEBIN="${ORACLE_HOME}/bin"

#スクリプトディレクトリ
SYSDIR=/home/oracle/Cron/createSPreport
SQL_DIR=$SYSDIR/sql
TMP_DIR=$SYSDIR/tmp
MONTH=$(date -d "1 days ago" '+%Y-%m')
REPORT_DIR=/mnt/backup/$MONTH
#保存先のディレクトリが無ければ作成
if [ ! -d $REPORT_DIR ]
then
    mkdir $REPORT_DIR
fi


### 前日分のスナップショットのリストを作成
${ORACLEBIN}/sqlplus -s / as sysdba @${SQL_DIR}/REPORT1.sql ${TMP_DIR}/REPORT.lst
if [ $? != 0 ] ; then
    echo "スナップIDの取得に失敗しました。"
    exit 99
fi

### Statspackレポートの作成
cat ${TMP_DIR}/REPORT.lst | while read SNAP_START SNAP_END DBID INSTANCE_NAME INSTANCE_NUMBER SNAP_TIME SNAP_LEVEL
do
    REPORT_NAME="${REPORT_DIR}/${INSTANCE_NAME}_${SNAP_TIME}"
    echo "${REPORT_NAME}.txt BEGIN_TIME[ ${SNAP_TIME} ]"
    ${ORACLEBIN}/sqlplus -s / as sysdba @${SQL_DIR}/REPORT2.sql "${SNAP_START}" "${SNAP_END}" "${REPORT_NAME}.txt" > /dev/null
    if [ $? != 0 ] ; then
        echo  "Statspackレポートの作成に失敗しました。"
        exit 99
    fi
done

exit 0

スナップショットリストを取得するSQL（REPORT1.sql）は下記の通りです。

set linesize 1000
set pagesize 0
set tab off
set echo off
set head off
set feedback off
set verify off
set trimspool on
set arraysize 5000
set term off

whenever oserror exit 99
whenever sqlerror exit 99

spool &1

SELECT
    a.snap_id as snap_start,
    a.snap_id + 1 as snap_end,
    a.dbid,
    b.instance_name,
    a.instance_number,
    to_char(a.snap_time,'YYYYMMDD_HH24MISS') as snap_time,
    a.snap_level
FROM
    stats$snapshot a,
    v$instance b
WHERE
    a.instance_number = b.instance_number  AND
    to_char(snap_time,'YYYY-MM-DD') = TO_CHAR(SYSDATE-1,'YYYY-MM-DD')  AND
    b.instance_name = 'ORCL'
ORDER BY
    a.snap_time;

spool off
exit

Statspackレポートを作成するSQL（REPORT2.sql）は下記の通りです。

set linesize 200
set pagesize 0
set tab off
set echo off
set head off
set feedback off
set verify off
set trimspool on
set arraysize 5000
set term off

whenever oserror exit 99
whenever sqlerror exit 99

define begin_snap="&1"
define end_snap="&2"
define report_name="&3"

@?/rdbms/admin/spreport
exit

以上で完了です。
あとはCronに get_sptext.sh を指定すれば、指定したバックアップディレクトリにレポートが定期的に作成されていきます。