Db-monitoring_script

[Nirmal7086]

#!/bin/bash
###############################################################################

Oracle 19c Session Monitor

- Blocking sessions

- Waiting sessions

- Row-level lock waits (TX enqueue)

- Lock holders

- Long-running operations

- Email alerts including "ALTER SYSTEM KILL SESSION" suggestions for DBA

Runs in nohup background mode, interval set by first parameter (default 60s)

###############################################################################

==== CONFIGURATION ====

ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1"
ORACLE_SID="ORCL" # Change to match your DB SID
PATH="$ORACLE_HOME/bin:$PATH"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
SQLPLUS_OPTS="-s / as sysdba"

Directories

LOG_DIR="/opt/dbmon/log"
SQL_DIR="/opt/dbmon/sql"

Sleep interval (seconds), passed as first parameter (default 60)

SLEEP_SECS="${1:-60}"

Email alerts

ENABLE_EMAIL_ALERTS=1
ALERT_TO="dba-team@example.com"
ALERT_SUBJ_PREFIX="[DBMON] ${ORACLE_SID}"

mkdir -p "$LOG_DIR"

timestamp() { date "+%Y-%m-%d %H:%M:%S"; }
logfile() { echo "${LOG_DIR}/session_monitor_$(date +%F).log"; }

run_sql() {
local sqlfile="$1"
sqlplus $SQLPLUS_OPTS <<EOF
set serveroutput on time on timing off
@$sqlfile
exit
EOF
}

emit_section() {
local title="$1"
echo "[$(timestamp)] ===== ${title} ====="
}

Email sending with "kill" suggestions (no actual kill)

send_alert_if_nonempty() {
local title="$1"
local tmpfile="$2"
if [ "$ENABLE_EMAIL_ALERTS" -eq 1 ] && [ -s "$tmpfile" ]; then
{
echo "=== $title ==="
cat "$tmpfile"
echo
echo "=== Suggested kill commands (manual DBA review) ==="
# Assuming SQL script outputs inst_id in col1, SID in col2, SERIAL# in col3
awk '
/^[0-9 ]+/ && $2 ~ /^[0-9]+$/ && $3 ~ /^[0-9]+$/ {
printf("ALTER SYSTEM KILL SESSION '''%s,%s,@inst_id=%s''' IMMEDIATE;\n", $2, $3, $1)
}
' "$tmpfile"
echo
} | mailx -s "${ALERT_SUBJ_PREFIX} ${title}" "$ALERT_TO"
fi
}

==== MAIN LOOP ====

echo "[$(timestamp)] Starting session monitor for ORACLE_SID=${ORACLE_SID} (interval ${SLEEP_SECS}s)"
while true
do
TODAY_LOG="$(logfile)"
TMP_BLOCKERS="$(mktemp)"
TMP_WAITERS="$(mktemp)"
TMP_ROWLOCKS="$(mktemp)"
TMP_LOCKHOLD="$(mktemp)"
TMP_LONGOPS="$(mktemp)"

{
emit_section "BLOCKERS"
run_sql "${SQL_DIR}/blocker.sql" | tee "$TMP_BLOCKERS"

emit_section "WAITERS"
run_sql "${SQL_DIR}/waiters.sql" | tee "$TMP_WAITERS"

emit_section "ROW-LEVEL LOCK WAITS (TX)"
run_sql "${SQL_DIR}/row_locks.sql" | tee "$TMP_ROWLOCKS"

emit_section "CURRENT LOCK HOLDERS"
run_sql "${SQL_DIR}/lock_holders.sql" | tee "$TMP_LOCKHOLD"

emit_section "LONG-RUNNING OPERATIONS"
run_sql "${SQL_DIR}/longops.sql" | tee "$TMP_LONGOPS"

} >> "$TODAY_LOG" 2>&1

Email alert condition

send_alert_if_nonempty "Blockers detected" "$TMP_BLOCKERS"
send_alert_if_nonempty "Waiters detected" "$TMP_WAITERS"
send_alert_if_nonempty "Row locks detected" "$TMP_ROWLOCKS"
send_alert_if_nonempty "Long operations detected" "$TMP_LONGOPS"

rm -f "$TMP_BLOCKERS" "$TMP_WAITERS" "$TMP_ROWLOCKS" "$TMP_LOCKHOLD" "$TMP_LONGOPS"

sleep "$SLEEP_SECS"
done