Unix Shell Scripts for Oracle DBA's

###################################
# CHECKING TABLESPACE USAGE     ##
###################################
#!/bin/ksh
#***********************************************
# First, we must set the environment . . . .
#***********************************************

ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$PATH:/usr/local/oracle/bin
DIR=/usr/local/oracle/bin
LOGDIR=/u01/scripts/logs/
export PATH DIR LOGDIR
. oraenv
ADMIN=`cat /usr/local/oracle/mail_list.lis`EM_LIST='jhenn@gmail.com'
today=`date '+%m/%d/%y %H:%M:%S'`
HOSTNAME=`hostname`
expdate=`date '+%d%m%Y'`

#***********************************************
# Log into SQL Plus
#***********************************************

sqlplus -s '/ as sysdba'  << EOF
set echo off
set feedback off
set lines 150 pages 66 feedback off
spool $LOGDIR/tablespace.alert
column tablespace_name                format a20        heading 'Tablespace'
column used_pct_of_max                format 999        heading 'Used % of Max'
column actual_free_gb                 format 999        heading 'Actual Free GB'
with
tbs_auto as
     (select distinct tablespace_name, autoextensible
      from dba_data_files
      where autoextensible = 'YES'),
files as
     (select tablespace_name, count (*) tbs_files,
             sum (bytes) total_tbs_bytes
      from dba_data_files
      group by tablespace_name),
fragments as
     (select tablespace_name, count (*) tbs_fragments,
             sum (bytes) total_tbs_free_bytes,
             max (bytes) max_free_chunk_bytes
      from dba_free_space
      group by tablespace_name),
autoextend as
     (select tablespace_name, sum (size_to_grow) total_growth_tbs
      from (select tablespace_name, sum (maxbytes) size_to_grow
            from dba_data_files
            where autoextensible = 'YES'
            group by tablespace_name
            union
            select   tablespace_name, sum (bytes) size_to_grow
            from dba_data_files
            where autoextensible = 'NO'
            group by tablespace_name)
      group by tablespace_name)
select a.tablespace_name,
       round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) used_pct_of_max,
       round(autoextend.total_growth_tbs/1024/1024/1024 - round((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/1024/1024/1024)) actual_free_gb
       from dba_tablespaces a, files, fragments, autoextend, tbs_auto
where a.tablespace_name = files.tablespace_name
  and a.tablespace_name = fragments.tablespace_name
  and a.tablespace_name = autoextend.tablespace_name
  and a.tablespace_name = tbs_auto.tablespace_name(+)
-- and round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) >=$pct_thresh
and round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes)/autoextend.total_growth_tbs)*100)) > 69
  order by used_pct_of_max desc;
spool off
exit
EOF

#***********************************************
# Send email if Tablespace reaches Threshold
#***********************************************

if [ `cat $LOGDIR/tablespace.alert|wc -l` -gt 0 ]
then
          mailx -s "$HOSTNAME TABLESPACE ALERT on Database $ORACLE_SID " $EM_LIST < $LOGDIR/tablespace.alert
fi
rm $LOGDIR/tablespace.alert


#######################################
## CHECKING FLASHBACK AREA SIZE        ##
#######################################

#!/bin/ksh

#***********************************************
# First, we must set the environment . . . .
#***********************************************

ORACLE_SID=$1
THRESHOLD=$2
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$PATH:/usr/local/oracle/bin
DIR=/usr/local/oracle/bin
LOGDIR=/u01/scripts/logs/
export PATH DIR LOGDIR
. oraenv
ADMIN=`cat /usr/local/oracle/rman/config/bkup_mail_list.lis`EM_LIST='jhenn@gmail.com'
today=`date '+%m/%d/%y %H:%M:%S'`
HOSTNAME=`hostname`
expdate=`date '+%d%m%Y'`
fra_size_file=$LOGDIR/fra_size.alert

#***********************************************
# Log into SQL Plus
#***********************************************

FRA_SIZE=`$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
   whenever sqlerror exit 2;
          SPOOL $fra_size_file  
          set echo off ver off feedb off head off pages 0
   select round((ceil(space_used / 1024 / 1024) / floor(space_limit  / 1024 / 1024))*100,0)  as PCT_USED
   from v\\$recovery_file_dest;
   spool OFF
   exit;
   EOF`
#***********************************************
# Verify SQL Plus command ran successfully
#***********************************************
fra_exit_status=$?
if [ $fra_exit_status -eq 0 ]
then
  echo "Flashback area size returned successfully"
else
  mailx -s "Failed::Flashback area size query Failed!!!" jhenn@gmail.com < $fra_size_file
  exit 1  
fi

#************************************************************
# Send email and start backup if FLASHBACK reaches Threshold
#************************************************************

if [ `cat $FRA_SIZE` -ge $THRESHOLD ]
then
          mailx -s "$HOSTNAME FLASHBACK ALERT on Database $ORACLE_SID $THRESHOLD% Used" $EM_LIST < $fra_size_file
fi
rm -f $fra_size_file


########################################
## CHECKING ALERT LOG FOR ERRORS     ##
########################################

#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$PATH:/u01/scripts
DIR=/u01/scripts
LOGDIR=/u01/scripts/logs
export PATH DIR LOGDIR
. oraenv
ADMIN=`cat /usr/local/oracle/mail_list.lis`EM_LIST='jhenn@gmail.com,jhenn2@gmail.com'
today=`date '+%m/%d/%y %H:%M:%S'`
HOSTNAME=`hostname`
expdate=`date '+%d%m%Y'`
alertlog=$LOGDIR/alertlog.err

######## Wrap the code is this CASE statement if you need to run this script against each database ##############
#cat /etc/oratab | while read LINE
#do
#case $LINE in
#\#*) ;; #comment-line in oratab
#*)
#ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
#export ORACLE_SID
#ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
#export ORACLE_HOME
########Enter Commands Here##############

sqlplus -s '/ as sysdba'  << EOF
whenever sqlerror exit 2;
@/u01/scripts/alertlog_err_check.sql
EOF
exit_status=$?
if [ $exit_status -eq 0 ]
then
  echo "Alertlog Query Executed Successfully"
else
  mailx -s "Failed!!! Alertlog Error Check SQL failed to complete on $ORACLE_SID" jhenn@gmail.com < $alertlog
  exit 1  
fi
if [ `cat $alertlog|wc -l` -gt 0 ]
then
          mailx -s "ORA- Error ALERT on $HOSTNAME for Database SID $ORACLE_SID " $EM_LIST < $alertlog
fi

#################################
# Log file cleanup
#################################

if [ -e "$alertlog" ]; then
  rm -f $logfile
fi


###############################
## Send email example script           ##
###############################

#!/bin/ksh
#
#
#***********************************************
# Send email example script
#***********************************************
#-- Sending email and SMS
#-- Date : 10/08/2015
#-- Program: email_sms.ksh
#-- Author: John Henninger
TESTAMP=`date +%m%d%Y`
TIMESTAMP=`date +%H%M`
LOGFILE_NAME='/u01/scripts/exp_08Oct2015.log'
SEARCH_STRING='completed'
EMAIL_LIST='jhenn@gmail.com,jhenn2@gmail.com'
SMS_LIST='9999999999@txt.att.net'
SUBJ_LINE='EXPDP job has Completed'
keeplooping=0

while [[ $keeplooping -eq 0 ]] ; do
sleep 50
echo waited 60 seconds...
keeplooping=`grep -c $SEARCH_STRING $LOGFILE_NAME`
        if [[ $keeplooping -eq 1 ]] ; then
        cat $LOGFILE_NAME | mail -s "$SUBJ_LINE" $EMAIL_LIST
        #tail -n15 $LOGFILE_NAME | mail -s $SUBJ_LINE $SMS_LIST
        keeplooping=1
        fi
done
exit

tail -n15 $LOGFILE_NAME | mail -s "$SUBJ_LINE" $EMAIL_LIST $SMS_LIST


#########################################################################################
##Script name: blocked_sessions.ksh
##Author: John Henninger
##Create Date: 06/01/2011
##Description: This script will check for blocking seesion for the ORACLE_SID specified
##and then send an email listing the SID blocker, blocked SID and Object blocked.
##Please Note: When setting this up in a new environment you will need to create a blank
##blocked_sessions.old file in the log direcory. The script will be looking for this file.
##
#########################################################################################
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$PATH:/usr/local/oracle/bin
DIR=/usr/local/oracle/bin
LOGDIR=/usr/local/oracle/logs
export PATH DIR LOGDIR
. oraenv
ADMIN=`cat /usr/local/oracle/rman/config/bkup_mail_list.lis`
EM_LIST=`cat /u01/scripts/blocking_mail_list.lis`
today=`date "+%m/%d/%Y %H %M %S"`
HOSTNAME=`hostname`
###########################
#Main Function
##########################
$ORACLE_HOME/bin/sqlplus -s <<!
/ as sysdba
spool $LOGDIR/blocked_sessions.log
set pagesize 200
set linesize 200
column "USERNAME" format a10
column "SID" format 999
column "SID_1" format 999
column "IS BLOCKING" format a12
column "OBJECT_NAME" format a30
column "seconds_in_wait" format 999
column "BLOCKING_INSTANCE" format 999
select DISTINCT a1.sid, ' IS BLOCKING ', a2.sid, db.object_name, s.username, TRUNC(s.seconds_in_wait/60,0) as minutes_in_wait, S.BLOCKING_INSTANCE
   from v\$lock a1, v\$lock a2, dba_objects db,  v\$session s
   where a1.block in (1) and a2.request > 0
   and a1.id1=a2.id1
   and a1.id2=a2.id2
   and db.object_id = s.row_wait_obj#
   and db.object_type = 'TABLE'
   and db.owner = 'UCMS'
/
spool off
!
count=`cat $LOGDIR/blocked_sessions.log| egrep -v 'no rows selected'|wc -l`
if [ $count -gt 3 ] ; then
   #*************************************************************
   # Only send the alert if there is a change to the output . . .
   #*************************************************************
   sed '$d' $LOGDIR/blocked_sessions.log > $LOGDIR/blocked_sessions.log.tmp
   mv $LOGDIR/blocked_sessions.log.tmp $LOGDIR/blocked_sessions.log
   newm=`diff $LOGDIR/blocked_sessions.log $LOGDIR/blocked_sessions.old|wc -l`
   chgflg=`expr $newm`
   if [ $chgflg -ne 0 ]
   then
      #*************************************************************
      # Mail the message to the DBA's in $dbalist
      #*************************************************************
      mailx -s "Blocking Sessions found on $HOSTNAME $ORACLE_SID at $today" $ADMIN $EM_LIST < $LOGDIR/blocked_sessions.log
   fi
fi
#*************************************************************
# Rename .log to .old
#*************************************************************
rm $LOGDIR/blocked_sessions.old
mv $LOGDIR/blocked_sessions.log $LOGDIR/blocked_sessions.old
exit;