#!/bin/sh
#
# sysstatgar.sh, Gather  system v$sysstat details .
#  sh sysstatgar.sh SERVICE_IP

if [ $# -ne 1 ]; then
 echo "Usage: sh sysstatgar.sh SERVICE_IP"
 exit 1;
fi

BASE_HOME=`/usr/bin/dirname "$0"`
. ~/.profile

# Log To LogFile
MLOGFILE=${BASE_HOME}/`hostname`_sysstat_`date +%Y%m`.log
BEFOREVALUE=${BASE_HOME}/before_value.file
KPIFILE=${BASE_HOME}/kpi_key.value


exec >> ${MLOGFILE} 2>&1

echo "Current time " `date +%Y%m%d%H%M%S`

IPADDR=$1

# Get Instance name
INSTNAME=`sqlplus -S '/ as sysdba' << EO
set pages 0 heading off verify off  feedback off echo off
SELECT instance_name FROM v\\\$instance;
EO`

ftp_upload_()                                                 
{                                                             
                                                              
TEMP="$1|$2|$3|`date +%Y%m%d%H%M%S`"                  
TMPFILE="`hostname`_oracle_`date +%Y%m%d%H%M%S`.tok"
echo ${TEMP} > /tmp/${TMPFILE}                                                                                       
ftp -n << EOD
open 10.19.251.64
user aiox zwzx%1208B
prompt off
bin
cd  /home/aiox/busicollect/oracle_coll/source
lcd /tmp
put ${TMPFILE}
close
bye
EOD
                                     
rm -f /tmp/${TMPFILE}                                        
                                                              
}


if [ ! -s ${KPIFILE} ]; then
echo "SDBB_01    user_commits              " >> ${KPIFILE}   
echo "SDBB_02    user_rollbacks            " >> ${KPIFILE}   
echo "SDBB_03    user_calls                " >> ${KPIFILE}   
echo "SDBB_04    session_logical_reads     " >> ${KPIFILE}   
echo "SDBB_05    CPU_used_by_this_session  " >> ${KPIFILE}   
echo "SDBB_06    DB_time                   " >> ${KPIFILE}   
echo "SDBB_07    session_pga_memory_max    " >> ${KPIFILE}   
echo "SDBB_08    enqueue_waits             " >> ${KPIFILE}   
echo "SDBB_09    consistent_gets           " >> ${KPIFILE}   
echo "SDBB_10    physical_reads            " >> ${KPIFILE}   
echo "SDBB_11    physical_writes           " >> ${KPIFILE}   
echo "SDBB_12    redo_size                 " >> ${KPIFILE}   
echo "SDBB_13    undo_change_vector_size   " >> ${KPIFILE}   
echo "SDBB_14    transaction_rollbacks     " >> ${KPIFILE}   
echo "SDBB_15    table_scans_(long_tables) " >> ${KPIFILE}   
echo "SDBB_16    parse_count_(total)       " >> ${KPIFILE}   
echo "SDBB_17    parse_count_(hard)        " >> ${KPIFILE}   
echo "SDBB_18    execute_count             " >> ${KPIFILE}   
echo "SDBB_19    sorts_(memory)            " >> ${KPIFILE}
echo "SDBB_20    sorts_(disk)              " >> ${KPIFILE}
fi

BCNT=`cat ${BEFOREVALUE}|wc -l`


echo "ORACLE DATABASE SYSSTAT GATHER BEGIN......................................"

TEMPFILE="${BASE_HOME}/systat_gar_`date +%Y%m%d%H%M%S`.log"





sqlplus -S '/ as sysdba' << EOF
set line 330
col name for a30
set pages 0 heading off verify off  feedback off echo off
col value for 9999999999999999999999999
spool ${TEMPFILE}
SELECT a.statistic#,replace(b.name,' ','_'),a.value value FROM v\$sysstat a,v\$statname b
WHERE a.statistic# = b.statistic#
AND replace(b.name,' ','_') IN ('user_commits','user_rollbacks','user_calls','session_logical_reads','CPU_used_by_this_session','DB_time','session_pga_memory_max','enq
ueue_waits','consistent_gets','physical_reads','physical_writes','redo_size','undo_change_vector_size','transaction_rollbacks','table_scans_(long_tables)','parse_count
_(total)','parse_count_(hard)','execute_count','sorts_(memory)','sorts_(disk)');
spool off;
exit;
EOF

if [ ${BCNT} -gt 4 ]; then
while read line
do
statname=`echo ${line}|awk '{print $2}'`
LASTVAL=`cat ${BEFOREVALUE}|grep ${statname}|awk '{print $3}'`
CURVAL=`echo ${line}|awk '{print $3}'`
DIFFVAL=`expr ${CURVAL} - ${LASTVAL}`

KPI=`cat ${KPIFILE}|grep ${statname}|awk '{print $1}'`

ftp_upload_  $IPADDR"*oracle*"${INSTNAME}  ${KPI} ${DIFFVAL}

sleep 2
done < ${TEMPFILE}
fi
cat ${TEMPFILE} > ${BEFOREVALUE}

rm -f  ${TEMPFILE}

echo "ORACLE DATABASE SYSSTAT GATHER END......................................"