Script to Monitor Output Post Processor (OPP) Concurrent Manager

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check Output Post Processor (OPP) Concurrent Manager status and to send alert notification if its DOWN. 



Download(MonOPPMgr.sh)

Download MonOPPMgr.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 MonOPPMgr.sh

#!/bin/bash

###########################################################################
# Script Name :  MonOPPMgr.sh                                             #
#                                                                         #
# Description:                                                            #
# Script to check Output Post Processor Concurrent Manager status and     #
# to send alert notification if its DOWN                                  #
#                                                                         #
# Usage : sh <script_name> <ORACLE_SID>                                   #
# For example : sh MonOPPMgr.sh ORCL                                      #
#                                                                         #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)               #
###########################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
 echo "$LOG_DATE" > $LOG_FILE
    echo "Script To Check Concurrent Managers Status"  >> $LOG_FILE
    echo "Usage   : sh <script_name> <ORACLE_SID> " >> $LOG_FILE
 echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE
    echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 select count(1) from
 (
  SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes"
    FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
   WHERE a.concurrent_queue_id = b.concurrent_queue_id 
    AND a.concurrent_queue_name = 'FNDCPOPP' 
    AND (a.max_processes != a.min_processes OR a.running_processes != a.min_processes)
 );

 exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Critical : $APPS_ID - OPP Concurrent Manager is DOWN on $HOST_NAME  " $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then
 sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
 table { background: #eee; } -
 th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
 td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT OPP concurrent manager is DOWN, Please check ASAP
 PROMPT


  SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes"
    FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
   WHERE a.concurrent_queue_id = b.concurrent_queue_id 
    AND a.concurrent_queue_name = 'FNDCPOPP' 
    AND (a.max_processes != a.min_processes OR a.running_processes != a.min_processes);

 SPOOL OFF
 SET MARKUP HTML OFF
 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Critical : $APPS_ID - OPP Concurrent Manager is DOWN on $HOST_NAME "
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "OPP Concurrent Manager is up and running fine" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID>

$ cd $HOME/DBA_MON/bin
$ sh MonOPPMgr.sh ORCL

This script will send the notification with details if Output Post Processor (OPP) Concurrent Manager 

is Down or Inactive.

Script To Monitor Concurrent Managers

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check Concurrent Manager status and to send alert notification if one or more concurrent managers are down. 



Download(CMStatusCheck.sh)

Download CMStatusCheck.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 CMStatusCheck.sh

#!/bin/bash

##########################################################################
# Script Name :  CMStatusCheck.sh                                        #
#                                                                        #
# Description:                                                           #
# Script to check Concurrent Manager status and                          #
# to send alert notification if one or more concurrent managers are down #
#                                                                        #
# Usage : sh <script_name> <ORACLE_SID>                                  #
# For example : sh CMStatusCheck.sh ORCL                                 #
#                                                                        #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)              #
##########################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
 echo "$LOG_DATE" > $LOG_FILE
    echo "Script To Check Concurrent Managers Status"  >> $LOG_FILE
    echo "Usage   : sh <script_name> <ORACLE_SID> " >> $LOG_FILE
 echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE
    echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!
 set heading off
 set feedback off
 select count(1) from
 (
  SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes" 
    FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
   WHERE a.concurrent_queue_id = b.concurrent_queue_id 
    AND a.running_processes <> a.max_processes
  UNION
  SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node",  a.running_processes "Actual Processes", a.max_processes "Target Processes"
    FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
   WHERE a.concurrent_queue_id = b.concurrent_queue_id 
     AND a.concurrent_queue_name='FNDICM'
     and (a.running_processes=0 or a.max_processes=0)
 );

 exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Critical : $APPS_ID - Concurrent Managers are DOWN on $HOST_NAME " $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then
 sqlplus -s '/as sysdba' <<EOF

 SET ECHO OFF
 SET pagesize 1000
 set feedback off
 set lines 180
 SET MARKUP HTML ON SPOOL ON -
 HEAD '<title></title> -
 <style type="text/css"> -
 table { background: #eee; } -
 th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
 td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
 </style>' TABLE "border='1' align='left'" ENTMAP OFF

 spool $OUT_FILE


 PROMPT Hi Team,

 PROMPT
 PROMPT Below concurrent managers are down, Please check ASAP
 PROMPT


 SELECT b.user_concurrent_queue_name "Concurrent Manager",b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes"
  FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
 WHERE a.concurrent_queue_id = b.concurrent_queue_id 
  AND a.running_processes <> a.max_processes
 UNION
 SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes"
  FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
  WHERE a.concurrent_queue_id = b.concurrent_queue_id 
   AND a.concurrent_queue_name='FNDICM'
         AND (a.running_processes=0 or a.max_processes=0);

 SPOOL OFF
 SET MARKUP HTML OFF
 exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Critical : $APPS_ID - Concurrent Managers are DOWN on $HOST_NAME "
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "Concurrent Managers are up and running fine" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID>

$ cd $HOME/DBA_MON/bin
$ sh CMStatusCheck.sh ORCL

This script will send the notification with details if one or more concurrent managers are down.

Script To Monitor Database Locks

Pre-requisites to Run the script:

Make sure mailx and sendmail is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to monitor Database locks which exists for more than given threshold (Minutes) Ex. 5 Mins and send alert notification with the database blocker and waiter session details.



Download(DBLocksMon.sh)

Download DBLocksMon.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 DBLocksMon.sh

#!/bin/bash

#########################################################################################
# Script Name : DBLocksMon.sh                                                           #
#                                                                                       #
# Description:                                                                          #
# Script to monitor Database locks which exists for more than given threshold (Minutes) #
# Ex. 5 Mins and send alert notification with the database blocker and waiter           #
# session details.                                                                      #
#                                                                                       #
# Usage : sh <script_name> <ORACLE_SID> <WaitTime>                                      #
# For example : sh DBLocksMon.sh ORCL 5                                                 #
#                                                                                       #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                             #
#########################################################################################

# Initialize variables

INSTANCE=$1
THRESHOLD=$2
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.html.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
    cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor database locks"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <WaitTime> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 5" >> $LOG_FILE
        echo
}

if [ $# -lt 2 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

get_count()
{
 sqlplus -s '/as sysdba' <<!

 set feedback off
 set head off
 set feed off;
 set veri off;
 Select count(1) from 
 (
  SELECT   --a.inst_id, --RAC
       b.status
     , a.SID
     , a.serial#
     , NVL (TO_CHAR (a.blocking_session), ' ') blocker_sid  --RAC
     , a.SCHEMANAME
     , a.machine
     , a.event
     , a.status
     --, a.module
     , a.action
     , a.PROGRAM
     -- , NVL (TO_CHAR (a.blocking_instance), ' ') blocker_inst --RAC
     , round((a.seconds_in_wait / 60)) "WaitTime (Mins)"
     , TO_CHAR(a.LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') "LOGONTIME"           
     , a.last_call_et "LAST_CALL_ET(Sec)"
     --, a.sql_id
   FROM gv\$session a
     , (SELECT   inst_id
     , DECODE (request, 0, 'Blocker ', 'Waiter ') status
     , SID
     FROM gv\$lock
       WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv\$lock WHERE request > 0) ORDER BY id1, request) b
     WHERE a.inst_id = b.inst_id 
     AND a.SID = b.SID 
     AND round((a.seconds_in_wait / 60)) >= $THRESHOLD 
 );

    exit;
!
}

count=`get_count`
#echo $count

echo "$LOG_DATE" > $ERR_FILE
get_count >> $ERR_FILE
ERR_COUNT=`grep "ORA-" $ERR_FILE |wc -l`

if [ $ERR_COUNT -gt 0 ]; then
 cat $ERR_FILE | mailx -s "<ERROR> Critical: $APPS_ID - Database Locks Exists on $HOST_NAME" $DBA_EMAIL_LIST
 exit
fi

if [ $count -gt 0 ];
then

  sqlplus -s '/as sysdba' <<EOF

  set echo off
  set feedback off
  set lines 1000
  col bytes for 999999999999999.99
  col value for 999999999999999.99
  set long 2000000
  set pagesize 300
  set linesize 300
  col SQL_FULLTEXT for a9999
  SET MARKUP HTML ON SPOOL ON -
  HEAD '<title></title> -
  <style type="text/css"> -
     table { background: #eee; } -
     th { font:bold 10pt Arial,Helvetica,sans-serif; color:#b7ceec; background:#151b54; padding: 5px; align:center; } -
     td { font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding: 5px; align:center; } -
  </style>' TABLE "border='1' align='left'" ENTMAP OFF


  spool $OUT_FILE


  PROMPT Hi Team,

  PROMPT
  PROMPT Please check below locks which are exists for more than $THRESHOLD minutes on $APPS_ID:
  PROMPT


  SELECT   --a.inst_id, --RAC
       b.status
     , a.SID
     , a.serial#
     , NVL (TO_CHAR (a.blocking_session), ' ') blocker_sid  --RAC
     , a.SCHEMANAME
     , a.machine
     , a.event
     , a.status "SID_STATUS"
     --, a.module
     , a.action
     , a.PROGRAM
     -- , NVL (TO_CHAR (a.blocking_instance), ' ') blocker_inst --RAC
     , round((a.seconds_in_wait / 60)) "WaitTime (Mins)"
     , TO_CHAR(a.LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') "LOGONTIME"           
     , a.last_call_et "LAST_CALL_ET(SEC)"
     --, a.sql_id
      FROM gv\$session a
     , (SELECT   inst_id
     , DECODE (request, 0, 'Blocker ', 'Waiter ') status
     , SID
     FROM gv\$lock
       WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv\$lock WHERE request > 0) ORDER BY id1, request) b
  WHERE a.inst_id = b.inst_id 
     AND a.SID = b.SID 
     AND round((a.seconds_in_wait / 60)) >= $THRESHOLD;

  SPOOL OFF
  SET MARKUP HTML OFF
  exit;

EOF

(
echo "To: $DBA_EMAIL_LIST"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/alternative; "
echo ' boundary="PAA08673.1018277622/server.xyz.com"'
echo "Subject: Critical: $APPS_ID - Database Locks Exists on $HOST_NAME"
echo ""
echo "This is a MIME-encapsulated message"
echo ""
echo "--PAA08673.1018277622/server.xyz.com"
echo "Content-Type: text/html"
echo ""
cat $OUT_FILE
echo "--PAA08673.1018277622/server.xyz.com"
) | /usr/sbin/sendmail -t

echo "$LOG_DATE" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "There are no locks on database" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:
Syntax :  sh <script_name> <ORACLE_SID> <WaitTime>

$ cd $HOME/DBA_MON/bin
$ sh DBLocksMon.sh ORCL 5
This script will send the notification with blocker and waiter session details if locks exists for more than 5 minutes.

Script to Monitor Database Listener

Pre-requisites to Run the script:

Make sure mailx is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to monitor Database Listener, if down then notify to DBA Team.



Download(MonDBListener.sh)

Download MonDBListener.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 MonDBListener.sh

#!/bin/bash

###################################################################
# Script Name : MonDBListener.sh                                  #
#                                                                 #
# Description:                                                    #
# Script to monitor Database Listener.                            #
# If down then notify the DBA Team.                               #
#                                                                 #
# Usage : sh <script_name> <ORACLE_SID>                           #
# For example : sh MonDBListener.sh ORCL                          #
#                                                                 #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)       #
###################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a planned downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor Database Listener availability"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

check_listener()
{
 listener=`ps -ef|grep -i "lsnr $ORACLE_SID"|grep -v grep | wc -l`
 echo $listener
}
 
count_listener=`check_listener`
#echo $count_listener


if [ `check_listener` -eq 0 ]; then
 echo "$APPS_ID Database LISTENER is DOWN. Please check ASAP!!!"| mailx -s "Critical : $APPS_ID Database LISTENER is DOWN on $HOST_NAME" $DBA_EMAIL_LIST 
else
 echo "$LOG_DATE" > $OUT_FILE
 echo "$APPS_ID Database LISTENER is up and running fine" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing 
the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID> 

$ cd $HOME/DBA_MON/bin
$ sh MonDBListener.sh ORCL

This script will send the notification if database listener is down or unavailable.

Script to Monitor Database Status or Availability

Pre-requisites to Run the script:

Make sure mailx  is installed on your OS, also check below environements/file is created prior to execute the scripts.

This script is tested on Linux Server.

This script needs to be deployed in database node/server.

#Base location for the DBA scripts
DBA_SCRIPTS_HOME=$HOME/DBA_MON

#OS User profile where database environment file is set
$HOME/.bash_profile

Create Custom .sysenv file for scripts

Download(.sysenv)

Download .sysenv file and save it under $HOME/DBA_MON
$ chmod 777 .sysenv

$ cat $HOME/DBA_MON/.sysenv

export DBA_SCRIPTS_HOME=$HOME/DBA_MON
export PATH=${PATH}:$DBA_SCRIPTS_HOME
export DBA_EMAIL_LIST=kiran.jadhav@domain.com,jadhav.kiran@domain.com
#Below parameter is used in script, whenever there is planned downtime you can set it to Y so there will be no false alert.
export DOWNTIME_MODE=N

Script to check database status or availability, if down then notify to DBA Team.



Download(DBStatusCheck.sh)

Download DBStatusCheck.sh and save it under $HOME/DBA_MON/bin/
$ chmod 755 DBStatusCheck.sh

#!/bin/bash

######################################################################################
# Script Name : DBStatusCheck.sh                                                     #
#                                                                                    #
# Description:                                                                       #
# Script to check database status or availability, if down then notify the DBA Team. #              #                                                                                    #
#                                                                                    #
# Usage : sh <script_name> <ORACLE_SID>                                              #
# For example : sh DBStatusCheck.sh ORCL                                             #
#                                                                                    #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                          #
######################################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname | cut -d'.' -f1`
PROGRAM=`basename $0 | cut -d'.' -f1`
export DBA_SCRIPTS_HOME=$HOME/DBA_MON
APPS_ID=`echo $INSTANCE | tr '[:lower:]' '[:upper:]'`
LOG_DIR=$DBA_SCRIPTS_HOME/logs/$HOST_NAME
OUT_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.out
LOG_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.log
ERR_FILE=$LOG_DIR/`echo $PROGRAM`_$APPS_ID.err
LOG_DATE=`date`


# Source the env
. $HOME/.bash_profile
. $DBA_SCRIPTS_HOME/.sysenv

if [ $? -ne 0 ]; then
   echo "$LOG_DATE" > $LOG_FILE  
   echo "Please pass correct environment : exiting the script  \n" >> $LOG_FILE
   cat $LOG_FILE
   exit
fi

if [ -s $OUT_FILE ]; then
 echo "$LOG_DATE" > $LOG_FILE
 echo "Deleting existing output file $OUT_FILE" >> $LOG_FILE
 rm -f $OUT_FILE
 cat $LOG_FILE
fi

# If there is a plan downtime then create $ORACLE_SID.down file in $DBA_SCRIPTS_HOME to silent the alerts during maintenance window.

if [ -f $DBA_SCRIPTS_HOME/`echo $ORACLE_SID`.down ]; then
 echo "$LOG_DATE" >> $LOG_FILE
        echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
        cat $LOG_FILE
 exit
fi

if [ $DOWNTIME_MODE = "Y" ]; then
 echo "$LOG_DATE" >> $LOG_FILE
 echo "Host: $HOST_NAME | Instance: $ORACLE_SID is under maintenance: exiting the script" >> $LOG_FILE
 cat $LOG_FILE
 exit
fi

usage()
{
  echo "$LOG_DATE" > $LOG_FILE
        echo "Script to monitor database availability"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID" >> $LOG_FILE
        echo
}

if [ $# -lt 1 ] || [ "$INSTANCE" != "$ORACLE_SID" ]; then
    usage
    echo "Error : Insufficient arguments." >> $LOG_FILE
 cat $LOG_FILE
    exit
fi

check_db()
{
 sqlplus -s '/as sysdba' <<!
    
 set heading off
 set feedback off
 select count(1) from 
 (
 select name from v\$database
 );
 
    exit;
!
}

count_db=`check_db`
#echo $count_db

check_pmon()
{
 process=`ps -ef|grep ora_pmon_$ORACLE_SID | grep -v grep | wc -l`
 echo $process
}
 
count_pmon=`check_pmon`
#echo $count_pmon

echo "$LOG_DATE" > $ERR_FILE
check_db >> $ERR_FILE
ERR_COUNT=`grep -c "ORA-" $ERR_FILE`

if [ $ERR_COUNT -gt 0 -o `check_pmon` -eq 0 ]; then
    echo "Database $APPS_ID is DOWN!! Please check ASAP"| mailx -s "Critical : Database $APPS_ID is DOWN on $HOST_NAME" $DBA_EMAIL_LIST
else 
    echo "$LOG_DATE" > $OUT_FILE
 echo "Database $APPS_ID is up and running fine" >> $OUT_FILE
fi

Logs and Out files will be generated under $DBA_SCRIPTS_HOME/logs/$HOST_NAME
So make sure to create logs/$HOST_NAME directory under $DBA_SCRIPTS_HOME before executing 
the script.

Once the script is ready, then as per the requirement please schedule it in crontab/OEM.

Execute the Script as below:

Syntax :  sh <script_name> <ORACLE_SID> 

$ cd $HOME/DBA_MON/bin
$ sh DBStatusCheck.sh ORCL

This script will send the notification if database is down or unavailable.