[ORACLE] Monitor DG lag

Monitor DG LAG

#!/usr/bin/env bash
# Monitor Data Guard apply lag (by sequences) from the PRIMARY.
# Usage : monitor_dg_lag.sh -sid <instance_name> -t 100

set -uo pipefail

# -------- Defaults --------
THRESHOLD=50
DEST_PRIMARY=1
DEST_STANDBY=2

RECIPIENTS="[e-mail]"
MAIL_FROM="[e-mail]"
MAIL_SUBJECT_PREFIX="[ALERTE][DG]"

ORACLE_SID=""
HOSTNAME_FQDN="$(hostname -f 2>/dev/null || hostname)"
LOG_TAG="monitor_dg_lag"

usage() {
  cat <<EOF
Usage: $0 -sid ORACLE_SID [-t SEUIL]

  -sid  SID Oracle (ex: CDBPRD04)                 [OBLIGATOIRE]
  -t    Seuil du lag (en séquences, défaut: 50)

Exemple :
  $0 -sid CDBPRD04 -t 100
EOF
  exit 1
}

log_info()  { logger -t "${LOG_TAG}" "INFO: $*";  echo "INFO: $*"; }
log_warn()  { logger -t "${LOG_TAG}" "WARN: $*";  echo "WARN: $*" >&2; }
log_error() { logger -t "${LOG_TAG}" "ERROR: $*"; echo "ERROR: $*" >&2; }

# -------- Parse options --------
while [[ $# -gt 0 ]]; do
  case "$1" in
    -sid)
      ORACLE_SID="$2"
      shift 2
      ;;
    -t)
      THRESHOLD="$2"
      shift 2
      ;;
    -h|--help)
      usage
      ;;
    *)
      usage
      ;;
  esac
done

# -------- Required --------
[ -z "${ORACLE_SID}" ] && usage

# Validate numeric threshold
case "$THRESHOLD" in ''|*[!0-9]*) log_error "Le seuil (-t) doit être un entier."; exit 2 ;; esac

### Vérification que le SID existe réellement ###
SID_EXISTS=false

# 1) Vérifier si un fichier init ou spfile existe pour ce SID
if [[ -f /etc/oratab ]]; then
    if grep -E "^${ORACLE_SID}:" /etc/oratab >/dev/null 2>&1; then
        SID_EXISTS=true
    fi
fi

# 2) Vérifier les fichiers init/spfile dans ORACLE_HOME (si oraenv a déjà tourné)
if [[ -d "${ORACLE_HOME:-}" ]]; then
    if [[ -f "${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora" ]] ||
       [[ -f "${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora" ]]; then
        SID_EXISTS=true
    fi
fi

# 3) Vérifier un processus PMON pour ce SID
if ps -ef | grep -v grep | grep -q "[p]mon_${ORACLE_SID}"; then
    SID_EXISTS=true
fi

# Si aucun test ne passe → SID invalide
if [[ "${SID_EXISTS}" = false ]]; then
    log_error "Le SID '${ORACLE_SID}' n'existe pas sur ce serveur. Vérifiez l'orthographe."
    exit 99
fi

# -------- Load Oracle environment --------
if ! command -v oraenv >/dev/null 2>&1; then
  [[ -f /usr/local/bin/oraenv ]] && PATH="/usr/local/bin:$PATH"
  [[ -f /usr/bin/oraenv ]]       && PATH="/usr/bin:$PATH"
fi

export ORACLE_SID
export ORAENV_ASK=NO
if ! . oraenv >/dev/null 2>&1; then
  log_error "Impossible de charger l'environnement Oracle pour SID=${ORACLE_SID}"
  exit 3
fi

if [[ ! -x "${ORACLE_HOME}/bin/sqlplus" ]]; then
  log_error "sqlplus introuvable. ORACLE_HOME=${ORACLE_HOME:-undefined}"
  exit 3
fi

# -------- Mail sender --------
if command -v mailx >/dev/null 2>&1; then
  MAIL_BIN="mailx"
elif command -v mail >/dev/null 2>&1; then
  MAIL_BIN="mail"
else
  MAIL_BIN=""
fi

send_mail() {
  local subject="$1"
  local body="$2"

  if [[ -n "$MAIL_BIN" ]]; then
    printf "%b" "$body" | ${MAIL_BIN} -r "$MAIL_FROM" -s "$subject" $(echo "$RECIPIENTS" | tr ',' ' ')
  else
    log_error "Aucun mailer installé."
    return 1
  fi
}

# -------- SQL Query --------
SQL_OUTPUT="$("${ORACLE_HOME}/bin/sqlplus" -s "/ as sysdba" <<EOF
set heading off feedback off pages 0 verify off echo off trimspool on
SELECT
  NVL((SELECT MAX(SEQUENCE#) FROM v$archived_log WHERE DEST_ID=${DEST_PRIMARY} AND ARCHIVED='YES'),0)
  || ','
  || NVL((SELECT MAX(SEQUENCE#) FROM v$archived_log WHERE DEST_ID=${DEST_STANDBY} AND APPLIED='YES'),0)
FROM dual;
EOF
)"

SQL_OUTPUT="$(echo "$SQL_OUTPUT" | tr -d '[:space:]')"

if ! echo "$SQL_OUTPUT" | grep -q ','; then
  log_error "Résultat SQL inattendu: '$SQL_OUTPUT'"
  exit 4
fi

PRIMARY_SEQ="${SQL_OUTPUT%%,*}"
STANDBY_SEQ="${SQL_OUTPUT##*,}"

# Validate numbers
[[ "$PRIMARY_SEQ" =~ ^[0-9]+$ ]] || { log_error "PRIMARY_SEQ invalide"; exit 4; }
[[ "$STANDBY_SEQ" =~ ^[0-9]+$ ]] || STANDBY_SEQ=0

LAG=$(( PRIMARY_SEQ - STANDBY_SEQ ))
(( LAG < 0 )) && LAG=0

TS="$(date '+%Y-%m-%d %H:%M:%S %Z')"

log_info "SID=${ORACLE_SID} Host=${HOSTNAME_FQDN} Primary=${PRIMARY_SEQ} Standby=${STANDBY_SEQ} Lag=${LAG} Seuil=${THRESHOLD}"

# -------- Trigger alert --------
if (( LAG >= THRESHOLD )); then
  SUBJECT="${MAIL_SUBJECT_PREFIX} ${ORACLE_SID} — Lag=${LAG}"

  BODY=$(cat <<EOT
Alerte Data Guard
-----------------
Hôte     : ${HOSTNAME_FQDN}
SID      : ${ORACLE_SID}
Horodatage : ${TS}

Dernier archivé : ${PRIMARY_SEQ}
Dernier appliqué : ${STANDBY_SEQ}
LAG actuel       : ${LAG}
Seuil            : ${THRESHOLD}

EOT
)

  if send_mail "$SUBJECT" "$BODY"; then
    log_warn "Alerte envoyée : FROM=${MAIL_FROM} TO=${RECIPIENTS}"
  else
    log_error "Erreur envoi mail"
    exit 5
  fi
fi

exit 0