#!/bin/ksh

#===================================================================#
# #
# Name : verifydb.sh #
# Synopsis : Verifies the datafiles of the database specified in #
# the script's only argument. #
# Please remember to customise the values in the #
# CONSTANTS section before attempting to use the script. #
# Arguments: The following parameters must be provided when #
# running this script: #
# 1) The sid of the database to run against #
# Source : http://www.oracle-dba.fr.pl #
# #
#===================================================================#

#--------------------------------------------------------------------
# ARGUMENTS

export ORACLE_SID=${1}

#--------------------------------------------------------------------
# CONSTANTS

export HOME=`awk -F: '/oracle/ {print $6}' /etc/passwd`
export DATESTAMP=`date '+%m-%d-%y_%H:%M:%S'`
export LOG=/tmp/ver.log
export SPOOL=/tmp/ver.lst
export DATAFILES=/tmp/datafiles.lst
export DATAFILES2=/tmp/datafiles2.lst
export RESULT_LOG="${HOME}/reports/verify_db.${ORACLE_SID}_${DATESTAMP}"

export SYSTEM_PWD=manager
export ORATAB=/var/opt/oracle/oratab

#--------------------------------------------------------------------
# FUNCTIONS

usage()
{

echo ""
echo "USAGE:"
echo "******"
echo "verifydb.sh sid"
echo ""
echo "where sid = database sid to run against"

}

derive_oracle_paths()
{

if [ -f ${ORATAB} ]; then
export ORACLE_HOME=`awk -F: "/^${ORACLE_SID}:/ {print \\$2; exit}"\
${ORATAB} 2>/dev/null`
export PATH=${ORACLE_HOME}/bin:${PATH}
else
echo "" >> ${RESULT_LOG}
echo "ERROR:" >> ${RESULT_LOG}
echo "******" >> ${RESULT_LOG}
echo "${ORATAB} not found" >> ${RESULT_LOG}
echo "Aborting verifydb.sh" >> ${RESULT_LOG}
cleanup
exit 1
fi

}

check_parameter()
{

if [ `grep -c ${ORACLE_SID} ${ORATAB}` -eq 0 ]; then
echo "" >> ${RESULT_LOG}
echo "ERROR:" >> ${RESULT_LOG}
echo "******" >> ${RESULT_LOG}
echo "Oracle sid ${ORACLE_SID} not found in ${ORATAB}" >> ${RESULT_LOG}
echo "Aborting verifydb.sh" >> ${RESULT_LOG}
cleanup
exit 1
fi

}

do_verify()
{

if [ -f ${LOG} ]; then
rm ${LOG}
fi

sqlplus system/${SYSTEM_PWD} << _E_O_F

set pagesize 0
set linesize 132
set feedback off
set sqlprompt ""
set echo off
set verify off
set trimspool on

Whenever SQLERROR exit 1

select '!dbv file='||file_name||' blocksize=4096 >> ${LOG} 2>>${LOG}'
from dba_data_files

spool ${SPOOL}
/
spool off

start ${SPOOL}

exit
_E_O_F

}

check_verify_log()
{

sqlplus system/${SYSTEM_PWD} << _E_O_F

set pagesize 0
set feedback off
set sqlprompt ""
set trimspool on

Whenever SQLERROR exit 1

select count(1)
from dba_data_files

spool ${DATAFILES}
/
spool off

exit
_E_O_F

sed -n '2p' ${DATAFILES} > ${DATAFILES2}
FILE_NO=`awk '{print $1}' ${DATAFILES2}`

if [ `grep -ic "Total Pages Processed (Data)" ${LOG}` -lt ${FILE_NO} ]; then
echo "" >> ${RESULT_LOG}
echo "ERROR:" >> ${RESULT_LOG}
echo "******" >> ${RESULT_LOG}
echo "Database verification terminated unexpectedly" >> ${RESULT_LOG}
echo "Aborting verifydb.sh" >> ${RESULT_LOG}
cleanup
exit 1
fi

FAILED_PAGES=`awk -F: '/Failing/ { s += $2 } END {print s}' ${LOG}`

if [ ${FAILED_PAGES} -gt 0 ]; then
echo "" >> ${RESULT_LOG}
echo "ERROR:" >> ${RESULT_LOG}
echo "******" >> ${RESULT_LOG}
echo "${FAILED_PAGES} pages failed database verification - check ${LOG} for details" >> ${RESULT_LOG}
echo "Aborting verifydb.sh" >> ${RESULT_LOG}
cleanup
exit 1
else
echo "" >> ${RESULT_LOG}
echo "Verification of ${ORACLE_SID} successful" >> ${RESULT_LOG}
fi

}

# Note: Cleanup does not remove ${LOG} in case this is needed in error tracking
cleanup()
{

if [ -f ${SPOOL} ]; then
rm ${SPOOL}
fi

if [ -f ${DATAFILES} ]; then
rm ${DATAFILES}
fi

if [ -f ${DATAFILES2} ]; then
rm ${DATAFILES2}
fi

}

#--------------------------------------------------------------------
# MAIN

if [ $# -lt 1 ]; then
usage
exit 1
fi

derive_oracle_paths
check_parameter
do_verify
check_verify_log
cleanup