Database Management

OCI Database Management - 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 #4: Poor network segmentation (Landing Zone isolates database tier)
• ❌ Bad Practice #9: Public database endpoints (Security Zones enforce private subnets)
• ❌ Bad Practice #10: No monitoring (Landing Zone auto-configures database alarms)

This skill provides: ADB operations, troubleshooting, 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
• Database service CLI operations (oci db autonomous-database)
• Wallet configuration and connection string formats
• Latest ADB features (23ai, 26ai) and API changes

When OCI operations are needed: 1. Use exact CLI commands from skill references 2. Do NOT guess OCI CLI syntax or parameters 3. Do NOT assume API endpoint structures 4. Load oracle-dba skill for detailed ADB operations

What you DO know:

• Oracle Database internals (SQL, PL/SQL)
• General database administration principles
• Connection pooling and HA concepts

This skill bridges the gap by providing current OCI-specific database operations.

---

You are an OCI Database expert. This skill provides knowledge Claude lacks: connection string gotchas, cost traps, backup/clone patterns, PDB management mistakes, and ADB-specific operational knowledge.

NEVER Do This

❌ NEVER use wrong connection service name (performance/cost impact)

Autonomous Database provides 3 service names:
- HIGH: Dedicated CPU, highest performance, **3x cost of LOW**
- MEDIUM: Shared CPU, balanced
- LOW: Most sharing, cheapest, sufficient for OLTP

# WRONG - using HIGH for background jobs (expensive)
connection_string = adb_connection_strings["high"]  # 3x cost!

# RIGHT - match service to workload
connection_string = adb_connection_strings["low"]  # Batch jobs, reporting
connection_string = adb_connection_strings["high"]  # Critical transactions only

Cost impact: Using HIGH vs LOW for 24/7 connection pool: $220/month vs $73/month wasted (3x)

❌ NEVER assume stopped database = zero cost

# WRONG assumption - "stopped" database is free
Stop ADB at night to save costs

# Reality:
Stopped ADB charges:
- Storage: $0.025/GB/month continues
- Backups: Retention charges continue
- Compute: ZERO (only part that stops)

Example: 1TB ADB stopped 16 hrs/day
- Compute savings: $584/month × 67% = $391 saved
- Storage cost: $25.60/month (still charged)
- Net savings: $391/month (not $610 expected)

❌ NEVER ignore password complexity (ALWAYS fails)

OCI Database password requirements (strict regex):
- 12-30 characters
- 2+ uppercase, 2+ lowercase
- 2+ numbers, 2+ special (#-_)
- NO username in password
- NO repeating chars (aaa, 111)

# WRONG - fails validation
--admin-password "MyPass123"  # Only 1 special char, < 12 chars

# RIGHT - meets requirements
--admin-password "MyP@ssw0rd#2024"  # 2 upper, 2 lower, 2 num, 2 special, 16 chars

❌ NEVER confuse clone types (performance/cost consequences)

| Clone Type | Use Case | Cost | Refresh | When Source Deleted |
|------------|----------|------|---------|---------------------|
| **Full clone** | Prod → Dev (one-time) | Full ADB cost | Cannot refresh | Clone survives |
| **Refreshable clone** | Prod → Test (weekly refresh) | Storage only (~30%) | Manual refresh | Clone deleted |
| **Metadata clone** | Schema-only copy | Minimal | N/A | Clone survives |

# WRONG - full clone for dev environment that needs weekly prod data
oci db autonomous-database create-from-clone-adb \
  --clone-type FULL  # Wastes $500/month, no refresh capability

# RIGHT - refreshable clone for test environments
oci db autonomous-database create-refreshable-clone \
  # Costs $150/month storage, can refresh from prod weekly

Cost trap: Full clone for testing = $500/month vs $150/month for refreshable clone (70% savings)

❌ NEVER delete CDB without checking PDBs first

# WRONG - deletes Container Database with PDBs inside (data loss)
oci db database delete --database-id <cdb-ocid>
# All pluggable databases deleted with no warning!

# RIGHT - check for PDBs first
oci db pluggable-database list --container-database-id <cdb-ocid>
# If PDBs exist, decide: unplug, clone, or explicitly delete each

❌ NEVER use ADMIN user in application code (security risk)

# WRONG - application uses ADMIN credentials
app_config = {
    'user': 'ADMIN',
    'password': admin_password  # Full database control!
}

# RIGHT - create app-specific user with least privilege
CREATE USER app_user IDENTIFIED BY <password>;
GRANT CONNECT, RESOURCE TO app_user;
GRANT SELECT, INSERT, UPDATE ON app_schema.* TO app_user;
# ADMIN only for DBA tasks, never in application code

❌ NEVER forget Always-Free limits (scale-up fails)

Always-Free Autonomous Database limits:
- 1 OCPU max (cannot scale beyond)
- 20 GB storage max
- 1 database per tenancy per region
- NO private endpoints
- NO auto-scaling

# WRONG - trying to scale always-free database
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --cpu-core-count 2  # FAILS: Always-free max is 1 OCPU

# RIGHT - convert to paid tier first, THEN scale
oci db autonomous-database update \
  --autonomous-database-id <adb-ocid> \
  --is-free-tier false  # Convert to paid
# Now can scale to 2+ OCPUs

Connection String Gotchas

Wallet Connection Failure Decision Tree

"Connection refused" or "Wallet error"?
│
├─ Wallet file issues?
│  ├─ Check: TNS_ADMIN env variable set?
│  │  └─ export TNS_ADMIN=/path/to/wallet
│  ├─ Check: sqlnet.ora has correct wallet location?
│  │  └─ WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/path/to/wallet")))
│  └─ Check: Wallet password correct?
│
├─ Network security?
│  ├─ Private endpoint ADB?
│  │  └─ Check: Source IP in NSG/security list?
│  │  └─ Check: VPN/FastConnect for on-premises access?
│  └─ Public endpoint ADB?
│     └─ Check: Database whitelisted your IP? (Access Control List)
│
├─ Database state?
│  └─ Check: Lifecycle state = AVAILABLE (not STOPPED, UPDATING)?
│     └─ oci db autonomous-database get --autonomous-database-id <ocid> --query 'data."lifecycle-state"'
│
└─ Service name wrong?
   └─ Check: Using correct service name from tnsnames.ora?
      └─ HIGH: <dbname>_high
      └─ MEDIUM: <dbname>_medium
      └─ LOW: <dbname>_low

Service Name Selection (Cost vs Performance)

Example: 2 OCPU ADB

• HIGH: 2 concurrent queries max, $584/month
• MEDIUM: 4 concurrent queries, $584/month
• LOW: 6 concurrent queries, $584/month (same cost, more concurrency)

Gotcha: HIGH doesn't cost more in ADB pricing, but uses more OCPU-hours if you scale based on load.

Cost Optimization with Exact Calculations

Stop vs Scale Down Decision

Scenario: Development ADB, 2 OCPUs, 1 TB storage, used 8 hrs/day weekdays only

Option 1: Stop when not in use (16 hrs/day + weekends)

Usage: 8 hrs/day × 5 days = 40 hrs/week (24% utilization)
Compute cost: $0.36/OCPU-hr × 2 × 40 × 4.3 weeks = $124/month
Storage cost: $0.025/GB/month × 1000 = $25/month
Total: $149/month

Option 2: Scale to 1 OCPU always-on

Compute cost: $0.36/OCPU-hr × 1 × 730 hrs = $263/month
Storage cost: $25/month
Total: $288/month

Winner: Stop/start saves $139/month (48% savings)

License Model Impact

Scenario: 4 OCPU ADB, 24/7 production

• License Included: $0.36 × 4 × 730 = $1,051/month
• BYOL: $0.18 × 4 × 730 = $526/month
• Savings: $525/month ($6,300/year) if you have licenses

Gotcha: BYOL requires proof of licenses if audited

Auto-Scaling Cost Control

# DANGER - unbounded auto-scaling
resource "oci_database_autonomous_database" "prod" {
  cpu_core_count = 2
  is_auto_scaling_enabled = true  # Can scale to 3× (6 OCPUs!)
}

# Cost: 2 OCPUs × $0.36 × 730 = $526/month baseline
# If auto-scales to 6 OCPUs during peak: $1,578/month (3× surprise bill!)

# SAFER - set scaling limit
# (Not available via API, must set in console: Manage Scaling → Max OCPU count)

Best practice: Set max OCPU = 2× baseline to control costs (2 OCPU → max 4 OCPU)

Backup and Clone Patterns

Automatic vs Manual Backup Retention

Automatic backups (free):

• Retention: 60 days default (configurable 1-60 days)
• Frequency: Daily incremental
• Cost: Included in ADB storage cost
• Gotcha: Deleting ADB deletes automatic backups after retention period

Manual backups:

• Retention: Until you delete them
• Cost: $0.025/GB/month (same as storage)
• Use case: Long-term retention (compliance, legal hold)

Cost trap:

Scenario: 1 TB ADB, keep 2 years of backups for compliance

Wrong assumption: Automatic backups are free forever
Reality: Automatic backups deleted 60 days after ADB deletion

Right approach: Manual backup before deleting ADB
Cost: $0.025/GB × 1000 GB × 24 months = $600 for 2-year retention

Clone vs Refreshable Clone Decision

Gotcha: Refreshable clone deleted when source ADB deleted - no warning!

Best practice:

• Dev environment (permanent): Full clone
• QA environment (weekly prod refresh): Refreshable clone
• Before prod migration: Full clone (survives source deletion)

PDB Management Gotchas

Hierarchy confusion:

DB System or Exadata
└─ Container Database (CDB)
   └─ Pluggable Database (PDB)  ← Application connects here
      └─ Schemas, tables, etc.

Critical: PDB connection string uses CDB host but PDB service name

# WRONG - trying to connect to CDB
sqlplus admin/pass@cdb-host:1521/ORCLCDB

# RIGHT - connect to PDB inside CDB
sqlplus app_user/pass@cdb-host:1521/PDB1

PDB lifecycle gotcha: Unplugging PDB doesn't delete data

# Unplug PDB → creates XML metadata file
oci db pluggable-database unplug --pdb-id <ocid>
# PDB still exists in storage, can re-plug elsewhere
# Charges continue until DELETE

Progressive Loading References

OCI Database Cloud Service CLI

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

• Creating or managing DB Systems (VM, RAC, Exadata)
• Configuring Data Guard for disaster recovery
• Patching and maintenance operations
• Backup and recovery procedures
• ExaDB-D and ExaDB-C@C operations

Do NOT load for:

• Autonomous Database operations (use oracle-dba skill)
• Connection troubleshooting (decision tree above)
• Cost calculations (tables above)

Official Oracle Documentation Sources

Primary References (30+ official sources scraped):

• Autonomous Database Serverless
• Database Cloud Service
• Exadata Database Service
• Data Guard Configuration

Note: Connection gotchas, password rules, and cost traps in this skill are derived from official Oracle docs

---

When to Use This Skill

• Connection issues: wallet errors, service name confusion, network troubleshooting
• Cost optimization: stop/start decisions, BYOL evaluation, auto-scaling limits
• Backup/clone: choosing clone type, retention planning, disaster recovery
• PDB management: hierarchy, connection strings, unplug/plug operations
• Password errors: complexity validation, ADMIN user restrictions
• Scaling: Always-Free limits, when to scale vs stop, cost calculations

OCI Database Cloud Service CLI Reference

Database System Operations

List Database Systems

# List all DB systems in compartment
oci db system list --compartment-id <compartment-ocid>

# List with filters
oci db system list --compartment-id <compartment-ocid> \
  --lifecycle-state AVAILABLE \
  --display-name "prod-*"

Create Database System

# Create VM DB System
oci db system launch \
  --compartment-id <compartment-ocid> \
  --availability-domain <ad-name> \
  --subnet-id <subnet-ocid> \
  --shape "VM.Standard2.4" \
  --cpu-core-count 4 \
  --database-edition "ENTERPRISE_EDITION" \
  --admin-password "<secure-password>" \
  --db-name "MYDB" \
  --db-version "19.0.0.0" \
  --display-name "prod-db-1" \
  --hostname "prod-db-1" \
  --initial-data-storage-size-in-gb 256 \
  --node-count 1 \
  --ssh-authorized-keys-file ~/.ssh/id_rsa.pub

# Create RAC DB System (2 nodes)
oci db system launch \
  --compartment-id <compartment-ocid> \
  --availability-domain <ad-name> \
  --subnet-id <subnet-ocid> \
  --shape "VM.Standard2.8" \
  --cpu-core-count 8 \
  --cluster-name "prodrac" \
  --database-edition "ENTERPRISE_EDITION_EXTREME_PERFORMANCE" \
  --admin-password "<secure-password>" \
  --db-name "RACDB" \
  --db-version "19.0.0.0" \
  --display-name "prod-rac-cluster" \
  --hostname "prod-rac" \
  --initial-data-storage-size-in-gb 512 \
  --node-count 2 \
  --ssh-authorized-keys-file ~/.ssh/id_rsa.pub

Scale Database System

# Scale CPU (online for Flex shapes)
oci db system update \
  --db-system-id <db-system-ocid> \
  --cpu-core-count 8

# Scale storage (online)
oci db system update \
  --db-system-id <db-system-ocid> \
  --data-storage-size-in-gbs 512

Database Operations

# List databases in a DB system
oci db database list \
  --compartment-id <compartment-ocid> \
  --db-system-id <db-system-ocid>

# Create additional database in existing system
oci db database create \
  --db-system-id <db-system-ocid> \
  --admin-password "<secure-password>" \
  --db-name "NEWDB" \
  --db-version "19.0.0.0"

# Delete database (CAUTION)
oci db database delete \
  --database-id <database-ocid> \
  --perform-final-backup true

Backup and Recovery

Manual Backups

# Create manual backup
oci db backup create \
  --database-id <database-ocid> \
  --display-name "pre-upgrade-backup"

# List backups
oci db backup list \
  --compartment-id <compartment-ocid> \
  --database-id <database-ocid>

# Restore from backup
oci db database restore \
  --database-id <database-ocid> \
  --latest true

# Restore to point-in-time
oci db database restore \
  --database-id <database-ocid> \
  --timestamp "2024-01-15T10:30:00.000Z"

Automatic Backup Configuration

# Enable automatic backups
oci db database update \
  --database-id <database-ocid> \
  --auto-backup-enabled true \
  --recovery-window-in-days 30

# Configure backup destination (custom)
oci db database update \
  --database-id <database-ocid> \
  --auto-backup-enabled true \
  --backup-destination '[{"type":"OBJECT_STORE"}]'

Data Guard Configuration

Enable Data Guard

# Create standby database
oci db data-guard-association create \
  --database-id <primary-database-ocid> \
  --creation-type "NewDbSystem" \
  --database-admin-password "<password>" \
  --protection-mode "MAXIMUM_PERFORMANCE" \
  --transport-type "ASYNC" \
  --availability-domain <standby-ad> \
  --display-name "standby-db-1" \
  --hostname "standby-db-1" \
  --subnet-id <standby-subnet-ocid>

# List Data Guard associations
oci db data-guard-association list \
  --database-id <database-ocid>

Switchover and Failover

# Switchover (planned, no data loss)
oci db data-guard-association switchover \
  --database-id <primary-database-ocid> \
  --data-guard-association-id <association-ocid> \
  --database-admin-password "<password>"

# Failover (emergency, potential data loss)
oci db data-guard-association failover \
  --database-id <standby-database-ocid> \
  --data-guard-association-id <association-ocid> \
  --database-admin-password "<password>"

# Reinstate old primary as standby
oci db data-guard-association reinstate \
  --database-id <new-standby-database-ocid> \
  --data-guard-association-id <association-ocid> \
  --database-admin-password "<password>"

Patching and Maintenance

View Available Patches

# List available patches
oci db patch list \
  --db-system-id <db-system-ocid>

# Get patch details
oci db patch get \
  --patch-id <patch-ocid>

Apply Patches

# Apply patch to DB system
oci db db-system-patch-history-entry list \
  --db-system-id <db-system-ocid>

# Check patch history
oci db patch-history list \
  --db-system-id <db-system-ocid>

ExaDB-D and ExaDB-C@C Operations

Exadata Infrastructure

# List Exadata infrastructures
oci db exadata-infrastructure list \
  --compartment-id <compartment-ocid>

# Get Exadata infrastructure details
oci db exadata-infrastructure get \
  --exadata-infrastructure-id <infra-ocid>

VM Clusters

# List VM clusters
oci db vm-cluster list \
  --compartment-id <compartment-ocid>

# Create VM cluster
oci db vm-cluster create \
  --compartment-id <compartment-ocid> \
  --exadata-infrastructure-id <infra-ocid> \
  --vm-cluster-network-id <network-ocid> \
  --cpu-core-count 8 \
  --display-name "prod-vmcluster" \
  --gi-version "19.0.0.0" \
  --ssh-public-keys-file ~/.ssh/id_rsa.pub

Common Troubleshooting

Connection Issues

# Verify DB system is running
oci db system get --db-system-id <ocid> --query "data.\"lifecycle-state\""

# Check listeners
oci db node list --compartment-id <id> --db-system-id <db-system-ocid>

# Verify NSG rules
oci network nsg-security-rule list --nsg-id <nsg-ocid>

Performance Diagnostics

# Get DB system metrics
oci monitoring metric-data summarize-metrics-data \
  --compartment-id <compartment-ocid> \
  --namespace "oci_database" \
  --query-text 'CpuUtilization[1m]{resourceId="<db-system-ocid>"}.mean()'

Cost Optimization Commands

# Find stopped DB systems (still incurring storage costs)
oci db system list --compartment-id <id> --lifecycle-state STOPPED

# List DB systems by shape for right-sizing analysis
oci db system list --compartment-id <id> --query "data[].{Name:\"display-name\",Shape:shape,CPUs:\"cpu-core-count\"}"

# Check backup storage usage
oci db backup list --compartment-id <id> --query "data[].{DB:\"database-id\",Size:\"database-size-in-gbs\",Type:type}"