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.

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).