acedergren/oracle-dba

Oracle DBA & DevOps Skill

Expert Oracle Database administration and DevOps engineering for Autonomous Database (ADB) on Oracle Cloud Infrastructure.

Overview

This skill provides comprehensive guidance for:

• Production DBA: Performance tuning, backup/recovery, monitoring, patching
• Security DBA: TDE, Database Vault, Data Safe, unified auditing, SQL Firewall
• Cloud DBA: OCI operations, scaling, Data Guard, cost optimization

When to Use This Skill

• Managing Oracle Autonomous Database (Shared/Dedicated/Free Tier)
• Writing optimized SQL queries and PLSQL procedures
• Configuring database security and compliance
• Implementing high availability and disaster recovery
• Using Oracle MCP servers for AI-assisted database operations
• Automating database tasks with OCI CLI
• Troubleshooting performance issues with AWR/ADDM

Quick Reference

Connect to ADB via SQLcl

# Using wallet
sql admin@charlstn_high?TNS_ADMIN=/path/to/wallet

# Using Cloud Shell (no wallet needed)
sql -cloudconfig wallet.zip admin@adb_name_high

Common OCI CLI Commands

# List Autonomous Databases
oci db autonomous-database list --compartment-id $C

# Start/Stop ADB
oci db autonomous-database start --autonomous-database-id $ADB_ID
oci db autonomous-database stop --autonomous-database-id $ADB_ID

# Scale ECPU
oci db autonomous-database update --autonomous-database-id $ADB_ID \
  --compute-count 4

# Create manual backup
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "pre-upgrade-backup"

SQL Best Practices

-- Always use bind variables
SELECT * FROM users WHERE id = :user_id;

-- Use FETCH FIRST (not LIMIT)
SELECT * FROM orders ORDER BY created_at DESC FETCH FIRST 10 ROWS ONLY;

-- Vector similarity search (26ai)
SELECT id, content, VECTOR_DISTANCE(embedding, :query_vec, COSINE) AS score
FROM documents
ORDER BY score
FETCH FIRST 5 ROWS ONLY;

MCP Server Integration

This skill integrates with three Oracle MCP servers for AI-assisted database operations:

1. Oracle SQLcl MCP Server

Enables AI agents to execute SQL queries and manage database connections.

Key Tools: | Tool | Purpose | |------|---------| | list-connections | List available database connections | | connect | Connect to a database | | run-sql | Execute SQL statements | | schema-information | Get schema metadata |

Usage Pattern:

1. Call list-connections to see available connections
2. Call connect with connection name
3. Call run-sql to execute queries
4. Call disconnect when done

2. Oracle Database MCP Server (100+ Tools)

Comprehensive database management through MCP tools.

Tool Categories:

• Schema Discovery: list tables, columns, constraints, indexes
• Query Execution: run SQL, explain plans, execution stats
• Performance: AWR reports, session analysis, wait events
• Security: user management, privilege grants, audit settings
• Backup/Recovery: backup status, restore points, PITR

3. Oracle DB Documentation MCP Server

Search official Oracle documentation from within AI conversations.

Tool: | Tool | Purpose | |------|---------| | search_oracle_database_documentation | Search Oracle docs by phrase |

Workflow Decision Tree

Database Task Required
├── Performance Issue?
│   ├── Slow Query → references/sql-patterns.md (query optimization)
│   ├── High CPU/Wait → AWR/ADDM analysis via MCP tools
│   └── Scaling Needed → OCI CLI scale commands
├── Security Task?
│   ├── Encryption → references/adb-security.md (TDE)
│   ├── Access Control → Database Vault, Label Security
│   └── Auditing → Unified Audit, Data Safe
├── HA/DR Task?
│   ├── Standby Setup → references/adb-ha-dr.md (Autonomous Data Guard)
│   ├── Backup/Restore → Automatic backups, PITR (95 days)
│   └── Failover → Switchover/Failover procedures
└── Development Task?
    ├── SQL Query → references/sql-patterns.md
    ├── Vector Search → DBMS_VECTOR, AI Vector Search
    └── JSON Processing → JSON Relational Duality

ADB Feature Summary

Automatic Features (No DBA Action Required)

• Auto Indexing: Automatic index creation based on workload
• Auto Scaling: CPU scales 1-3x based on demand (when enabled)
• Auto Backup: Daily incremental, weekly full (60 days retention)
• Auto Patching: Security and bug fixes applied automatically
• Auto Tuning: SQL Plan Baselines, Segment Advisor

DBA-Managed Features

• Manual Scaling: Adjust base ECPU/storage via console or CLI
• Autonomous Data Guard: Enable cross-region standby
• Backup-Based DR: Cross-region backup replication
• Point-in-Time Recovery: Restore to any point (up to 95 days)
• Refreshable Clones: Read-only clones with auto-refresh

Version-Specific Features

| Feature | 19c | 21c | 23ai | 26ai | |---------|-----|-----|------|------| | JSON Duality | - | - | ✓ | ✓ | | AI Vector Search | - | - | ✓ | ✓ | | JavaScript Stored Procs | - | - | - | ✓ | | Select AI | - | - | ✓ | ✓ | | Property Graphs | - | ✓ | ✓ | ✓ | | True Cache | - | - | - | ✓ |

Common Operations

Performance Troubleshooting

-- Find top SQL by elapsed time (last hour)
SELECT sql_id, elapsed_time/1000000 AS elapsed_sec, executions,
       ROUND(elapsed_time/executions/1000,2) AS avg_ms
FROM v$sql
WHERE executions > 0 AND last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Check session wait events
SELECT event, total_waits, 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;

-- Generate AWR report (requires DBA privilege)
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
  l_dbid => (SELECT dbid FROM v$database),
  l_inst_num => 1,
  l_bid => :begin_snap_id,
  l_eid => :end_snap_id
));

Security Configuration

-- Enable TDE for tablespace (auto-enabled in ADB)
ALTER TABLESPACE users ENCRYPTION USING 'AES256' ENCRYPT;

-- Create read-only user
CREATE USER report_user IDENTIFIED BY :password;
GRANT CREATE SESSION TO report_user;
GRANT SELECT ON schema.table TO report_user;

-- Enable unified auditing for schema
CREATE AUDIT POLICY audit_sales_schema
  ACTIONS ALL ON sales.orders, ALL ON sales.customers;
AUDIT POLICY audit_sales_schema;

Backup and Recovery

# Restore to point in time (OCI Console or CLI)
oci db autonomous-database restore \
  --autonomous-database-id $ADB_ID \
  --timestamp "2024-01-15T10:30:00Z"

# Create refreshable clone
oci db autonomous-database create-clone \
  --source-autonomous-database-id $SOURCE_ID \
  --compartment-id $C \
  --clone-type REFRESHABLE_CLONE \
  --db-name "dev_clone" \
  --display-name "Development Clone"

Known Issues and Workarounds

PDB Visibility Delay

• Issue: New PDBs don't appear in console for several hours
• Workaround: PDBs are operational via SQL; console sync is eventual

TDE Wallet Migration (12c R1/R2)

• Issue: File-based to customer-managed key migration fails
• Workaround: Use dbaascli --skip_patch_check true

Backup to Object Storage Failures

• Issue: SSL certificate changes cause RMAN backup failures
• Workaround: Update Oracle Database Cloud Backup Module

Resources

For detailed reference information, see:

• references/mcp-tools.md - Complete MCP server tool catalog
• references/oci-cli.md - OCI CLI commands for Autonomous Database
• references/adb-security.md - Security configuration (TDE, Vault, Data Safe)
• references/adb-ha-dr.md - High availability and disaster recovery
• references/sql-patterns.md - SQL/PLSQL patterns optimized for ADB

External Documentation

• Oracle Database 26ai Documentation
• Autonomous Database Documentation
• OCI CLI Reference
• Oracle MCP Servers
• SQLcl Documentation