# Oracle Dba Monitoring Alerting

> USE THIS SKILL WHENEVER the user mentions database monitoring, alerting, health checks, up/down or availability monitoring, listener status, alert log errors, tablespace / temp / undo / ASM / FRA space usage, archivelog generation rate, session or connection counts, blocking locks, long-running queries, datafile/tablespace offline status, invalid objects, failed logins, scheduler or cron job failures, backup success/failure monitoring, Data Guard transport/apply lag, RAC node/instance monitoring, host CPU/memory/IO, alert acknowledgment / ticket creation, custom metric or threshold tuning, OEM agent deployment, or monitoring template / metric-extension setup — EVEN IF they do not name the specific check. Also fires on ORA-19809/ORA-19804 (FRA full), ORA-00257 (archiver stuck), "MRP0 not running", "apply lag", "TNS-12541/12514 listener", "node eviction", or "tablespace 9x% full". Every self-executing check is read-only T1 (observe); threshold tuning, OEM agent deploy, metric-extension setup and ITSM ticketing

- Skill: `shreyas70773/oracle-dba-monitoring-alerting` (Agent Skill, multi-file: 72 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-monitoring-alerting`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-monitoring-alerting/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-monitoring-alerting

---

# Oracle DBA - Monitoring & Alerting

Read-only health/availability/space/lag monitors plus the small set of reversible
configuration tasks that stand monitoring up (threshold tuning, OEM agent deploy,
metric-extension setup, ITSM ticketing) for Oracle 19c and 23ai (EE; single_instance
and Data Guard; on-prem). **Everything self-executing is T1 (observe)** — the checks
emit machine-parseable `KEYVAL`/metric lines and threshold *findings*, they never
mutate the database — **or T2 (reversible, dry-run-default).** Every deep diagnosis
and remediation (role transition, FRA reclaim, listener/RAC restart) is **T3** and
lives only in `references/runbooks.md`; it prints commands for a human and never
self-executes.

In-DB work routes through the SQLcl MCP saved connection `dba_ai_conn` (`sqlcl-mcp`);
each check's host wrapper (`.sh`/`.ps1`) is the scheduler/alerting shell around its
`sql/<check>.sql`. Host-only collectors (alert log via ADRCI, listener via `lsnrctl`,
RAC via `crsctl`/`srvctl`, host CPU/mem/IO) cannot go through SQLcl MCP and do the
work in the script (`script`).

## Scope & risk map

Skill log segment = `monitoring` (T3 runbooks log under `monitoring` too).
Idempotent? — all checks are read-only (re-runnable by nature); T2 setters detect
"already at desired" and no-op.

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Database up/down monitoring | L1 | T1 | `scripts/checks/check_db_instance.sh`/`.ps1` + `sql/db_instance_status.sql` | yes — read-only |
| Instance availability check | L1 | T1 | `scripts/checks/check_db_instance.sh`/`.ps1` + `sql/db_instance_status.sql` (`instance_available`) | yes — read-only (exits non-zero on real outage) |
| Listener status monitoring | L1 | T1 | `scripts/checks/check_listener.sh`/`.ps1` (`lsnrctl`/`tnsping`; `srvctl` on RAC) | yes — read-only |
| Alert log error monitoring | L1 | T1 | `scripts/checks/check_alert_log.sh`/`.ps1` (ADRCI, fallback `V$DIAG_ALERT_EXT`) | yes — read-only; rolling-offset state file |
| Tablespace space usage monitoring | L1 | T1 | `scripts/checks/check_tablespace_usage.sh`/`.ps1` + `sql/tablespace_usage.sql` | yes — read-only |
| Temp tablespace usage monitoring | L1 | T1 | `scripts/checks/check_temp_undo_usage.sh`/`.ps1` + `sql/temp_undo_usage.sql` | yes — read-only |
| Undo tablespace usage monitoring | L1 | T1 | `scripts/checks/check_temp_undo_usage.sh`/`.ps1` + `sql/temp_undo_usage.sql` | yes — read-only |
| ASM disk group space monitoring | L1 | T1 | `scripts/checks/check_asm_diskgroup.sh`/`.ps1` + `sql/asm_diskgroup_usage.sql` | yes — read-only |
| FRA usage monitoring | L1 | T1 | `scripts/checks/check_fra_usage.sh`/`.ps1` + `sql/fra_usage.sql` | yes — read-only (escalates to `#fra-full`) |
| Archive log generation rate monitoring | L1 | T1 | `scripts/checks/check_archivelog_rate.sh`/`.ps1` + `sql/archivelog_rate.sql` | yes — read-only |
| Session / connection count monitoring | L1 | T1 | `scripts/checks/check_session_count.sh`/`.ps1` + `sql/session_count.sql` | yes — read-only |
| Blocking lock monitoring | L1 | T1 | `scripts/checks/check_blocking_locks.sh`/`.ps1` + `sql/blocking_locks.sql` | yes — read-only |
| Long-running query monitoring | L1 | T1 | `scripts/checks/check_long_running_queries.sh`/`.ps1` + `sql/long_running_queries.sql` | yes — read-only |
| Datafile / tablespace offline status monitoring | L1 | T1 | `scripts/checks/check_datafile_status.sh`/`.ps1` + `sql/datafile_tablespace_status.sql` | yes — read-only |
| Invalid object monitoring | L1 | T1 | `scripts/checks/check_invalid_objects.sh`/`.ps1` + `sql/invalid_objects.sql` | yes — read-only |
| Failed login attempt monitoring | L1 | T1 | `scripts/checks/check_failed_logins.sh`/`.ps1` + `sql/failed_logins.sql` (unified + traditional fallback) | yes — read-only |
| Scheduler / cron job failure monitoring | L1 | T1 | `scripts/checks/check_scheduler_jobs.sh`/`.ps1` + `sql/scheduler_job_failures.sql` | yes — read-only |
| Backup success/failure monitoring | L1 | T1 | `scripts/checks/check_backup_health.sh`/`.ps1` + `sql/backup_health.sql` | yes — read-only (see backup-and-recovery for deeper) |
| Data Guard transport/apply lag monitoring | L1 | T1 | `scripts/checks/check_dataguard_lag.sh`/`.ps1` + `sql/dataguard_lag.sql` | yes — read-only (escalates to `#dg-lag-deep`) |
| RAC node & instance monitoring | L1 | T1 | `scripts/checks/check_rac_instance.sh`/`.ps1` + `sql/rac_instance_status.sql`; `scripts/checks/rac_monitor.sh`/`.ps1` (`crsctl`/`srvctl`) | yes — read-only (escalates to `#rac-node-recovery`) |
| Host CPU / memory / IO monitoring | L1 | T1 | `scripts/checks/check_host_resources.sh`/`.ps1` (host counters; no SQLcl) | yes — read-only |
| Alert acknowledgment & ticket creation | L1 | T2 | `scripts/raise_ticket.sh`/`.ps1` (`$ODB_ITSM_HOOK`) | yes — dedupe ledger; re-run within window ACKs, never duplicates |
| Custom metric / threshold tuning | L2 | T2 | `scripts/tune_thresholds.sh`/`.ps1` + `config/thresholds.conf` | yes — setting a key to its current value is a noop; timestamped `.bak` |
| OEM agent deployment & configuration | L2 | T2 | `scripts/oem_agent_deploy.sh`/`.ps1` (`emcli`) | yes — agent already deployed+UP -> noop (lifecycle owned by enterprise-manager-oem-tooling) |
| Monitoring template / metric extension setup | L2 | T2 | `scripts/monitoring_template_setup.sh`/`.ps1` + `sql/server_alert_thresholds.sql` (read) + `sql/set_server_alert_threshold.sql` (set) | yes — metric already at desired warn/crit -> noop; reversible |
| Data Guard lag deep-dive & role transition (switchover/failover) | deep | **T3** | `references/runbooks.md#dg-lag-deep` | no — switchover/failover not trivially reversible |
| FRA / recovery area full remediation | deep | **T3** | `references/runbooks.md#fra-full` | partial — grow is reversible; deletes are not |
| Listener down / not serving remediation | deep | **T3** | `references/runbooks.md#listener-restart` | partial — restart is self-contained; config edits need revert |
| RAC node / instance down remediation | deep | **T3** | `references/runbooks.md#rac-node-recovery` | partial — instance restart reversible; clusterware/eviction is not |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** is reachable and wallet-backed. All in-DB checks,
  prechecks, the metric-extension report/setter and idempotency probes run through it via
  `connect_db` / `Connect-Db`. **Never SYS/SYSTEM** — except inside a T3 runbook that
  explicitly states `AS SYSDBA` (e.g. `ALTER DATABASE RECOVER MANAGED STANDBY ...`),
  supplied interactively by the operator.
- **Secrets resolve from the Oracle Wallet / OS authentication. No plaintext anywhere.**
  No script accepts a credential on argv; `lsnrctl`/`crsctl`/`srvctl`/`adrci` use OS auth,
  `emcli` uses its own login / OEM **named credential**, the ITSM hook resolves its own
  token. Any credential-shaped argument is rejected with `ERR_SECRET` (8).
- **Helpers are sourced from `_common`** (`scripts/lib.sh` / `lib.ps1`): `connect_db`,
  `log_event`, `emit_metric`, `require_dry_run`, `guard_blocked_dry_run`,
  `require_approval_token`, `precheck`, `postcheck`, `print_banner`. Not reinvented.
- **Logging**: structured `key=value` lines to the canonical paths
  `/var/log/oracle-dba/monitoring/monitoring.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/monitoring/monitoring.log` (Windows). Every check emits
  `KEYVAL <metric>=<value>` (parsed into `emit_metric` events) plus `finding=...` events.
- **Thresholds**: defaults live in `config/thresholds.conf` (plaintext THRESHOLDS ONLY,
  never credentials). A check's CLI flag overrides the file per-invocation. Tune the file
  via `scripts/tune_thresholds.*` (T2, dry-run default).
- **T1 vs T2 behaviour**: checks self-execute freely and exit `0` even with findings
  (monitoring surfaces, it does not crash) — except availability, which exits non-zero on a
  real outage so a pager fires. T2 scripts preview and change nothing unless given `--execute`
  (`.ps1`: `-Execute`).

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. Checks are
T1 and self-execute; T2 scripts default to `--dry-run` (`-Execute` on Windows). See each
script's `--help` / `-Help`. Common pattern for every check:

- **Trigger**: scheduled cadence (see `cron/`) or ad-hoc when investigating an incident.
- **Precheck**: `dba_ai_conn` reachable (read-only `SELECT 1 FROM dual;`) for in-DB checks;
  tool present (`adrci`/`lsnrctl`/`crsctl`/`emcli`) for host checks.
- **Action**: run `scripts/checks/<check>.sh` / `.ps1` (link in the risk map).
- **Postcheck**: n/a for read-only checks — they emit `KEYVAL` metrics + `finding=` events.
- **Rollback note**: none — read-only. A finding escalates to the matching T3 runbook.

### Availability & connectivity (DB up/down, instance, listener)
- `check_db_instance.*` reports `V$INSTANCE`/`V$DATABASE`/`V$PDBS`; `instance_available=0`
  or an unreachable `dba_ai_conn` exits `EX_PRECHECK` (3) — the pager signal.
- `check_listener.*` runs `lsnrctl status`/`tnsping` (single-instance) or `srvctl status
  listener/scan_listener` (RAC); `listener_down` / no `READY` handlers escalate to
  `references/runbooks.md#listener-restart`.

### Space & capacity (tablespace, temp/undo, ASM, FRA, archivelog rate)
- `check_tablespace_usage.*`, `check_temp_undo_usage.*`, `check_asm_diskgroup.*`,
  `check_fra_usage.*`, `check_archivelog_rate.*` compare usage vs `config/thresholds.conf`
  (`*_warn`/`*_crit`). FRA at/over critical escalates to `#fra-full` (ORA-19809/19804 risk).

### Concurrency & objects (sessions, blocking, longops, datafile, invalid, logins, jobs)
- `check_session_count.*` (`V$RESOURCE_LIMIT`), `check_blocking_locks.*`
  (`BLOCKING_SESSION`/`GV$LOCK`), `check_long_running_queries.*` (`GV$SESSION_LONGOPS`),
  `check_datafile_status.*` (offline/recover datafiles), `check_invalid_objects.*`
  (`DBA_OBJECTS`), `check_failed_logins.*` (`UNIFIED_AUDIT_TRAIL` + traditional fallback),
  `check_scheduler_jobs.*` (`*_JOB_RUN_DETAILS`). All read-only findings.

### Replication & HA (backup, Data Guard, RAC, host)
- `check_backup_health.*` surfaces stale/failed RMAN jobs (deep work -> backup-and-recovery).
- `check_dataguard_lag.*` parses `V$DATAGUARD_STATS`/`V$MANAGED_STANDBY`; `apply_lag_high`,
  `transport_lag_high`, `mrp_not_running` escalate to `#dg-lag-deep`.
- `check_rac_instance.*` (`GV$INSTANCE`, lost-block events) + `rac_monitor.*`
  (`crsctl status resource -t`, `srvctl status`); OFFLINE/down escalates to `#rac-node-recovery`.
- `check_host_resources.*` collects CPU/mem/IO from host counters (no SQLcl).

### Alert acknowledgment & ticketing (T2)
- **Trigger**: a check produced a `finding=` worth a ticket.
- **Action**: `scripts/raise_ticket.sh --key <stable-key> --summary "..." [--severity ...]
  --execute` / `.ps1`. Dry-run previews the ITSM call. Idempotent dedupe ledger: a re-run
  for the same key within `--dedupe-mins` ACKs/updates rather than opening a duplicate.
- **Rollback note**: a ticket can be closed/cancelled via the hook; opening is low-blast-radius.

### Threshold tuning (T2)
- **Trigger**: a metric's warn/crit needs adjusting after baselining (the *judgement* is human).
- **Action**: `scripts/tune_thresholds.sh --key <k> --value <n> --execute` / `.ps1 -Key -Value
  -Execute`. Dry-run previews; `--execute` writes `config/thresholds.conf` and keeps a `.bak`.
- **Postcheck**: file now holds the new value. **Rollback note**: re-run with the old value, or
  restore the timestamped `.bak`.

### OEM agent deployment (T2)
- **Trigger**: bring a new host under OEM monitoring.
- **Action**: `scripts/oem_agent_deploy.sh --host <h> [--cred-name <named>] --execute` / `.ps1
  -OemHost -CredName -Execute`. Dry-run previews the `emcli submit_add_host`. Idempotent: an
  agent already deployed+UP is a noop. **Rollback note**: `emcli delete_target`. Broad OEM
  lifecycle is owned by the **enterprise-manager-oem-tooling** skill.

### Monitoring template / metric extension setup (T2)
- **Trigger**: define the DB's own metric thresholds (Server-Generated Alerts via
  `DBMS_SERVER_ALERT`) — e.g. per-tablespace `TABLESPACE_PCT_FULL` warn/crit.
- **Precheck**: `dba_ai_conn` reachable. `--list` reports current `DBA_THRESHOLDS` +
  `DBA_OUTSTANDING_ALERTS` (read-only).
- **Action**: `scripts/monitoring_template_setup.sh --metric-id 9 --object-type 7
  --object-name USERS --warn 85 --crit 92 --execute` / `.ps1`. Dry-run previews; `--execute`
  runs `sql/set_server_alert_threshold.sql` (idempotent: already-at-desired -> noop).
- **Postcheck**: threshold present in `DBA_THRESHOLDS`. **Rollback note**: re-run with the
  prior warn/crit (or empty to clear). `--emcli-template` only PREVIEWS an OEM template apply
  and delegates to enterprise-manager-oem-tooling.

## Tier-3 runbooks

All **deep diagnosis and remediation** is Tier 3 and lives in **`references/runbooks.md`**:
`#dg-lag-deep` (Data Guard apply/transport lag deep-dive, MRP restart, gap resolution,
switchover/failover with broker + manual-SQL forms), `#fra-full` (FRA/recovery-area full
remediation — grow / back-up-and-reclaim / delete-within-retention), `#listener-restart`
(listener down / not-serving, single-instance `lsnrctl` and RAC `srvctl` paths), and
`#rac-node-recovery` (RAC node/instance down, instance restart vs clusterware restart/eviction).

Each runbook **PRINTS exact RMAN/SQL/`dgmgrl`/`crsctl`/`srvctl`/`lsnrctl` commands for a
human operator and NEVER self-executes.** They are gated by `require_approval_token` /
`Require-ApprovalToken`: an automated agent's only sanctioned action is to print the relevant
section and stop with `ERR_APPROVAL` (6) until a human supplies a real change ticket/token
(`--token <TICKET>` or `ODB_APPROVAL_TOKEN`; placeholders are rejected). Read-only diagnosis
inside a runbook is safe to run; the **role transitions, deletions, and clusterware restarts
are the gated, destructive steps**. SYSDBA is a T3-only, interactively-supplied exception.
When in doubt, runbook.

See `references/version-notes.md` for 19c-vs-23ai behavioural deltas (non-CDB desupport &
`V$PDBS`, pure-unified audit & `UNIFIED_AUDIT_TRAIL`, scheduler/DG/RAC view stability,
ADRCI/`V$DIAG_ALERT_EXT` alert-log access).