check_db_activity.sh performs a quick health and activity snapshot across multiple Oracle database instances on a host. It automatically discovers instances from PMON processes or accepts an explicit list of SIDs, initializes the environment per SID, connects as SYSDBA, and captures key runtime indicators (state, sessions, waits, Data Guard lag, long operations).Output is written to a timestamped log file.
#!/usr/bin/env bash
# check_db_activity.sh
# Quick multi-instance activity check based on PMON list or args
# Exit codes: 0 ok, 1 partial (some SIDs failed), 2 fatal
set -euo pipefail
# -------- Config --------
ORATAB="${ORATAB:-/etc/oratab}"
NLS_LANG="${NLS_LANG:-AMERICAN_AMERICA.AL32UTF8}"
export NLS_LANG
LOG_DIR="/appli/home/oracle/dbascripts/logs"
mkdir -p "$LOG_DIR"
RUN_TS="$(date '+%Y-%m-%d_%H-%M-%S')"
REPORT="$LOG_DIR/db_activity_${RUN_TS}.log"
say() { printf '%s %sn' "[$(date '+%F %T')]" "$*" | tee -a "$REPORT"; }
have_cmd() { command -v "$1" >/dev/null 2>&1; }
fatal() { echo "FATAL: $*" | tee -a "$REPORT" >&2; exit 2; }
# -------- Pre-checks --------
have_cmd sqlplus || fatal "sqlplus not found in PATH (source Oracle environment first)."
[ -r "$ORATAB" ] || fatal "Cannot read $ORATAB (needed by oraenv)."
# Try to make oraenv available (function or script)
if ! have_cmd oraenv; then
for p in /usr/local/bin/oraenv /usr/bin/oraenv; do
[ -f "$p" ] && PATH="$(dirname "$p"):$PATH"
done
fi
have_cmd oraenv || fatal "oraenv not available in PATH."
# Resolve oraenv path if not a function
ORAENV_CMD="oraenv"
if ! type oraenv >/dev/null 2>&1; then
ORAENV_CMD="$(command -v oraenv)"
fi
# -------- Build SID list --------
SIDS=()
if [ "$#" -gt 0 ]; then
for s in "$@"; do SIDS+=("$s"); done
else
# Auto-detect from PMON processes
while read -r _u _pid _ppid _c _d _tty _time pname; do
sid="$(echo "$pname" | sed -n 's/.*ora_pmon_//p')"
[ -n "$sid" ] && SIDS+=("$sid")
done < <(/usr/bin/ps -ef | awk '$8 ~ /ora_pmon_/')
fi
# Deduplicate preserving order
uniq_sids=()
for s in "${SIDS[@]}"; do
skip=0; for u in "${uniq_sids[@]}"; do [ "$u" = "$s" ] && { skip=1; break; }; done
[ $skip -eq 0 ] && uniq_sids+=("$s")
done
SIDS=("${uniq_sids[@]}")
[ "${#SIDS[@]}" -gt 0 ] || fatal "No ORACLE_SID found (from PMON or args)."
say "Starting database activity check for: ${SIDS[*]}"
say "Report: $REPORT"
fail_count=0
# -------- SQL block --------
SQL_BLOCK="$(cat <<'SQL'
SET PAGES 999 LINES 300 TRIMSPOOL ON TAB OFF FEEDBACK OFF VERIFY OFF HEADING ON
COLUMN ts FORMAT A25
COLUMN instance_name FORMAT A12
COLUMN host_name FORMAT A20
COLUMN status FORMAT A10
COLUMN database_role FORMAT A16
COLUMN open_mode FORMAT A14
COLUMN sid_serial FORMAT A17
COLUMN username FORMAT A20
COLUMN machine FORMAT A20
COLUMN program FORMAT A35
COLUMN event FORMAT A40
COLUMN wait_class FORMAT A20
COLUMN sql_id FORMAT A13
COLUMN opname FORMAT A25
COLUMN sofar FORMAT 999,999,999
COLUMN totalwork FORMAT 999,999,999
COLUMN pct_done FORMAT 990.0
PROMPT === [1/6] Instance / Database state ===
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS TZH:TZM') ts,
i.instance_name, i.host_name, i.status,
d.database_role, d.open_mode
FROM v$instance i CROSS JOIN v$database d;
PROMPT
PROMPT === [2/6] Sessions ===
PROMPT - Active user sessions (non-background)
SELECT COUNT(*) AS active_user_sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND type <> 'BACKGROUND';
PROMPT - Blocking / Blocked sessions
SELECT
(SELECT COUNT(*) FROM gv$lock l1 WHERE l1.block = 1) AS blocking_locks,
(SELECT COUNT(*) FROM gv$lock l2 WHERE l2.request > 0) AS waiting_locks
FROM dual;
PROMPT
PROMPT === [3/6] Top 3 sessions by CPU (approx via last_call_et) ===
SELECT s.sid||','||s.serial# AS sid_serial,
s.username,
s.machine,
s.program,
s.sql_id,
s.event,
s.state,
s.seconds_in_wait
FROM gv$session s
WHERE s.status = 'ACTIVE'
AND s.type <> 'BACKGROUND'
ORDER BY s.last_call_et DESC
FETCH FIRST 3 ROWS ONLY;
PROMPT
PROMPT === [4/6] Wait classes snapshot (current) ===
SELECT wait_class,
COUNT(*) AS sess
FROM gv$session
WHERE status = 'ACTIVE'
AND type <> 'BACKGROUND'
GROUP BY wait_class
ORDER BY sess DESC;
PROMPT
PROMPT === [5/6] Data Guard (if STANDBY) ===
SELECT name, value
FROM v$dataguard_stats
WHERE name IN ('transport lag','apply lag','apply finish time')
ORDER BY name;
PROMPT
PROMPT === [6/6] Long operations (top 5 ongoing) ===
SELECT opname,
sofar,
totalwork,
CASE WHEN totalwork > 0 THEN ROUND(sofar*100/totalwork,1) END AS pct_done,
elapsed_seconds,
time_remaining,
sid, serial#
FROM v$session_longops
WHERE sofar < totalwork
ORDER BY elapsed_seconds DESC FETCH FIRST 5 ROWS ONLY;
SQL
)"
# -------- Loop over SIDs --------
for SID in "${SIDS[@]}"; do
{
echo
echo "============================================================"
echo "ORACLE_SID = $SID"
echo "============================================================"
} | tee -a "$REPORT"
# 1) Set ORACLE_SID for this iteration
export ORACLE_SID="$SID"
export ORAENV_ASK=NO
# 2) Validate SID exists in oratab
if ! awk -F: -v s="$SID" '($1==s){found=1} END{exit found?0:1}' "$ORATAB"; then
say "WARN: $SID not found in $ORATAB — skipping."
fail_count=$((fail_count+1))
continue
fi
# 3) Load env via oraenv (function or script path)
if ! . "$ORAENV_CMD" >/dev/null 2>&1; then
say "WARN: oraenv failed for $SID — skipping."
fail_count=$((fail_count+1))
continue
fi
# 4) Trace which ORACLE_HOME is used
if [ -z "${ORACLE_HOME:-}" ]; then
say "WARN: ORACLE_HOME is empty after oraenv for $SID — skipping."
fail_count=$((fail_count+1))
continue
else
say "Using ORACLE_HOME=${ORACLE_HOME} for SID=$SID"
fi
# 5) Connectivity check
if ! echo "select 'OK' from dual;" | sqlplus -s '/ as sysdba' >/dev/null; then
say "WARN: Cannot connect as sysdba on $SID — skipping."
fail_count=$((fail_count+1))
continue
fi
# 6) Run activity SQL
sqlplus -s '/ as sysdba' <<SQL >>"$REPORT"
$SQL_BLOCK
SQL
done
echo
say "Completed. Failures: $fail_count. See report: $REPORT"
# Exit code
if [ $fail_count -gt 0 ]; then exit 1; fi
exit 0