# Oracle Dba Capacity Planning Reporting

> USE THIS SKILL WHENEVER the user asks about capacity planning, capacity/sizing reviews, growth trend analysis or forecasting, "days to full" / "when will this tablespace fill", space-usage trend reports, database/instance status reports (daily/weekly health snapshot), resource-utilization reports (CPU / memory / IO / sessions / AAS), SLA / uptime / availability reporting, chargeback or showback / cost-allocation reporting, or a capacity-planning recommendation — EVEN IF they do not name the specific report. Covers the read-only reporting family (status, space-trend, resource, SLA, growth forecast, capacity recommendation, chargeback) plus the single additive daily space-metrics capture that feeds long-term forecasting. Follows the oracle-dba-common contracts: SQLcl MCP saved connection dba_ai_conn, Oracle Wallet secrets (never plaintext), --dry-run default on the one T2 mechanism, and tier gating (max self-execute = T2; all PROVISIONING and repo/structural DDL is T3 runbook).

- Skill: `shreyas70773/oracle-dba-capacity-planning-reporting` (Agent Skill, multi-file: 18 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-capacity-planning-reporting`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-capacity-planning-reporting/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Marketing & Growth
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/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.

| Task | Level | Tier | Mechanism (sql / script / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Daily / weekly status report | L1 | T1 | `scripts/checks/run_report.sh --report status` / `.ps1 -Report status` -> `sql/status_report.sql` | yes — read-only (emits KEYVAL metrics + threshold findings, never mutates) |
| Space usage trend report | L2 | T1 | `scripts/checks/run_report.sh --report space-trend` / `.ps1 -Report space-trend` -> `sql/space_trend_report.sql` | yes — read-only; trend from `CPR_SPACE_SNAPSHOT` else AWR fallback |
| Resource utilization report | L2 | T1 | `scripts/checks/run_report.sh --report resource` / `.ps1 -Report resource` -> `sql/resource_utilization_report.sql` | yes — read-only; live V$ + AWR (graceful if AWR unlicensed) |
| SLA / uptime report | L2 | T1 | `scripts/checks/run_report.sh --report sla` / `.ps1 -Report sla` -> `sql/sla_uptime_report.sql` | yes — read-only; MTTR-proxy availability, DG role-transition aware |
| Growth trend analysis & forecasting | L3 | T1 | `scripts/checks/run_report.sh --report forecast` / `.ps1 -Report forecast` -> `sql/growth_forecast.sql` | yes — read-only OLS (slope/R²/days-to-full); repo else AWR |
| Capacity planning recommendation | L3 | T1 | `scripts/checks/run_report.sh --report recommendation` / `.ps1 -Report recommendation` -> `sql/capacity_recommendation.sql` | yes — read-only ADVICE ONLY; never provisions |
| Chargeback / showback reporting | L3 | T1 | `scripts/checks/run_report.sh --report chargeback` / `.ps1 -Report chargeback` -> `sql/chargeback_report.sql` | yes — read-only; rate card from `CPR_RATE_CARD` else inline DEFINEs |
| Daily space-metrics capture (forecast feed) | L3 (support) | T2 | `scripts/capture_space_snapshot.sh` / `.ps1` -> `sql/capture_space_snapshot.sql` | yes — additive MERGE on `(snap_day, tablespace_name)`; re-run updates today's rows; no-op if repo absent |
| Metrics repository setup (`CPR_SPACE_SNAPSHOT` + seq DDL) | L3 | **T3** | `references/runbooks.md#metrics-repo` | n/a — structural DDL; human-run, refused by capture (which no-ops) |
| Chargeback repository setup (`CPR_RATE_CARD` / `CPR_CHARGEBACK_SNAPSHOT` DDL) | L3 | **T3** | `references/runbooks.md#chargeback-repo` | n/a — structural DDL; human-run |
| Capacity provisioning (add datafile / resize FRA / raise sessions / add CPU/mem/node) | L3 | **T3** | `references/runbooks.md#capacity-provision` | no — structural/resource change; not trivially reversible |
| Authoritative SLA source wiring (CMDB / ITSM outage feed) | L3 | **T3** | `references/runbooks.md#sla-source` | n/a — design/integration; human-driven |

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