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;

Oracle DB Installation Checklist

Task ID	Task Items	Completed By	Date Completed	Comments
1	Review Oracle Installation guides:
1.1	http://docs.oracle.com/database/121/nav/portal_11.htm
2	Hardware and Memory Requirements:
2.1	RAM Size           /usr/sbin/lsattr -E -l sys0 -a realmem
2.2	Swap Space      /usr/sbin/lsps -s
2.3	CPU                 lsconf|grep Proces
3	Storage Miniumum Requirements:
3.1	Software Installation Size
3.2	Verify Mount Point has enough space for Install files
3.3	Sizing for file system based Oracle Environment:
3.4	/u01 Binaries
3.5	/u02 Data
3.6	/u03 Backups
3.7	Determine if minimum amount of /tmp space is available
4	Operating System Requirements:
4.1	Determine version of AIX installed   oslevel -s
4.2	Verify that the required packages are installed
4.3	Determine the supported kernel mode
4.4	Determine an APAR is installed
4.5	Verify required Operating System Groups and Users
4.6	Configure Shell Limits
4.7	Configure System Configuration Kernel Parameters
4.8	Confirm the aio_maxreqs value
4.9	Enable the "iocp" option
4.10	Creating Required Directories
4.11	Port Requirements (OEM requires specific ports to be open)
5	Database Requirements:
5.1	Oracle Parameters
5.2	PGA/SGA
5.3	Database Storage - Data, Backup, Logs
6	Install GRID Infrastructure: (Only if ASM is to be configured)
7	Install 12c Database Binaries:
7.1	Running the rootpre.sh Script
7.2	Start CYGWIN
7.3	Insall 12c Binaries
8	Create Database using DBCA
8.1	Refer to the Database Requirements while creating the new database
9	Post Installation Tasks
9.1	Update .profile
9.2	Verify /etc/oratab
9.3	Setup RMAN Backups
9.4	Setup Crontab entries
9.5	Update TNSNAMES.ORA
9.6	Change email identifier from oracle to <HOSTNAME>
9.7	Setup TDP for RMAN backups
9.8	Setup Startup scripts
9.9	Review and verify oracle parameters
9.10	Alter DEFAULT profile in new database to unlimited
9.11	Configure trace file retention policy using ADRCI