# Oci Autonomous Db

> >-

- **Type:** Skill
- **Install:** `agentstack add skill-adibirzu-oci-skills-oci-autonomous-db`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [adibirzu](https://agentstack.voostack.com/s/adibirzu)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [adibirzu](https://github.com/adibirzu)
- **Source:** https://github.com/adibirzu/oci-skills/tree/main/skills/oci-autonomous-db

## Install

```sh
agentstack add skill-adibirzu-oci-skills-oci-autonomous-db
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# OCI Autonomous Database — lifecycle & connectivity

Tenancy-agnostic helpers for **operating** an Autonomous Database (lifecycle,
wallet, scaling, ACL) and **connecting** applications to it (wallet/DSN,
python-oracledb, SQLAlchemy, Alembic). All CLI runs through `oci_cli`
(`../../scripts/common.sh`); mutations through `run_action`. Never
inline real OCIDs, DSNs, IPs, or wallet contents — use `` tokens.

> **Wallets are credentials.** `cwallet.sso` is a passwordless auto-login store;
> `ewallet.p12`/`ewallet.pem` hold the client private key. **Never commit a
> wallet** (`.pem .p12 .jks .sso`, any `wallet/` dir) and never paste its bytes.
> Keep wallets outside the repo and point at them with `TNS_ADMIN`.

## First move (always)

1. **Preflight the tenancy** so you never act on the wrong ADB:
   ```bash
   ./scripts/oci_preflight.sh -c 
   ```
   Eyeball the resolved tenancy/compartment **names**. Wrong tenancy → stop.
2. **Check the KB** for a known fix before debugging from scratch:
   ```bash
   python3 ../../scripts/kb_lookup.py ""
   ```
3. **Read before write.** `get` the ADB and confirm its `lifecycle-state`
   (`AVAILABLE` / `STOPPED`) before any mutation.

## Routing

| User intent | Go to |
|-------------|-------|
| Provision/create a new ADB/ATP/ADW (ECPU, private endpoint, idempotent) | ADB provisioning (this skill) |
| Start/stop/restart, scale ECPU/storage, auto-scaling, clone, restore, backup | ADB lifecycle (this skill) |
| Wallet: generate, rotate, mTLS vs TLS, `TNS_ADMIN`, regional vs instance | Wallet & connectivity (this skill) |
| Access control list / `whitelisted-ips` / private endpoint | Network access (this skill) |
| Connect an app: DSN service levels, pooling, `oracledb`, SQLAlchemy, Alembic | Application integration (this skill) |
| **Read-only in-DB diagnostics** over the connection: blocking sessions, wait events, top SQL, long-running ops, full table scans, plans (`DBMS_XPLAN`) via SQLcl/oracledb | In-DB diagnostics (this skill) → `../../references/oracle-db-diagnostics.md` |
| **Monitor** the DB (Performance Hub, DBM, Ops Insights, metrics/alarms) | → `oci-observability-db` |
| **Provision/enable** DBM/OPSI on the DB | → `oci-observability-db` |
| Register the DB as a Data Safe target, assessments, masking | → `oci-data-safe` |
| **Mutate** inside the DB (DDL/DML, `KILL SESSION`, RMAN, Data Guard, deep tuning) | → `oracle/skills` `db/` (confirmation-gated) |

Full sanitized command/SDK shapes: `../../references/autonomous-db.md`.
Safety rules (auth modes, read-before-write, redaction):
`../../references/tenancy-safety.md`.

## Common multi-step flows

| Task | Sequence |
|------|----------|
| Provision a new ADB (idempotent) | `list --display-name` (reuse if non-TERMINATED) → preflight quota → `confirm` → `run_action ... create` → on `db-name already in use` retry alt name → on timeout re-discover by `--display-name` → poll `AVAILABLE` → `generate-wallet` |
| App can't reach a stopped ADB | `get` (state `STOPPED`) → `confirm` → `run_action ... start` → poll state `AVAILABLE` → reconnect |
| Wallet leaked / rotated staff | **Console → DB → Database Connection → Rotate Wallet** (invalidates old wallets) → `generate-wallet` fresh → redeploy `TNS_ADMIN` → rotate the DB password too |
| New client IP blocked | `get` ACL → `confirm` → `update --whitelisted-ips '[...existing + new]'` (the list is **replace, not append**) → verify |
| Wire an app to a new ADB | `generate-wallet` (out of repo) → set `TNS_ADMIN` + DSN service level → `oracledb.connect`/pool smoke test → SQLAlchemy `oracle+oracledb://` → `alembic upgrade head` |
| "DB is hung / sessions stuck" | smoke-test (`SELECT 1 FROM dual`) → run blocking-chain query (§ diagnostics) → find the **root** blocker → hand off any `KILL SESSION` to a confirmation-gated remediation (never from the diagnostic path) |

## Common tasks

**Find & inspect** (read-only; ADB list has no subtree flag — iterate compartments):
```bash
./scripts/oci_adb.sh -c       # quick posture: state/workload/ECPU/mTLS/ACL
oci_cli db autonomous-database list --compartment-id  --all \
  --query "data[].{name:\"db-name\",disp:\"display-name\",state:\"lifecycle-state\",ecpu:\"compute-count\",id:id}"
oci_cli db autonomous-database get --autonomous-database-id  \
  --query 'data.{state:"lifecycle-state",mtls:"is-mtls-connection-required",acl:"whitelisted-ips"}'
```

**Provision a new ADB** (idempotent: reuse an existing one before creating). ECPU
compute model — storage is in **GBs** (`--data-storage-size-in-gbs`); the legacy
OCPU model used `--cpu-core-count` + `--data-storage-size-in-tbs`:
```bash
# 1. Reuse an existing non-terminated instance by display name (don't double-create).
existing="$(oci_cli db autonomous-database list --compartment-id  \
  --display-name '' \
  --query "data[?\"lifecycle-state\"!='TERMINATED' && \"lifecycle-state\"!='TERMINATING'].id | [0]" --raw-output)"
# 2. Create a complete command JSON payload in a 0700 temp directory, chmod it
#    0600, and place the Vault-sourced admin password only inside that file.
#    Async op — the CLI may return before AVAILABLE or time out.
run_action --risk additive --compartment  --description "create ADB" -- oci_cli db autonomous-database create \
  --from-json file://
#    Private endpoint (VCN must already carry a DNS label — set at VCN creation, immutable):
#      --subnet-id  --nsg-ids '[""]' --private-endpoint-label ''
# 3. Poll to AVAILABLE; if create timed out, re-discover by display name before assuming failure.
```
The payload contains the compartment/display name, DB name/workload, ECPU count,
GB storage, autoscaling/free-tier booleans, and admin password. Footguns (all
real, KB-121/122/123): `--db-name` is alnum, ≤14 chars, **globally
unique per region** — a collision returns `db-name ... already in use`; randomize
and retry. `--admin-password` is 12–30 chars (upper+lower+digit, no `"` and not the
literal `admin`). A private-endpoint ADB listens on **TCP 1522** (not 1521) — the
NSG/security list must allow `client-subnet → ADB-PE:1522`. `create` can return
`not currently enabled for this tenancy` even when service limits show capacity →
request Autonomous Database quota in **Console → Limits** and retry.

**Start / stop** (stop to save cost; confirm — it drops sessions):
```bash
oci_cli db autonomous-database get --autonomous-database-id  --query 'data."lifecycle-state"'
run_action --risk in-place --compartment  --description "stop ADB" -- oci_cli db autonomous-database stop --autonomous-database-id 
run_action --risk in-place --compartment  --description "start ADB" -- oci_cli db autonomous-database start --autonomous-database-id 
```

**Scale** (ECPU + storage; auto-scaling is a separate flag):
```bash
run_action --risk in-place --compartment  --description "scale ADB" -- oci_cli db autonomous-database update --autonomous-database-id  \
  --compute-count 4 --data-storage-size-in-tbs 2 --is-auto-scaling-enabled true
```

**Generate a wallet** (download fresh; write OUTSIDE the repo; never commit):
```bash
run_action --risk credential --compartment  --description "generate ADB wallet" -- oci_cli db autonomous-database generate-wallet \
  --from-json file:// \
  --file ~/secure/_wallet.zip
unzip -o ~/secure/_wallet.zip -d ~/secure/_wallet && chmod 700 ~/secure/_wallet
export TNS_ADMIN=~/secure/_wallet     # never under the repo tree
```
> CLI `generate-wallet` only **downloads**; it does not invalidate old wallets.
> To invalidate a leaked wallet use **Console → Rotate Wallet** (no clean CLI op).

**Update the IP access-control list** (the list is **replaced**, so include all keepers):
```bash
oci_cli db autonomous-database get --autonomous-database-id  --query 'data."whitelisted-ips"'
run_action --risk in-place --compartment  --description "update ADB ACL" -- oci_cli db autonomous-database update \
  --autonomous-database-id  --whitelisted-ips file://
```

**Connect from Python** (`python-oracledb`, thin mode + wallet):
```python
import os, oracledb
pool = oracledb.create_pool(
    user=os.environ["ADB_USER"], password=os.environ["ADB_PASSWORD"],
    dsn=os.environ["ADB_DSN"],                     # e.g. _high / _tp
    config_dir=os.environ["TNS_ADMIN"],            # wallet dir (thin mode reads it)
    wallet_location=os.environ["TNS_ADMIN"],
    wallet_password=os.environ["ADB_WALLET_PASSWORD"],
    min=1, max=5, increment=1)
```
DSN service levels: `_tp`/`_tpurgent` (OLTP), `_high`/`_medium`/`_low` (DW
concurrency tiers). Thin mode needs no Instant Client; thick mode does.

**SQLAlchemy engine** (modern `oracledb` driver, pooled, self-healing):
```python
url = f"oracle+oracledb://{user}:{password}@{dsn}"   # dsn = wallet service alias
engine = create_engine(url, pool_pre_ping=True, pool_recycle=3600,
                       pool_size=10, max_overflow=20,
                       connect_args={"config_dir": tns_admin,
                                     "wallet_location": tns_admin,
                                     "wallet_password": wallet_password})
```

**Alembic migrations** against Oracle (same `oracle+oracledb://` URL in `env.py`):
```bash
python cli/db.py current      # confirm revision
python cli/db.py upgrade      # apply to head
python cli/db.py downgrade -1 # roll back one
```

**Enable ADB-native monitoring** (Database Management + Operations Insights are
control-plane toggles on the ADB resource — idempotent, treat `409`/already-enabled
as success). Deep monitoring/Performance-Hub work still routes to `oci-observability-db`:
```bash
run_action --risk additive --compartment  --description "enable DBM" -- oci_cli db autonomous-database enable-autonomous-database-management --autonomous-database-id 
run_action --risk additive --compartment  --description "enable OPSI" -- oci_cli db autonomous-database enable-operations-insights        --autonomous-database-id 
oci_cli db autonomous-database get --autonomous-database-id  \
  --query 'data.{dbm:"database-management-status",opsi:"operations-insights-status"}'
```

**Create a least-privilege monitoring user** *inside* the ADB (thin-mode wallet
connect as ADMIN — no `sqlplus`/Instant Client needed). Grant only read on the
catalog, not DBA:
```python
import oracledb  # thin mode; config_dir/wallet_location = $TNS_ADMIN
conn = oracledb.connect(user="ADMIN", password=admin_pw, dsn=f"{svc}_tp",
    config_dir=tns_admin, wallet_location=tns_admin, wallet_password=wallet_pw)
cur = conn.cursor()
cur.execute(f'CREATE USER {mon} IDENTIFIED BY "{mon_pw}"')        # quote → preserves case/specials
cur.execute(f"GRANT CREATE SESSION TO {mon}")
cur.execute(f"GRANT SELECT_CATALOG_ROLE TO {mon}")               # v$ / dba_ views (read-only)
cur.execute(f"GRANT SELECT ANY DICTIONARY TO {mon}")
cur.execute(f"GRANT READ ON awr_pdb_snapshot TO {mon}")          # AWR/ASH on ADB (PDB-scoped)
```
Wrap each `GRANT` in its own try/except — some views/packages vary by ADB version,
and a missing one should warn, not abort the whole setup.

## Working DB diagnostics (read-only, in-DB SQL)

Once connected, you can answer "why is the DB slow/hung?" with read-only SQL over
the **same** wallet/DSN connection. **Pick the cheapest tier first** — drop to raw
SQL only when a managed path can't answer:

**managed Database Tools MCP → OPSI → DBM → guarded SQLcl/oracledb**

Tiers 1–2 (OPSI capacity/ADDM, DBM AWR/Performance Hub) need no wallet → route to
`oci-observability-db`. Tier 3 (live `V$`/`GV$` truth) runs here. Always smoke-test
first and keep every query **read-only** (`V$`/`GV$`/`DBA_*`/`DBMS_XPLAN` only —
never DDL/DML/`KILL SESSION`).

```sql
SELECT 1 FROM dual;   -- smoke-test the connection before any real query
```

**Blocking chains** (highest-value during a hang — find the root blocker):
```sql
SELECT s.sid AS waiter_sid, s.username AS waiter_user, s.event AS wait_event,
       s.seconds_in_wait, s.blocking_session AS blocker_sid,
       bs.username AS blocker_user, l.type AS lock_type
FROM v$session s
LEFT JOIN v$session bs ON s.blocking_session = bs.sid
LEFT JOIN v$lock    l  ON s.sid = l.sid AND l.request > 0
WHERE s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;
```
**Top wait events** (`v$system_event`, non-idle), **top SQL** (`v$sqlarea` by
`elapsed_time`), **long-running ops** (`v$session_longops`), and **plans**
(`DBMS_XPLAN.DISPLAY_CURSOR` / `DISPLAY_AWR`) follow the same pattern.

Full proven SQL library, the connection model, and the **container-safe runtime
wallet** (rewrite `retry_count=20` → `1` so a stopped DB fails fast, KB-121):
`../../references/oracle-db-diagnostics.md`.

## Safety notes

- **Never commit wallet/key files** (`*.pem *.p12 *.jks *.sso`, `**/wallet/`).
  Gitignore them; if one ever lands in history, rotate the wallet + DB password.
- **ACL is replace-not-append (KB-119).** `--whitelisted-ips` overwrites the whole
  list — always `get` the current list first and include every entry you keep.
- **Stopped/unreachable ADB stalls startup (KB-118).** A DSN with `retry_count=20`
  can hang app boot ~60s. Bound the connect probe (hard wall-clock timeout) and,
  outside production, fall back to local SQLite so the app still boots; in
  production fail fast so an outage is never masked.
- **Use the `oracle+oracledb://` dialect, not `oracle+cx_oracle://` (KB-120).**
  `python-oracledb` (thin mode, no Instant Client) supersedes `cx_Oracle`.
- **mTLS vs TLS.** mTLS needs both the wallet **and** DB credentials; TLS-only
  (if enabled) drops the wallet but still needs the ACL to allow the client IP.
- **In-DB diagnostics are read-only (KB-122).** Query `V$`/`GV$`/`DBA_*`/`DBMS_XPLAN`
  only; never `KILL SESSION`/DDL/DML from a diagnostic path. Bound every Tier-3 call
  with a hard timeout and use a fast-fail runtime wallet (KB-121). SQL text and bind
  values can leak data — redact before sharing.
- **Create is async + idempotent (KB-123).** The CLI may return before `AVAILABLE`
  or time out while the resource is still being created. Always `list --display-name`
  first to reuse, and on timeout re-discover by display name before re-creating.
- **`--db-name` is globally unique per region, ≤14 alnum chars (KB-124).** Collisions
  return `db-name ... already in use`; randomize and retry, don't fail.
- **Private-endpoint ADB needs the VCN's DNS label (immutable) and listens on TCP
  1522 (KB-125).** No DNS label at VCN creation → no private endpoint (recreate the
  VCN). Open `client-subnet → ADB-PE:1522` in the NSG/security list, not 1521.
- Read before write; treat `409 Conflict` as "already exists" and re-`get`.
- Mutations go through `run_action` (honors `OCI_SKILLS_DRY_RUN=true`);
  destructive ops (stop, restore, terminate) also through `confirm`.
- **Never invent `oci` flags.** Fetch the exact shape first:
  `python3 ../../scripts/oci_cli_help.py db autonomous-database`.
- After fixing a new error, add a `KB-` entry to `../../references/KB.md`.

## Expected output

```
Finding:      
Evidence:     
Action:       
Verification: 
KB:            if a new error was resolved, else n/a>
```

## Official documentation

[Autonomous Database](https://docs.oracle.com/en-us/iaas/autonomous-database/index.html) ·
[Download wallet / connection info](https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/connect-download-wallet.html) ·
[Network access (ACLs & private endpoints)](https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/autonomous-network-access.html) ·
[`oci db autonomous-database` CLI](https://docs.oracle.com/en-us/iaas/tools/oci-cli/latest/oci_cli_docs/cmdref/db/autonomous-database.html) ·
[`DBMS_XPLAN`](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html) ·
[Database Reference (V$ views)](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/index.html).
Driver/ORM references (not Oracle-doc-indexed): python-oracledb, SQLAlchemy, and
Alembic project docs. Full registered list in the
[autonomous-db reference](../../references/a

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [adibirzu](https://github.com/adibirzu)
- **Source:** [adibirzu/oci-skills](https://github.com/adibirzu/oci-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** yes
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-adibirzu-oci-skills-oci-autonomous-db
- Seller: https://agentstack.voostack.com/s/adibirzu
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.