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.
HI Nice script
ReplyDeletecan you tell me how can we schedule this script , how can this script will send notification as soon as managers are down.
You can schedule it in crontab. This script will send the notification if you have sendmail and mailx configured at OS level
Deletewhat do i do if i dont have sendmail configured in my environment...i have only mailx
ReplyDeleteHi Priyanka,
DeleteYou can try to send output as a attachment in mailx using uuencode. Hope this helps!