Oracle Dba

OCI Oracle DBA - Expert Knowledge

🏗️ Use OCI Landing Zone Terraform Modules

Don't reinvent the wheel. Use oracle-terraform-modules/landing-zone for database infrastructure.

Landing Zone solves:

• ❌ Bad Practice #1: Generic compartments (Landing Zone creates dedicated Database/Security compartments for ADB organization)
• ❌ Bad Practice #9: Public database endpoints (Landing Zone Security Zones enforce private endpoints only)
• ❌ Bad Practice #10: No monitoring (Landing Zone auto-configures ADB performance alarms, slow query notifications)

This skill provides: ADB-specific operations, performance tuning, and cost optimization for databases deployed WITHIN a Landing Zone.

---

⚠️ OCI CLI/API Knowledge Gap

You don't know OCI CLI commands or OCI API structure.

Your training data has limited and outdated knowledge of:

• OCI CLI syntax and parameters (updates monthly)
• OCI API endpoints and request/response formats
• Autonomous Database CLI operations (oci db autonomous-database)
• OCI service-specific commands and flags
• Latest OCI features and API changes

When OCI operations are needed: 1. Use exact CLI commands from this skill's references 2. Do NOT guess OCI CLI syntax or parameters 3. Do NOT assume API endpoint structures 4. Load `oci-cli-adb.md` for ADB management operations

What you DO know:

• Oracle Database internals (SQL, PL/SQL, performance tuning)
• General cloud concepts
• Database administration principles

This skill bridges the gap by providing current OCI CLI/API commands for Autonomous Database operations.

---

You are an Oracle Autonomous Database expert on OCI. This skill provides knowledge Claude lacks: ADB-specific behaviors, cost traps, SQL_ID debugging workflows, auto-scaling gotchas, and production anti-patterns.

NEVER Do This

❌ NEVER use ADMIN user in application code

-- WRONG - application uses ADMIN credentials
app_config = {'user': 'ADMIN', 'password': admin_pwd}

-- RIGHT - create app-specific user with least privilege
CREATE USER app_user IDENTIFIED BY :password;
GRANT CREATE SESSION, SELECT ON schema.* TO app_user;

Why critical: ADMIN has full database control, audit trail shows all actions as ADMIN (no accountability), ADMIN can't be locked/disabled without breaking automation.

❌ NEVER scale without checking wait events first

-- WRONG decision path: "CPU is high → scale ECPUs"

-- RIGHT decision path:
1. Check v$system_event for top wait events
2. High 'CPU time' wait → Bad SQL, need optimization (DON'T scale)
3. High 'db file sequential read' → Missing indexes (DON'T scale)
4. High 'User I/O' sustained → Scale storage IOPS OR auto-scaling
5. Only scale ECPUs if: CPU wait sustained + SQL already optimized

Cost impact: Scaling 2→4 ECPU = $526/month increase. If root cause is bad SQL, wasted $526/month.

❌ NEVER assume stopped ADB = zero cost

Stopped Autonomous Database charges:
✓ Compute: $0 (stopped)
✗ Storage: $0.025/GB/month continues
✗ Backups: Retention charges continue

Example: 1TB ADB stopped for 30 days
Storage: 1000 GB × $0.025 = $25/month (CHARGED!)

Better for long-term idle (>60 days):
1. Export data (Data Pump)
2. Delete ADB
3. Restore from backup when needed

❌ NEVER forget retention on manual backups (cost trap)

# WRONG - manual backup with no retention (kept forever)
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "pre-upgrade-backup"
# Cost: $0.025/GB/month FOREVER

# RIGHT - set retention
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "pre-upgrade-backup" \
  --retention-days 30

Cost trap: 1TB manual backup × $0.025/GB/month × 12 months = $300/year waste

❌ *NEVER use SELECT in production queries**

-- WRONG - fetches all columns, heavy network/parsing
SELECT * FROM orders WHERE customer_id = :cust_id;

-- RIGHT - specify needed columns
SELECT order_id, total_amount, status FROM orders WHERE customer_id = :cust_id;

Impact: 50-column table, fetching 5 needed columns
- SELECT *: 50 columns × 1000 rows = 50k data points
- Explicit: 5 columns × 1000 rows = 5k data points (90% reduction)

❌ NEVER ignore SQL_ID when debugging slow queries

-- WRONG - "my query is slow, tune the database"
ALTER SYSTEM SET optimizer_mode = 'FIRST_ROWS';  # Affects ALL queries!

-- RIGHT - identify specific SQL_ID, tune that query
SELECT sql_id, elapsed_time/executions/1000 AS avg_ms, executions
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

Then tune specific SQL_ID (not entire database)

❌ NEVER use ROWNUM with ORDER BY (wrong results)

-- WRONG - ROWNUM applied BEFORE ORDER BY (wrong top 10)
SELECT * FROM orders WHERE ROWNUM <= 10 ORDER BY created_at DESC;

-- RIGHT - FETCH FIRST (Oracle 12c+)
SELECT * FROM orders ORDER BY created_at DESC FETCH FIRST 10 ROWS ONLY;

❌ NEVER scale auto-scaling ADB without checking current behavior

ADB Auto-Scaling Gotcha:
- Base ECPU: 2
- Auto-scaling: Scales 1-3x (2 → 6 ECPU max)
- Cost: Charged for PEAK usage during period

# WRONG - enable auto-scaling then forget about it
Cost surprise: Base 2 ECPU ($526/month) → Peak 6 ECPU ($1,578/month)

# RIGHT - set max ECPU limit in console
Max ECPU = 4 (2× base, not 3×)
Cost control: Peak 4 ECPU ($1,052/month) max

Performance Troubleshooting Decision Tree

"Queries are slow"?
│
├─ Is it ONE query or ALL queries?
│  ├─ ONE query slow
│  │  └─ Get SQL_ID from v$sql (top by elapsed_time)
│  │     └─ Check execution plan:
│  │        SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
│  │        ├─ Full table scan? → Add index
│  │        ├─ Wrong join order? → Use hints or SQL Plan Management
│  │        └─ Cartesian join? → Fix query logic
│  │
│  └─ ALL queries slow (system-wide)
│     └─ Check wait events:
│        SELECT event, time_waited_micro/1000000 AS wait_sec
│        FROM v$system_event
│        WHERE wait_class != 'Idle'
│        ORDER BY time_waited_micro DESC
│        FETCH FIRST 10 ROWS ONLY;
│
│        ├─ Top wait: 'CPU time' → Optimize SQL OR scale ECPU
│        ├─ Top wait: 'db file sequential read' → Missing indexes
│        ├─ Top wait: 'db file scattered read' → Full table scans
│        ├─ Top wait: 'log file sync' → Too many commits (batch)
│        └─ Top wait: 'User I/O' → Scale storage IOPS or auto-scale
│
└─ When did slowness start?
   ├─ After schema change? → Gather stats (DBMS_STATS)
   ├─ After data load? → Gather stats + check partitioning
   ├─ After version upgrade? → Check execution plan changes
   └─ Gradual over time? → Data growth, need indexing/partitioning

ADB Cost Calculations (Exact)

ECPU Scaling Cost

License-Included pricing: $0.36/ECPU-hour
BYOL pricing: $0.18/ECPU-hour (if you have Oracle licenses)

Monthly cost = ECPU count × hourly rate × 730 hours

Examples:
2 ECPU: 2 × $0.36 × 730 = $526/month
4 ECPU: 4 × $0.36 × 730 = $1,052/month
8 ECPU: 8 × $0.36 × 730 = $2,104/month

BYOL (50% off):
2 ECPU: 2 × $0.18 × 730 = $263/month
4 ECPU: 4 × $0.18 × 730 = $526/month

Storage Cost

Storage pricing: $0.025/GB/month (all tiers: Standard, Archive)

Examples:
1 TB: 1000 GB × $0.025 = $25/month
5 TB: 5000 GB × $0.025 = $125/month

CRITICAL: Storage charged even when ADB stopped!

Auto-Scaling Cost Impact

Scenario: Base 2 ECPU with auto-scaling enabled (1-3×)

Without auto-scaling:
2 ECPU × $0.36 × 730 = $526/month (fixed)

With auto-scaling (spiky load):
- 50% of time: 2 ECPU = $263
- 30% of time: 4 ECPU = $315
- 20% of time: 6 ECPU = $315
Monthly cost: $893 (70% increase)

When auto-scaling makes sense:
- Spiky load (not sustained high)
- Want to avoid manual scaling
- Cost increase acceptable (up to 3×)

SQL_ID Debugging Workflow

Step 1: Find problem SQL_ID

SELECT sql_id,
       elapsed_time/executions/1000 AS avg_ms,
       executions,
       sql_text
FROM v$sql
WHERE executions > 0
  AND last_active_time > SYSDATE - 1/24  -- Last hour
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

Step 2: Get execution plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

Step 3: Analyze plan issues

• TABLE ACCESS FULL on large table → Missing index
• NESTED LOOPS with high cardinality → Wrong join method
• HASH JOIN OUTER → Consider index join

Step 4: Create SQL Tuning Task

DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => '&sql_id',
    task_name => 'tune_slow_query'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;
/

-- Get recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_slow_query') FROM DUAL;

Step 5: Implement fix

• Recommendation: Add index → Create index
• Recommendation: Use hint → Test with hint, then SQL Plan Baseline
• Recommendation: Gather stats → EXEC DBMS_STATS.GATHER_TABLE_STATS

ADB-Specific Behaviors (OCI Gotchas)

Auto-Scaling Limits

Auto-scaling rules (cannot change):
- Minimum: 1× base ECPU
- Maximum: 3× base ECPU
- Scaling trigger: CPU > 80% for 5+ minutes
- Scale-down: CPU < 60% for 10+ minutes
- Time to scale: 5-10 minutes

Example: Base 2 ECPU
- Can scale: 2 → 4 → 6 ECPU
- Cannot scale: Beyond 6 ECPU (hard limit)
- Cost: Pay for peak usage each hour

ADMIN User Restrictions

In Autonomous Database, ADMIN user:
✓ Can: Create users, grant roles, DDL operations
✗ Cannot: Create tablespaces (DATA is auto-managed)
✗ Cannot: Modify SYSTEM/SYSAUX tablespaces
✗ Cannot: Access OS (no shell, no file system)
✗ Cannot: Use SYSDBA privileges (not available in ADB)

For applications:
- ADMIN: Only for database setup/maintenance
- App users: Create dedicated users with minimal grants

Service Name Performance Impact

ADB provides 3 service names per database:

| Service | CPU Allocation | Concurrency | Use For |
|---------|---------------|-------------|---------|
| HIGH | Dedicated OCPU | 1× ECPU | Interactive queries, OLTP |
| MEDIUM | Shared OCPU | 2× ECPU | Reporting, batch jobs |
| LOW | Most sharing | 3× ECPU | Background tasks, ETL |

Cost: All service names use same ECPU pool (no extra cost)
Performance: HIGH is faster but limits concurrency
Gotcha: Using HIGH for background jobs wastes resources

Backup Retention (Automatic vs Manual)

Automatic backups (free, included):
- Frequency: Daily incremental, weekly full
- Retention: 60 days default (configurable 1-60)
- Cost: Included in ADB storage cost
- Deletion: Automatic after retention period

Manual backups (charged separately):
- Frequency: On-demand
- Retention: FOREVER (until you delete)
- Cost: $0.025/GB/month
- Deletion: Manual only

Cost trap: 10 manual backups × 1TB × $0.025/GB/month = $250/month
Recommendation: Use automatic backups, manual only for long-term archival

Version-Specific Features (Know Which ADB Version)

Upgrade path: 19c → 21c → 23ai → 26ai Downgrade: NOT supported (cannot go back) Recommendation: Test in clone before upgrading production

Common ADB Errors Decoded

Advanced Operations (Progressive Loading)

SQLcl Direct Database Access

WHEN TO LOAD `sqlcl-workflows.md`:

• Need to execute SQL queries directly via Bash
• Want to get execution plans, wait events, or active sessions
• Performing SQL tuning tasks (DBMS_SQLTUNE)
• Exporting/importing data with Data Pump
• Generating DDL for schema objects

Example: Finding top SQL by elapsed time

sql admin/password@adb_high <<EOF
SELECT sql_id, elapsed_time/executions/1000 AS avg_ms
FROM v\$sql WHERE executions > 0
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
EXIT;
EOF

Do NOT load for:

• Standard troubleshooting advice - covered in this skill's decision trees
• Cost calculations - exact formulas provided above
• Anti-patterns - NEVER list covers common mistakes

---

OCI CLI for ADB Management

WHEN TO LOAD `oci-cli-adb.md`:

• Need to provision, scale, or delete ADB instances
• Creating backups or clones (full vs metadata)
• Downloading wallet files
• Changing configuration (auto-scaling, license type, version upgrades)
• Batch operations across multiple ADBs

Example: Scale ADB from 2 to 4 ECPUs

oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --cpu-core-count 4 \
  --wait-for-state AVAILABLE

Example: Create metadata clone (70% cheaper - schema only, no data)

oci db autonomous-database create-from-clone \
  --source-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "dev-schema" \
  --db-name "DEVSCHEMA" \
  --clone-type METADATA \
  --wait-for-state AVAILABLE

Do NOT load for:

• SQL operations (use SQLcl instead)
• Performance analysis (v$sql queries covered in this skill)
• Cost formulas (exact calculations provided above)

---

OCI Autonomous Database Best Practices (Official Oracle Documentation)

WHEN TO LOAD `oci-adb-best-practices.md`:

• Need comprehensive ADB architecture and design patterns
• Understanding ADB workload types (ATP, ADW, APEX, JSON)
• Implementing production-grade ADB deployments
• Need official Oracle guidance on ADB features and limitations
• Planning migrations to ADB from on-premises Oracle

Do NOT load for:

• Quick SQL_ID debugging (workflow in this skill)
• Cost calculations (exact formulas above)
• Common gotchas (NEVER list covers them)

---

When to Use This Skill

• Performance issues: Slow queries, high CPU, scaling decisions
• Cost optimization: ECPU sizing, stopped ADB charges, backup retention
• Debugging: SQL_ID workflow, wait events, execution plans
• Auto-scaling: When to enable, cost impact, limits
• Version planning: Feature comparison (19c vs 26ai), upgrade timing
• Security: ADMIN restrictions, user setup, service name selection

OCI CLI for Autonomous Database Operations

Direct OCI CLI commands for ADB management. Use these instead of MCP server calls.

Prerequisites

# Verify OCI CLI is configured
oci --version

# Test connectivity
oci iam region list --output table

# Set default profile (optional)
export OCI_CLI_PROFILE=DEFAULT

List and Discover

List All Autonomous Databases

# In specific compartment
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --output table

# Filter by display name
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --display-name "prod-adb" \
  --output json | jq '.data[] | {id, name: .["display-name"], state: .["lifecycle-state"]}'

# All compartments (requires tenancy permissions)
oci db autonomous-database list \
  --compartment-id ocid1.tenancy.oc1..xxx \
  --all \
  --output table

Get ADB Details

oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --output json | jq '{
    name: .data["display-name"],
    ecpu: .data["cpu-core-count"],
    storage: .data["data-storage-size-in-tbs"],
    state: .data["lifecycle-state"],
    version: .data["db-version"],
    autoscaling: .data["is-auto-scaling-enabled"]
  }'

List Backups

# Automatic backups
oci db autonomous-database-backup list \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --output table

# Filter by type
oci db autonomous-database-backup list \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '.data[] | select(.type == "FULL")'

Create and Provision

Create Autonomous Database

oci db autonomous-database create \
  --compartment-id ocid1.compartment.oc1..xxx \
  --display-name "prod-adb" \
  --db-name "PRODADB" \
  --cpu-core-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password 'SecurePass123!' \
  --db-version "19c" \
  --license-model LICENSE_INCLUDED \
  --is-auto-scaling-enabled false \
  --wait-for-state AVAILABLE

# With auto-scaling and specific version
oci db autonomous-database create \
  --compartment-id ocid1.compartment.oc1..xxx \
  --display-name "dev-adb-23ai" \
  --db-name "DEVADB" \
  --cpu-core-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password 'SecurePass123!' \
  --db-version "23ai" \
  --license-model LICENSE_INCLUDED \
  --is-auto-scaling-enabled true \
  --wait-for-state AVAILABLE

Create Clone

# Full clone
oci db autonomous-database create-from-clone \
  --compartment-id ocid1.compartment.oc1..xxx \
  --source-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "prod-adb-clone" \
  --db-name "PRODCLONE" \
  --clone-type FULL \
  --wait-for-state AVAILABLE

# Metadata clone (70% cheaper - no data)
oci db autonomous-database create-from-clone \
  --compartment-id ocid1.compartment.oc1..xxx \
  --source-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "dev-schema-only" \
  --db-name "DEVSCHEMA" \
  --clone-type METADATA \
  --wait-for-state AVAILABLE

Scale and Update

Scale ECPUs

# Scale from 2 to 4 ECPUs
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --cpu-core-count 4 \
  --wait-for-state AVAILABLE

# Enable auto-scaling (1-3x base ECPU, cannot configure max via CLI)
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --is-auto-scaling-enabled true \
  --wait-for-state AVAILABLE

# IMPORTANT: Auto-scaling limits are FIXED (cannot change via CLI):
# - Min: 1x base ECPU
# - Max: 3x base ECPU (hard limit)
# - Scaling trigger: CPU > 80% for 5+ minutes
# - Scale-down: CPU < 60% for 10+ minutes
#
# Cost impact example (Base 2 ECPU):
# - Without auto-scaling: 2 × $0.36 × 730 = $526/month (fixed)
# - With auto-scaling peak: 6 × $0.36 × 730 = $1,578/month (if sustained)
#
# To limit costs: Start with higher base ECPU, disable auto-scaling

Scale Storage

# Scale from 1TB to 2TB
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --data-storage-size-in-tbs 2 \
  --wait-for-state AVAILABLE

Change License Type

# Switch to BYOL (50% cost reduction)
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --license-model BRING_YOUR_OWN_LICENSE \
  --wait-for-state AVAILABLE

Lifecycle Management

Stop ADB

oci db autonomous-database stop \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state STOPPED

# IMPORTANT: Storage charges continue even when stopped!
# 1TB ADB stopped = $25/month storage cost

Start ADB

oci db autonomous-database start \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

Delete ADB

# Delete immediately
oci db autonomous-database delete \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --force

# Delete with final backup
oci db autonomous-database-backup create \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "final-backup-before-delete" \
  --retention-days 30

oci db autonomous-database delete \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --force

Backup Operations

Create Manual Backup

# With retention (CRITICAL - prevents forever storage)
oci db autonomous-database-backup create \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "pre-upgrade-backup" \
  --retention-days 30 \
  --wait-for-state ACTIVE

# NEVER create without retention - costs $0.025/GB/month FOREVER
# 1TB backup × $0.025 × ∞ = $300/year perpetually

Restore from Backup

oci db autonomous-database restore \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --timestamp "2026-01-28T10:00:00Z" \
  --wait-for-state AVAILABLE

Delete Manual Backup

oci db autonomous-database-backup delete \
  --autonomous-database-backup-id ocid1.autonomousdatabasebackup.oc1..xxx \
  --force

Wallet Management

Download Wallet

# Regional wallet (one wallet for this ADB)
oci db autonomous-database generate-wallet \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --password 'WalletPass123!' \
  --file ~/wallets/adb_wallet.zip

# Extract and use
mkdir ~/wallets/adb_wallet
unzip ~/wallets/adb_wallet.zip -d ~/wallets/adb_wallet
export TNS_ADMIN=~/wallets/adb_wallet

Rotate Wallet

# Generate new wallet (invalidates old ones)
oci db autonomous-database generate-wallet \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --password 'NewWalletPass123!' \
  --file ~/wallets/adb_wallet_new.zip

Monitoring and Metrics

Get Connection Strings

oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '.data["connection-strings"]'

# Extract specific service
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["connection-strings"]["profiles"][] | select(.["consumer-group"] == "HIGH") | .value'

Check ECPU Usage (requires monitoring API)

# Query metrics namespace
oci monitoring metric-data summarize-metrics-data \
  --namespace oci_autonomous_database \
  --compartment-id ocid1.compartment.oc1..xxx \
  --query-text 'CpuUtilization[1m].mean()' \
  --start-time "2026-01-28T00:00:00Z" \
  --end-time "2026-01-28T23:59:59Z" \
  --output table

Cost Management

Check Current Cost Configuration

oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '{
    ecpu: .data["cpu-core-count"],
    storage_tb: .data["data-storage-size-in-tbs"],
    license: .data["license-model"],
    autoscaling: .data["is-auto-scaling-enabled"],
    state: .data["lifecycle-state"]
  }'

# Calculate monthly cost
# License-Included: ECPU × $0.36/hr × 730 hrs + Storage_TB × 1000 × $0.025
# BYOL: ECPU × $0.18/hr × 730 hrs + Storage_TB × 1000 × $0.025

Switch to BYOL (50% compute savings)

oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --license-model BRING_YOUR_OWN_LICENSE \
  --wait-for-state AVAILABLE

# Savings: 2 ECPU × ($0.36 - $0.18) × 730 = $263/month

Advanced Operations

Update to Latest Version

# Update to 23ai
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --db-version "23ai" \
  --wait-for-state AVAILABLE

# CRITICAL: Cannot downgrade! Test in clone first.

Change Workload Type

# Switch from OLTP to Data Warehouse
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --db-workload "DW" \
  --wait-for-state AVAILABLE

Enable/Disable Features

# Enable operations insights
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --is-operations-insights-enabled true \
  --wait-for-state AVAILABLE

# Enable database management
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --is-database-management-enabled true \
  --wait-for-state AVAILABLE

High Availability and Disaster Recovery

Create Autonomous Data Guard (Standby Database)

# Enable Autonomous Data Guard (creates standby in different region)
oci db autonomous-database create-autonomous-database-dataguard-association \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --protection-mode MAXIMUM_PERFORMANCE \
  --wait-for-state AVAILABLE

# Check Data Guard status
oci db autonomous-database-dataguard-association list \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --output table

Failover (Disaster Recovery)

# Failover to standby (makes standby the new primary)
# Use when primary is unavailable
oci db autonomous-database failover \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

# CRITICAL: This is a disaster recovery operation
# Primary must be unavailable or you'll get an error

Switchover (Planned Maintenance)

# Switchover to standby (makes standby the new primary)
# Use for planned maintenance
oci db autonomous-database switchover \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

# After switchover:
# - Old primary becomes new standby
# - Old standby becomes new primary
# - Zero data loss

Reinstate Failed Primary

# After failover, reinstate old primary as new standby
oci db autonomous-database reinstate-autonomous-database-dataguard-association \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

Version-Specific Features

Check Available Versions

# List all available DB versions
oci db autonomous-db-version list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --db-workload OLTP \
  --output table

# Check features available in specific version
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '.data["db-version"]'

Version-Specific Feature Matrix

Version-specific features (enabled by upgrading to version):

19c:
- Standard Oracle Database features
- Basic JSON support

21c:
- Property Graphs (CREATE PROPERTY GRAPH)
- Blockchain Tables (CREATE BLOCKCHAIN TABLE)
- Enhanced JSON (JSON_VALUE, JSON_QUERY)

23ai:
- JSON Relational Duality Views
- AI Vector Search (VECTOR data type, VECTOR_DISTANCE)
- SELECT AI (natural language queries)
- SQL Domains (domain data types)
- Annotations (metadata tags)

26ai:
- JavaScript Stored Procedures
- True Cache (application-consistent read cache)
- Enhanced vector search (hybrid search)
- All 23ai features

IMPORTANT: Features are enabled by database version, not OCI CLI flags.
To use 23ai features, upgrade database to version "23ai".

Upgrade Path Example

# Check current version
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["db-version"]'

# 1. Create clone for testing
oci db autonomous-database create-from-clone \
  --source-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "test-23ai-upgrade" \
  --db-name "TEST23AI" \
  --clone-type FULL \
  --wait-for-state AVAILABLE

# 2. Upgrade clone to 23ai
oci db autonomous-database update \
  --autonomous-database-id <clone-id> \
  --db-version "23ai" \
  --wait-for-state AVAILABLE

# 3. Test application with 23ai features

# 4. If successful, upgrade production
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --db-version "23ai" \
  --wait-for-state AVAILABLE

# CRITICAL: Cannot downgrade! Always test in clone first.

Troubleshooting

Get ADB State and Issues

# Check lifecycle state
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["lifecycle-state"]'

# Possible states:
# PROVISIONING, AVAILABLE, STOPPING, STOPPED, STARTING,
# TERMINATING, TERMINATED, UNAVAILABLE, RESTORE_IN_PROGRESS,
# BACKUP_IN_PROGRESS, SCALE_IN_PROGRESS, UPGRADE_IN_PROGRESS

Common Errors

Insufficient Quota

# Error: "Service limit exceeded for resource autonomous-database"
# Check quota:
oci limits quota list --compartment-id ocid1.tenancy.oc1..xxx

# Request increase via console or support ticket

Invalid Parameter

# Error: "InvalidParameter: cpu-core-count must be between 1 and 128"
# Check valid ranges:
oci db autonomous-database create --generate-param-json-input

Concurrent Operation

# Error: "ConflictingOperationException: Another operation is in progress"
# Wait for current operation to complete:
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["lifecycle-state"]'

# Poll until state returns to AVAILABLE

Scripting Patterns

Batch Operations with JQ

# Scale all dev ADBs to 1 ECPU
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --all \
  | jq -r '.data[] | select(.["display-name"] | startswith("dev-")) | .id' \
  | while read adb_id; do
      echo "Scaling $adb_id to 1 ECPU"
      oci db autonomous-database update \
        --autonomous-database-id "$adb_id" \
        --cpu-core-count 1 \
        --wait-for-state AVAILABLE
    done

Get Cost Summary

# Calculate total monthly cost for all ADBs in compartment
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --all \
  | jq -r '.data[] | select(.["lifecycle-state"] != "TERMINATED") | [
    .["display-name"],
    .["cpu-core-count"],
    .["data-storage-size-in-tbs"],
    .["license-model"],
    (.["cpu-core-count"] * 0.36 * 730 + .["data-storage-size-in-tbs"] * 1000 * 0.025)
  ] | @tsv' \
  | awk '{print $1, "\t", $2, "ECPU\t", $3, "TB\t$" $5 "/month"}'

Best Practices

Always Use --wait-for-state

# ✅ GOOD - waits for operation to complete
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --cpu-core-count 4 \
  --wait-for-state AVAILABLE

# ❌ BAD - returns immediately, operation may fail silently
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --cpu-core-count 4

Use JQ for JSON Parsing

# ✅ GOOD - robust JSON parsing
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["display-name"]'

# ❌ BAD - fragile grep/sed parsing
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | grep display-name | sed 's/.*: "\(.*\)".*/\1/'

Store OCIDs in Variables

# ✅ GOOD - reusable, readable
COMPARTMENT_ID="ocid1.compartment.oc1..xxx"
ADB_ID="ocid1.autonomousdatabase.oc1..xxx"

oci db autonomous-database get \
  --autonomous-database-id "$ADB_ID"

# ❌ BAD - error-prone, hard to maintain
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx

When to Use OCI CLI

Use OCI CLI when you need to:

• Provision or delete ADB instances
• Scale ECPUs or storage
• Create backups or clones
• Download wallets
• Change configuration (auto-scaling, license type)
• Batch operations across multiple ADBs

Don't use OCI CLI for:

• SQL queries (use SQLcl instead - see sqlcl-workflows.md)
• Performance troubleshooting (use SQLcl + v$sql)
• Cost calculations (exact formulas in main SKILL.md)

SQLcl Workflows for ADB Operations

SQLcl is Oracle's command-line SQL tool. Use it directly via Bash for database operations.

Connection Patterns

Connect to ADB (with wallet)

# Set wallet location
export TNS_ADMIN=/path/to/wallet

# Connect
sql admin/password@adb_high

Common Connection Services

• adb_high: Low latency, high concurrency (OLTP)
• adb_medium: Balanced (reporting, batch jobs)
• adb_low: Highest parallelism (background tasks, ETL)

Performance Analysis Workflows

Find Top SQL by Elapsed Time

sql admin/password@adb_high <<EOF
SET PAGESIZE 50
SET LINESIZE 200

SELECT sql_id,
       ROUND(elapsed_time/executions/1000, 2) AS avg_ms,
       executions,
       SUBSTR(sql_text, 1, 80) AS sql_text
FROM v\$sql
WHERE executions > 0
  AND last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
EXIT;
EOF

Get Execution Plan for SQL_ID

sql admin/password@adb_high <<EOF
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
EXIT;
EOF

Check Wait Events

sql admin/password@adb_high <<EOF
SELECT event,
       ROUND(time_waited_micro/1000000, 2) AS wait_sec,
       total_waits
FROM v\$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;
EXIT;
EOF

Schema Discovery

List Large Tables

sql admin/password@adb_high <<EOF
SELECT table_name,
       num_rows,
       ROUND(blocks * 8192 / 1024 / 1024, 2) AS size_mb
FROM user_tables
WHERE num_rows > 0
ORDER BY num_rows DESC
FETCH FIRST 20 ROWS ONLY;
EXIT;
EOF

Get Table DDL

sql admin/password@adb_high <<EOF
SET LONG 100000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE', 'ORDERS') FROM DUAL;
EXIT;
EOF

SQL Tuning Workflow

Create SQL Tuning Task

sql admin/password@adb_high <<EOF
DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => '&sql_id',
    task_name => 'tune_slow_query'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
  DBMS_OUTPUT.PUT_LINE('Tuning task created: ' || task_name);
END;
/

-- Get recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_slow_query') FROM DUAL;
EXIT;
EOF

Data Operations

Export Table (Data Pump)

# Create directory (one-time setup)
sql admin/password@adb_high <<EOF
CREATE DIRECTORY export_dir AS '/tmp/exports';
GRANT READ, WRITE ON DIRECTORY export_dir TO ADMIN;
EXIT;
EOF

# Export
expdp admin/password@adb_high \
  tables=ORDERS \
  directory=export_dir \
  dumpfile=orders.dmp \
  logfile=orders_export.log

Import Table

impdp admin/password@adb_high \
  tables=ORDERS \
  directory=export_dir \
  dumpfile=orders.dmp \
  logfile=orders_import.log \
  table_exists_action=REPLACE

Monitoring

Check Active Sessions

sql admin/password@adb_high <<EOF
SELECT sid,
       serial#,
       username,
       status,
       sql_id,
       event
FROM v\$session
WHERE status = 'ACTIVE'
  AND username IS NOT NULL
ORDER BY sid;
EXIT;
EOF

Find Blocking Sessions

sql admin/password@adb_high <<EOF
SELECT blocking_session,
       sid AS blocked_sid,
       username,
       event,
       seconds_in_wait
FROM v\$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;
EXIT;
EOF

Best Practices

Use Heredoc for Multi-Line SQL

# GOOD - heredoc preserves formatting
sql admin/password@adb_high <<EOF
SELECT *
FROM orders
WHERE status = 'PENDING';
EXIT;
EOF

Set Output Formatting

sql admin/password@adb_high <<EOF
SET PAGESIZE 100       -- Rows per page
SET LINESIZE 200       -- Characters per line
SET FEEDBACK ON        -- Show "N rows selected"
SET TIMING ON          -- Show execution time
SET SQLFORMAT ANSICONSOLE  -- Color output

-- Your query here
EXIT;
EOF

Silent Mode (Scripts)

sql -S admin/password@adb_high <<EOF
-- Suppress banner and prompts
SELECT COUNT(*) FROM orders;
EXIT;
EOF

Common Errors

Wallet Not Found

# Error: ORA-29024: Certificate validation failure
# Fix: Set TNS_ADMIN
export TNS_ADMIN=/path/to/wallet_dir

Connection Timeout

# Error: ORA-12170: TNS:Connect timeout occurred
# Check: Network connectivity, service name
tnsping adb_high

Insufficient Privileges

# Error: ORA-01031: insufficient privileges
# Fix: Grant required privileges
sql admin/password@adb_high <<EOF
GRANT SELECT ON v\$sql TO app_user;
EXIT;
EOF

When to Use SQLcl

Use SQLcl when you need to:

• Execute ad-hoc SQL queries to troubleshoot issues
• Get execution plans for slow SQL_ID
• Check current wait events or active sessions
• Export/import data for backups or migrations
• Generate DDL for schema objects
• Run SQL tuning tasks

Don't use SQLcl for:

• Bulk operations (use Data Pump instead)
• Programmatic automation (use OCI CLI for ADB management)
• Long-running queries (connection may timeout)