From oci-administrator
Manages Oracle Autonomous Database lifecycle (start/stop/scale, wallet, ACL, clone/restore) and connects applications via python-oracledb, SQLAlchemy, and Alembic. Includes in-DB diagnostics.
How this skill is triggered — by the user, by Claude, or both
Slash command
/oci-administrator:oci-autonomous-dbThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Tenancy-agnostic helpers for **operating** an Autonomous Database (lifecycle,
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_mutating / confirm. Never
inline real OCIDs, DSNs, IPs, or wallet contents — use <PLACEHOLDER> tokens.
Wallets are credentials.
cwallet.ssois a passwordless auto-login store;ewallet.p12/ewallet.pemhold the client private key. Never commit a wallet (.pem .p12 .jks .sso, anywallet/dir) and never paste its bytes. Keep wallets outside the repo and point at them withTNS_ADMIN.
./scripts/oci_preflight.sh -c <COMPARTMENT_OCID>
Eyeball the resolved tenancy/compartment names. Wrong tenancy → stop.python3 ../../scripts/kb_lookup.py "<error text or component>"
get the ADB and confirm its lifecycle-state
(AVAILABLE / STOPPED) before any mutation.| 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.
| Task | Sequence |
|---|---|
| Provision a new ADB (idempotent) | list --display-name (reuse if non-TERMINATED) → preflight quota → confirm → run_mutating ... 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_mutating ... 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) |
Find & inspect (read-only; ADB list has no subtree flag — iterate compartments):
./scripts/oci_adb.sh -c <COMPARTMENT_OCID> # quick posture: state/workload/ECPU/mTLS/ACL
oci_cli db autonomous-database list --compartment-id <COMPARTMENT_OCID> --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 <ADB_OCID> \
--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:
# 1. Reuse an existing non-terminated instance by display name (don't double-create).
existing="$(oci_cli db autonomous-database list --compartment-id <COMPARTMENT_OCID> \
--display-name '<DISPLAY_NAME>' \
--query "data[?\"lifecycle-state\"!='TERMINATED' && \"lifecycle-state\"!='TERMINATING'].id | [0]" --raw-output)"
# 2. Create only if none. Async op — the CLI may return before AVAILABLE or time out.
run_mutating "create ADB" oci_cli db autonomous-database create \
--compartment-id <COMPARTMENT_OCID> --display-name '<DISPLAY_NAME>' \
--db-name '<DBNAME>' --db-workload OLTP --admin-password "$ADMIN_PASSWORD" \
--compute-model ECPU --compute-count 2 --data-storage-size-in-gbs 20 \
--is-auto-scaling-enabled true --is-free-tier false \
--query 'data.id' --raw-output
# Private endpoint (VCN must already carry a DNS label — set at VCN creation, immutable):
# --subnet-id <SUBNET_OCID> --nsg-ids '["<NSG_OCID>"]' --private-endpoint-label '<LABEL>'
# 3. Poll to AVAILABLE; if create timed out, re-discover by display name before assuming failure.
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):
oci_cli db autonomous-database get --autonomous-database-id <ADB_OCID> --query 'data."lifecycle-state"'
run_mutating "stop ADB" oci_cli db autonomous-database stop --autonomous-database-id <ADB_OCID>
run_mutating "start ADB" oci_cli db autonomous-database start --autonomous-database-id <ADB_OCID>
Scale (ECPU + storage; auto-scaling is a separate flag):
run_mutating "scale ADB" oci_cli db autonomous-database update --autonomous-database-id <ADB_OCID> \
--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):
run_mutating "generate ADB wallet" oci_cli db autonomous-database generate-wallet \
--autonomous-database-id <ADB_OCID> --generate-type ALL \
--password "$WALLET_PASSWORD" --file ~/secure/<db>_wallet.zip
unzip -o ~/secure/<db>_wallet.zip -d ~/secure/<db>_wallet && chmod 700 ~/secure/<db>_wallet
export TNS_ADMIN=~/secure/<db>_wallet # never under the repo tree
CLI
generate-walletonly 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):
oci_cli db autonomous-database get --autonomous-database-id <ADB_OCID> --query 'data."whitelisted-ips"'
run_mutating "update ADB ACL" oci_cli db autonomous-database update \
--autonomous-database-id <ADB_OCID> --whitelisted-ips '["<CIDR_OR_OCID_1>","<CIDR_OR_OCID_2>"]'
Connect from Python (python-oracledb, thin mode + wallet):
import os, oracledb
pool = oracledb.create_pool(
user=os.environ["ADB_USER"], password=os.environ["ADB_PASSWORD"],
dsn=os.environ["ADB_DSN"], # e.g. <service>_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):
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):
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:
run_mutating "enable DBM" oci_cli db autonomous-database enable-autonomous-database-management --autonomous-database-id <ADB_OCID>
run_mutating "enable OPSI" oci_cli db autonomous-database enable-operations-insights --autonomous-database-id <ADB_OCID>
oci_cli db autonomous-database get --autonomous-database-id <ADB_OCID> \
--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:
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.
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).
SELECT 1 FROM dual; -- smoke-test the connection before any real query
Blocking chains (highest-value during a hang — find the root blocker):
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.
*.pem *.p12 *.jks *.sso, **/wallet/).
Gitignore them; if one ever lands in history, rotate the wallet + DB password.--whitelisted-ips overwrites the whole
list — always get the current list first and include every entry you keep.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.oracle+oracledb:// dialect, not oracle+cx_oracle:// (KB-120).
python-oracledb (thin mode, no Instant Client) supersedes cx_Oracle.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.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.client-subnet → ADB-PE:1522 in the NSG/security list, not 1521.409 Conflict as "already exists" and re-get.run_mutating (honors OCI_SKILLS_DRY_RUN=true);
destructive ops (stop, restore, terminate) also through confirm.oci flags. Fetch the exact shape first:
python3 ../../scripts/oci_cli_help.py db autonomous-database.KB-<n> entry to ../../references/KB.md.Finding: <e.g. app 500s — ADB is STOPPED / client IP not in ACL>
Evidence: <redacted get/list output proving the state>
Action: <oci_cli ... via run_mutating, dry-run shown first>
Verification: <re-get showing AVAILABLE / ACL now contains the IP / pool connects>
KB: <KB-<n> if a new error was resolved, else n/a>
Autonomous Database ·
Download wallet / connection info ·
Network access (ACLs & private endpoints) ·
oci db autonomous-database CLI ·
DBMS_XPLAN ·
Database Reference (V$ views).
Driver/ORM references (not Oracle-doc-indexed): python-oracledb, SQLAlchemy, and
Alembic project docs. Full registered list in the
autonomous-db reference; the read-only in-DB
SQL library lives in the
oracle-db-diagnostics reference.
Open Knowledge Format grounding — every docs.oracle.com link here is
registered and liveness-checked in the oracle-docs.md index
(the pack's single source of truth). Cite the most specific official page through
that index so every claim stays verifiable.
npx claudepluginhub adibirzu/oci-skills --plugin oci-administratorBlocks Edit/Write/Bash actions until Claude investigates importers, data schemas, and user instructions. Improves output quality by forcing concrete facts before edits.