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

   ./scripts/oci_preflight.sh -c <COMPARTMENT_OCID>
   

   Eyeball the resolved tenancy/compartment names. Wrong tenancy → stop.
2. Check the KB for a known fix before debugging from scratch:

   python3 ../../scripts/kb_lookup.py "<error text or component>"
   

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

./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 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 <COMPARTMENT_OCID> --description "create ADB" -- oci_cli db autonomous-database create \
  --from-json file://<TMP_0600_ADB_CREATE_JSON>
#    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.

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 <ADB_OCID> --query 'data."lifecycle-state"'
run_action --risk in-place --compartment <COMPARTMENT_OCID> --description "stop ADB" -- oci_cli db autonomous-database stop --autonomous-database-id <ADB_OCID>
run_action --risk in-place --compartment <COMPARTMENT_OCID> --description "start ADB" -- oci_cli db autonomous-database start --autonomous-database-id <ADB_OCID>

Scale (ECPU + storage; auto-scaling is a separate flag):

run_action --risk in-place --compartment <COMPARTMENT_OCID> --description "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_action --risk credential --compartment <COMPARTMENT_OCID> --description "generate ADB wallet" -- oci_cli db autonomous-database generate-wallet \
  --from-json file://<TMP_0600_WALLET_REQUEST_JSON> \
  --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-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 <ADB_OCID> --query 'data."whitelisted-ips"'
run_action --risk in-place --compartment <COMPARTMENT_OCID> --description "update ADB ACL" -- oci_cli db autonomous-database update \
  --autonomous-database-id <ADB_OCID> --whitelisted-ips file://<TMP_0600_FULL_ADB_ACL_JSON>

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_action --risk additive --compartment <COMPARTMENT_OCID> --description "enable DBM" -- oci_cli db autonomous-database enable-autonomous-database-management --autonomous-database-id <ADB_OCID>
run_action --risk additive --compartment <COMPARTMENT_OCID> --description "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.

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-<n> entry to ../../references/KB.md.

Expected output

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_action, 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>

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