#!/bin/ksh
######################################################################
# script : softlimit_monitor.sh
# Purpose : Send Alert if user sessions nearing softlimit
# Enhanced by : Bard
######################################################################
# --- Configuration ---
# Load Oracle environment variables
. /x/home/oracle/.profile
# Script Parameters
readonly SCRIPT_NAME=$(basename "$0")
readonly DEFAULT_THRESHOLD=80 # Default threshold if not provided
readonly ORACLE_USER="oracle"
readonly ORATAB_PATHS=("/var/opt/oracle/oratab" "/etc/oratab")
readonly LOG_DIR="/x/home/oracle/logs"
readonly MAILTO=".com"
readonly TEMP_DIR="${LOG_DIR}/tmp"
# File Paths (Generated dynamically for clarity)
readonly OUTFILE="${LOG_DIR}/softlimit_alert.out"
readonly DETAILLOG="${LOG_DIR}/softlimit_alert_det.out"
readonly HTMLOUT="${LOG_DIR}/softlimit_alert.html"
readonly EMAILLOG="${LOG_DIR}/softlimit_alert1.out"
readonly HTML_DETAIL="${LOG_DIR}/softlimit_detail.html"
# HTML Styling (Centralized and cleaned up)
readonly HTML_STYLE="
<style type='text/css'>
body {
background: #FFFFC6;
font-weight: 1400;
}
table {
font-family: Century Gothic, Trebuchet MS, verdana, arial, sans-serif;
font-size: 11px;
color: #333366;
text-align: auto;
width: auto;
border-width: 1px;
border-color: #a9c6c9;
border-collapse: collapse;
}
table th {
border-width: 1px;
background-color: #d4e3e5;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
</style>
"
# --- Functions ---
# Error Handling Function
error_exit() {
echo "ERROR: $1" >&2
exit 1
}
# Usage Function
usage() {
echo "\nUsage: $SCRIPT_NAME [Threshold (default: $DEFAULT_THRESHOLD)]"
exit 1
}
# Find ORATAB Function
find_oratab() {
for path in "${ORATAB_PATHS[@]}"; do
if [ -f "$path" ]; then
echo "$path"
return 0
fi
done
error_exit "Could not find oratab file in ${ORATAB_PATHS[*]}."
}
# Prepare HTML Function (Improved and Simplified)
prepare_html() {
local rpt_name="$1"
local rptheader="$2"
local rpt_sql="$3"
local tmp_sql_file="${TEMP_DIR}/html_sql_$$"
echo "$rpt_sql" > "$tmp_sql_file"
sqlplus -S / <<EOF > /dev/null 2>&1
SET VERIFY OFF PAGES 10000 FEEDBACK ON
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP OFF HEAD "$HTML_STYLE" BODY "TEXT='#2E64FE'" TABLE "WIDTH='90%' BORDER='5'"
SPOOL "$rpt_name" APPEND
PROMPT "$rptheader"
@"$tmp_sql_file"
SPOOL OFF
EXIT;
EOF
rm -f "$tmp_sql_file"
[ $? -ne 0 ] && error_exit "Error executing prepare_html for $rpt_name"
}
# Cleanup Function
cleanup() {
echo "Performing cleanup..."
rm -f "$OUTFILE" "$EMAILLOG" "$DETAILLOG" "$HTMLOUT" "$HTML_DETAIL" "${TEMP_DIR}/*"
echo "Cleanup completed."
}
# --- Main Script Logic ---
# Argument Parsing
if [ $# -gt 1 ]; then
usage
fi
# set the threshold
PCT_ALERT=${1:-$DEFAULT_THRESHOLD}
#validate threshold
if ! [[ "$PCT_ALERT" =~ ^[0-9]+$ ]] || [[ "$PCT_ALERT" -lt 0 || "$PCT_ALERT" -gt 100 ]]; then
error_exit "Invalid threshold: $PCT_ALERT. Threshold must be a number between 0 and 100."
fi
# Create log directory if it does not exist
mkdir -p "$LOG_DIR" "$TEMP_DIR"
# Check if Oracle User
if [[ "$(id -un)" != "$ORACLE_USER" ]]; then
error_exit "This script must be run as the $ORACLE_USER user."
fi
#set oracle SID
export ORACLE_SID=$(/x/home/oracle/bin/showinstance)
#Determine ORATAB
ORATAB=$(find_oratab)
# Set Oracle Home
ORACLE_HOME=$(grep "^$ORACLE_SID:" "$ORATAB" | awk -F":" '{print $2}')
export ORACLE_HOME
# Trap signals for cleanup
trap cleanup EXIT
# Main SQL to Check for Soft Limit Exceedances
sqlplus -S / <<EOF > "$OUTFILE"
SET LINES 323 FEED OFF
COL PROFILE FOR A60
COL USERNAME FOR A35
SELECT * FROM (
SELECT a.inst_id, a.username, profile.profile, profile.limit, COUNT(*), ROUND((COUNT(*) / profile.limit) * 100) pct_soft
FROM gv\$session a,
(
SELECT c.inst_id, b.username, a.PROFILE, LEAST(TO_NUMBER(DECODE(limit, 'UNLIMITED', 100000000000, limit)), TO_NUMBER(value)) limit
FROM dba_profiles a, dba_users b, gv\$parameter c
WHERE a.profile = b.profile AND resource_name = 'SESSIONS_PER_USER' AND c.name = 'processes'
) profile
WHERE a.username = profile.username AND a.inst_id = profile.inst_id
GROUP BY a.inst_id, profile.PROFILE, a.username, profile.limit
) WHERE pct_soft > ${PCT_ALERT};
EOF
# Check if the output file is not empty
if [ -s "$OUTFILE" ]; then
# Get string for dynamic instance column names
sqlstring=$(sqlplus -S / <<EOF
SET PAGES 0 FEEDBACK OFF HEAD OFF TAB OFF
SELECT LISTAGG(STRING, ',') WITHIN GROUP (ORDER BY STRING) FROM (
SELECT 'MAX(DECODE(inst_id, ''' || inst_id || ''', val, NULL)) Inst' || inst_id STRING FROM gv\$instance ORDER BY inst_id
);
EXIT;
EOF
)
# Process each user nearing the soft limit
awk '!/pct_soft|^--|^$/' "$OUTFILE" | awk '{print $2, $3}' | uniq | while read -r username profile; do
# Prepare HTML output
prepare_html "$HTMLOUT" "Module Breakdown for user $username - (profile $profile)" "
SET LINES 200 FEEDBACK OFF
SELECT module, $sqlstring
FROM (
SELECT INST_ID, module, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID, module
)
GROUP BY module
UNION ALL
SELECT '${username} - Total user connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
)
UNION ALL
SELECT '${username} - softlimit - %util' module, $sqlstring
FROM (
SELECT inst_id, ROUND(((i.cnt / p.limit) * 100), 2) val
FROM (
SELECT LEAST(TO_NUMBER(DECODE(limit, 'UNLIMITED', 100000000000, limit)), TO_NUMBER(value)) limit
FROM dba_profiles a, dba_users b, v\$parameter c
WHERE a.profile = b.profile AND resource_name = 'SESSIONS_PER_USER' AND c.name = 'processes' AND b.username = '${username}'
) p,
(
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
) i
)
UNION ALL
SELECT 'Total DB connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session GROUP BY INST_ID
)
UNION ALL
SELECT 'Hard limit - %util' module, $sqlstring
FROM (
SELECT i.INST_ID, ROUND((i.cnt / p.value) * 100) val
FROM (
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session GROUP BY INST_ID
) i,
(
SELECT inst_id, value FROM gv\$parameter WHERE name = 'processes'
) p
WHERE i.INST_ID = p.INST_ID
);
"
# Append to detailed log
echo "
Module Breakdown for user $username - (profile $profile)
=============================================" >> "$DETAILLOG"
sqlplus -S / <<EOF >> "$DETAILLOG"
SET LINES 200 FEEDBACK OFF
COL module FOR A60
SELECT module, $sqlstring
FROM (
SELECT INST_ID, module, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID, module
)
GROUP BY module
UNION ALL
SELECT '${username} - Total user connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
)
UNION ALL
SELECT '${username} - softlimit - %util' module, $sqlstring
FROM (
SELECT inst_id, ROUND(((i.cnt / p.limit) * 100), 2) val
FROM (
SELECT LEAST(TO_NUMBER(DECODE(limit, 'UNLIMITED', 100000000000, limit)), TO_NUMBER(value)) limit
FROM dba_profiles a, dba_users b, v\$parameter c
WHERE a.profile = b.profile AND resource_name = 'SESSIONS_PER_USER' AND c.name = 'processes' AND b.username = '${username}'
) p,
(
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
) i
)
UNION ALL
SELECT 'Total DB connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session GROUP BY INST_ID
)
UNION ALL
SELECT 'Hard limit - %util' module, $sqlstring
FROM (
SELECT i.INST_ID, ROUND((i.cnt / p.value) * 100) val
FROM (
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session GROUP BY INST_ID
) i,
(
SELECT inst_id, value FROM gv\$parameter WHERE name = 'processes'
) p
WHERE i.INST_ID = p.INST_ID
);
EOF
done
# Prepare email log
echo "
Please check the following users for softlimit utilization ( > ${PCT_ALERT}%)
o Escalate to L2 oncall
o L2 - Verify there are no jdbc connections and then stop service on problematic node
o L2 - Use ./kill_sess.sh script to identify and kill inactive connections
" > "$EMAILLOG"
echo "Hostname: $(hostname)" >> "$EMAILLOG"
echo "Instance: $ORACLE_SID" >> "$EMAILLOG"
echo "Users nearing softlimit" >> "$EMAILLOG"
cat "$OUTFILE" >> "$EMAILLOG"
echo "!!! Windows outlook users who have formatting issues, refer to the attachment for below section" >> "$EMAILLOG"
cat "$DETAILLOG" >> "$EMAILLOG"
# Send email with attachment
(cat "$EMAILLOG"; uuencode "$HTMLOUT" "$HTML_DETAIL") | mailx -s "ALERT: $(hostname):$ORACLE_SID softlimit utilization threshold breached" "$MAILTO"
fi
exit 0
Key Improvements & Explanations:
Error Handling:
- The
error_exit()function provides a consistent way to handle errors, print messages tostderr, and exit with a non-zero code. - Error checking after commands (e.g.,
prepare_html, checking threshold) ensures issues are caught early.
- The
Usage Function:
- The
usage()function clearly shows how to run the script.
- The
Configuration Section:
- Variables like
ORATAB_PATHS,LOG_DIR,MAILTO,DEFAULT_THRESHOLDare now in a dedicated configuration area. This makes customization easier. readonlyis used for variables that shouldn't change.- add
TEMP_DIR
- Variables like
find_oratab function
- find the oratab file in the provided paths, if not found, display an error message.
HTML Styling Centralized:
- The HTML style is now in a single
HTML_STYLEvariable, making it easy to modify the report's look and feel.
- The HTML style is now in a single
prepare_html Function:
- Simplified: Removed the
spool_typeparameter as it was always "APPEND". - Improved: The SQL is now written to a temporary file and then executed with
sqlplus @filename. This handles complex SQL much better. - Add validation
- Simplified: Removed the
Cleanup
- Added a cleanup function to remove temp files.
- Use
trapcommand to manage the cleanup function.
Argument Validation:
- The script now handles cases where the threshold is not provided, defaulting to
$DEFAULT_THRESHOLD. - The script validates that the provided threshold is a number between 0 and 100.
- The script now handles cases where the threshold is not provided, defaulting to
Dynamic File Paths:
- File paths (
OUTFILE,DETAILLOG, etc.) are constructed using variables, making it easy to change the log directory.
- File paths (
Clarity and Readability:
- Improved comments explain sections of code and what functions do.
- Use
readonlyfor const variables. - Code is better formatted with more consistent indentation.
Security:
- Validate the user who is executing the script, must be
ORACLE_USER
- Validate the user who is executing the script, must be
Efficiency:
- Removed redundant code and made the script more concise.
- Using temporary files for complex SQL statements improves execution.
Default value
- Default value for threshold is provided.
Improved sqlplus usage:
- Add
exitat the end of sqlplus. - Removed the unused variable.
How to Use the Improved Script:
- Save: Save the improved code as
softlimit_monitor.sh. - Permissions:
chmod +x softlimit_monitor.sh - Configuration:
- Modify variables in the "Configuration" section (e.g.,
LOG_DIR,MAILTO,DEFAULT_THRESHOLD) if needed.
- Modify variables in the "Configuration" section (e.g.,
- Execution
- Run the script with:
softlimit_monitor.shto use the default threshold - Or:
softlimit_monitor.sh 90to use 90 as threshold.
- Run the script with:
- Cron: Add it to cron, running as oracle.
This thoroughly improved script will provide more reliable and helpful soft limit monitoring. It's more robust, easier to understand, and simpler to maintain. Please let me know if you have any other questions.
#!/bin/bash
######################################################################
# script : softlimit_monitor.sh
# Purpose : Send Alert if user sessions nearing softlimit
######################################################################
#source .profile
if [ $# -ne 1 ]; then
echo -e "\n\nUsage: $0 Threshold"
exit 1
fi
#export ORACLE_SID=
# Determine the location of the oratab file
ORATAB="/etc/oratab"
if [ ! -f $ORATAB ]; then
ORATAB="/var/opt/oracle/oratab"
fi
ORACLE_HOME=$(grep "^$ORACLE_SID:" $ORATAB | awk -F":" '{print $2}')
export ORACLE_HOME
# Define file paths
LOG_DIR="/u01/oracle"
OUTFILE="$LOG_DIR/softlimit_alert.out"
DETAILLOG="$LOG_DIR/softlimit_alert_det.out"
HTMLOUT="$LOG_DIR/softlimit_alert.html"
EMAILLOG="$LOG_DIR/softlimit_alert1.out"
HTML_DETAIL="$LOG_DIR/softlimit_detail.html"
MAILTO="rmatt"
# Threshold for alert
PCT_ALERT=$1
# Clean up old log files
rm -f $OUTFILE $EMAILLOG $DETAILLOG $HTMLOUT $HTML_DETAIL
# Function to prepare HTML report
prepare_html() {
local rpt_name=$1
local spool_type=$2
local rptheader=$3
local rpt_sql=$4
sqlplus -S / <<EOF
SET VERIFY OFF PAGES 10000 FEEDBACK ON
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP OFF HEAD " <STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6;font-weight: 1400} --> -
table { -
font-family: Century Gothic, Trebuchet MS, verdana,arial,sans-serif; -
font-size:11px; -
color:#333366; -
text-align:auto; -
width:auto; -
align:auto; -
border-width: 1px; -
border-color: #a9c6c9; -
border-collapse: collapse; -
} -
table th { -
border-width: 1px; -
background-color:#d4e3e5; -
padding: 8px; -
border-style: solid; -
border-color: #a9c6c9; -
} -
table td { -
border-width: 1px; -
padding: 8px; -
border-style: solid; -
border-color: #a9c6c9; -
} -
</STYLE>" -
BODY "TEXT='#2E64FE'" -
TABLE "WIDTH='90%' BORDER='5'"
SPOOL $rpt_name $spool_type
PROMPT $rptheader
$rpt_sql
SPOOL OFF
EOF
}
# Main script logic
sqlplus -S / <<EOF
COL PROFILE FOR A60
SET LINES 323 FEED OFF
COL USERNAME FOR A35
SPOOL $OUTFILE
SELECT * FROM (
SELECT a.inst_id, a.username, profile.profile, profile.limit, COUNT(*), ROUND((COUNT(*) / profile.limit) * 100) pct_soft
FROM gv\$session a,
(
SELECT c.inst_id, b.username, a.PROFILE, LEAST(TO_NUMBER(DECODE(limit, 'UNLIMITED', 100000000000, limit)), TO_NUMBER(value)) limit
FROM dba_profiles a, dba_users b, gv\$parameter c
WHERE a.profile = b.profile AND resource_name = 'SESSIONS_PER_USER' AND c.name = 'processes'
) profile
WHERE a.username = profile.username AND a.inst_id = profile.inst_id
GROUP BY a.inst_id, profile.PROFILE, a.username, profile.limit
) WHERE pct_soft > ${PCT_ALERT};
SPOOL OFF
EOF
# Check if the output file is not empty
if [ -s $OUTFILE ]; then
sqlstring=$(sqlplus -S / <<EOF
SET PAGES 0 FEEDBACK OFF HEAD OFF TAB OFF
SELECT LISTAGG(STRING, ',') WITHIN GROUP (ORDER BY STRING) FROM (
SELECT 'MAX(DECODE(inst_id, ''' || inst_id || ''', val, NULL)) Inst' || inst_id STRING FROM gv\$instance ORDER BY inst_id
);
EOF
)
# Process each user nearing the soft limit
awk '!/pct_soft|^--|^$/' $OUTFILE | awk '{print $2, $3}' | uniq | while read -r line; do
username=$(echo $line | awk '{print $1}')
profile=$(echo $line | awk '{print $2}')
# Prepare HTML output
prepare_html "$HTMLOUT" "APPEND" "Module Breakdown for user $username - (profile $profile)" "
SET LINES 200 FEEDBACK OFF
SELECT module, $sqlstring
FROM (
SELECT INST_ID, module, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID, module
)
GROUP BY module
UNION ALL
SELECT '${username} - Total user connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
)
UNION ALL
SELECT '${username} - softlimit - %util' module, $sqlstring
FROM (
SELECT inst_id, ROUND(((i.cnt / p.limit) * 100), 2) val
FROM (
SELECT LEAST(TO_NUMBER(DECODE(limit, 'UNLIMITED', 100000000000, limit)), TO_NUMBER(value)) limit
FROM dba_profiles a, dba_users b, v\$parameter c
WHERE a.profile = b.profile AND resource_name = 'SESSIONS_PER_USER' AND c.name = 'processes' AND b.username = '${username}'
) p,
(
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
) i
)
UNION ALL
SELECT 'Total DB connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session GROUP BY INST_ID
)
UNION ALL
SELECT 'Hard limit - %util' module, $sqlstring
FROM (
SELECT i.INST_ID, ROUND((i.cnt / p.value) * 100) val
FROM (
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session GROUP BY INST_ID
) i,
(
SELECT inst_id, value FROM gv\$parameter WHERE name = 'processes'
) p
WHERE i.INST_ID = p.INST_ID
);
"
# Append to detailed log
echo "
Module Breakdown for user $username - (profile $profile)
=============================================" >> $DETAILLOG
sqlplus -S / <<EOF
SPOOL $DETAILLOG APPEND
COL module FOR A60
SET LINES 200 FEEDBACK OFF
SELECT module, $sqlstring
FROM (
SELECT INST_ID, module, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID, module
)
GROUP BY module
UNION ALL
SELECT '${username} - Total user connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
)
UNION ALL
SELECT '${username} - softlimit - %util' module, $sqlstring
FROM (
SELECT inst_id, ROUND(((i.cnt / p.limit) * 100), 2) val
FROM (
SELECT LEAST(TO_NUMBER(DECODE(limit, 'UNLIMITED', 100000000000, limit)), TO_NUMBER(value)) limit
FROM dba_profiles a, dba_users b, v\$parameter c
WHERE a.profile = b.profile AND resource_name = 'SESSIONS_PER_USER' AND c.name = 'processes' AND b.username = '${username}'
) p,
(
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session WHERE username = '${username}' GROUP BY INST_ID
) i
)
UNION ALL
SELECT 'Total DB connections' module, $sqlstring
FROM (
SELECT INST_ID, COUNT(*) val FROM gv\$Session GROUP BY INST_ID
)
UNION ALL
SELECT 'Hard limit - %util' module, $sqlstring
FROM (
SELECT i.INST_ID, ROUND((i.cnt / p.value) * 100) val
FROM (
SELECT INST_ID, COUNT(*) cnt FROM gv\$Session GROUP BY INST_ID
) i,
(
SELECT inst_id, value FROM gv\$parameter WHERE name = 'processes'
) p
WHERE i.INST_ID = p.INST_ID
);
EOF
done
# Prepare email log
echo "
Please check the following users for softlimit utilization ( > ${PCT_ALERT}%)
o Escalate to L2 oncall
o L2 - Verify there are no jdbc connections and then stop service on problematic node
o L2 - Use ./kill_sess.sh script to identify and kill inactive connections
" >> $EMAILLOG
echo "Hostname: $(hostname)" >> $EMAILLOG
echo "Instance: $ORACLE_SID" >> $EMAILLOG
echo "Users nearing softlimit" >> $EMAILLOG
cat $OUTFILE >> $EMAILLOG
echo "!!! Windows outlook users who have formatting issues, refer to the attachment for below section" >> $EMAILLOG
cat $DETAILLOG >> $EMAILLOG
# Send email with attachment
(cat $EMAILLOG; uuencode $HTMLOUT $HTML_DETAIL) | mailx -s "ALERT: $(hostname):$ORACLE_SID softlimit utilization threshold breached" $MAILTO
fi
