#!/bin/ksh
#============================================================================
# File:		chk_df.sh
# Type:		UNIX korn-shell script
# Author:	Tim Gorman (Evergreen Database Technologies, Inc)
# Date:		02jul99
#
# Description:
#
#	For all UNIX file-systems which can impact the running of the
#	specified Oracle database instance, notify if they are more than
#	more than 99% full.
#
#	The list of UNIX file-systems to be checked comes from the current
#	settings of the following Oracle initialization parameters:
#
#		LOG_ARCHIVE_DEST_1	(Oracle8/9i EE)
#		LOG_ARCHIVE_DEST_2	(Oracle8/9i EE)
#		LOG_ARCHIVE_DEST_3	(Oracle8/9i EE)
#		LOG_ARCHIVE_DEST_4	(Oracle8/9i EE)
#		LOG_ARCHIVE_DEST_5	(Oracle8/9i EE)
#		AUDIT_FILE_DEST
#		BACKGROUND_DUMP_DEST
#		CORE_DUMP_DEST
#		USER_DUMP_DEST
#		ORACLE_TRACE_FACILITY_PATH
#		ORACLE_TRACE_COLLECTION_PATH
#
#	as well as the following directories:
#
#		/
#		/tmp
#
# Exit statuses:
#	0	normal succesful completion
#	1	ORACLE_SID not specified - user error
#	2	ORACLE_SID not valid in ORATAB - user error
#	3	SQL*Plus failed to create "spool" file for report
#	4	SQL*Plus failed to connect to database
#	5	SQL*Plus failed while generating report
#	6	Something is running out of space - check report!!
#
# Modifications:
#	TGorman	02jul99	written with "90% full" as threshold
#============================================================================
_Pgm=chk_df.sh
#
#----------------------------------------------------------------------------
# Korn-shell function to be called multiple times in the script...
#----------------------------------------------------------------------------
notify_via_email() # ...use email to notify people...
{
	if [ ! -r ~/.notify_via_email ]
	then
		echo "...generating default \"~/.notify_via_email\" file..."
		echo root > ~/.notify_via_email
		echo oracle >> ~/.notify_via_email
	fi
	_EmailList=`cat ~/.notify_via_email`
	for _Email in "${_EmailList}"
	do
		echo "${_ErrMsg}" | mailx -s "${_Pgm} ${_OraSid}" ${_Email}
	done
} # ...end of shell function "notify_via_email"...
#
#----------------------------------------------------------------------------
# Verify that the ORACLE_SID has been specified on the UNIX command-line...
#----------------------------------------------------------------------------
if (( $# != 1 ))
then
	exit 1 > /dev/null 2>&1
fi
_OraSid=$1
#
#----------------------------------------------------------------------------
# Verify that the database instance specified is "up"...
#----------------------------------------------------------------------------
_Up=`ps -eaf | grep ora_pmon_${_OraSid} | grep -v grep | awk '{print $NF}'`
if [[ "${_Up}" = "" ]]
then
	exit 0 > /dev/null 2>&1
fi
#
#----------------------------------------------------------------------------
# Verify that the ORACLE_SID is registered in the ORATAB file...
#----------------------------------------------------------------------------
/usr/local/bin/dbhome ${_OraSid} > /dev/null 2>&1
if (( $? != 0 ))
then
	echo "${_Pgm}: \"${_OraSid}\" not local to this host; aborting..."
	exit 2
fi
#
#----------------------------------------------------------------------------
# Set the Oracle environment variables for this database instance...
#----------------------------------------------------------------------------
export ORACLE_SID=${_OraSid}
export ORAENV_ASK=NO
. /usr/local/bin/oraenv > /dev/null 2>&1
unset ORAENV_ASK
#
#----------------------------------------------------------------------------
# Connect via SQL*Plus and product the report...
#----------------------------------------------------------------------------
sqlplus -s /nolog << __EOF__ > /dev/null 2>&1
whenever oserror exit 2
whenever sqlerror exit 2
connect / as sysdba
whenever oserror exit 3
whenever sqlerror exit 4
set echo off feedb off timi off pau off pages 0 lines 500 trimsp on
spool /tmp/chk_df_${ORACLE_SID}.tmp
col name format a30
col value format a100
col kb format 999999999999990
select  'data/temp/logfiles' name,
        value,
        sum(kb) kb
from    (
         select substr(file_name, 1, decode(instr(file_name, '/oradata/'), 0, 0, instr(file_name, '/oradata/')-1)) value,
                sum(decode(autoextensible,'YES',maxbytes,bytes)) / 1024 kb
         from   dba_data_files
         group by substr(file_name, 1, decode(instr(file_name, '/oradata/'), 0, 0, instr(file_name, '/oradata/')-1))
         union all
         select substr(file_name, 1, decode(instr(file_name, '/oradata/'), 0, 0, instr(file_name, '/oradata/')-1)) value,
                sum(decode(autoextensible,'YES',maxbytes,bytes)) / 1024 kb
         from   dba_temp_files
         group by substr(file_name, 1, decode(instr(file_name, '/oradata/'), 0, 0, instr(file_name, '/oradata/')-1))
	 union all
         select substr(f.member, 1, decode(instr(f.member, '/oradata/'), 0, 0, instr(f.member, '/oradata/')-1)) value,
		sum(l.bytes)/1024 kb
	 from	v\$logfile	f,
		v\$log		l
	 where	l.group# = f.group#
         group by substr(f.member, 1, decode(instr(f.member, '/oradata/'), 0, 0, instr(f.member, '/oradata/')-1))
        )
group by value
union all
select	'controlfiles' name,
	substr(name, 1, decode(instr(name, '/oradata/'), 0, 0, instr(name, '/oradata/')-1)) value,
	0 kb
from	v\$controlfile
union all
select	name,
	replace(value, '?', '${ORACLE_HOME}') value,
	0 kb
from	v\$parameter
where	(name like '%_dump_dest'
    or	 name = 'audit_file_dest')
and	value is not null
union all
select	name,
	replace(value, '?', '${ORACLE_HOME}') value,
	0 kb
from	v\$parameter
where	name like 'oracle_trace_%_path'
and	exists (select 1 from v\$parameter
		where name = 'oracle_trace_enable'
		and value = 'TRUE')
and	value is not null
union all
select	name,
	substr(value, 9, (instr(value, ' ') - 9)) value,
	0 kb
from	v\$parameter
where	name = 'log_archive_dest_1'
and	upper(value) like 'LOCATION=%'
and	exists (select 1 from v\$parameter
		where name = 'log_archive_dest_state_1'
		and value = 'enabled')
union all
select	name,
	substr(value, 9, (instr(value, ' ') - 9)) value,
	0 kb
from	v\$parameter
where	name = 'log_archive_dest_2'
and	upper(value) like 'LOCATION=%'
and	exists (select 1 from v\$parameter
		where name = 'log_archive_dest_state_2'
		and value = 'enabled')
union all
select	name,
	substr(value, 9, (instr(value, ' ') - 9)) value,
	0 kb
from	v\$parameter
where	name = 'log_archive_dest_3'
and	upper(value) like 'LOCATION=%'
and	exists (select 1 from v\$parameter
		where name = 'log_archive_dest_state_3'
		and value = 'enabled')
union all
select	name,
	substr(value, 9, (instr(value, ' ') - 9)) value,
	0 kb
from	v\$parameter
where	name = 'log_archive_dest_4'
and	upper(value) like 'LOCATION=%'
and	exists (select 1 from v\$parameter
		where name = 'log_archive_dest_state_4'
		and value = 'enabled')
union all
select	name,
	substr(value, 9, (instr(value, ' ') - 9)) value,
	0 kb
from	v\$parameter
where	name = 'log_archive_dest_5'
and	upper(value) like 'LOCATION=%'
and	exists (select 1 from v\$parameter
		where name = 'log_archive_dest_state_5'
		and value = 'enabled')
union all
select	'UNIX_root' name, '/' value, 0 kb from dual
union all
select	'UNIX_tmp' name, '/tmp' value, 0 kb from dual
/
exit success
__EOF__
#
#----------------------------------------------------------------------------
# If SQL*Plus exited with a failure status, then exit the script also...
#----------------------------------------------------------------------------
_Rtn=$?
if (( ${_Rtn} != 0 ))
then
	case "${_Rtn}" in
		2) _ErrMsg="${_Pgm}: cannot \"CONNECT / AS SYSDBA\"" ;;
		3) _ErrMsg="${_Pgm}: spool report failed" ;;
		4) _ErrMsg="${_Pgm}: report failed" ;;
	esac
	notify_via_email
	exit ${_Rtn} > /dev/null 2>&1
fi
#
#----------------------------------------------------------------------------
# If the report contains anything, then run the UNIX command "df -k" against
# the entries 
#----------------------------------------------------------------------------
_ErrMsg=""
while read _Parm _Dir _Kb _Comments
do
	#
	if (( ${_Kb} == 0 ))
	then
		#
		integer _PctFull=`df -k ${_Dir} | tail -1 | awk '{print $1}' | sed 's/%//'`
		if (( ${_PctFull} >= 90 ))
		then
			#
			_ErrMsg="${_ErrMsg}Over 90% full:              ${_Parm} = \"${_Dir}\"\n"
			#
		fi
		#
	else
		#
		integer _AvailKb=`df -k ${_Dir} | grep "total allocated Kb" | awk '{print $4}'`
		_PctFull=`bc << __EOF2__
scale=2
( ${_Kb} / ${_AvailKb} ) * 100
__EOF2__`
		if (( ${_PctFull} >= 95 ))
		then
			#
			_Mb=`bc << __EOF3__
scale=0
${_Kb} / 1024
__EOF3__`
			#
			_AvailMb=`bc << __EOF4__
scale=0
${_AvailKb} / 1024
__EOF4__`
			#
			_ErrMsg="${_ErrMsg}Possibly over-allocated:    ${_Parm} = \"${_Dir}\"\n"
			_ErrMsg="${_ErrMsg}                            Expecting ${_Mb} Mb, only ${_AvailMb} Mb available...\n"
			_ErrMsg="${_ErrMsg}                            (could be due to AUTOEXTEND - use \"spc.sql\" to check)\n"
			#
		fi
		#
	fi
	#
done < /tmp/chk_df_${ORACLE_SID}.tmp
rm -f /tmp/chk_df_${ORACLE_SID}.tmp
#
if [[ "${_ErrMsg}" != "" ]]
then
	notify_via_email
	exit 6 > /dev/null 2>&1
fi
#
#----------------------------------------------------------------------------
# Return the exit status from SQL*Plus...
#----------------------------------------------------------------------------
exit 0 > /dev/null 2>&1