Oracle – Récupération des Statspacks par lots

#!/bin/bash
###############################################################################
# @(#) Script : all_spreports.sh
# @(#) Licence : GNU GPL
# @(#) Auteure : Alexandra Champavert
# @(#) Date creation : 10/01/2015
# @(#) Date modification : 16/03/2015
# @(#) Version : 1.00
# @(#) Versions :
# @(#) 0.90 : 10/01/2015 ACH : Version initiale
# @(#) 1.00 : 16/03/2015 ACH : Test parametres, ajout liste instances, ajout oraenv
#
# @(#) Parametres :
# @(#) ${1} : Date de debut au format JJ-MM-AAAA
# @(#) ${2} : Date de fin au format JJ-MM-AAAA
# @(#) ${3} : Liste des instances a parcourir entre double-cotes et separees par des espaces. Ex : "INSTA1 INSTB2 INSTC0"
#
# @(#) Utilisation :
# @(#) Extraction des rapports Statspack d'une liste d'instances avec creation d'un sous-repertoire par instance.
###############################################################################

if [ $# -ne 3 ]
then
 echo "ERREUR - nombre d'argument"
 echo "USAGE : $0 <Date debut> <Date fin> <Liste instances entre double-cotes separees par des espaces>"
 exit 2
fi

for i in ${3}
do
 export ORACLE_SID=${i}
 ORAENV_ASK=NO
 . oraenv
 min_snap=`
 export ORACLE_SID=${i}
 sqlplus -S /nolog<<ENDSQL
connect / as sysdba
set echo off
set head off
set trimspool on
set pagesize 0
set linesize 100
ttitle off
btitle off
set verify off
set feedback off
select min(snap_id) from stats\\$snapshot where snap_time >= to_date('${1} 00:00:00','DD-MM-YYYY HH24:MI:SS');
exit
ENDSQL`

 max_snap=`
 export ORACLE_SID=${i}
 sqlplus -S /nolog<<ENDSQL
connect / as sysdba
set echo off
set head off
set trimspool on
set pagesize 0
set linesize 100
ttitle off
btitle off
set verify off
set feedback off
select max(snap_id) from stats\\$snapshot where snap_time <= to_date('${2} 23:59:59','DD-MM-YYYY HH24:MI:SS');
exit
ENDSQL`

 snap_curr=${min_snap}
 snap_stop=${max_snap}
 list_snaps=`
 export ORACLE_SID=${i}
sqlplus -S /nolog<<ENDSQL
connect / as sysdba
set echo off
set head off
set trimspool on
set pagesize 0
set linesize 100
ttitle off
btitle off
set verify off
set feedback off
select snap_id from stats\\$snapshot where snap_id between ${min_snap} and ${max_snap} order by snap_id;
exit
ENDSQL`
 debut=1
 mkdir -p ${i}
 for j in ${list_snaps}
 do
 snap_next=${j}
 if [ ${debut} -eq 0 ]
 then
 psc=`echo ${snap_curr}|awk '{printf("%05d",$1);}'`
 psn=`echo ${snap_next}|awk '{printf("%05d",$1);}'`
 sqlplus /nolog<<ENDSQL
connect / as sysdba
define num_days=${num_days}
define begin_snap=${snap_curr}
define end_snap=${snap_next}
define report_name=${ORACLE_SID}/sp_${ORACLE_SID}_${psc}_${psn}.lst
define num_days=1000
@@?/rdbms/admin/spreport.sql
ENDSQL
 fi
 debut=0
 snap_curr=${j}
 done
done