#!/bin/sh
#--------------------------------------------------------------------------------
# Name: stat.sh
# Purpose: Ejecucion Estadisticas de bases de Datos ORACLE 10G
# Parameters: ORACLE_SID / Nombre de archivo con los esquemas a analizar
# Created by: Angel Armas
# Version: 1.0 Version Date: Nov 19, 2013
# Change History:
#--------------------------------------------------------------------------------
# Modifica las personas que recibiran el correo en caso de ser necesario
#--------------------------------------------------------------------------------
#### Environment ########
ORACLE_SID=$1
SCHEMAS_TAB=$2
export ORACLE_SID SCHEMAS_TAB
#### Environment #####
ORACLE_HOME=/opt/app/oracle/product/10.2.0
ORACLE_BASE=/opt/app/oracle
PATH=$ORACLE_HOME/bin:/usr/ccs/bin:$PATH.
TNS_ADMIN=$ORACLE_HOME/network/admin
NLS_LANG=american_america.we8dec ; export NLS_LANG
export ORACLE_HOME ORACLE_BASE PATH TNS_ADMIN NLS_LANG
#### Environment for running script #####
SCP_PATH=/oracle/scripts/estadistica
PATH_LISTA=/oracle/scripts/estadistica
LOG_PATH=$SCP_PATH/logs
FECHABK=`date "+%Y%m%d-%H%M"`; export FECHABK
LOGFILE=stat_${ORACLE_SID}_${FECHABK}.log
HOST=`hostname`
DBMAIL="logs.dpto.operaciones@seguroshorizonte.com"
ERROR_MAIL="basededatos@seguroshorizonte.com"
export SCP_PATH PATH_LISTA LOG_PATH FECHABK LOGFILE HOST DBMAIL ERROR_MAIL
#### Log Directories that keep for 7 days ####
if [ -d $LOG_PATH ]
then
find $LOG_PATH/*.log -maxdepth 1 -mindepth 1 -mtime +7 | xargs rm -f
else
mkdir -p $LOG_PATH
fi
#### Ejecucion de Estadisticas ######
for i in `cat $PATH_LISTA/$SCHEMAS_TAB`
do
echo "Starting to gather statistics" $i >> $LOG_PATH/$LOGFILE
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF >> $LOG_PATH/$LOGFILE
connect / as sysdba;
set timi on;
exec dbms_stats.gather_schema_stats(ownname => '$i', estimate_percent => 100, granularity => 'ALL', method_opt => 'FOR ALL COLUMNS SIZE 1', degree => 20, options => 'GATHER', cascade => TRUE);
exit;
EOF
echo "It is finisshed " $i >> $LOG_PATH/$LOGFILE
done
##### to send email ######
resultado=`grep 'ORA-' $LOG_PATH/$LOGFILE`
export resultado
if [ "$resultado" ]
then
mailx -s "ERROR -> Gather statistics on $HOST database $ORACLE_SID" $ERROR_MAIL < $LOG_PATH/$LOGFILE
else
mailx -s "Success -> Gather statistics on $HOST database $ORACLE_SID" $DBMAIL < $LOG_PATH/$LOGFILE
echo "its success to Gather statistics" > $LOG_PATH/status_last_stat_${ORACLE_SID}.log
fi
