Script to Monitor Oracle Database Sessions Consuming High TEMP Tablespace

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 database sessions which are consuming TEMP space more than given threshold(in MB) of TEMP tablespace 
and to send alert notification with the database session details. 



Download(TempTsUsage.sh)

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

#!/bin/bash

####################################################################################################
# Script Name : TempTsUsage.sh                                                                     #
#                                                                                                  #
# Description:                                                                                     #
# Script to check database sessions which are consuming TEMP space more than given threshold(in MB)#
# of TEMP tablespace and to send alert notification with the database session details              #          
#                                                                                                  #
# Usage : sh <script_name> <ORACLE_SID> <Used in MB>                                               #
# For example : sh TempTsUsage.sh ORCL 200                                                         #
#                                                                                                  #
# 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 check TEMP Tablespace usage for the database session"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <Used in MB> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 200" >> $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   T.tablespace,S.SID,S.serial#,S.username, S.osuser, s.module,
 S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
 COUNT(*) sort_ops
 FROM     v\$sort_usage T, v\$session S, dba_tablespaces TBS, v\$process P
 WHERE    T.session_addr = S.saddr
 AND      S.paddr = P.addr
 AND      T.tablespace = TBS.tablespace_name
 GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
 S.program, TBS.block_size, T.tablespace,S.MACHINE)
    where MB_USED > $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> Alert: $APPS_ID - Database Sessions Consuming High TEMP Tablespace 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 database sessions which are consuming more than $THRESHOLD MB of TEMP Tablespace:
  PROMPT


  select * from (
  SELECT   T.tablespace,S.SID,S.serial#,S.username, S.osuser, s.module,
     S.program,S.MACHINE , SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, 
     COUNT(*) sort_ops
  FROM     v\$sort_usage T, v\$session S, dba_tablespaces TBS, v\$process P
  WHERE    T.session_addr = S.saddr
  AND      S.paddr = P.addr
  AND      T.tablespace = TBS.tablespace_name
  GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,S.client_identifier,
     S.program, TBS.block_size, T.tablespace,S.MACHINE)
       where MB_USED > $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: Alert: $APPS_ID - Database Sessions Consuming High TEMP Tablespace 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 high temp usage sessions" >> $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> <Used in MB>

$ cd $HOME/DBA_MON/bin
$ sh TempTsUsage.sh ORCL 200

This script will send the notification with database session details which are consuming 

more than 200MB of TEMP tablespace.

Script To Monitor Database Tablespace Usage

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 all Tablespace Usage and to send alert notification if Tablespace free space % is less than given threshold. 

This script takes care if any datafile is in autoextensible ON, so free space will 

be calculated by considering the MAX Size of datafile.

Download(TSUsageMon.sh)

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

#!/bin/bash

#####################################################################################
# Script Name : TSUsageMon.sh                                                       #
#                                                                                   #
# Description:                                                                      #
# Script to monitor all Tablespace usage and                                        #
# to send alert notification if Tablespace free space % is less than given threshold#
#                                                                                   #
# Usage : sh <script_name> <ORACLE_SID> <%Free Threshold>                           #
# For example : sh TSUsageMon.sh ORCL 10                                            #
#                                                                                   #
# 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 Tablespace Usage"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <%Free Threshold> " >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 10"  >> $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 heading off
 set feedback off
 select count(1) from
 (
  select distinct a.tablespace_name,
  SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Total (MB)",
  (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Used (MB)",
  round(((SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Used", 
  (SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Free (MB)",
  round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Free"
  --round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
  from
  dba_data_files a,
  sys.filext$ b,
  (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
  where a.file_id = b.file#(+)
  and a.tablespace_name = c.tablespace_name
  GROUP by a.tablespace_name, c."Free"/1024
  HAVING round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) <= $THRESHOLD
  order by a.tablespace_name
 );

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 - Tablespace Free space is less than $THRESHOLD% 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 Please check below Tablespaces which are having less than $THRESHOLD% of free space 
 PROMPT


 select distinct a.tablespace_name,
 SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "Total (MB)",
 (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "Used (MB)",
 round(((SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Used", 
 (SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "Free (MB)",
 round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) "%Free"
 --round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
 from
 dba_data_files a,
 sys.filext$ b,
 (SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
 where a.file_id = b.file#(+)
 and a.tablespace_name = c.tablespace_name
 GROUP by a.tablespace_name, c."Free"/1024
 HAVING round(((SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)))/SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))*100) <= $THRESHOLD
 order by a.tablespace_name;

 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 - Tablespace Free space is less than $THRESHOLD% 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 "Tablespace Usage is Normal" >> $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> <%Free Threshold>

$ cd $HOME/DBA_MON/bin
$ sh DBAlertMon.sh VIS 10

This script will send the notification with details if there are any tablespaces which are having less 10% of free space.

Sample Notification Mail:

Script to Monitor Long Running Concurrent Requests

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 requests which are running for more than given threshold (mins) to send alert notification with the details to DBA Team. 



Download(LongRunningReq.sh)

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

#!/bin/bash

#############################################################################################
# Script Name : LongRunningReq.sh                                                           #
#                                                                                           #
# Description:                                                                              # 
# Script to check Concurrent requests which are running for more than given threshold (mins)#
# to send alert notification with the details to DBA Team                                   #
#                                                                                           #
# Usage : sh <script_name> <ORACLE_SID> <Time in mins>                                      #
# For example : sh LongRunningReq.sh ORCL 30                                                #
#                                                                                           #
# 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 Check Long Running Concurrent Requests"  >> $LOG_FILE
        echo "Usage   : sh <script_name> <ORACLE_SID> <Time in mins>" >> $LOG_FILE
  echo "For example : sh $PROGRAM.sh $ORACLE_SID 30" >> $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 heading off
 set feedback off
 select count(1) from 
 (
  select distinct qt.user_concurrent_queue_name "Queue Name"
      ,c2.user_concurrent_program_name "Program Name" 
     ,a.request_id "Request Id"
     ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
     ,FLVP.meaning "Phase"
     ,FLVS.meaning "Status"
     ,a.argument_text " Arguments " 
     ,b.os_process_id "OS Process"
     ,vs.sid "DB SID"
     ,vp.spid "DB PID"
     ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
     ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "Time Spent (Min)"
     ,u.user_name "Who Submitted"
     ,u.email_address "Submitted By - Email"
  from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
   ,applsys.fnd_concurrent_queues q
   ,applsys.fnd_concurrent_queues_tl qt
   ,APPLSYS.fnd_concurrent_programs_tl c2
   ,APPLSYS.fnd_concurrent_programs c
   ,APPLSYS.FND_LOOKUP_VALUES FLVP
   ,APPLSYS.FND_LOOKUP_VALUES FLVS
   ,APPLSYS.FND_USER u
   ,gv\$session vs
   ,gv\$process vp
  where a.controlling_manager = b.concurrent_process_id
    and a.concurrent_program_id = c.concurrent_program_id
    and a.program_application_id = c.application_id
    and c2.concurrent_program_id = c.concurrent_program_id
    and a.phase_code in ('I','P','R','T')
    and u.user_id=a.requested_by
    and a.phase_code=FLVP.Lookup_Code
    and FLVP.Lookup_Type='CP_PHASE_CODE'
    and FLVP.language='US'
    and a.status_code=FLVS.Lookup_Code
    and FLVS.Lookup_Type='CP_STATUS_CODE'
    and FLVS.language='US'
    and FLVS.view_application_id=0
    and b.queue_application_id = q.application_id
    and b.concurrent_queue_id = q.concurrent_queue_id
    and q.application_id = qt.application_id
    and qt.language='US'
    and q.concurrent_queue_id = qt.concurrent_queue_id
    and c2.language = 'US'
    and vs.process (+) = b.os_process_id
    and vs.paddr = vp.addr (+)
    and a.status_code='R'
    and c2.USER_CONCURRENT_PROGRAM_NAME not in ('Planning Manager','Cost Manager')
    and vs.inst_id=vp.inst_id
    and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > $THRESHOLD
  order by 11 desc
 );

 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> Alert: $APPS_ID - Long Running Concurrent Requests 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 Please check below concurrent requests which are running for more than $THRESHOLD Mins 
 PROMPT


 select distinct qt.user_concurrent_queue_name "Queue Name"
     ,c2.user_concurrent_program_name "Program Name" 
    ,a.request_id "Request Id"
    ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent Req"
    ,FLVP.meaning "Phase"
    ,FLVS.meaning "Status"
    ,a.argument_text " Arguments " 
    ,b.os_process_id "OS Process"
    ,vs.sid "DB SID"
    ,vp.spid "DB PID"
    ,TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') "Start Date"
    ,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) "Time Spent (Min)"
    ,u.user_name "Who Submitted"
    ,u.email_address "Submitted By - Email"
 from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
  ,applsys.fnd_concurrent_queues q
  ,applsys.fnd_concurrent_queues_tl qt
  ,APPLSYS.fnd_concurrent_programs_tl c2
  ,APPLSYS.fnd_concurrent_programs c
  ,APPLSYS.FND_LOOKUP_VALUES FLVP
  ,APPLSYS.FND_LOOKUP_VALUES FLVS
  ,APPLSYS.FND_USER u
  ,gv\$session vs
  ,gv\$process vp
 where a.controlling_manager = b.concurrent_process_id
   and a.concurrent_program_id = c.concurrent_program_id
   and a.program_application_id = c.application_id
   and c2.concurrent_program_id = c.concurrent_program_id
   and a.phase_code in ('I','P','R','T')
   and u.user_id=a.requested_by
   and a.phase_code=FLVP.Lookup_Code
   and FLVP.Lookup_Type='CP_PHASE_CODE'
   and FLVP.language='US'
   and a.status_code=FLVS.Lookup_Code
   and FLVS.Lookup_Type='CP_STATUS_CODE'
   and FLVS.language='US'
   and FLVS.view_application_id=0
   and b.queue_application_id = q.application_id
   and b.concurrent_queue_id = q.concurrent_queue_id
   and q.application_id = qt.application_id
   and qt.language='US'
   and q.concurrent_queue_id = qt.concurrent_queue_id
   and c2.language = 'US'
   and vs.process (+) = b.os_process_id
   and vs.paddr = vp.addr (+)
   and a.status_code='R'
   and c2.USER_CONCURRENT_PROGRAM_NAME not in ('Planning Manager','Cost Manager')
   and vs.inst_id=vp.inst_id
   and round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440,2) > $THRESHOLD
 order by 11 desc;

 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: Alert : $APPS_ID - Long Running Concurrent Requests 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 "No Long Running Concurrent Requests" >> $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> <Time in mins>

$ cd $HOME/DBA_MON/bin
$ sh LongRunningReq.sh ORCL 30

This script will send the notification with Concurrent request details which are running for 

more than 30 minutes.

Script to check workflow mailer service components status and send notification if DOWN

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 WorkflowStatusCheck.sh to check workflow mailer service components status:

Download(WorkflowStatusCheck.sh)

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

#!/bin/bash
###################################################################################
# Script Name :  WorkflowStatusCheck.sh                                           #
#                                                                                 #
# Description:                                                                    #
# Script to check workflow mailer service components status and                   #
# to send alert notification if one or more workflow mailer components are down   #
#                                                                                 #
# Usage : sh <script_name> <ORACLE_SID>                                           #
# For example : sh WorkflowStatusCheck.sh ORCL                                    #
#                                                                                 #
# Created by : Kiran Jadhav - (https://h2hdba.blogspot.com)                       #
###################################################################################

# Initialize variables

INSTANCE=$1
HOST_NAME=`hostname`
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 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 Check Workflow Mailer Components 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 component_id, startup_mode,component_status,component_type,component_name
  from applsys.fnd_svc_components 
  where component_status <> 'RUNNING'
  order by component_id
 );

 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 - Workflow Mailer Components 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 Logon on to OAM (Oracle Applications Manager) and make sure the following processes are running, if not restart them.
 PROMPT


 select component_id, startup_mode,component_status,component_type,component_name
 from applsys.fnd_svc_components 
 where component_status <> 'RUNNING'
 order by component_id;

 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 - Workflow Mailer Components 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 "`date`" > $LOG_FILE
echo "Details sent through an email" >> $LOG_FILE
cat $LOG_FILE

else 
    echo "`date`" > $OUT_FILE
 echo "Workflow Mailer Components 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 WorkflowStatusCheck.sh ORCL

This script will send alert notification if one or more workflow mailer components are down.

Sample Email Notification Output: