oracle-dba-agentic-skills

<div align="center"> # 🛢️ Oracle DBA — Agentic Skills ### A safety-first toolkit that lets an AI assistant *understand* **and** *operate* Oracle Database — natively, on the machine where the database lives.        *Two layers, one library: **Oracle's official knowledge** (the brain) + **tier-gated executable automation** (the hands) — bridged by a safety harness and two MCP servers.* </div> --- ## Table of contents - [The 60-second version](#the-60-second-version) - [Why this exists](#why-this-exists) - [The mental model: brain + hands](#the-mental-model-brain--hands) - [The autonomy model (read this part)](#the-autonomy-model-read-this-part) - [How it runs natively on a database VM](#how-it-runs-natively-on-a-database-vm) - [What's inside](#whats-inside) - [The 24 action domains](#the-24-action-domains) - [The knowledge base](#the-knowledge-base) - [The `oracle-dba-ops` MCP server](#the-oracle-dba-ops-mcp-server) - [Quick start](#quick-start) - [Usage walkthroughs](#usage-walkthroughs) - [Anatomy of an action skill](#anatomy-of-an-action-skill) - [Safety & governance](#safety--governance) - [Validation & testing](#validation--testing) - [Repository layout](#repository-layout) - [Version & compatibility notes](#version--compatibility-notes) - [Design decisions (FAQ)](#design-decisions-faq) - [Roadmap](#roadmap) - [Contributing](#contributing) - [License, attribution & trademarks](#license-attribution--trademarks) --- ## The 60-second version This repository turns an AI coding assistant (built for **Claude Code**, usable by any MCP-capable client) into a **disciplined junior-to-senior Oracle DBA** that can work across **24 operational domains** — backup & recovery, Data Guard, RAC, performance, security, patching, multitenant, and more — on **Oracle Database 19c and 23ai/26ai**, on **Linux and Windows**. It does this by combining three things that are usually missing from "AI + database" demos: 1. **Real, source-backed knowledge.** 165 Oracle guidance documents (vendored from Oracle's official [`oracle/skills`](https://github.com/oracle/skills) repository under UPL-1.0) so the agent *reasons correctly* before it touches anything — including Oracle's own **agent-safety patterns** (pre-flight checks, idempotency, `DBMS_ASSERT`). 2. **Executable, validated automation.** 210 Bash + 210 PowerShell scripts, 249 SQL files, and deep recovery runbooks — every one of them classified by **risk tier** and wired into a shared safety harness. 3. **A governance model that is enforced, not suggested.** Read-only work runs freely; reversible work runs *dry-run-first*; destructive/recovery work **never** auto-executes — it requires a human approval token and falls back to a printed runbook. > **One sentence:** *the knowledge layer tells the agent **how** and **why**, the action layer **does** it, and the tier policy makes sure the agent can never quietly do something it shouldn't.* --- ## Why this exists Most "connect an LLM to your database" integrations stop at *"here's a SQL tool, good luck."* That is fine for a read-only analytics sandbox. It is **not** fine for real database administration, for three reasons: - **DBA work lives outside the database.** Backups (`rman`), Data Guard role transitions (`dgmgrl`), cluster services (`srvctl`), Data Pump (`expdp`/`impdp`), diagnostics (`adrci`, AHF/TFA), patching, and recovery are **host commands** — and Oracle's own in-database MCP server deliberately *cannot* run them. - **The blast radius is enormous.** A careless `DROP TABLE ... PURGE`, a `TRUNCATE`, an `UNTIL TIME` recovery, or a `FAILOVER` can be unrecoverable. An agent that treats these like any other SQL is a liability. - **Judgment matters before action.** The difference between a good DBA and a dangerous one is the pre-flight thinking: *what depends on this object? is this reversible? is there a backup? who loses access?* That reasoning has to be available to the agent and applied **before** it acts. This library encodes all three concerns into structure rather than hoping the model "just knows": | Problem | How this library answers it | |---|---| | Host operations are out of reach for in-DB tools | A **custom guarded MCP server** (`oracle-dba-ops`) fronts RMAN / Data Pump / dgmgrl / srvctl / recovery / OS as a typed, permissioned tool surface | | Destructive actions are too easy | Every action is **tier-classified (T1/T2/T3)**; the harness enforces dry-run defaults and human approval for anything dangerous | | The model lacks DBA judgment | A **vendored knowledge base** (incl. Oracle's agent-safety patterns) is wired to each action domain via a knowledge↔action map | | "It worked on my laptop" | Everything is **cross-platform** (`.sh` + `.ps1`), **idempotent**, **syntactically validated**, and installed/registered by a **VM bootstrap** | --- ## The mental model: brain + hands ``` ┌──────────────────────────────┐ ┌──────────────────────────────┐ │ 🧠 KNOWLEDGE (the brain) │ │ 🛠️ ACTION (the hands) │ │ knowledge/ · 165 .md │ │ 24 domains · 669 artifacts │ │ source-backed Oracle docs │ │ .sh / .ps1 / .sql + runbooks│ │ "how & why" │ │ "do it — under the tiers" │ └───────────────┬──────────────┘ └───────────────┬──────────────┘ │ references/KNOWLEDGE-MAP.md bridges them per domain │ └─────────────────────────┬───────────────────────────────┘ ▼ SKILL.md (master router) classifies the request → routes to brain or hands, always loads agent-safety patterns, always enforces tiers ``` - **Brain** — read-only Markdown. The agent *reads* it; it never executes. Breadth far beyond ops: SQL/PL-SQL authoring, app development (JDBC/MLE/drivers), frameworks, migrations *off* other databases, ORDS, schema design, performance concepts, and the all-important `agent/` safety patterns. - **Hands** — executable, tier-gated automation that *does* the work, every script sourcing a shared `_common/` harness (`connect_db`, `log_event`, `require_dry_run`, `require_approval_token`, `precheck`, `postcheck`, `emit_metric`). - **Router** — `SKILL.md` decides: *Is this a "how do I / design / explain / migrate" question?* → brain. *Is this a "do X / monitor / back up / recover" request?* → hands, under the tier policy. --- ## The autonomy model (read this part) This is the safety core. Every action — in code, and again in the MCP gateway — is classified into one of three tiers, and the harness **enforces** what the agent may do without a human. | Tier | Meaning | Representative tasks | What the agent may do autonomously | |:---:|---|---|---| | 🟢 **T1** | **Observe** — read-only | health checks, AWR/ASH, space/lag/lock/blocking reports, status, `LIST`/`REPORT` | **Runs freely.** No mutation possible. | | 🟡 **T2** | **Reversible** — low blast-radius | scheduled backups, `RMAN CONFIGURE`, gather stats, Data Pump export, restore-point create, crosscheck within retention | **Dry-run by default.** Prints exactly what it *would* do + logs + a postcheck; acts only with an explicit `--execute` / `execute=true`. Capped here. | | 🔴 **T3** | **Destructive / recovery / HA / structural** | restore/recover, PITR/TSPITR, block media recovery, switchover/**failover**, `DROP`/`TRUNCATE`, patch apply, node eviction, TDE key ops, decommission | **Never auto-executes.** Requires a human **approval token** *and* defaults to emitting a copy-pasteable **runbook**. The gateway refuses to run it. | **Worked example — the agent is asked to "clean up the OE schema's old orders table":** 1. Router sees a *change* request → loads `knowledge/agent/destructive-op-guards.md` + `schema-discovery.md`. 2. Agent runs the **pre-flight** (T1): row count, dependent views/MVs, child FKs, grants, synonyms, active locks, scheduler jobs referencing it. 3. It presents findings and proposes `DROP TABLE` (recycle-bin, **not** `PURGE`) — a **T3** action. 4. Because it's T3, the agent does **not** execute. It produces the exact statement + a rollback note (`FLASHBACK TABLE ... TO BEFORE DROP`) and asks for an approval token / human execution. This mirrors Oracle's own published guidance for LLM database access: **least-privilege user (never SYS/SYSTEM), prefer sanitized non-prod, audit all AI activity, and never enable auto-approve.** The library's motto: **when in doubt, runbook.** --- ## How it runs natively on a database VM Two cooperating MCP servers, both registered with Claude Code, both speaking stdio: ``` ┌─────────────────────────────────────────────┐ Claude Code ──stdio──▶│ 1) sqlcl (Oracle official · `sql -mcp`) │ IN-DATABASE (this skill) │ │ tools: run-sql · connect · list-conns │ SQL / PL-SQL │ │ restrict level 4 (no host commands) │ via least-priv │ │ auto-audit: DBTOOLS$MCP_LOG, V$SESSION tags │ dba_ai_conn (wallet) │ └─────────────────────────────────────────────┘ │ ┌─────────────────────────────────────────────┐ └──stdio──▶│ 2) oracle-dba-ops (this repo · Python) │ HOST / OS │ typed, tier-aware gateway to the scripts │ RMAN · Data Pump └────────────────────┬────────────────────────┘ dgmgrl · srvctl │ resolves <domain>/scripts/<action>, AHF · recovery │ reads its `# TIER:` banner, enforces │ dry-run / execute / approval, logs ▼ 24 action domains ──source──▶ _common/ (connect_db · log_event · require_dry_run · (.sh / .ps1 / .sql + runbooks) require_approval_token · pre/postcheck · metric) ``` - **`sqlcl`** — Oracle's official SQLcl MCP server (`sql -mcp`, SQLcl **25.2+**, JRE 17/21). It owns the connection, resolves **wallet** credentials (saved with `-savepwd`, never passed at runtime), and **auto-tags AI sessions** (`V$SESSION.MODULE` = client, `V$SESSION.ACTION` = model, every query prefixed `/* LLM in use … */`) into the `DBTOOLS$MCP_LOG` audit table. By default it runs at **restrict level 4** — no host commands — which is exactly why the second server exists. - **`oracle-dba-ops`** — the server in this repo (`mcp-server/oracle_dba_ops_mcp.py`). Pure Python 3 standard library (zero `pip` installs — runs on any Oracle Linux box). It is a **gateway, not a re-implementation**: it resolves the requested `<domain>/scripts/<action>`, reads that script's `# TIER:` banner as the single source of truth, and enforces the autonomy policy before spawning the (already-vetted) script with `shell=False`. Full architecture & open questions: [`references/INTEGRATION-PLAYBOOK.md`](references/INTEGRATION-PLAYBOOK.md). Authoritative SQLcl-MCP reference (vendored): [`knowledge/sqlcl/sqlcl-mcp-server.md`](knowledge/sqlcl/sqlcl-mcp-server.md). --- ## What's inside | Layer | Path | Count | |---|---|---| | Master router skill | `SKILL.md` | 1 | | Shared safety harness | `_common/` | `lib.sh` + `lib.ps1` + conventions | | **Action domains** | 24 directories | **210 `.sh` + 210 `.ps1` + 249 `.sql`** + 24 runbooks + cron specs | | **Knowledge base** | `knowledge/` | **165 `.md`** across 18 categories (vendored, UPL-1.0) | | Host-ops MCP server | `mcp-server/` | `oracle_dba_ops_mcp.py` + `selftest.py` (15/15) | | VM bootstrap | `bootstrap/` | `bootstrap.sh` + `bootstrap.ps1` | | Install / validate | `install.sh` / `install.ps1` | npx-skills-style installer + validation suite | | Bridges & docs | `references/` | `INTEGRATION-PLAYBOOK.md`, `KNOWLEDGE-MAP.md` | --- ## The 24 action domains Each domain ships a `SKILL.md` (with a **Scope & risk map** table), idempotent `scripts/*.{sh,ps1}` (+ read-only `checks/`), `sql/*.sql` (run via the SQLcl MCP), a deep `references/runbooks.md` for T3 work, a `references/version-notes.md` (19c vs 23ai/26ai), and `cron/` schedules. | # | Domain | What it covers | Tier mix | |--:|---|---|:--:| | 1 | `provisioning-installation-configuration` | RDBMS/GI install, DBCA, ASM, listener, params, NLS, DB links | T2·T3 | | 2 | `user-role-access-management` | users, roles, profiles, grants, password policy, recertification | T1·T2 | | 3 | `monitoring-alerting` | up/down, alert log, space, sessions, locks, lag, jobs, OEM templates | T1·T2 | | 4 | `space-storage-management` | datafiles, tablespaces, ASM, FRA, undo/temp, cleanup, reorg | T1·T2 | | 5 | `backup-and-recovery` | RMAN full/incr/archivelog, Data Pump, crosscheck, restore points, recovery runbooks | T1·T2·T3 | | 6 | `performance-tuning-diagnostics` | AWR/ASH, top-SQL, stats, plan baselines, SQL tuning advisor, kill session | T1·T2 | | 7 | `patching-upgrades` | OPatch/RU, datapatch, AutoUpgrade, rolling & standby-first patch | T2·T3 | | 8 | `rac-high-availability` | srvctl services, VIP/SCAN, OCR, node add/del, clusterware RCA | T1·T2·T3 | | 9 | `data-guard-disaster-recovery` | transport/apply lag, broker, switchover/failover, standby build, observer | T1·T2·T3 | | 10 | `goldengate-replication` | Extract/Replicat, trail files, initial load, heartbeat, MViews, CDR | T1·T2 | | 11 | `cloning-refresh-migration` | RMAN DUPLICATE, PDB clone/refresh, TTS/XTTS, ZDM, data masking | T2·T3 | | 12 | `multitenant-cdb-pdb` | PDB open/close/save-state, clone, plug/unplug, resource mgr, lockdown | T1·T2·T3 | | 13 | `job-scheduling-automation` | DBMS_SCHEDULER jobs, chains, external jobs, failure triage | T1·T2 | | 14 | `routine-maintenance` | recompile invalids, purge recyclebin/audit, index rebuild, MView refresh | T1·T2 | | 15 | `security-encryption-auditing` | unified audit, CIS hardening, TDE/wallet, redaction, VPD, Vault | T1·T2·T3 | | 16 | `networking-connectivity` | listener config, tnsnames, CMAN, ORA-12xxx, SQL*Net tuning | T1·T2 | | 17 | `flashback-technologies` | flashback query/table/drop/transaction, restore points, FDA, Flashback DB | T1·T2·T3 | | 18 | `capacity-planning-reporting` | status/space/SLA reports, growth forecasting, chargeback | T1·T2 | | 19 | `incident-problem-management` | triage, ORA-error/RCA, hang/deadlock, AHF/TFA, ORA-600/7445, SR workflow | T1·T2·T3 | | 20 | `exadata-engineered-systems` | exachk, cell mgmt, IORM, Smart Scan/flash cache, expansion | T1·T2·T3 | | 21 | `advanced-features` | compression/HCC, In-Memory, partitioning, sharding, result cache | T1·T2·T3 | | 22 | `enterprise-manager-oem-tooling` | OEM target/agent/blackout/template, repository, Cloud Control | T1·T2 | | 23 | `cloud-autonomous-oci` | Autonomous/Base DB provisioning, cloud backup, auto-scaling, ZDM/DMS | T1·T2·T3 | | 24 | `decommissioning-lifecycle-end` | final backup, shutdown/decommission, cleanup, archival, secure destruction | T2·T3 | --- ## The knowledge base `knowledge/` is vendored, largely verbatim, from Oracle's official [`oracle/skills`](https://github.com/oracle/skills) `db/` domain (UPL-1.0). It is the agent's reference library: | Category | Highlights | |---|---| | `agent/` | **destructive-op guards, idempotency patterns, safe-DML, schema discovery, ORA-error catalog, NL→SQL, intent disambiguation** — the safety brain | | `sqlcl/` | SQLcl basics, scripting, Liquibase, DDL gen, data loading, CI/CD, **the authoritative MCP-server doc** | | `performance/` | explain plan, AWR, ASH, wait events, optimizer stats, indexing, memory | | `plsql/` · `sql-dev/` | package/error/perf design, collections, cursors, debugging; SQL patterns, dynamic SQL, injection avoidance | | `security/` | privilege management, VPD, masking, auditing, encryption, network security | | `appdev/` · `frameworks/` | JDBC, node-oracledb, python-oracledb, .NET, Go, MLE; SQLAlchemy, Django, JPA, MyBatis, TypeORM, GORM | | `migrations/` | from PostgreSQL, MySQL, SQL Server, MongoDB, Snowflake, DB2, Sybase, Teradata, Redshift, SQLite | | `architecture/` · `design/` · `devops/` · `features/` · `admin/` · `monitoring/` · `ords/` · `containers/` | RAC/Multitenant/Exadata/In-Memory/DG architecture; ERD/partitioning/tablespaces; schema migrations & EBR; AQ/scheduler/MViews/vector search/Select AI; ORDS; OCR containers | [`references/KNOWLEDGE-MAP.md`](references/KNOWLEDGE-MAP.md) maps every action domain to its relevant knowledge pages, and names the universal agent-safety set to load before *any* change. --- ## The `oracle-dba-ops` MCP server A pure-stdlib **Python 3** stdio MCP server (`mcp-server/oracle_dba_ops_mcp.py`) — zero dependencies, runs anywhere Python 3 does. It exposes the host-side action layer as a safe, typed tool surface. **Generic gateway tools** | Tool | Tier | Purpose | |---|:--:|---| | `list_domains` | T1 | list the 24 action domains present on this host | | `list_actions` | T1 | list a domain's runnable scripts, each with its tier | | `describe_action` | T1 | show a script's header/usage + tier (no execution) | | `read_runbook` | T1 | return a domain's Tier-3 runbook for planning | | `run_action` | tiered | run a domain action under the autonomy policy | **Named convenience tools** (discoverable wrappers over `run_action`, same guards): `rman_backup` *(full / incremental / archivelog)* · `backup_status` · `dataguard_status` · `db_health` · `tablespace_usage` · `alert_log_scan` · `blocking_locks` · `perf_triage` · `audit_report` · `datapump_export` · `crosscheck_delete` · `gather_stats` · `recompile_invalid`. **Safety properties:** reads the tier from the script banner; T1 runs, T2 is dry-run-by-default, T3 is refused → runbook; rejects shell metacharacters; contains path traversal under the skill root; spawns with `shell=False`; never handles credentials; logs every call. **Example — a T2 call defaults to a safe preview:** ```jsonc // tools/call → rman_backup { "name": "rman_backup", "arguments": { "kind": "full" } } // → "tier=T2 decision=dry-run would run: bash .../rman_full_backup.sh --dry-run" // (nothing is backed up until you pass "execute": true) ``` **Example — a T3 call is refused, with the runbook:** ```jsonc { "name": "run_action", "arguments": { "domain": "backup-and-recovery", "action": "restore_database" } } // → "TIER-3 BLOCKED (no valid approval token) … RUNBOOK: .../references/runbooks.md" ``` Register & test: ```bash claude mcp add oracle-dba-ops python3 /path/to/oracle-dba-agentic-skills/mcp-server/oracle_dba_ops_mcp.py python3 mcp-server/selftest.py # -> 15 passed, 0 failed ``` More: [`mcp-server/README.md`](mcp-server/README.md). --- ## Quick start ### Prerequisites (on the database VM or a host that can reach it) - **SQLcl 25.2+** and **JRE 17 or 21** (SQLcl MCP requirements) - **Python 3** (for `oracle-dba-ops` — stdlib only) - **Claude Code** (or another MCP client) - A **least-privilege** DB user for AI access — *never* SYS/SYSTEM (see `knowledge/security/privilege-management.md`) ### 1. Install the skill ```bash git clone https://github.com/Shreyas70773/oracle-dba-agentic-skills.git cp -r oracle-dba-agentic-skills ~/.claude/skills/oracle-dba-super # or project-local .claude/skills/ ``` ### 2. Bootstrap the VM + register both MCP servers (preview first) ```bash # Linux/macOS ./bootstrap/bootstrap.sh --sqlcl-bin /opt/sqlcl/bin/sql --register-mcp # dry-run preview ./bootstrap/bootstrap.sh --sqlcl-bin /opt/sqlcl/bin/sql --register-mcp --execute # apply ``` ```powershell # Windows ./bootstrap/bootstrap.ps1 -SqlclBin C:\sqlcl\bin\sql.exe -RegisterMcp -Execute ``` ### 3. Save the least-privilege connection (the bootstrap never takes a password) ```sql sql /nolog SQL> conn -save dba_ai_conn -savepwd <DBA_AI_USER>/<pwd>@//<host>:1521/<service> ``` ### 4. Install + validate in one step ```bash ./install.sh --register-mcp # install, register MCPs, run the validation suite ./install.sh --validate-only # re-run validation only ``` Full VM walkthrough: [`INSTALL.md`](INSTALL.md). ### Onboarding a fleet (many VMs) — one command Setting up each VM by hand (SSH keys, repo copy, saving the SQLcl connection, registering MCP servers) is tedious and error-prone. The **fleet onboarder** does it all from an inventory file: ```bash cp fleet/inventory.example.json fleet/inventory.json # list your VMs (no passwords in here) python3 fleet/onboard.py --inventory fleet/inventory.json --dry-run # preview python3 fleet/onboard.py --inventory fleet/inventory.json # onboard them all ``` Per VM it installs the SSH key, deploys the repo, and runs an on-VM **auto-provisioner** that detects `ORACLE_HOME`/SQLcl, **finds or installs Java 17** (which `sql -mcp` needs but the bundled JDK lacks), writes a self-contained SQLcl wrapper, and **saves the `dba_ai_conn` connection** — preferring OS-auth `/ as sysdba` (no listener needed) and otherwise auto-discovering the host/port/service from `lsnrctl` (after `ALTER SYSTEM REGISTER`) and verifying it. Then it registers **both** MCP servers, namespaced (`oracle-dba-ops-<vm>`, `sqlcl-<vm>`), so multiple VMs coexist. You type each VM's SSH and DB password **once**; nothing is stored on disk or on a command line. It writes `.mcp.json` directly (more reliable than `claude mcp add`). All the field gotchas — Java 17, the env-not-set-over-SSH problem, the 1521-vs-1522 / NAT-IP / `.localdomain` listener mess, bracketed paste — are handled or documented in [`fleet/README.md`](fleet/README.md). > **Linux fleets & `.ps1`:** the MCP servers run **on each Linux VM** (over SSH), so they > auto-select the `.sh` scripts — the `.ps1` files are simply unused there (they exist for > Windows-host deployments). No action needed; it's not a mismatch in practice. --- ## Usage walkthroughs Once installed, you just talk to your assistant. The skill routes and enforces tiers automatically. **🟢 "Is the database healthy this morning?"** → routes to `monitoring-alerting` (T1) → runs `db_health` / `check_*` read-only scripts and the SQLcl MCP → returns up/down, space, lag, blocking locks, invalid objects, last backup status. No confirmation needed (read-only). **🟡 "Take a full RMAN backup of PRODDB."** → routes to `backup-and-recovery` (T2) → first returns a **dry-run preview** of the exact RMAN script + channels + destination + retention impact → you say "go" → it re-runs with `execute=true`, logs structured output, and runs a postcheck on the backup set. **🔴 "We lost a datafile — recover the database to 14:00."** → routes to `backup-and-recovery` recovery (T3) → it does **not** run anything. It reads `references/runbooks.md`, presents the exact `RESTORE`/`RECOVER ... UNTIL TIME` sequence, a pre-flight checklist (backup availability, space, downtime window, approvals), verification steps, and the rollback/abort path — and asks for a human approval token. --- ## Anatomy of an action skill Every domain follows the same shape, so the agent (and you) always know where to look: ``` backup-and-recovery/ ├── SKILL.md # Scope & risk map: Task | Level | Tier | Mechanism | Idempotent? ├── scripts/ │ ├── rman_full_backup.sh/.ps1 # T2: dry-run default, --execute to act, idempotent, logged │ ├── crosscheck_delete.sh/.ps1 # T2: deletes only within retention; never DELETE FORCE │ └── checks/ │ └── check_backup_status.sh/.ps1 # T1: read-only ├── sql/ │ └── *.sql # run via the SQLcl MCP on dba_ai_conn ├── references/ │ ├── runbooks.md # T3: PITR, media recovery, TSPITR, block recovery (human-run) │ └── version-notes.md # 19c vs 23ai/26ai deltas └── cron/ ├── crontab.d # Linux schedules └── task-scheduler.xml # Windows Task Scheduler import ``` Every `.sh`/`.ps1` carries a `# TIER:` banner, sources `_common`, defaults to dry-run at T2+, emits structured logs, exits non-zero on failure, and detects "already done" to stay idempotent. --- ## Safety & governance - **Least-privilege & wallet-only.** The agent connects as a dedicated low-privilege user (`dba_ai_conn`); credentials live in the SQLcl wallet and are never placed in argv, env, or files. - **Tiered autonomy, enforced twice** — once in the `_common` harness inside each script, and again in the `oracle-dba-ops` MCP gateway. - **T3 is human-gated.** Destructive/recovery/HA work is runbook + approval token; the gateway refuses to auto-execute it even *with* a token (it routes you to the procedure). - **Auditability built in.** SQLcl auto-logs all AI activity (`DBTOOLS$MCP_LOG`, `V$SESSION` MODULE/ACTION tagging, `/* LLM in use */` query tags); the ops server writes structured log lines. - **Injection-hardened gateway** (OWASP MCP05 / CWE-78): direct spawn, no shell, shell-metacharacter rejection, path-traversal containment. - **Aligned with Oracle's own guidance:** minimum permissions, prefer sanitized non-prod, audit LLM queries, **never enable auto-approve**. --- ## Validation & testing | Gate | Result | |---|---| | `bash -n` over all action + bootstrap scripts | ✅ all pass | | PowerShell parse over all `.ps1` | ✅ all pass | | `.sh` / `.ps1` behavioural parity | ✅ no orphans | | `oracle-dba-ops` MCP self-test (protocol, tiers, dry-run/execute, T3 refusal, injection/traversal) | ✅ **15 / 15** | | `install.* --validate-only` | ✅ **PASS** | | Deep T3 runbook present per domain | ✅ | | Secrets/logs committed | ✅ none | > **Honest status:** all of the above is **static / protocol** validation. None of it has been run against a **live Oracle database** yet. On a non-prod VM, run **T1 (read-only) first**, then **T2 in `--dry-run`**, and only then `--execute` — exactly as the autonomy model intends. --- ## Repository layout ``` . ├── SKILL.md · README.md · LICENSE · NOTICE.md · INSTALL.md · MANIFEST.md ├── install.sh / install.ps1 # install + validation suite ├── _common/ # lib.sh · lib.ps1 · references/conventions.md ├── bootstrap/ # bootstrap.sh · bootstrap.ps1 · README.md ├── mcp-server/ # oracle_dba_ops_mcp.py · selftest.py · README.md ├── references/ # INTEGRATION-PLAYBOOK.md · KNOWLEDGE-MAP.md · action-domains-router.md ├── knowledge/ # 165 vendored Oracle guidance files (UPL-1.0) + UPL-LICENSE.txt └── <24 action domains>/ # backup-and-recovery/ · rac-high-availability/ · … ``` --- ## Version & compatibility notes - **Baseline 19c.** Action automation is authored and validated against **19c and 23ai**; vendored knowledge notes call out **26ai** where newer behavior applies. Treat any version-specific item as *verify-before-trust* against your running release. - **Editions/topology:** built for Enterprise Edition, single-instance + Data Guard, on-prem; many domains carry RAC/Exadata/Autonomous notes. - **OS:** every host-level task ships both Bash (`.sh`) and PowerShell (`.ps1`). --- ## Design decisions (FAQ) **Why a separate MCP server instead of just lowering SQLcl's restrict level?** SQLcl's restrict level 4 (no host commands) is a feature, not a limitation — you *want* the in-DB tool sandboxed. Host operations belong behind their own typed, tier-aware, logged surface. Keeping them separate means the in-DB path stays minimal-privilege while the host path is explicitly governed. **Why does the MCP server call scripts instead of running `rman`/`expdp` itself?** Reuse and safety. The scripts already encode dry-run defaults, idempotency, logging, and the tier banner. The gateway reads the banner as the single source of truth and never improvises a command line — far less error-prone than re-implementing each binary. **Why vendor Oracle's knowledge instead of linking it?** Self-containment and offline use on locked-down VMs. It's UPL-1.0, so vendoring with attribution is permitted; see [`NOTICE.md`](NOTICE.md). Refresh instructions are below. **Is it safe to point this at production?** Only after validating on non-prod: T1 read-only → T2 dry-run → T2 execute, with T3 kept human-approved. The architecture is built to make that the path of least resistance. --- ## Roadmap - [x] 24 executable, tier-gated action domains + shared harness - [x] Vendored Oracle knowledge base + knowledge↔action bridge - [x] Native two-MCP integration (SQLcl + custom `oracle-dba-ops`) + VM bootstrap - [x] MCP self-test + install/validate tooling + named convenience tools - [ ] Validate end-to-end against a live non-prod database - [ ] GitHub Actions CI (run `bash -n` + PowerShell parse + MCP self-test on every push) - [ ] Expand named MCP tools per domain as usage patterns emerge - [ ] Periodic upstream re-sync of `knowledge/` --- ## Contributing Issues and PRs welcome. Please keep the conventions that make this safe: - New host scripts ship **both** `.sh` and `.ps1`, carry a `# TIER:` banner, default to dry-run at T2+, source `_common`, and stay idempotent. - T3 work is a **runbook**, not an auto-executing script. - Knowledge pages follow Oracle's authoring style (`## Overview` → examples → best practices → `## Oracle Version Notes` → `## Sources`). - Run `python3 mcp-server/selftest.py` and `./install.sh --validate-only` before opening a PR. Refresh the vendored knowledge from upstream: ```bash git clone --depth 1 https://github.com/oracle/skills.git /tmp/oracle-skills cp -r /tmp/oracle-skills/db/. knowledge/ # keep NOTICE.md + knowledge/UPL-LICENSE.txt ``` --- ## License, attribution & trademarks - **Original work** (everything outside `knowledge/`) — **MIT**, see [`LICENSE`](LICENSE). - **`knowledge/`** — vendored from [`oracle/skills`](https://github.com/oracle/skills), **UPL-1.0**, © 2025 Oracle and/or its affiliates. See [`NOTICE.md`](NOTICE.md) and `knowledge/UPL-LICENSE.txt`. - Oracle, Oracle Database, SQLcl, RMAN, Data Guard, GoldenGate, Exadata, Autonomous Database and related marks are trademarks of Oracle and/or its affiliates, used here for identification only. > **Disclaimer:** This is an independent, community project. It is **not affiliated with, sponsored by, or endorsed by Oracle.** Database administration is high-stakes — validate on non-prod, keep destructive operations human-approved, and use at your own risk. <div align="center"> *Built with a bias toward safety. Read-only freely, reversible carefully, destructive never without a human.* </div>