Oracle Dba Incident Problem Management

Oracle DBA - Incident & Problem Management

First-line incident response (triage, acknowledge, escalate, restart a listener), operational diagnosis (ORA-error, hang/deadlock, AHF/TFA collection, SR logging, incident investigation, KEDB docs), and the plan-only Tier-3 runbooks (instance / clusterware restart, kill / hanganalyze / systemstate, RCA, ORA-00600/07445 internal errors, working an Oracle SR, performance war-room) for Oracle 19c and 23ai (EE; single-instance and Data Guard; on-prem). Everything self-executing is T1 (observe, read-only diagnosis) or T2 (reversible ledger/doc/collection writes, dry-run default). Every destructive / outage-causing / deep-judgement action is T3 and lives only in references/runbooks.md — it prints commands for a human and never self-executes.

Scope & risk map

Skill log segment is incident for all tasks (T3 runbook steps log under incident too). Canonical logs: /var/log/oracle-dba/incident/incident.log (Linux) / C:/ProgramData/oracle-dba/logs/incident/incident.log (Windows). The incident ledger (incident_ledger.tsv), SR register/drafts and AHF repo default under that dir (override via ODB_INCIDENT_LEDGER, ODB_SR_DIR, ODB_AHF_REPO, ODB_KEDB_FILE).

Preconditions

• SQLcl MCP connection dba_ai_conn is reachable and wallet-backed. All in-DB prechecks, triage/diagnosis/investigation reports, idempotency probes, and sql/*.sql run through it via connect_db / Connect-Db. Never SYS/SYSTEM — except inside a T3 runbook that explicitly states AS SYSDBA (instance restart, some KILL configs, oradebug), supplied interactively by the operator.
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere. No script accepts credentials on argv; a credential-looking argument is rejected with ERR_SECRET (8). My-Oracle-Support CSI/SSO live in a site SR hook (ODB_SR_HOOK) or are entered interactively — never in a file here. Host ops (lsnrctl/srvctl/ahf/tfactl/adrci/opatch) use OS/wallet auth only.
• 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.
• Routing (see _common/references/INTEGRATION-PLAYBOOK.md): in-DB SQL/PL-SQL goes to sql/*.sql via SQLcl MCP run-sql on dba_ai_conn ("sqlcl-mcp"). Host ops (AHF/TFA, adrci, listener/instance/clusterware restart, OPatch, kill at OS level) cannot go through SQLcl MCP (restrict level 4) → .sh/.ps1 ("script", to later front a guarded oracle-dba-ops MCP). T3 → runbook + approval token.
• T2 dry-run default: every T2 script (incident_triage ack, escalate_incident, service_restart, collect_diagnostics, sr_log, kedb_document) previews and changes nothing unless given --execute.

Procedures

Scripts ship as behaviour-equivalent .sh (Linux) / .ps1 (Windows) pairs. T2 scripts default to --dry-run; pass --execute to act. See each script's --help.

Incident triage + acknowledgment (L1, T2)

• Trigger: a new alert/incident lands; you need the situational snapshot + to record pick-up.
• Precheck: dba_ai_conn reachable (read-only SELECT 1).
• Action: scripts/incident_triage.sh --incident INC0012345 [--source alert] --execute / .ps1. The read-only triage report (sql/incident_triage.sql) ALWAYS runs and emits a severity_hint (SEV1 internal error/instance-not-OPEN, SEV2 blocking, else SEV3).
• Postcheck: ack line present in the incident ledger.
• Rollback note: remove the ack line to un-acknowledge; re-running today no-ops.

Escalation per escalation matrix (L1, T2)

• Trigger: severity warrants paging the next tier (often from the triage severity_hint).
• Precheck: valid incident id + severity (SEV1–SEV4).
• Action: scripts/escalate_incident.sh --incident INC0012345 --severity SEV1 --execute / .ps1. Matrix is env-overridable (ODB_ESC_SEV1..4); optional site notification via ODB_ESCALATE_HOOK.
• Postcheck: escalation line present in the ledger.
• Rollback note: remove the line to retract; same incident+tier today no-ops.

Service restart per runbook (L1, T2 listener only)

• Trigger: a listener is down/wedged and a restart is the runbook remedy.
• Precheck: valid listener name; --only-if-down skips if already up.
• Action: scripts/service_restart.sh --target listener [--name LISTENER] [--only-if-down] --execute / .ps1 (auto-detects srvctl vs lsnrctl). --target instance|database|clusterware|crs|asm is REFUSED → references/runbooks.md#service-restart.
• Postcheck: listener running after restart.
• Rollback note: a restart returns the listener to running; re-run to repeat. Instance/clusterware restart is T3.

ORA-error diagnosis + KEDB lookup (L2, T1)

• Trigger: an ORA- error needs identifying / mapping to a known cause + action.
• Precheck: dba_ai_conn reachable.
• Action: scripts/ora_error_diagnose.sh --code 01555 / .ps1 (digits only; omit for a broad internal/critical scan). Surfaces occurrences in V$DIAG_ALERT_EXT/V$DIAG_INCIDENT, prints the matching references/kedb.md entry, and flags ORA-00600/07445 → references/runbooks.md#ora-600-7445.
• Postcheck: n/a — read-only; emits ora_occurrences_30d + findings.
• Rollback note: none — read-only.

Hang / deadlock analysis (L2, T1)

• Trigger: blocking-lock monitoring fires, or sessions report hanging.
• Precheck: dba_ai_conn reachable.
• Action: scripts/hang_deadlock_analyze.sh [--block-secs 60] / .ps1 + sql/hang_deadlock_analyze.sql (blocking tree, lock waits, ORA-00060 history, hang-suspect heuristic). It NEVER kills or dumps → resolution is references/runbooks.md#hang-deadlock.
• Postcheck: n/a — read-only; emits blocked_sessions/longest_block_secs/deadlocks_7d + findings.
• Rollback note: none — read-only.

Diagnostic data collection (AHF / TFA) (L2, T2)

• Trigger: an SR/RCA needs a packaged diagnostic bundle (alert log + traces + incident).
• Precheck: ahf or tfactl on PATH.
• Action: scripts/collect_diagnostics.sh --incident INC0012345 [--since 4h] [--components DB,ASM,CRS] --execute / .ps1 (host op; OS auth). Idempotent: a zip tagged for this incident today -> noop.
• Postcheck: collection zip present in the repo.
• Rollback note: delete the output zip; re-running today no-ops.

Oracle Service Request (SR) logging (L2, T2)

• Trigger: a problem needs an Oracle SR; assemble the package.
• Precheck: dba_ai_conn reachable (for config facts).
• Action: scripts/sr_log.sh --incident INC0012345 --severity 2 --summary "ORA-00600 on insert" [--diag <zip>] --execute / .ps1. Builds an SR draft (problem statement + sql/sr_config_facts.sql facts) and registers it; MOS submission left to ODB_SR_HOOK / a human (no CSI/SSO in files).
• Postcheck: SR draft + register entry present.
• Rollback note: delete the draft + register line; re-running today no-ops. Working the SR to resolution is T3 (#working-sr).

Incident investigation (L2, T1)

• Trigger: build the timeline for an investigation / RCA across a window.
• Precheck: dba_ai_conn reachable.
• Action: scripts/incident_investigation.sh [--window-hours 6] [--incident INC0012345] / .ps1 + sql/incident_investigation.sql (alert-log timeline, ADR incidents, ASH top waits/SQL, changed params). ASH needs Diagnostics Pack.
• Postcheck: n/a — read-only; emits alert_events_in_window/incidents_in_window.
• Rollback note: none — read-only. RCA writeup is T3 (#rca).

Known Error DB (KEDB) documentation (L2, T2)

• Trigger: a closed problem should be recorded for next time.
• Precheck: valid ORA code + required fields.
• Action: scripts/kedb_document.sh --code 01555 --title "..." --cause "..." --action "..." [--sr <id>] [--update] --execute / .ps1 → appends/replaces a references/kedb.md anchor block that ora_error_diagnose later prints.
• Postcheck: KEDB entry present (anchor ora-<code>).
• Rollback note: revert the file (git/file); existing entry without --update no-ops.

Tier-3 tasks (RCA, internal-error analysis, working an SR, war-room, instance restart, hang resolution)

• Trigger: a destructive / outage-causing / deep-judgement action is required.
• Precheck: universal pre-flight in references/runbooks.md + read-only diagnosis (the L1/L2 scripts above) first.
• Action: runbook-only — print the relevant references/runbooks.md#… section; gated by require_approval_token. No .sh/.ps1 performs these.
• Postcheck / rollback: defined per section in the runbook (verification + rollback/abort).

Tier-3 runbooks

All destructive / outage-causing / deep-judgement work is Tier 3 and lives in references/runbooks.md: #service-restart (instance/clusterware/CRS-service restart), #hang-deadlock (kill session / oradebug hanganalyze / systemstate), #rca (root cause analysis method + writeup), #ora-600-7445 (internal-error ADR packaging, MOS lookup, patch decision), #working-sr (SR lifecycle + one-off patch / workaround), #war-room (SEV1 live triage with decision branches).

These runbooks PRINT exact commands for a human operator and NEVER self-execute. 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). The L1/L2 scripts deliberately refuse the destructive escalations (service_restart refuses instance/clusterware; hang_deadlock_analyze never kills/dumps; sr_log never auto-submits to MOS) and point to the runbook instead. SYSDBA is a T3-only, interactively-supplied exception. Corruption-class incidents cross to the backup-and-recovery skill (#block-recover/#media-recover); switchover/failover to the HA / Data Guard skill; patch mechanics to the patching-and-upgrades skill. When in doubt, runbook.

See references/version-notes.md for 19c-vs-23ai behavioural deltas and references/kedb.md for the seeded Known Error DB.