Cron Alerts in Oracle Database --- Mostly used

Flush the SHARED POOL
-----------------------------------------------
#set -ux
#This script is used for flushing the shared pool
SCRIPT_DIR=/stage/scripts/otmprod
export SCRIPT_DIR
EMAIL_TO=<your Mail Group>

. $SCRIPT_DIR/.sysvars --- <your own directory path>
. /home/oracle/OTMPROD.env
LOGFILE=<your log location>flush_shared_pool.log
sqlplus -s system/${SYSTEMPWD}@$ORACLE_SID <<EOF 2>&1
spool ${LOGFILE}
set echo off
set pagesize 100
set linesize 100
set copytypecheck on
set long 4000
set trimspool on
set pagesize 100
set heading on
set pages 0
set termout off
alter system flush shared_pool;
spool off;
exit;
EOF
REV_STS=$?
if [ $REV_STS -gt 0 ]; then
echo " Previous statement is failed check the log file for DB connection " >$LOGFILE
fi
mailx -s "Flushed the shared pool in $ORACLE_SID at `date`"  ${EMAIL_FROM} ${EMAIL_TO} < ${LOGFILE}

=======================================================================

TABLE SPACE Report
------------------------------------
col TABLESPACE_NAME for a12
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 8;

=====================================================================

Monitor Alert Log
----------------------------

#! /bin/bash
#
# Filename:     alertFileMonitoring.sh
#
# Description:  Monitor database alert log
#
# Author:       Akash Pramanik
#
# Version       Name                Date            Changes
# v1.0          Akash Pramanik      24/09/2013      Inital revision
mailgrp=<Your Mail Group>
LASTNEWERR="" # current error found in alert log
LASTERR=""
ERRLINE=0 # line number of the current error

ORACLE_SID=<Your SID>

ALERTDIR="<Your alert log location>"
ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading spaces
ALERTDIR=`echo $ALERTDIR | sed 's/^ //'` # strip off leading tabs
ALERTLOG=$ALERTDIR/alert_$ORACLE_SID.log
LASTERR=$ALERTDIR/alert_$ORACLE_SID.last # file where last alert log

# Look for last new error
#
LASTNEWERR=`egrep -n "DBA-|ORA-|ORA-" $ALERTLOG | tail -1`

# If error found, save error information
# Exit if no errors were found
#
LAST=$LASTNEWERR

if [ `echo $LASTNEWERR | wc -w` -eq 0 ]
#if [ ${LAST:-1} -eq 0 ]
then
exit 0
fi
# Determine whether this error was already identified
#

if [ -s $LASTERR ]
then
LASTERRLINE=` cat $LASTERR | awk -F: '{print $1}'`
if [ "`echo $LASTNEWERR`" != "`cat $LASTERR`" ]
then
# This is a new error, replace last error found with this one
#
echo $LASTNEWERR > $LASTERR
else
# Already identified, nothing new to report
#
exit 0
fi
else
# No previous error to look at, save this error
#
LASTERRLINE=0
echo $LASTNEWERR > $LASTERR
fi

# Now create a file with all new error #s
CURRERRFILE=$ALERTDIR/$ORACLE_SID.currerrs
sed "1,${LASTERRLINE}d" $ALERTLOG | egrep "^DBA-|^ORA-|ORA-" > $CURRERRFILE

mailx -s  "$ORACLE_SID Db Alert Log Notification - `date`" $mailgrp  < $CURRERRFILE
exit 0
 

====================================================================

Author - Akash Pramanik

Mobile - 9804944189