Database Management

OCI Database Management

NEVER Do This

❌ NEVER use HIGH service name for non-critical workloads (3x cost trap)

ADB service names:
- HIGH:   Dedicated OCPU, 1× concurrency per OCPU, highest priority
- MEDIUM: Shared OCPU, 2× concurrency per OCPU
- LOW:    Most sharing, 3× concurrency per OCPU

# WRONG - using HIGH for background jobs
connection_string = adb_connection_strings["high"]  # 3x wasted OCPU-hours!

# RIGHT - match service to workload
connection_string = adb_connection_strings["low"]    # Batch, reporting, data loads
connection_string = adb_connection_strings["high"]   # Interactive OLTP only

Cost impact: Using HIGH vs LOW for connection pools wastes 3x OCPU allocation.

❌ NEVER assume stopped ADB = zero cost

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

Example: 1TB ADB, stopped 16 hrs/day
- Compute saved: ~67% of compute bill
- Storage still: $25/month
- Total savings are less than expected — plan accordingly

❌ NEVER guess ADB password complexity — it always fails validation

Requirements (strict):
- 12-30 characters
- 2+ uppercase, 2+ lowercase
- 2+ numbers, 2+ special chars (#-_ only)
- NO username substring
- NO repeating chars (aaa, 111)

# WRONG - fails validation
--admin-password "MyPass123"  # Too short, only 1 special char

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

❌ NEVER use full clone for test environments (70% cost waste)

| Clone Type        | Cost           | Refresh capability | When source deleted |
|-------------------|----------------|--------------------|---------------------|
| Full clone        | 100% of source | Cannot refresh     | Clone survives      |
| Refreshable clone | ~30% (storage) | Manual refresh     | Clone auto-deleted! |
| Metadata clone    | Minimal        | N/A                | Clone survives      |

# WRONG - full clone for QA needing weekly prod data
oci db autonomous-database create-from-clone-adb --clone-type FULL
# $500/month, no refresh capability

# RIGHT - refreshable clone for test environments
# $150/month storage only, refresh from prod weekly (70% savings)

Critical gotcha: Refreshable clone is silently deleted when source ADB is deleted — no warning.

❌ NEVER delete CDB without checking for PDBs first

# WRONG - deletes all PDBs with no warning
oci db database delete --database-id <cdb-ocid>

# RIGHT - check first
oci db pluggable-database list --container-database-id <cdb-ocid>
# Then explicitly unplug, clone, or delete each PDB

❌ NEVER forget Always-Free ADB limits

Always-Free limits:
- 1 OCPU max (scale-up fails)
- 20 GB storage max
- 2 ADBs total per TENANCY (not per region) — stopped ADBs count!
- NO private endpoints, NO auto-scaling

# To free a slot: must DELETE, not just STOP the ADB

Wallet Connection Failure Decision Tree

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

PDB Connection Gotcha

DB System or Exadata
└─ Container Database (CDB)
   └─ Pluggable Database (PDB)  ← Application connects HERE

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

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

Unplug gotcha: Unplugging PDB does NOT delete data — charges continue until you explicitly DELETE. Unplug only creates an XML metadata file for portability.

Cost Optimization

Stop vs Always-On Comparison

Stop/start is worth it for dev databases with predictable hours:

• 2 OCPU ADB, used 8 hrs/day weekdays: Stop saves ~48% vs always-on 1 OCPU
• Storage cost ($0.025/GB/month) continues regardless

License Model (High Impact)

4 OCPU production ADB 24/7: BYOL saves $525/month ($6,300/year) if you have licenses.

Auto-Scaling Cap (Missing from Console)

# DANGER - auto-scaling can 3x your bill silently
resource "oci_database_autonomous_database" "prod" {
  cpu_core_count            = 2
  is_auto_scaling_enabled   = true  # Scales to 6 OCPUs = 3x surprise cost!
}

Setting max OCPU cap is not available via API or Terraform — must be set in console under Manage Scaling. Set to 2x baseline to control costs.

Reference Files

Load `references/oci-dbcs-cli.md` when you need:

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

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}"