OCI Resource Analytics: Your Cloud Environment as a Queryable Database

Why this is different from every other CMDB

Every cloud architect eventually builds the same thing: a script that calls the OCI CLI, dumps resource JSON into a table, and sets up a cron job to keep it fresh. The problem is never the initial scrape — it's the drift. A compute instance gets terminated at 2am, a new subnet is carved out during an incident, an Exadata rack moves compartments. By the time anyone looks at the report, reality has diverged.

OCI Resource Analytics solves the staleness problem at the source. Instead of polling the control plane on a schedule, the service hooks directly into the same Kafka event stream that powers OCI's internal event bus. Resource state changes are captured as they happen and written into an Autonomous Data Warehouse that lives inside your tenancy. You own the data, you control the IAM, you pay for the ADW — and it's never more than a minute stale for same-region resources.

The critical architecture distinction

The ADW provisioned by Resource Analytics is not a shared Oracle-managed backend. It is a dedicated 4-ECPU Autonomous AI Lakehouse database in your own tenancy compartment. You can create new schemas in it, grant access to your own users, load external data into it, and build custom analytics on top of it. Oracle writes OCIRA metadata into it; you own everything else.

AI intelligence layerSelect AI · vector search · in-DB ML · SQLcl MCP

Analytics & application layerOAC dashboards · Graph Studio · APEX apps

Extensible data layer (your tenancy ADW)OCIRA.* views + custom schemas for enrichment

Event ingestion layerKafka CDC streams · <1 min same-region · <5 min cross-region

Architecture: what gets provisioned

Enabling the service creates three discrete, billable components in your tenancy — nothing is silently hosted on Oracle's backend.

Autonomous AI Lakehouse

4 ECPUs. Hosts the OCIRA.* schema with dimension views for every supported resource type. Supports vector search, in-DB ML, and Select AI natural language translation.

Oracle Analytics Cloud

2 OCPUs. Pre-configured with 10+ domain dashboards: Compute, Network, Database, Backup, OKE, Object Storage, Load Balancer, Logging, Integration Cloud. OAC AI Agent included.

Graph Studio

Embedded in the ADW. Six pre-built notebooks model Compute/Volume, Network, DBaaS, LBaaS, NLB, and Subscription hierarchies as interactive property graphs — no graph modelling required.

4

ECPU ADW baseline

<1m

same-region sync

10+

pre-built dashboards

6

graph notebooks

Supported resource types include compute instances, networking resources (VCN, subnet, NSG, DRG, local peering gateways), block storage, boot volumes, load balancers, network load balancers, tenancies, accounts, subscriptions, Autonomous Databases, DB Systems, and Exadata infrastructure.

The OCIRA schema: what you can query

Oracle exposes resource metadata through named dimension views in the OCIRA schema. These are stable interfaces — not base tables — meaning Oracle manages the underlying denormalization while you query clean, versioned view names. Every view exposes DEFINED_TAGS and FREEFORM_TAGS as JSON columns, queryable with standard JSON_VALUE / JSON_TABLE functions.

Compute

COMPUTE_INSTANCE_DIM_V
COMPUTE_VOLUME_ATTACHMENT_DIM_V
COMPUTE_BOOT_VOLUME_ATTACHMENT_DIM_V

Block storage

BLOCK_VOLUME_BACKUP_DIM_V
BLOCK_BOOT_VOLUME_BACKUP_DIM_V
BLOCK_VOLUME_DIM_V

Databases

AUTONOMOUS_DATABASE_DIM_V
ADB_BACKUP_DIM_V
DB_SYSTEM_DIM_V
EXADATA_INFRA_DIM_V

Networking

VCN_DIM_V
SUBNET_DIM_V
NETWORK_SECURITY_GROUP_DIM_V
DRG_DIM_V

Load balancers

LOAD_BALANCER_DIM_V
NLB_DIM_V
BACKEND_SET_DIM_V

Tenancy & IAM

TENANCY_DIM_V
COMPARTMENT_DIM_V
SUBSCRIPTION_DIM_V

Tag access pattern

All OCIRA views surface tags as JSON. Extract cost-centre, environment, or owner tags without ETL: JSON_VALUE(DEFINED_TAGS, '$.CostCenter.code') works inline in any query. This is particularly useful for compliance GROUP BY and allocation reporting without maintaining a separate tag dimension table.

SQL scenarios: 6 real operational queries

Because resource metadata lives in a full Autonomous Database, every SQL capability is available: analytic functions, CTEs, JSON functions, PIVOT, REGEXP, and OML. The queries below address the six operational questions that come up most consistently in enterprise OCI environments.

Full backup posture: compute instances × block + boot volumes

The classic cross-entity audit. Block volumes, boot volumes, and instances are separate entity types — this UNION ALL ties them into a single governance report with days-since-backup calculated inline.

OCIRA backup coverage — all volume types per instanceSQL

SELECT
    i.DISPLAY_NAME          AS instance_name,
    i.LIFECYCLE_STATE       AS state,
    va.DISPLAY_NAME         AS volume_name,
    va.DEVICE,
    bv.LIFECYCLE_STATE      AS backup_state,
    bv.TIME_CREATED         AS backup_time,
    bv.SIZE_IN_GBS          AS size_gb,
    TRUNC(SYSDATE - bv.TIME_CREATED) AS days_since_backup
FROM  OCIRA.COMPUTE_INSTANCE_DIM_V              i
LEFT JOIN OCIRA.COMPUTE_VOLUME_ATTACHMENT_DIM_V va  ON i.ID = va.INSTANCE_ID
LEFT JOIN OCIRA.BLOCK_VOLUME_BACKUP_DIM_V       bv  ON va.VOLUME_ID = bv.VOLUME_ID
UNION ALL
SELECT
    i.DISPLAY_NAME, i.LIFECYCLE_STATE,
    bva.DISPLAY_NAME,
    'BOOT'                  AS device,
    bbv.LIFECYCLE_STATE,
    bbv.TIME_CREATED,
    bbv.SIZE_IN_GBS,
    TRUNC(SYSDATE - bbv.TIME_CREATED)
FROM  OCIRA.COMPUTE_INSTANCE_DIM_V                     i
LEFT JOIN OCIRA.COMPUTE_BOOT_VOLUME_ATTACHMENT_DIM_V  bva  ON i.ID = bva.INSTANCE_ID
LEFT JOIN OCIRA.BLOCK_BOOT_VOLUME_BACKUP_DIM_V         bbv  ON bva.BOOT_VOLUME_ID = bbv.BOOT_VOLUME_ID
ORDER BY instance_name, backup_time DESC;

Graph Studio: when SQL is the wrong tool

There is a class of infrastructure question that relational SQL answers badly: "show me everything that depends on this subnet," "trace the path from this compute instance through the network to the internet gateway," "enumerate all databases in the subtree rooted at this compartment." These are graph traversal questions. Oracle Graph Studio — embedded in the same ADW — handles them natively.

Graph Studio constructs a virtual property graph over the OCIRA relational views. No data is copied. The graph reflects the same sub-minute refresh cycle as the underlying dimension tables. Six pre-built notebooks ship with the service:

Compute & Volume explorer

Tenancy → compartment → instance → volume attachments → block/boot volumes. Full dependency chain, traversable in both directions.

Network topology explorer

VCN → subnets → NSGs → LPGs → DRGs. Understand connectivity paths and isolation domain boundaries without reading JSON route tables.

DBaaS explorer

DB Systems → ADB → Exadata Infrastructure → associated network subnets. Critical for change-impact analysis before maintenance windows.

LBaaS explorer

Load balancers → listeners → backend sets → individual backends. Trace traffic routing and spot misconfigured backend health configurations.

NLB explorer

Network Load Balancer topology at OSI Layer 4. Essential for environments using NLB for high-throughput, connection-preserving workloads.

Subscription hierarchy

Subscriptions → tenancies → compartment parent-child trees. One-glance governance view for multi-tenancy environments and policy inheritance verification.

Data enrichment: building the single pane of glass

The OCIRA schema answers "what exists and how is it configured." Operators need two more dimensions to work effectively: "how is it performing" and "what does it cost." Because the ADW is yours, you can load both into the same database and JOIN against the OCIRA views — without building a separate data platform.

Layer 1: OCI Ops Insights host and database metrics

CPU percentiles, memory consumption, I/O throughput, SQL execution counts, and wait event distributions flow from Ops Insights Host Insights and Database Insights. JOINed against OCIRA dimension views on OCID, this produces the configuration-plus-performance surface needed for root-cause analysis — "did this instance's shape change before CPU spiked?"

Layer 2: Oracle Enterprise Manager repository data

EM's repository database carries the deepest Oracle performance history available: AWR snapshots, Exadata cell statistics, ASM metadata, middleware telemetry, and patch history across on-premises and cloud. Replicating or exporting key EM repository tables into the enrichment schema unifies deep EM performance intelligence with cloud-native OCIRA metadata in a single queryable store — without requiring EM console access for ad-hoc queries. Candidate tables: MGMT$DB_DBNINSTANCEINFO, MGMT$DB_CPU_USAGE, MGMT$METRIC_DETAILS, ExaCC cell metrics.

Layer 3: OCI Usage Reports for FinOps analysis

Cost and usage data from OCI Usage Reports (available as CSV from Object Storage) can be loaded into a custom schema and JOINed on OCID to correlate cost-per-resource against configuration, owner tags, and utilization profile. This is the FinOps query surface most teams build manually — here it coexists with the rest of your observability data in a single database.

Integration dependency metrics

Beyond individual resource metrics, the enrichment model can include end-to-end service health by modelling dependency chains: an Integration Cloud instance calling an ADB, a load balancer fronting compute running middleware. Annotating each dependency link with its own health and latency metric shifts the operational view from isolated resource health to service health — the data model that actually matches how incidents propagate.

AI operations: MCP, pattern recognition, recommendations

The Autonomous AI Lakehouse underlying Resource Analytics supports vector search, in-database machine learning, and natural language SQL via Select AI. The most immediately actionable capability for operations teams is the SQLcl 25.2 MCP server.

SQLcl MCP server (Oracle SQLcl 25.2+)

The built-in MCP server exposes three tools — run-sql, list-connections, connect — to any MCP-compatible AI assistant: Claude, VS Code Copilot, OpenAI Codex, or a custom agent. The AI translates natural language to SQL, executes it against the ADW, and returns structured results. No custom integration code. IAM policies on the ADW govern access exactly as they would for any database user.

Three capability tiers, ordered by maturity:

Natural language queries — available now

Which Autonomous Databases in the finance compartment have not had a successful backup in the last 7 days?

Which compute instances are missing mandatory cost-center tags across all regions?

Show me the parent-child compartment hierarchy as a tree structure and flag compartments with no tags applied.

AI-driven pattern recognition — with enriched metrics

Analyze all DB Systems and flag any more than two patch levels behind their compartment peers.

Find block volumes with no attachments and no recent I/O activity — flag them as decommission candidates.

Identify databases whose backup frequency deviates significantly from their peer group in the same compartment.

Intelligent recommendations — with cost data enriched

Compare provisioned ECPU capacity versus 30-day average usage across all Autonomous Databases and recommend right-sizing opportunities.

Identify resources created more than 180 days ago with no tags, no recent activity, and no dependencies — flag for decommissioning review.

Do I have capacity constraints in VM cluster prod-exacc-01? If so, which DB systems are impacted and what is the provisioned vs used ECPU ratio?

Pre-built dashboards and APEX apps

For teams not ready to write queries directly, the pre-configured OAC instance provides immediate value. Ten domain dashboards are pre-built and pre-connected to the ADW with no setup required.

Resource Discovery

Cross-tenancy totals filterable by region, compartment, and resource type. Starting point for any environment audit.

Compute infrastructure

Shape distribution, lifecycle state, availability domain spread, instance age cohorts.

Database dashboard

ADB, DB System, and Exadata inventory with configuration details and backup status.

Backup coverage

Block volume, boot volume, and database backup coverage rates. Compliance by policy type, region, and compartment.

Tagging compliance

Mandatory tag coverage rates across all resource types. Highlights untagged resources for governance remediation.

OAC AI Agent

Conversational interface over the full OCIRA dataset, natively within Oracle Analytics Cloud. No separate tool or API key required.

For teams wanting a custom data application without hand-coding SQL pages, APEX Generative AI App Builder — embedded in the ADW — generates fully functional APEX applications from a natural language prompt describing the required screens, visualizations, and data model mappings. What previously required a sprint of development is now a prompt-engineering exercise.

SQL query patterns for DBAs

The metadata lands in a full Autonomous Database, every standard SQL capability is available: analytical functions, PIVOT, CTEs, JSON functions, REGEXP, spatial operators, and ML via OML. The OCIRA schema exposes dimension views rather than base tables — meaning Oracle handles the underlying denormalization and you query clean, stable interfaces.

Here are patterns representative of real operational questions:

Backup coverage audit — compute instances × block volumes × boot volumesSQL
-- Full backup posture: each compute instance with its most recent
-- block volume AND boot volume backup state in a single result set
SELECT
    i.DISPLAY_NAME        AS instance_name,
    i.LIFECYCLE_STATE     AS instance_state,
    va.DISPLAY_NAME       AS volume_name,
    va.DEVICE,
    bv.LIFECYCLE_STATE    AS backup_state,
    bv.TIME_CREATED       AS backup_time,
    bv.SIZE_IN_GBS        AS backup_size_gb,
    TRUNC(SYSDATE - bv.TIME_CREATED) AS days_since_backup
FROM  OCIRA.COMPUTE_INSTANCE_DIM_V i
LEFT JOIN OCIRA.COMPUTE_VOLUME_ATTACHMENT_DIM_V   va  ON i.ID = va.INSTANCE_ID
LEFT JOIN OCIRA.BLOCK_VOLUME_BACKUP_DIM_V          bv  ON va.VOLUME_ID = bv.VOLUME_ID
UNION ALL
SELECT
    i.DISPLAY_NAME,
    i.LIFECYCLE_STATE,
    bva.DISPLAY_NAME,
    'BOOT'               AS device,
    bbv.LIFECYCLE_STATE,
    bbv.TIME_CREATED,
    bbv.SIZE_IN_GBS,
    TRUNC(SYSDATE - bbv.TIME_CREATED)
FROM  OCIRA.COMPUTE_INSTANCE_DIM_V i
LEFT JOIN OCIRA.COMPUTE_BOOT_VOLUME_ATTACHMENT_DIM_V  bva  ON i.ID = bva.INSTANCE_ID
LEFT JOIN OCIRA.BLOCK_BOOT_VOLUME_BACKUP_DIM_V         bbv  ON bva.BOOT_VOLUME_ID = bbv.BOOT_VOLUME_ID
ORDER BY instance_name, backup_time DESC;

ADB backup gap detection — databases with no backup in last 7 daysSQL
-- Identify Autonomous Databases at risk: no backup within SLA window
SELECT
    adb.DISPLAY_NAME,
    adb.DB_WORKLOAD,
    adb.LIFECYCLE_STATE,
    adb.COMPARTMENT_ID,
    MAX(bk.TIME_ENDED) AS last_backup_completed,
    TRUNC(SYSDATE - MAX(bk.TIME_ENDED)) AS days_since_backup,
    CASE
        WHEN MAX(bk.TIME_ENDED) IS NULL        THEN 'NEVER_BACKED_UP'
        WHEN SYSDATE - MAX(bk.TIME_ENDED) > 7  THEN 'OVERDUE'
        ELSE 'COMPLIANT'
    END AS backup_status
FROM       OCIRA.AUTONOMOUS_DATABASE_DIM_V  adb
LEFT JOIN  OCIRA.ADB_BACKUP_DIM_V           bk   ON adb.ID = bk.AUTONOMOUS_DATABASE_ID
                                                  AND bk.LIFECYCLE_STATE = 'ACTIVE'
GROUP BY   adb.DISPLAY_NAME, adb.DB_WORKLOAD, adb.LIFECYCLE_STATE, adb.COMPARTMENT_ID
HAVING     MAX(bk.TIME_ENDED) IS NULL
           OR SYSDATE - MAX(bk.TIME_ENDED) > 7
ORDER BY   days_since_backup DESC NULLS FIRST;

Over-provisioning candidate detection — compute with <10% average CPUSQL
-- After enriching ADW with OCI Monitoring metrics (see §05),
-- correlate config × actual utilization for right-sizing candidates
SELECT
    ci.DISPLAY_NAME,
    ci.SHAPE,
    ci.OCPUS,
    ci.MEMORY_IN_GBS,
    ROUND(m.avg_cpu_pct, 2)   AS avg_cpu_utilization,
    ROUND(m.max_cpu_pct, 2)   AS peak_cpu_utilization,
    ci.REGION                  AS region,
    ci.DEFINED_TAGS            AS tags
FROM       OCIRA.COMPUTE_INSTANCE_DIM_V        ci
JOIN       your_schema.INSTANCE_METRICS_V      m   ON ci.ID = m.INSTANCE_ID
WHERE      ci.LIFECYCLE_STATE = 'RUNNING'
  AND      m.avg_cpu_pct < 10
  AND      m.observation_days >= 30
ORDER BY   m.avg_cpu_pct ASC;

OCI Resource Analytics is generally available. Provisioning creates a dedicated ADW, OAC instance, and Graph Studio environment entirely within your tenancy — no shared backend. The data is yours, the schema is extensible, and the SQL surface is unrestricted.

Oracle Applications R12.2.x| Exadata | OCI|Oracle GRC|ECC

Wednesday, May 13, 2026