Oracle DBA

Troubleshooting with OCI CLI: Practical Examples

1. Checking Database System Status

When users complain about slow connections or failures, the first step is to verify if the DB system is healthy.

oci db system list --compartment-id ocid1.compartment.oc1..aaaaexample

Sample Output

{
  "data": [
    {
      "availability-domain": "kIdk:AP-MUMBAI-1-AD-1",
      "backup-subnet-id": null,
      "compartment-id": "ocid1.compartment.oc1..aaaaexample",
      "cpu-core-count": 4,
      "display-name": "Prod-DB-System",
      "id": "ocid1.dbsystem.oc1.ap-mumbai-1.abcdexample",
      "lifecycle-state": "AVAILABLE",
      "node-count": 2,
      "subnet-id": "ocid1.subnet.oc1.ap-mumbai-1.abcdexample",
      "version": "19.21.0.0.0",
      "hostname": "proddb01"
    }
  ]
}

Interpretation:

• If lifecycle-state is AVAILABLE, the DB system is up.
• If it shows STOPPED or MAINTENANCE_IN_PROGRESS, you can escalate or check maintenance windows.

2. Checking Database Instance Health

oci db database list --compartment-id ocid1.compartment.oc1..aaaaexample

Sample Output

{
  "data": [
    {
      "db-name": "PRODDB",
      "db-unique-name": "PRODDB_iad1xyz",
      "id": "ocid1.database.oc1.ap-mumbai-1.aaaexample",
      "lifecycle-state": "AVAILABLE",
      "db-version": "19.21.0.0.0"
    }
  ]
}

Interpretation:

• Ensure lifecycle-state is AVAILABLE.
• If FAILED or NEEDS_ATTENTION, check logs.

3. Checking Recent Database Backups

When a backup job fails, confirm the backup status:

oci db backup list --compartment-id ocid1.compartment.oc1..aaaaexample

Sample Output

{
  "data": [
    {
      "display-name": "ProdDB_Backup_2025_09_05",
      "id": "ocid1.dbbackup.oc1.ap-mumbai-1.aaaaexample",
      "database-id": "ocid1.database.oc1.ap-mumbai-1.abcdexample",
      "lifecycle-state": "ACTIVE",
      "time-start": "2025-09-05T02:12:00+00:00",
      "time-end": "2025-09-05T03:14:00+00:00",
      "type": "INCREMENTAL"
    }
  ]
}

Interpretation:

• If lifecycle-state = ACTIVE, backup completed successfully.
• If FAILED, check DB logs and retry.

4. Fetching Database Logs for Errors

Logs are crucial for troubleshooting. Example: fetching listener logs from Autonomous DB.

oci db autonomous-database get --autonomous-database-id ocid1.autonomousdatabase.oc1.ap-mumbai-1.abcdexample

Then, fetch logs via:

oci db autonomous-database generate-wallet \
--autonomous-database-id ocid1.autonomousdatabase.oc1.ap-mumbai-1.abcdexample \
--file wallet.zip \
--password 'YourPassword123'

With the wallet, you can connect using SQL*Plus and check alert logs:

SELECT * FROM V$DIAG_ALERT_EXT WHERE ORIGINATING_COMPONENT = 'rdbms' AND MESSAGE_TEXT LIKE '%ORA-%' ORDER BY ORIGINATING_TIMESTAMP DESC;

5. Checking Resource Utilization with Metrics

To verify if high CPU is causing slowness:

oci monitoring metric-data summarize-metrics-data \
--compartment-id ocid1.compartment.oc1..aaaaexample \
--namespace oci_database \
--query-text "CpuUtilization[1m].mean()"

Sample Output

{
  "data": [
    {
      "aggregated-datapoints": [
        {
          "timestamp": "2025-09-05T14:00:00+00:00",
          "value": 85.2
        },
        {
          "timestamp": "2025-09-05T14:01:00+00:00",
          "value": 88.6
        }
      ]
    }
  ]
}

Interpretation:

• CPU usage is above 85% → performance degradation likely.
• Recommend checking long-running queries (v$session, v$sql).