Oracle Database Health Check Script

dbdailychk.sh

# ##############################################################################################
# DAILY HEALTH CHECK MONITORING SCRIPT 
#
# =========================================================
# BECAUSE OF THE PERFORMANCE IMPACT THIS SCRIPT CAN CAUSE,
# I STRONGLY RECOMMEND TO SCHEDULE IT TO RUN IN NON PEAK HOURS
# E.G. SCHEDULE IT TO RUN ONE TIME BETWEEN 12:00AM to 5:00AM
# =========================================================
#
# FEATURES:
# CHECKING ALL DATABASES ALERTLOGS FOR ERRORS.
# CHECKING ALL LISTENERS ALERTLOGS FOR ERRORS.
# CHECKING CPU UTILIZATION.
# CHECKING FILESYSTEM UTILIZATION.
# CHECKING TABLESPACES UTILIZATION.
# CHECKING BLOCKING SESSIONS ON THE DATABASE.
# CHECKING UNUSABLE INDEXES ON THE DATABASE.
# CHECKING INVALID OBJECTS ON THE DATABASE.
# CHECKING FAILED LOGIN ATTEMPTS ON THE DATABASE.
# CHEKCING AUDIT RECORDS ON THE DATABASE.
# CHECKING CORRUPTED BLOCKS ON THE DATABASE.
# CHECKING FAILED JOBS IN THE DATABASE.
#
#					#   #     #
# Author:	Mahmmoud ADEL	      # # # #   ###
# 				    #   #   # #   #  
#
# Created:      22-12-13 Created with name dbalarm
# Modified:	23-12-13 Handled non exist logs 1run
#		14-05-14 Handled non existance of
#			 LOG_DIR directory.
#		18-05-14 Added Filsystem monitoring.
#		19-05-14 Added CPU monitoring.
#		09-12-14 Converted the script to "dbdailychk"
#			 Added Tablespaces monitoring
#			 Added BLOCKING SESSIONS monitoring
#			 Added UNUSABLE INDEXES monitoring 
#			 Added INVALID OBJECTS monitoring
#			 Added FAILED LOGINS monitoring
#			 Added AUDIT RECORDS monitoring
#			 Added CORRUPTED BLOCKS monitoring
#			 [It will NOT run a SCAN just checkng V$DATABASE_BLOCK_CORRUPTION view.]
#			 Added FAILED JOBS monitorings 
#		06-10-15 Replaced mpstat with iostat for CPU Utilization Check
# 
# TO BE ADDED LATER:	>LOGIN ATTEMPTS DURING NON BUSINESS HOURS
#			>LOGIN ATTEMPTS USING NON REGULAR APPLICATIONS
# ##############################################################################################
SCRIPT_NAME="dbdailychk.sh"
SRV_NAME=`uname -n`
MAIL_LIST="[email protected]"

	case ${MAIL_LIST} in "[email protected]")
	 echo
	 echo "#######################################################"
	 echo "Please EDIT line# 51 in dbdailychk.sh script and change"
	 echo "[email protected] to your E-mail address."
	 echo "#######################################################"
	 echo
	 echo "Script Terminated !"
	 echo 
	 exit;;
	esac

# #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:

FSTHRESHOLD=98		# THRESHOLD FOR FILESYSTEM %USED		[OS]
CPUTHRESHOLD=95		# THRESHOLD FOR CPU %UTILIZATION		[OS]
TBSTHRESHOLD=98		# THRESHOLD FOR TABLESPACE %USED		[DB]
UNUSEINDXTHRESHOLD=1    # THRESHOLD FOR NUMBER OF UNUSABLE INDEXES	[DB]
INVOBJECTTHRESHOLD=1    # THRESHOLD FOR NUMBER OF INVALID OBJECTS	[DB]
FAILLOGINTHRESHOLD=1    # THRESHOLD FOR NUMBER OF FAILED LOGINS		[DB]
AUDITRECOTHRESHOLD=1    # THRESHOLD FOR NUMBER OF AUDIT RECORDS         [DB]
CORUPTBLKTHRESHOLD=1    # THRESHOLD FOR NUMBER OF CORRUPTED BLOCKS	[DB]
FAILDJOBSTHRESHOLD=1    # THRESHOLD FOR NUMBER OF FAILED JOBS		[DB]

# #########################
# Checking The FILESYSTEM:
# #########################

# Report Partitions that have 2% or less of FREE space:

FSLOG=/tmp/filesystem_DBA_BUNDLE.log
echo "Reported By Script: ${SCRIPT_NAME}" > ${FSLOG}
echo "" >> ${FSLOG}
df -h >> ${FSLOG}
df -h | grep -v "^Filesystem" | awk '{print $(NF-1)" "$NF}'| while read OUTPUT
   do
  	PRCUSED=`echo ${OUTPUT}|awk '{print $1}'|cut -d'%' -f1`
  	FILESYS=`echo ${OUTPUT}|awk '{print $2}'`
  		if [ ${PRCUSED} -ge ${FSTHRESHOLD} ]
   		 then
mail -s "ALARM: Filesystem [${FILESYS}] on Server [${SRV_NAME}] has reached ${PRCUSED}% of USED space" $MAIL_LIST < ${FSLOG}
  		fi
   done

rm -f /tmp/filesystem_DBA_BUNDLE.log

# #############################
# Checking The CPU Utilization:
# #############################

# Report CPU Utilization if reach >= 95%:
OS_TYPE=`uname -s`
CPUUTLLOG=/tmp/CPULOG_DBA_BUNDLE.log

# Getting CPU utilization in last 5 seconds:
case `uname` in
        Linux ) CPU_REPORT_SECTIONS=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1 | grep ';' -o | wc -l`
                        if [ ${CPU_REPORT_SECTIONS} -ge 6 ]; then
                           CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 7`
                        else
                           CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 6`
                        fi
        ;;
        AIX )   CPU_IDLE=`iostat -t $INTERVAL_SEC $NUM_REPORT | sed -e 's/,/./g'|tr -s ' ' ';' | tail -1 | cut -d ";" -f 6`
        ;;
        SunOS ) CPU_IDLE=`iostat -c $INTERVAL_SEC $NUM_REPORT | tail -1 | awk '{ print $4 }'`
        ;;
        HP-UX) SAR="/usr/bin/sar"
                if [ ! -x $SAR ]; then
                 echo "sar command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
                else
                 CPU_IDLE=`/usr/bin/sar 1 5 | grep Average | awk '{ print $5 }'`
                fi
        ;;
        *) echo "uname command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
        ;;
        esac

# Getting Utilized CPU (100-%IDLE):
CPU_UTL_FLOAT=`echo "scale=2; 100-($CPU_IDLE)"|bc`

# Convert the average from float number to integer:
CPU_UTL=${CPU_UTL_FLOAT%.*}

	if [ -z ${CPU_UTL} ]
	 then
	  CPU_UTL=1
	fi

        if [ ${CPU_UTL} -ge ${CPUTHRESHOLD} ]
	 then
		echo "Top 10 Processes:"  >  /tmp/top_processes_DBA_BUNDLE.log
		echo "================"   >> /tmp/top_processes_DBA_BUNDLE.log
		echo "" 		  >> /tmp/top_processes_DBA_BUNDLE.log
		ps -eo pcpu,pid,user,args | sort -k 1 -r | head -11 >> /tmp/top_processes_DBA_BUNDLE.log
mail -s "ALERT: CPU Utilization on Server [${SRV_NAME}] has reached [${CPU_UTL}%]" $MAIL_LIST < /tmp/top_processes_DBA_BUNDLE.log
	fi

rm -f ${CPUUTLLOG}
rm -f /tmp/top_processes_DBA_BUNDLE.log


# #########################
# Getting ORACLE_SID:
# #########################
# Exit with sending Alert mail if No DBs are running:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|wc -l )
	if [ $INS_COUNT -eq 0 ]
	 then
	 echo "Reported By Script: ${SCRIPT_NAME}:" > /tmp/oracle_processes_DBA_BUNDLE.log
	 echo " " >> /tmp/oracle_processes_DBA_BUNDLE.log
	 echo "The following are the processes running by oracle user on server ${SRV_NAME}:" >> /tmp/oracle_processes_DBA_BUNDLE.log
	 echo " " >> /tmp/oracle_processes_DBA_BUNDLE.log
	 ps -ef|grep ora >> /tmp/oracle_processes_DBA_BUNDLE.log
mail -s "ALARM: No Databases Are Running on Server: $SRV_NAME !!!" $MAIL_LIST < /tmp/oracle_processes_DBA_BUNDLE.log
	 rm -f /tmp/oracle_processes_DBA_BUNDLE.log
 	 exit
	fi

# #########################
# Setting ORACLE_SID:
# #########################
for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
   do
    export ORACLE_SID

# #########################
# Getting ORACLE_HOME
# #########################
  ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|awk '{print $1}'|tail -1`
  USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`

## If OS is Linux:
if [ -f /etc/oratab ]
  then
  ORATAB=/etc/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME

## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
  then
  ORATAB=/var/opt/oracle/oratab
  ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
  export ORACLE_HOME
fi

## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user's profile:
if [ -z "${ORACLE_HOME}" ]
 then
  ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
  export ORACLE_HOME
fi

# #########################
# Variables:
# #########################
export PATH=$PATH:${ORACLE_HOME}/bin
export LOG_DIR=${USR_ORA_HOME}/BUNDLE_Logs
mkdir -p ${LOG_DIR}
chown -R ${ORA_USER} ${LOG_DIR}
chmod -R go-rwx ${LOG_DIR}

        if [ ! -d ${LOG_DIR} ]
         then
          mkdir -p /tmp/BUNDLE_Logs
          export LOG_DIR=/tmp/BUNDLE_Logs
          chown -R ${ORA_USER} ${LOG_DIR}
          chmod -R go-rwx ${LOG_DIR}
        fi

# ########################
# Getting ORACLE_BASE:
# ########################

# Get ORACLE_BASE from user's profile if it EMPTY:

if [ -z "${ORACLE_BASE}" ]
 then
  ORACLE_BASE=`grep -h 'ORACLE_BASE=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
fi

# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME_UPPER=`echo $VAL1| perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
DB_NAME_LOWER=$( echo "$DB_NAME_UPPER" | tr -s  '[:upper:]' '[:lower:]' )
export DB_NAME_UPPER
export DB_NAME_LOWER

# DB_NAME is Uppercase or Lowercase?:

     if [ -d $ORACLE_HOME/diagnostics/${DB_NAME_LOWER} ]
        then
                DB_NAME=$DB_NAME_LOWER
        else
                DB_NAME=$DB_NAME_UPPER
     fi


# #########################
# Tablespaces Size Check:
# #########################
# Check if AUTOEXTEND OFF (MAXSIZE=0) is set for any of the datafiles divide by ALLOCATED size else divide by MAXSIZE:
VAL33=$(${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_DATA_FILES WHERE MAXBYTES=0;
exit;
EOF
)
VAL44=`echo $VAL33| awk '{print $NF}'`
                case ${VAL44} in
                "0") CALCPERCENTAGE1="((sbytes - fbytes)*100 / MAXSIZE) bused " ;;
                  *) CALCPERCENTAGE1="round(((sbytes - fbytes) / sbytes) * 100,2) bused " ;;
                esac

VAL55=$(${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_TEMP_FILES WHERE MAXBYTES=0;
exit;
EOF
)
VAL66=`echo $VAL55| awk '{print $NF}'`
                case ${VAL66} in
                "0") CALCPERCENTAGE2="((sbytes - fbytes)*100 / MAXSIZE) bused " ;;
                  *) CALCPERCENTAGE2="round(((sbytes - fbytes) / sbytes) * 100,2) bused " ;;
                esac

TBSCHK=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 termout off echo off feedback off 
col tablespace for A25
col "MAXSIZE MB" format 999999
col x for 999999999 heading 'Allocated MB'
col y for 999999999 heading 'Free MB'
col z for 999999999 heading 'Used MB'
col bused for 999.99 heading '%Used'
--bre on report
spool ${LOG_DIR}/tablespaces_DBA_BUNDLE.log
select a.tablespace_name tablespace,bb.MAXSIZE/1024/1024 "MAXSIZE MB",sbytes/1024/1024 x,fbytes/1024/1024 y,
(sbytes - fbytes)/1024/1024 z,
$CALCPERCENTAGE1
--round(((sbytes - fbytes) / sbytes) * 100,2) bused
--((sbytes - fbytes)*100 / MAXSIZE) bused
from (select tablespace_name,sum(bytes) sbytes from dba_data_files group by tablespace_name ) a,
     (select tablespace_name,sum(bytes) fbytes,count(*) ext from dba_free_space group by tablespace_name) b,
     (select tablespace_name,sum(MAXBYTES) MAXSIZE from dba_data_files group by tablespace_name) bb
--where a.tablespace_name in (select tablespace_name from dba_tablespaces)
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name = bb.tablespace_name
and round(((sbytes - fbytes) / sbytes) * 100,2) > 0
UNION ALL
select c.tablespace_name tablespace,dd.MAXSIZE/1024/1024 MAXSIZE_GB,sbytes/1024/1024 x,fbytes/1024/1024 y,
(sbytes - fbytes)/1024/1024 obytes,
$CALCPERCENTAGE2
from (select tablespace_name,sum(bytes) sbytes
      from dba_temp_files group by tablespace_name having tablespace_name in (select tablespace_name from dba_tablespaces)) c,
     (select tablespace_name,sum(bytes_free) fbytes,count(*) ext from v\$temp_space_header group by tablespace_name) d,
     (select tablespace_name,sum(MAXBYTES) MAXSIZE from dba_temp_files group by tablespace_name) dd
--where c.tablespace_name in (select tablespace_name from dba_tablespaces)
where c.tablespace_name = d.tablespace_name (+)
and c.tablespace_name = dd.tablespace_name
order by tablespace;
select tablespace_name,null,null,null,null,null||'100.00' from dba_data_files minus select tablespace_name,null,null,null,null,null||'100.00'  from dba_free_space;
spool off
exit;
EOF

TBSLOG=${LOG_DIR}/tablespaces_DBA_BUNDLE.log
TBSFULL=${LOG_DIR}/full_tbs.log
cat ${TBSLOG}|awk '{ print $1" "$NF }'| while read OUTPUT2
   do
        PRCUSED=`echo ${OUTPUT2}|awk '{print $NF}'`
        TBSNAME=`echo ${OUTPUT2}|awk '{print $1}'`
        echo "Reported By Script: ${SCRIPT_NAME}:" > ${TBSFULL}
        echo " " >> ${TBSFULL}
	echo "Tablespace_name          %USED" >> ${TBSFULL}
	echo "----------------------          -----------" >> ${TBSFULL}
	echo ${OUTPUT2}|awk '{print $1"                              "$NF}' >> ${TBSFULL}
# Convert PRCUSED from float number to integer:
PRCUSED=${PRCUSED%.*}
	if [ -z ${PRCUSED} ]
	 then
	  PRCUSED=1
	fi
# If the tablespace %USED >= the defined threshold send an email for each tablespace:
               if [ ${PRCUSED} -ge ${TBSTHRESHOLD} ]
                 then
mail -s "ALERT: TABLESPACE [${TBSNAME}] reached ${PRCUSED}% on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${TBSFULL}
               fi
   done

rm -f ${LOG_DIR}/tablespaces_DBA_BUNDLE.log
rm -f ${LOG_DIR}/full_tbs.log
)

# ############################################
# Checking BLOCKING SESSIONS ON THE DATABASE:
# ############################################
VAL77=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
select count(*) from gv\$LOCK l1, gv\$SESSION s1, gv\$LOCK l2, gv\$SESSION s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1=l2.id1 and l2.id2=l2.id2;
exit;
EOF
) 
VAL88=`echo $VAL77| awk '{print $NF}'`
                case ${VAL88} in
                "0") ;;
                  *)
VAL99=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 160 pages 0 echo off feedback off
col BLOCKING_STATUS for a90
spool ${LOG_DIR}/blocking_sessions.log
select 'User: '||s1.username || '@' || s1.machine || '(SID=' || s1.sid ||' ) running SQL_ID:'||s1.sql_id||' is blocking
User: '|| s2.username || '@' || s2.machine || '(SID=' || s2.sid || ') running SQL_ID:'||s2.sql_id||' For '||s2.SECONDS_IN_WAIT||' sec
------------------------------------------------------------------------------
Warn user '||s1.username||' Or use the following statement to kill his session:
------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '''||s1.sid||','||s1.serial#||''' immediate;' AS blocking_status
from gv\$LOCK l1, gv\$SESSION s1, gv\$LOCK l2, gv\$SESSION s2
 where s1.sid=l1.sid and s2.sid=l2.sid 
 and l1.BLOCK=1 and l2.request > 0
 and l1.id1 = l2.id1
 and l2.id2 = l2.id2 ;
spool off
exit;
EOF
)
mail -s "ALERT: BLOCKING SESSIONS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/blocking_sessions.log
rm -f ${LOG_DIR}/blocking_sessions.log
		     ;;
                esac
  
# ############################################
# Checking UNUSABLE INDEXES ON THE DATABASE:
# ############################################
VAL111=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from DBA_INDEXES where status='UNUSABLE';
exit;
EOF
) 
VAL222=`echo $VAL111 | awk '{print $NF}'`
                if [ ${VAL222} -ge ${UNUSEINDXTHRESHOLD} ] 
                 then
VAL333=$(${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" << EOF
set linesize 160 pages 0 echo off feedback off
spool ${LOG_DIR}/unusable_indexes.log
PROMPT FIX UN-USABLE INDEXES USING THE FOLLOWING STATEMENTS:
PROMPT ------------------------------------------------------------------------------
                
PROMPT            
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;' from dba_indexes where status='UNUSABLE';
spool off
exit;
EOF
)
mail -s "INFO: UNUSABLE INDEXES detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/unusable_indexes.log
rm -f ${LOG_DIR}/unusable_indexes.log
                fi

# ############################################ 
# Checking INVALID OBJECTS ON THE DATABASE:
# ############################################
VAL444=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from dba_objects where status <> 'VALID';
exit;
EOF
)
VAL555=`echo $VAL444 | awk '{print $NF}'`
                if [ ${VAL555} -ge ${INVOBJECTTHRESHOLD} ]
                 then
VAL666=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 160 pages 0 echo off feedback off
spool ${LOG_DIR}/invalid_objects.log
PROMPT FIX INVALID OBJECTS USING THE FOLLOWING STATEMENTS:
PROMPT ---------------------------------------------------------------------------

PROMPT
select 'alter package '||owner||'.'||object_name||' compile;' from dba_objects where status <> 'VALID' and object_type like '%PACKAGE%' union
select 'alter type '||owner||'.'||object_name||' compile specification;' from dba_objects where status <> 'VALID' and object_type like '%TYPE%'union
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status <> 'VALID' and object_type not in ('PACKAGE','PACKAGE BODY','SYNONYM','TYPE','TYPE BODY') union
select 'alter public synonym '||object_name||' compile;' from dba_objects where status <> 'VALID' and object_type ='SYNONYM';
spool off
exit;
EOF
)
mail -s "WARNING: INVALID OBJECTS # reached the THRESHOLD on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/invalid_objects.log
rm -f ${LOG_DIR}/invalid_objects.log
                fi


# ###############################################
# Checking FAILED LOGIN ATTEMPTS ON THE DATABASE:
# ###############################################
VAL777=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select /*+ parallel 2 */ COUNT(*) from DBA_AUDIT_SESSION where returncode = 1017 and timestamp > (sysdate-1);
exit;
EOF
)
VAL888=`echo $VAL777 | awk '{print $NF}'`
                if [ ${VAL888} -ge ${FAILLOGINTHRESHOLD} ]
                 then
VAL999=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 160 pages 100
spool ${LOG_DIR}/failed_logins.log
PROMPT FAILED LOGIN ATTEMPT [SESSION DETAILS]:
PROMPT --------------------------------------------------------------------

PROMPT
col OS_USERNAME for a20
col USERNAME for a25
col TERMINAL for a30
col ACTION_NAME for a20
col TIMESTAMP for a21
col USERHOST for a40
select /*+ parallel 2 */ to_char (EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,OS_USERNAME,USERNAME,TERMINAL,USERHOST,ACTION_NAME
from DBA_AUDIT_SESSION
where returncode = 1017
and timestamp > (sysdate -1)
order by 1;
spool off
exit;
EOF
)
mail -s "INFO: FAILED LOGIN ATTEMPT detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/failed_logins.log
rm -f ${LOG_DIR}/failed_logins.log
		fi

# ###############################################
# Checking AUDIT RECORDS ON THE DATABASE:
# ###############################################
VAL70=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT (SELECT COUNT(*) FROM dba_audit_trail
where ACTION_NAME not like 'LOGO%' and ACTION_NAME not in ('SELECT','SET ROLE') and timestamp > SYSDATE-1)
+
(SELECT COUNT(*) FROM dba_fga_audit_trail WHERE timestamp > SYSDATE-1) AUD_REC_COUNT FROM dual;
exit;
EOF
)
VAL80=`echo $VAL70 | awk '{print $NF}'`
                if [ ${VAL80} -ge ${AUDITRECOTHRESHOLD} ]
                 then
VAL90=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 160 pages 100
spool ${LOG_DIR}/audit_records.log
col EXTENDED_TIMESTAMP for a36
col OWNER for a25
col OBJ_NAME for a25
col OS_USERNAME for a20
col USERNAME for a25
col USERHOST for a21
col ACTION_NAME for a25
col ACTION_OWNER_OBJECT for a55
prompt
prompt
prompt ----------------------------------------------------------

prompt Audit records in the last 24Hours AUD$...
prompt ----------------------------------------------------------

prompt
select extended_timestamp,OS_USERNAME,USERNAME,USERHOST,ACTION_NAME||'  '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
from dba_audit_trail 
where
ACTION_NAME not like 'LOGO%'
and ACTION_NAME not in ('SELECT','SET ROLE')
-- and USERNAME not in ('CRS_ADMIN','DBSNMP')
-- and OS_USERNAME not in ('workflow')
-- and OBJ_NAME not like '%TMP_%'
-- and OBJ_NAME not like 'WRKDETA%'
-- and OBJ_NAME not in ('PBCATTBL','SETUP','WRKIB','REMWORK')
and timestamp > SYSDATE-1 order by EXTENDED_TIMESTAMP;
prompt
prompt ----------------------------------------------------------

prompt Fine Grained Auditing Data ...
prompt ----------------------------------------------------------

prompt
col sql_text for a70
col time for a36
col USERHOST for a21
col db_user for a15
select to_char(timestamp,'DD-MM-YYYY HH24:MI:SS') as time,db_user,userhost,sql_text,SQL_BIND
from dba_fga_audit_trail
where
timestamp > SYSDATE-1
-- and policy_name='PAYROLL_TABLE'
order by EXTENDED_TIMESTAMP;
spool off
exit;
EOF
)
mail -s "INFO: AUDIT RECORDS on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/audit_records.log
rm -f ${LOG_DIR}/audit_records.log
                fi

# ############################################
# Checking CORRUPTED BLOCKS ON THE DATABASE:
# ############################################
# It won't validate the datafiles nor scan for corrupted blocks, it will just check V$DATABASE_BLOCK_CORRUPTION view if populated.
VAL10=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from V\$DATABASE_BLOCK_CORRUPTION;
exit;
EOF
)
VAL20=`echo $VAL10 | awk '{print $NF}'`
                if [ ${VAL20} -ge ${CORUPTBLKTHRESHOLD} ]
                 then
VAL30=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 160 pages 100
spool ${LOG_DIR}/corrupted_blocks.log
PROMPT CORRUPTED BLOCKS DETAILS:
PROMPT --------------------------------------

PROMPT
select * from V\$DATABASE_BLOCK_CORRUPTION;
spool off
exit;
EOF
)
mail -s "ALARM: CORRUPTED BLOCKS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/corrupted_blocks.log
rm -f ${LOG_DIR}/corrupted_blocks.log
                fi

# ############################################
# Checking FAILED JOBS ON THE DATABASE:
# ############################################
VAL40=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT (SELECT COUNT(*) FROM dba_jobs where failures <> '0') + (SELECT COUNT(*) FROM dba_scheduler_jobs where FAILURE_COUNT <> '0') FAIL_COUNT FROM dual;
exit;
EOF
)
VAL50=`echo $VAL40 | awk '{print $NF}'`
                if [ ${VAL50} -ge ${FAILDJOBSTHRESHOLD} ]
                 then
VAL60=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 160 pages 100
spool ${LOG_DIR}/failed_jobs.log
PROMPT DBMS_JOBS:
PROMPT -----------

col LAST_RUN for a25
col NEXT_RUN for a25
select job,schema_user,failures,to_char(LAST_DATE,'DD-Mon-YYYY hh24:mi:ss')LAST_RUN,to_char(NEXT_DATE,'DD-Mon-YYYY hh24:mi:ss')NEXT_RUN from dba_jobs where failures <> '0';

PROMPT 
PROMPT DBMS_SCHEDULER:
PROMPT ----------------

col OWNER for a25
col JOB_NAME for a40
col STATE for a11
col FAILURE_COUNT for 999 heading 'Fail'
--col LAST_START_DATE for a40
select JOB_NAME,OWNER,ENABLED,STATE,FAILURE_COUNT,to_char(LAST_START_DATE,'DD-Mon-YYYY hh24:mi:ss')LAST_RUN,to_char(NEXT_RUN_DATE,'DD-Mon-YYYY hh24:mi:ss')NEXT_RUN from dba_scheduler_jobs where FAILURE_COUNT <> '0' order by ENABLED,STATE;

spool off
exit;
EOF
)
mail -s "WARNING: FAILED JOBS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/failed_jobs.log
rm -f ${LOG_DIR}/failed_jobs.log
                fi


# #########################
# Getting ALERTLOG path:
# #########################
VAL2=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
SELECT value from v\$parameter where NAME='background_dump_dest';
exit;
EOF
)
ALERTZ=`echo $VAL2 | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
ALERTDB=${ALERTZ}/alert_${ORACLE_SID}.log


# ###########################
# Checking Database Errors:
# ###########################

# Determine the ALERTLOG path:
	if [ -f ${ALERTDB} ]
	 then
	  ALERTLOG=${ALERTDB}
	elif [ -f $ORACLE_BASE/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log ]
	 then
	  ALERTLOG=$ORACLE_BASE/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log
	elif [ -f $ORACLE_HOME/diagnostics/${DB_NAME}/diag/rdbms/${DB_NAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log ]
	 then
	  ALERTLOG=$ORACLE_HOME/diagnostics/${DB_NAME}/diag/rdbms/${DB_NAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log
	else
	  ALERTLOG=`/usr/bin/find ${ORACLE_BASE} -iname alert_${ORACLE_SID}.log  -print 2>/dev/null`
	fi

# Rename the old log generated by the script (if exists):
 if [ -f ${LOG_DIR}/alert_${ORACLE_SID}_new.log ]
  then
   mv ${LOG_DIR}/alert_${ORACLE_SID}_new.log ${LOG_DIR}/alert_${ORACLE_SID}_old.log
   # Create new log:
   tail -1000 ${ALERTLOG} > ${LOG_DIR}/alert_${ORACLE_SID}_new.log
   # Extract new entries by comparing old & new logs:
   echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/diff_${ORACLE_SID}.log
   echo " "  >> ${LOG_DIR}/diff_${ORACLE_SID}.log
   diff ${LOG_DIR}/alert_${ORACLE_SID}_old.log ${LOG_DIR}/alert_${ORACLE_SID}_new.log |grep ">" | cut -f2 -d'>' >> ${LOG_DIR}/diff_${ORACLE_SID}.log

   # Search for errors:
   ERRORS=`cat ${LOG_DIR}/diff_${ORACLE_SID}.log | grep 'ORA-\|TNS-' | grep -v 'ORA-2396' | tail -1`
   FILE_ATTACH=${LOG_DIR}/diff_${ORACLE_SID}.log

 else
   # Create new log:
   echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/alert_${ORACLE_SID}_new.log
   echo " "  >> ${LOG_DIR}/alert_${ORACLE_SID}_new.log
   tail -1000 ${ALERTLOG} >> ${LOG_DIR}/alert_${ORACLE_SID}_new.log

   # Search for errors:
   ERRORS=`cat ${LOG_DIR}/alert_${ORACLE_SID}_new.log | grep 'ORA-\|TNS-' | grep -v "ORA-2396" | tail -1`
   FILE_ATTACH=${LOG_DIR}/alert_${ORACLE_SID}_new.log
 fi

 # Send mail in case error exist:
 	case "$ERRORS" in
 	*ORA-*|*TNS-*)
mail -s "ALERT: Instance [${ORACLE_SID}] on Server [${SRV_NAME}] reporting errors: ${ERRORS}" ${MAIL_LIST} < ${FILE_ATTACH}
 	esac

# #####################
# Reporting Offline DBs:
# #####################
# Populate ${LOG_DIR}/alldb_DBA_BUNDLE.log from ORATAB:
  grep -v '^\#' $ORATAB | grep -v "ASM" |grep -v "${DB_NAME}:"| grep -v '^$' | grep "^" | cut -f1 -d':' > ${LOG_DIR}/alldb_DBA_BUNDLE.log
# Populate ${LOG_DIR}/updb_DBA_BUNDLE.log:
  echo $ORACLE_SID >> ${LOG_DIR}/updb_DBA_BUNDLE.log
  echo $DB_NAME >> ${LOG_DIR}/updb_DBA_BUNDLE.log

# End looping for databases:
done

# Continue Reporting Offline DBs...
# Sort the lines alphabetically with removing duplicates:
sort ${LOG_DIR}/updb_DBA_BUNDLE.log  | uniq -d > ${LOG_DIR}/updb_DBA_BUNDLE.log.sort
sort ${LOG_DIR}/alldb_DBA_BUNDLE.log > ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort
diff ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort ${LOG_DIR}/updb_DBA_BUNDLE.log.sort > ${LOG_DIR}/diff_DBA_BUNDLE.sort
echo "The Following Instances are Down on $SRV_NAME :" > ${LOG_DIR}/offdb_DBA_BUNDLE.log
grep "^< " ${LOG_DIR}/diff_DBA_BUNDLE.sort | cut -f2 -d'<' >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
echo " " >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
echo "If those instances are permanently offline, please hash their entries in $ORATAB to let the script ignore them in the next run." >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
OFFLINE_DBS_NUM=`cat ${LOG_DIR}/offdb_DBA_BUNDLE.log| wc -l`

# If OFFLINE_DBS is not null:
	if [ ${OFFLINE_DBS_NUM} -gt 3 ]
	 then
mail -s "ALARM: Database Down on Server: [$SRV_NAME]" $MAIL_LIST < ${LOG_DIR}/offdb_DBA_BUNDLE.log
	fi

# Wiping Logs:
#cat /dev/null >  ${LOG_DIR}/updb_DBA_BUNDLE.log
#cat /dev/null >  ${LOG_DIR}/alldb_DBA_BUNDLE.log
#cat /dev/null >  ${LOG_DIR}/updb_DBA_BUNDLE.log.sort
#cat /dev/null >  ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort
#cat /dev/null >  ${LOG_DIR}/diff_DBA_BUNDLE.sort

rm -f ${LOG_DIR}/updb_DBA_BUNDLE.log
rm -f ${LOG_DIR}/alldb_DBA_BUNDLE.log
rm -f ${LOG_DIR}/updb_DBA_BUNDLE.log.sort
rm -f ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort
rm -f ${LOG_DIR}/diff_DBA_BUNDLE.sort


# ###########################
# Checking Listeners log:
# ###########################

# In case there is NO Listeners are running send an (Alarm):
LSN_COUNT=$( ps -ef|grep -v grep|grep tnslsnr|wc -l )

 if [ $LSN_COUNT -eq 0 ]
  then
   echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/listener_processes.log
   echo " " >> ${LOG_DIR}/listener_processes.log
   echo "The following are the processes running by user ${ORA_USER} on server ${SRV_NAME}:" >> ${LOG_DIR}/listener_processes.log
   echo " " >> ${LOG_DIR}/listener_processes.log
   ps -ef|grep -v grep|grep oracle >> ${LOG_DIR}/listener_processes.log
mail -s "ALARM: No Listeners Are Running on Server: $SRV_NAME !!!" $MAIL_LIST < ${LOG_DIR}/listener_processes.log
  
  # In case there is a listener running analyze it's log:
  else
	for LISTENER_NAME in $( ps -ef|grep -v grep|grep tnslsnr|awk '{print $(NF-1)}' )
	 do
	  LISTENER_HOME=`ps -ef|grep -v grep|grep tnslsnr|grep "${LISTENER_NAME} "|awk '{print $(NF-2)}' |sed -e 's/\/bin\/tnslsnr//g'|grep -v sed|grep -v "s///g"`
	  TNS_ADMIN=${LISTENER_HOME}/network/admin; export TNS_ADMIN
	  LISTENER_LOGDIR=`${LISTENER_HOME}/bin/lsnrctl status ${LISTENER_NAME} |grep "Listener Log File"| awk '{print $NF}'| sed -e 's/\/alert\/log.xml//g'`
	  LISTENER_LOG=${LISTENER_LOGDIR}/trace/${LISTENER_NAME}.log

	  # Determine if the listener name is in Upper/Lower case:
	        if [ -f  ${LISTENER_LOG} ]
	         then
		  # Listner_name is Uppercase:
	          LISTENER_NAME=$( echo ${LISTENER_NAME} | perl -lpe'$_ = reverse' |perl -lpe'$_ = reverse' )
	          LISTENER_LOG=${LISTENER_LOGDIR}/trace/${LISTENER_NAME}.log
	        else
		  # Listener_name is Lowercase:
	          LISTENER_NAME=$( echo "${LISTENER_NAME}" | tr -s  '[:upper:]' '[:lower:]' )
	          LISTENER_LOG=${LISTENER_LOGDIR}/trace/${LISTENER_NAME}.log
	        fi
	
	  # Rename the old log (If exists):
	  if [ -f ${LOG_DIR}/alert_${LISTENER_NAME}_new.log ]
	   then
	      mv ${LOG_DIR}/alert_${LISTENER_NAME}_new.log ${LOG_DIR}/alert_${LISTENER_NAME}_old.log
	    # Create a new log:
	      tail -1000 ${LISTENER_LOG} > ${LOG_DIR}/alert_${LISTENER_NAME}_new.log
	    # Get the new entries:
	      echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/diff_${LISTENER_NAME}.log
	      echo " " >> ${LOG_DIR}/diff_${LISTENER_NAME}.log
	      diff ${LOG_DIR}/alert_${LISTENER_NAME}_old.log  ${LOG_DIR}/alert_${LISTENER_NAME}_new.log | grep ">" | cut -f2 -d'>' >> ${LOG_DIR}/diff_${LISTENER_NAME}.log
	    # Search for errors:
	     ERRORS=`cat ${LOG_DIR}/diff_${LISTENER_NAME}.log| grep "TNS-" |tail -1`
	     SRVC_REG=`cat ${LOG_DIR}/diff_${LISTENER_NAME}.log| grep "service_register" `
	     FILE_ATTACH=${LOG_DIR}/diff_${LISTENER_NAME}.log

	 # If no old logs exist:
	 else
	    # Just create a new log without doing any comparison:
             echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/alert_${LISTENER_NAME}_new.log
	     echo " " >> ${LOG_DIR}/alert_${LISTENER_NAME}_new.log
             tail -1000 ${LISTENER_LOG} >> ${LOG_DIR}/alert_${LISTENER_NAME}_new.log

            # Search for errors:
              ERRORS=`cat ${LOG_DIR}/alert_${LISTENER_NAME}_new.log | grep "TNS-" | tail -1`
              SRVC_REG=`cat ${LOG_DIR}/alert_${LISTENER_NAME}_new.log | grep "service_register" `
              FILE_ATTACH=${LOG_DIR}/alert_${LISTENER_NAME}_new.log
	 fi

          # Report TNS Errors (Alert)
            case "$ERRORS" in
            *TNS-*)
mail -s "ALERT: Listener [${LISTENER_NAME}] on Server [${SRV_NAME}] reporting errors: ${ERRORS}" $MAIL_LIST < ${FILE_ATTACH}
            esac

          # Report Registered Services to the listener (Info)
            case "$SRVC_REG" in
            *service_register*)
mail -s "INFO: Service Registered on Listener [${LISTENER_NAME}] on Server [${SRV_NAME}] | TNS poisoning posibility" $MAIL_LIST < ${FILE_ATTACH}
            esac

	done
 fi

# #############
# END OF SCRIPT
# #############
# REPORT BUGS to: [email protected]
# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".