Oci Autonomous Db

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.

1. Check the KB for a known fix before debugging from scratch:

``bash python3 ../../scripts/kb_lookup.py "" ``

1. 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):

./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:

# 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):

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):

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):

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):

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):

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):

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_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:

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

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.

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 · 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](../../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
• Source: adibirzu/oci-skills
• License: MIT

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