Oracle Dba Capacity Planning Reporting

Oracle DBA - Capacity Planning & Reporting

Read-only operational + capacity reporting (status, space, resource, SLA, growth forecast, capacity recommendation, chargeback) and the one additive metrics-capture job that gives forecasting long-term history — for Oracle 19c and 23ai (EE; single-instance and Data Guard; on-prem). Everything self-executing is T1 (observe) except the daily space-snapshot capture, which is T2 (reversible, additive, dry-run-default). All ACTING on a recommendation — provisioning storage/CPU/memory/sessions, and creating the optional repository tables (DDL) — is T3 and lives only in references/runbooks.md: it prints commands for a human and never self-executes. The reports recommend; humans (with an approval token) provision. "When in doubt, runbook."

Scope & risk map

Covers every task in section 18 ("Capacity Planning & Reporting") of the oracle_dba_task_level_map.md, plus the supporting capture job and the T3 repo / provisioning runbooks the L3 reports point at.

Preconditions

• SQLcl MCP connection dba_ai_conn is reachable and wallet-backed. Every sql/*.sql report, the capture, and all pre/postchecks run through it via connect_db / Connect-Db. Never SYS/SYSTEM — except inside a T3 runbook that explicitly states AS SYSDBA, supplied interactively by the operator (e.g. some provisioning steps). The connection user needs SELECT on the V$/GV$/DBA_*/CDB_* views the reports read (and, for the capture, INSERT/UPDATE on CPR_SPACE_SNAPSHOT).
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere — not in argv, env, files, or these scripts. An argument that looks like user/pass@db 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/capacity/capacity.log (Linux) / C:/ProgramData/oracle-dba/logs/capacity/capacity.log (Windows); skill segment capacity. Reports also print KEYVAL lines parsed into emit_metric events.
• Optional repositories (better signal, created once by a human, T3 DDL): CPR_SPACE_SNAPSHOT (+ CPR_SPACE_SNAPSHOT_SEQ) for long-term space history (#metrics-repo); CPR_RATE_CARD / CPR_CHARGEBACK_SNAPSHOT for a persisted chargeback rate card (#chargeback-repo). Every report degrades gracefully if they are absent — space/forecast fall back to AWR DBA_HIST_* (Diagnostics Pack), chargeback falls back to inline DEFINE rates — so the skill works out-of-the-box. AWR sections are wrapped and never crash if AWR is unlicensed.

Procedures

Reporting scripts ship as behaviour-equivalent .sh (Linux) / .ps1 (Windows) pairs. The seven reports are all T1 read-only and self-execute freely via the one run_report driver (it runs the report, prints it, and converts the report's KEYVAL lines into metrics + non-fatal threshold findings). The capture is the only T2 mechanism: --dry-run is the default; pass --execute to act. See each script's --help / -Help.

Daily / weekly status report (T1)

• Trigger: morning operational snapshot; ad-hoc "is this DB healthy / how full".
• Precheck: dba_ai_conn reachable (read-only probe). No mutation.
• Action: scripts/checks/run_report.sh --report status / .ps1 -Report status -> sql/status_report.sql (identity/role, uptime, top tablespaces, sev-1 alert-log errors, FRA, blocked sessions).
• Postcheck: n/a — emits KEYVAL metrics (uptime_days, max_ts_used_pct, fra_pct_used, sev1_errors_window, blocked_sessions) + threshold findings.
• Rollback note: none — read-only.

Space usage trend report (T1)

• Trigger: weekly space review; before/after a large load; investigating growth.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/run_report.sh --report space-trend / .ps1 -Report space-trend -> sql/space_trend_report.sql (alloc vs used vs autoextend ceiling per tablespace; DB total; per-tablespace GB/day slope from CPR_SPACE_SNAPSHOT else AWR).
• Postcheck: n/a — emits db_total_gb, max_ts_used_pct, per-tablespace trend KEYVALs.
• Rollback note: none — read-only. (Enable long-term trend via the capture + #metrics-repo.)

Resource utilization report (T1)

• Trigger: sizing review; "are we CPU/memory/session bound"; pre-consolidation.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/run_report.sh --report resource / .ps1 -Report resource -> sql/resource_utilization_report.sql (V$SYSMETRIC load, V$OSSTAT CPU/mem denominators, SGA/PGA, V$RESOURCE_LIMIT, AWR 24h trend).
• Postcheck: n/a — emits host_cpu_pct, avg_active_sessions, sessions_used_pct.
• Rollback note: none — read-only.

SLA / uptime report (T1)

• Trigger: monthly availability reporting; SLA review; post-incident.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/run_report.sh --report sla / .ps1 -Report sla -> sql/sla_uptime_report.sql (start/shutdown + ORA-600/7445 events, DG role transitions, MTTR-proxy availability vs target).
• Postcheck: n/a — emits availability_pct, sla_target_pct, sla_status (MET/BREACH).
• Rollback note: none — read-only. For audited availability wire a real outage feed via #sla-source.

Growth trend analysis & forecasting (T1, L3)

• Trigger: capacity-planning cycle; "when does this tablespace fill"; budgeting.
• Precheck: dba_ai_conn reachable; history present (repo preferred, else AWR; needs >= min_points).
• Action: scripts/checks/run_report.sh --report forecast / .ps1 -Report forecast -> sql/growth_forecast.sql (OLS slope / R² / projected GB / days-to-full per tablespace).
• Postcheck: n/a — emits forecast_source, per-tablespace fc_<ts>_slope_gb_day / _proj_gb / _days_to_full; the driver raises a finding when any days-to-full <= --warn-days-full.
• Rollback note: none — read-only. Run the T2 capture daily so the forecast has real long-term history rather than short AWR.

Capacity planning recommendation (T1 advice; provisioning is T3)

• Trigger: after a forecast/SLA review; quarterly capacity sign-off; pressure alert.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/run_report.sh --report recommendation / .ps1 -Report recommendation -> sql/capacity_recommendation.sql (prioritised CRIT/WARN list across SPACE / SESSIONS / FRA / MEMORY with a suggested action + runbook pointer). Advice only — it never provisions.
• Postcheck: n/a — emits recommendations count; non-fatal finding when > 0.
• Rollback note: none — read-only. Acting on a recommendation is T3 via #capacity-provision (add datafile / raise MAXSIZE / grow FRA / raise sessions / add CPU/mem/node) and requires an approval token.

Chargeback / showback reporting (T1, L3)

• Trigger: monthly cost allocation / showback; tenant cost review.
• Precheck: dba_ai_conn reachable.
• Action: scripts/checks/run_report.sh --report chargeback / .ps1 -Report chargeback -> sql/chargeback_report.sql (per-PDB or per-schema storage + compute cost using CPR_RATE_CARD else inline DEFINE rates).
• Postcheck: n/a — emits per-tenant + chargeback_total_month KEYVALs.
• Rollback note: none — read-only. Persist a versioned rate card + monthly history via #chargeback-repo.

Daily space-metrics capture (T2 — the only mutating mechanism)

• Trigger: scheduled daily (drives long-term forecasting); before/after a capacity event to pin a measurement.
• Precheck: dba_ai_conn reachable. The SQL itself probes for CPR_SPACE_SNAPSHOT and no-ops cleanly if absent (create it via #metrics-repo).
• Action: scripts/capture_space_snapshot.sh --execute / .ps1 --execute -> sql/capture_space_snapshot.sql (MERGE one alloc/used/max row per tablespace for today). Default (no flag) previews only.
• Postcheck: a row for TRUNC(SYSDATE) exists in CPR_SPACE_SNAPSHOT (skipped on the no-repo no-op).
• Rollback note: additive metadata only (one row/tablespace/day; MERGE-updated on re-run). To undo, DELETE the day's rows from the repo. No production data is touched. Creating/altering/dropping the repo table itself is T3 (#metrics-repo).

Tier-3 runbooks

All provisioning and all repository / structural DDL are Tier 3 and live in references/runbooks.md: #metrics-repo (create CPR_SPACE_SNAPSHOT + seq), #chargeback-repo (create CPR_RATE_CARD / CPR_CHARGEBACK_SNAPSHOT), #capacity-provision (add datafile / raise MAXSIZE / grow db_recovery_file_dest_size / raise sessions+processes / add CPU/memory/RAC node), and #sla-source (wire an authoritative CMDB/ITSM outage feed).

These runbooks PRINT exact SQL/RMAN/host 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 T1 reports deliberately recommend but never act, and the T2 capture deliberately refuses to create its own repo table (it no-ops and points at #metrics-repo). SYSDBA is a T3-only, interactively-supplied exception. When in doubt, runbook.

See references/version-notes.md for 19c-vs-23ai behavioural deltas.