acedergren/oracle-dba-skill
Overview
This skill provides expert Oracle DBA and DevOps guidance for managing Autonomous Database (ADB) on Oracle Cloud Infrastructure. It covers performance tuning, backup and recovery, security (TDE, Database Vault, Data Safe), HA/DR, OCI CLI automation, and AI-assisted operations with Oracle MCP servers. Supported database versions include 19c, 21c, 23ai, and 26ai.
How this skill works
The skill inspects ADB configuration, performance metrics, and schema metadata using SQL/PLSQL patterns and MCP server tools. It recommends concrete OCI CLI commands and SQL snippets for scaling, backup/restore, and security configuration. For complex diagnostics it guides AWR/ADDM analysis and orchestrates workflows across MCP tools for query execution, schema discovery, and documentation lookup.
When to use it
- Managing Autonomous Database instances (Shared, Dedicated, Free Tier)
- Optimizing SQL and PL/SQL or diagnosing slow queries
- Configuring encryption, auditing, and Database Vault controls
- Implementing HA/DR strategies like Autonomous Data Guard and PITR
- Automating tasks with OCI CLI or integrating AI via Oracle MCP servers
- Creating refreshable clones, performing point-in-time restores, or scaling compute/storage
Best practices
- Always use bind variables and FETCH FIRST for pagination to avoid hard parses and excessive memory
- Collect AWR snapshots and use ADDM to prioritize tuning efforts before changing schema or indexes
- Automate routine tasks (start/stop, scale, backups) with OCI CLI and version-controlled scripts
- Enforce encryption (TDE) and unified auditing; use Data Safe for sensitive data discovery and masking
- Test DR and restore processes regularly using refreshable clones or PITR to validate recovery SLAs
- Limit console dependency: use SQLcl or Cloud Shell for reliable connections when wallet sync is delayed
Example use cases
- Tune a top-consuming SQL: run v$sql analysis, capture AWR, apply bind-aware rewrites and baselines
- Perform scheduled scaling and pre-upgrade manual backup via OCI CLI, then validate with a refreshable clone
- Enable TDE and configure unified auditing with an audit policy for sensitive schemas
- Set up cross-region Autonomous Data Guard and test failover using documented switchover/failover steps
- Use MCP SQLcl server to list connections, connect, run diagnostic queries, and fetch execution plans
- Implement vector similarity search on 23ai/26ai using DBMS_VECTOR or VECTOR_DISTANCE for AI retrieval use cases
FAQ
Point-in-time recovery is limited by the configured retention (up to 95 days for ADB). For longer retention, implement cross-region backup replication or export backups to object storage.
When should I use OCI CLI versus the Console?
Use OCI CLI for repeatable automation, scripting, and CI/CD integration. Use Console for ad hoc visual tasks; be aware some new PDBs or metadata changes may take time to appear in the UI.
