Oracle AI Vector Search Explained

Oracle AI Vector Search Explained Simply

One Picture to Remember Everything

Oracle AI Database 26ai | Embeddings | HNSW | IVF | RAG

1. Why Should Oracle DBAs Learn Vector Search?

Imagine you are an Oracle DBA managing thousands of production incidents. Your team has previously resolved RMAN failures, ASM issues, Data Guard synchronization problems, and Oracle EBS login errors.

One day, another DBA asks:

"How did we resolve the listener issue after the last environment refresh?"

Traditional SQL keyword searches may miss related incidents when different terminology is used.

AI Vector Search helps find records based on similar meaning, even when the words are different.

2. Complete Oracle AI Vector Search Architecture

The following picture summarizes the complete architecture, including embeddings, vector indexes, similarity search, and Retrieval-Augmented Generation (RAG).

Oracle AI Vector Search - Complete Memory Architecture

3. The Easiest Memory Formula

V → E → D → I → S → R

Vector → Embedding → Distance → Index → Search → RAG

• V - Vector: A collection of numbers.
• E - Embedding: Numerical representation of meaning.
• D - Distance: Compare two vectors.
• I - Index: HNSW graph or IVF clusters.
• S - Search: Find similar information.
• R - RAG: Use retrieved information to generate an AI answer.

4. What Is a Vector? (ELI10)

A vector is simply a collection of numbers. An embedding model converts text into a numerical representation.

Database is running slowly

↓

AI Embedding Model

↓

[0.12, 0.85, 0.34, 0.91]

These example numbers represent an embedding. Real embedding models often generate hundreds or thousands of dimensions.

Memory Trick: Think of vectors as GPS coordinates for meaning. Similar sentences may have nearby vector representations.

5. Vector Distance Methods

Method	Meaning	Memory Trick
Cosine	Compares directions	Same direction?
Euclidean	Straight-line distance	How far apart?
Dot Product	Vector alignment	How strongly aligned?

6. HNSW - Graph-Based Vector Index

HNSW stands for Hierarchical Navigable Small World. It uses multiple layers of connected vectors.

Imagine finding a house by first locating the city, then the neighborhood, and finally the street.

Top Layer - Few Nodes
↓
Middle Layer - More Nodes
↓
Bottom Layer - Many Nodes
↓
Nearest Matching Vectors

Remember: HNSW = Graph Navigation.

7. IVF - Cluster-Based Vector Index

IVF stands for Inverted File. It organizes vectors into clusters.

Imagine a school with 100 classrooms. Instead of checking every student, you first identify the most relevant classrooms.

All Vectors
↓
Cluster A | Cluster B | Cluster C
↓
Search Closest Clusters
↓
Matching Vectors

Remember: IVF = Group First, Search Next.

8. HNSW vs. IVF - Quick Comparison

Feature	HNSW	IVF
Method	Graph	Clusters
Memory	Often higher	Can be lower
Main Benefit	Fast approximate search	Scalable clustered search

9. Practical Oracle SQL Example

Create a table with a VECTOR column:

CREATE TABLE support_incidents (
    incident_id NUMBER PRIMARY KEY,
    description VARCHAR2(500),
    severity NUMBER,
    incident_vector VECTOR(4, FLOAT32)
);

Insert an example vector:

INSERT INTO support_incidents
VALUES (
    101,
    'Database running slowly',
    1,
    TO_VECTOR('[0.12,0.85,0.34,0.91]')
);

COMMIT;

Find the closest matching incidents:

SELECT incident_id,
       description,
       VECTOR_DISTANCE(
           incident_vector,
           :search_vector,
           COSINE
       ) AS distance
FROM support_incidents
WHERE severity = 1
ORDER BY distance
FETCH FIRST 10 ROWS ONLY;

The bind variable :search_vector must contain a compatible vector generated for the search question.

The four-dimensional values are only for learning. Production vectors should match the dimensions and distance metric of the embedding model.

10. How Vector Search Works with RAG

RAG means Retrieval-Augmented Generation. It retrieves relevant information before an AI model generates its answer.

DBA Question
↓
Embedding Model
↓
Oracle Vector Search
↓
Relevant Incident Records
↓
Large Language Model
↓
Answer with References

Example question: "How did we resolve ORA-12505 after the last refresh?"

Oracle Vector Search can retrieve related incident records. The AI application can then use those records to explain previous resolutions.

Always verify AI-generated recommendations before applying changes to production.

11. Five Important DBA Takeaways

1. Oracle stores vectors alongside relational data.
2. VECTOR_DISTANCE compares vector representations using SQL.
3. HNSW uses graph navigation.
4. IVF uses clusters to narrow the search.
5. RAG combines retrieved information with AI-generated answers.

12. Interview Revision Questions

Q1. What is a vector?
A collection of numbers representing data features.

Q2. What is an embedding?
A numerical representation generated by an AI model.

Q3. What is HNSW?
A hierarchical graph-based approximate vector index.

Q4. What is IVF?
A cluster-based approximate vector index.

Q5. What is VECTOR_DISTANCE?
An Oracle SQL function for calculating vector distance.

Q6. What is RAG?
Retrieval-Augmented Generation.

13. Final Memory Summary

V → E → D → I → S → R

• Vector = Numbers
• Embedding = Meaning representation
• Distance = Comparison
• Index = HNSW or IVF
• Search = Similar information
• RAG = AI answer using retrieved information

Oracle AI Vector Search brings semantic similarity search into the Oracle database, allowing DBAs to work with traditional relational data and AI vector embeddings together.

Happy Learning & Happy DBA-ing!

Oracle DBA Learning Series

Oracle AI Vector Search Core Architecture and Enterprise Scaling

Oracle AI Vector Search Core Architecture and Enterprise Scaling

 

Visual Model Primer: How Neighbor Graphs and Partitions Traverse High-Dimensional Data

1. Conceptual Foundations: From Raw Data to Vector Proximity

1.1 Translating Meaning into Numerical Vectors

In modern enterprise artificial intelligence architectures, a vector is a multi-dimensional array of numerical values that translates unstructured data into a structured mathematical coordinate space. Traditional database systems rely on exact string matching or static pixel attributes. In contrast, high-dimensional vector representations capture the underlying semantic meaning of complex entities, allowing systems to evaluate conceptual relationships rather than superficial syntax.

To understand how high-dimensional vectors preserve semantic context across heterogeneous data types, consider three domain examples:

• Computer Incident Reports: Rather than processing an IT ticket as plain text keywords (e.g., searching strictly for the term "crash"), an incident report is embedded into a high-dimensional vector. This vector simultaneously encodes distinct semantic dimensions—such as hardware model, issue severity level, observed system symptoms, ticket status, and host operating system.
• Animal Photographs (e.g., Canine Classification): A picture of a dog cannot be adequately indexed by raw RGB pixel colors alone. Vector embeddings capture deep contextual semantics, enabling an image search engine to mathematically distinguish between an image of a dog looking sad versus the same dog exhibiting happy, playful physical cues.
• Environmental Landscapes: A photograph of a mountain trail is embedded into a numerical vector that represents discrete, co-occurring semantic features within the visual scene—including a Douglas Fir tree, a background footbridge, a fast-running river, an active hiker, and the dirt hiking path itself.

Pedagogical Insight: The fundamental breakthrough of AI vector representations is their ability to capture holistic semantic context. By mapping unstructured input into a continuous vector space, database engines shift from rigid keyword or pixel matching to nuanced mathematical proximity evaluations.

1.2 Distance Metrics: Quantifying Similarity

To perform semantic operations, the database evaluates vector distance—a mathematical metric quantifying spatial proximity between two vectors in high-dimensional space. Calculating vector distance underpins three core enterprise search workloads:

1. Similarity Search: Locating data points that reside nearest to a target query vector in semantic space.
2. Outlier Search: Identifying anomalous data points that fall outside established clusters or normal spatial trends.
3. Retrieval-Augmented Generation (RAG): Grounding Large Language Models (LLMs) by retrieving precise, domain-specific enterprise data points to serve as contextual prompts.

The choice of distance calculation directly affects how vector space geometry is evaluated:

Format Name	Role in Proximity Evaluation	Primary Use Case & Mathematical Characterization
Cosine	Measures vector proximity based strictly on orientation and angle (\theta), completely independent of vector magnitude.	Default Algorithm. Ideal for text semantic embeddings where string length or document word count varies, ensuring magnitude does not distort contextual similarity.
Euclidean	Calculates the absolute spatial straight-line distance (L_2 norm) between vector coordinates.	Preferred for fixed-scale spatial data, image feature maps, and geometric datasets where absolute spatial coordinate displacement is meaningful.
Dot	Computes the inner dot product, evaluating both angular alignment and vector magnitude simultaneously.	Essential for specialized neural network embedding models where vector length carries explicit semantic weight, frequency, or confidence.

To express these metrics in database queries, native SQL extensions combine distance functions with approximate search specifications:

-- Illustrative SQL Syntax: Performing an Approximate Similarity Search
SELECT incident_id, product_name, 
       VECTOR_DISTANCE(incident_vector, :query_vector, COSINE) AS distance
FROM support_incidents
WHERE state_id = 'GA' AND severity = 1
ORDER BY distance
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

Because evaluating exact vector distances across millions of high-dimensional points requires linear scans that rapidly saturate memory bandwidth, enterprise database systems rely on specialized vector indexes to accelerate search performance.

2. Neighbor Partitioning: Clustering High-Dimensional Data for Targeted Search

2.1 Cluster Creation and Structural Organization

When vector datasets scale to millions of rows, executing an exhaustive brute-force distance evaluation across every vector becomes computationally prohibitive. Neighbor partitioning vector indexes overcome this bottleneck by pre-clustering the vector space into discrete, localized partitions during index creation.

For example, a dataset can be organized using a K=5 clustering configuration (K-Means). The vector space is partitioned into five discrete clusters, where vectors exhibiting high semantic similarity are assigned to the same physical cluster block. Instead of scanning the entire database, the engine evaluates cluster centroids first, limiting vector-level distance evaluations to candidate partitions.

====================================================================================
               DYNAMIC NEIGHBOR PARTITIONING TRAVERSAL (K=5)
====================================================================================

 [Step 1: Arriving Query Vector]
                │
                ▼
      ( Evaluate Centroids ) ──► Compares distance to Centroids C1, C2, C3, C4, C5
                │
                ▼
 [Step 2: Centroid Selection] ──► Identifies 2 Nearest Centroids (e.g., C2 and C4)
                │
                ├──────────────────────────────────────┐
                ▼                                      ▼
┌──────────────────────────────┐      ┌──────────────────────────────┐
│   PARTITION 2 (Cluster C2)   │      │   PARTITION 4 (Cluster C4)   │
│ ░░░░░░░░░░░░░░░░░░░░░░░░░░░░ │      │ ░░░░░░░░░░░░░░░░░░░░░░░░░░░░ │
│ ░░ Target Vector Probe ░░░░  │      │ ░░ Target Vector Probe ░░░░  │
└──────────────────────────────┘      └──────────────────────────────┘
 [Bypassed Partitions: C1, C3, C5 (Zero Memory Scan Overhead)]
====================================================================================

2.2 The Two-Step Partition Traversal Process

When executing a search query against a neighbor partition index, the execution engine follows a targeted two-step traversal pipeline:

1. Centroid Proximity Evaluation: The incoming query vector is evaluated against the mathematical center points (centroids) of all defined clusters (C_1 \dots C_K).
2. Nearest Cluster Identification: The engine selects the N nearest cluster centroids based on the calculated distance (for instance, filtering down to the top 2 closest cluster centers).
3. Targeted Partition Probe: The search engine bypasses all non-selected partition blocks entirely, running a fine-grained distance evaluation exclusively within the row sets of those chosen clusters.

While neighbor partitioning successfully narrows search scopes through static geometric boundaries, real-time dynamic traversals often require multi-layered graph structures to achieve sub-millisecond latency at high precision.

3. Neighbor Graphs: Hierarchical Multi-Layer Traversal

3.1 Anatomy of a Layered Graph Vector Index

An In-Memory Neighbor Graph Index models vector space as a multi-layered network of vertices connected by directed edge pointers. Constructed bottom-up, it forms a hierarchical skip-list architecture in memory:

• Layer 0 (Base Layer): Contains every vector point in the table, linked by fine-grained directed pointers to immediate geometric neighbors.
• Intermediate & Top Layers: As the index ascends, vector density decreases exponentially based on clustering density. The uppermost layer contains only a sparse network of entry points that represent broad global regions of the vector space.

====================================================================================
               HIERARCHICAL IN-MEMORY NEIGHBOR GRAPH TRAVERSAL
====================================================================================

 [TOP LAYER]        [Entry Point Node] ────────► [Sparse Node]
                           │ (Vertical Step-Down)
                           ▼
 [LAYER 1]          [Intermediate Node] ───────► [Intermediate Node]
                           │ (Vertical Step-Down)
                           ▼
 [LAYER 0 (Base)]   [Node] ──► [Node] ──► [Node] ──► [TARGET VECTOR NEIGHBORS]
                    (All Vector Points & Directed Outgoing Neighbor Pointers)
====================================================================================

Technical Deep-Dive: Graph Traversal vs. Tree Indexing Traditional database B-Trees rely on rigid single-dimensional scalar boundaries to partition data. High-dimensional vector spaces lack natural scalar order. Hierarchical neighbor graphs resolve this by creating probabilistic skip-graphs: higher layers jump across vast distance gaps in vector space, while lower layers refine localized spatial resolution.

3.2 Top-Down Traversal Mechanics

During query execution, the engine traverses the hierarchical graph from top to bottom using directed path decisions:

1. Top-Layer Entry Point Selection: The query engine lands at the primary entry point node residing on the highest graph layer.
2. Directed Outgoing Pointer Evaluation: The engine evaluates the distance from the query vector to nodes connected via directed outgoing edges.
   • Critical Graph Constraint: Traversal strictly follows pre-constructed outgoing pointers. If a node is geometrically close to the query in spatial coordinates but lacks a direct outgoing edge from the current vertex, the traversal algorithm bypasses it.
3. Vertical Layer Step-Down: Upon identifying the local minimum node at the current layer, the engine follows a vertical pointer down to the corresponding node in the layer below.
4. Layer 0 Base Traversal & Candidate Extraction: At Layer 0, the engine executes a localized edge traversal surrounding the entry vertex, extracting the exact number of nearest vector neighbors requested by the SQL query.

Navigating structural trade-offs between memory-bound graphs and disk-capable partitions is critical for tuning index performance and maintaining system resources.

4. Index Selection, Accuracy, and Resource Trade-offs

4.1 Structural Comparison: In-Memory Neighbor Graph vs. Neighbor Partitioning

Selecting an index organization requires balancing memory constraints, query throughput, and hardware configurations.

Dimension	In-Memory Neighbor Graph	Neighbor Partitioning
Primary Location & Memory Footprint	Strictly In-Memory. Requires continuous memory allocation to store graph vertices and directed pointers.	Partially In-Memory / Disk-Backed. Can operate directly over partitioned table blocks on disk with low memory overhead.
Speed & Accuracy Ranking	Highest Performance. Delivers the fastest search response times and highest recall accuracy rankings.	Balanced Performance. Provides high-speed approximate search while optimizing hardware cost and memory usage.
Fallback & System Behavior	Configured as the primary index clause. If system memory is exhausted, the engine falls back to neighbor partitioning.	Serves as the primary operational fallback when RAM capacity cannot host full graph structures.

The following DDL illustrates how index structures and metrics are declared within the database engine:

-- Creating an In-Memory Neighbor Graph Index with Fallback Specification
CREATE VECTOR INDEX docs_graph_idx ON documents(doc_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
TARGET ACCURACY 95;

-- Creating a Disk-Capable Neighbor Partition Index
CREATE VECTOR INDEX docs_part_idx ON documents(doc_vector)
ORGANIZATION NEIGHBOR PARTITION
DISTANCE EUCLIDEAN
TARGET ACCURACY 85;

Operational Feature: Real-Time Transaction Maintenance As underlying database tables process concurrent DML operations (e.g., INSERT, UPDATE, DELETE), vector indexes are automatically maintained behind the scenes. This guarantees strict transaction consistency and ensures vector search results reflect committed enterprise data without requiring manual index rebuilds.

4.2 Calibrating Target Accuracy vs. Search Speed

Approximate vector indexing trades absolute recall accuracy for low search latency. Database administrators calibrate this behavior using the TARGET ACCURACY clause in the index DDL.

• High Target Accuracy (e.g., 95–99%): Instructs the search engine to expand internal probe candidate lists and evaluate more graph entry points or partition clusters. This increases recall precision but consumes higher memory bandwidth and introduces marginal search latency.
• Lower Target Accuracy (e.g., 70–80%): Restricts the number of scanned entry probes, minimizing CPU cycles and memory access. This accelerates response times at the expense of potential recall misses near spatial cluster boundaries.

Database Administrator Takeaway: Tuning vector search relies on pragmatic compromise. Setting TARGET ACCURACY allows administrators to configure search parameters to match workload requirements, balancing latency against resource consumption.

5. Architectural Integration and Scalability Options

5.1 Enterprise Scale-Out Strategies

To maintain sub-second search latencies across massive enterprise datasets, vector operations integrate directly with core database scale-out architectures:

                                INGESTION PIPELINE
                                        │
                         [DBMS_VECTOR.load_onnx_model]
                                        │
                                        ▼
┌───────────────────────────────────────────────────────────────────────────────┐
│                          ENTERPRISE VECTOR ARCHITECTURE                       │
├───────────────────────────────────────────────────────────────────────────────┤
│  Relational Partitioning  │  Subdivide vectors by attributes (e.g., country)  │
│                           │  to isolate index scans to local partitions.      │
├───────────────────────────┼───────────────────────────────────────────────────┤
│  Real Application Clusters│  Scale parallel vector searches across multiple   │
│  (RAC)                    │  active nodes while ensuring ACID consistency.    │
├───────────────────────────┼───────────────────────────────────────────────────┤
│  Database Sharding        │  Deploy local vector indexes per geographic shard;│
│                           │  eliminates global index bottlenecking completely.│
├───────────────────────────┼───────────────────────────────────────────────────┤
│  Exadata Smart Storage    │  Offload vector distance calculations directly to │
│                           │  intelligent storage hardware.                    │
└───────────────────────────────────────────────────────────────────────────────┘

• Relational Partitioning and Subpartitioning: Enterprise datasets can be logically partitioned by relational attributes (such as grouping records by country or department) before building vector indexes. The database engine builds vector indexes independently on each partition, enabling queries to prune non-relevant data partitions prior to performing vector distance calculations.
• Oracle Real Application Clusters (RAC): Executes vector searches in parallel across active database instances. This scale-out model allows enterprise Lakehouses to process high-concurrency vector queries while maintaining transaction consistency across all cluster nodes.
• Database Sharding (Eliminating Global Management Bottlenecks): Vector datasets can be distributed across independent physical database shards located worldwide. Each shard constructs shard-independent local vector indexes. Because each shard processes queries independently without relying on a global index manager, cross-node network synchronization bottlenecks are eliminated, scaling throughput while satisfying local data sovereignty laws.
• Engineered Systems (Exadata Storage Offloading): In engineered systems, vector distance evaluations are offloaded directly to intelligent storage servers. Offloading evaluates vector math at the storage layer, reducing memory bus traffic and boosting query throughput transparently.
• In-Database ONNX Model Ingestion: Machine learning embedding models can be ingested directly into the database engine using utilities such as DBMS_VECTOR.load_onnx_model. This enables real-time vector generation inline with standard database operations.

6. Summary & Conceptual Roadmap

Understanding vector search requires recognizing how raw data transforms into high-dimensional representations, how indexing structures optimize spatial traversal, and how enterprise database architectures scale these workloads.

Operational Stage	Core Function	Key Technical Architectural Mechanism
1. Semantic Vector Encoding	Maps unstructured entities (text, images, reports) into numerical coordinates.	Generates vector embeddings via in-database ONNX pipelines (DBMS_VECTOR.load_onnx_model).
2. Proximity Metric Evaluation	Quantifies spatial distance between vector pairs.	Evaluates distance metrics using Cosine (angular), Euclidean (spatial), or Dot (magnitude) functions.
3. High-Speed Index Traversal	Accelerates similarity lookups over large datasets.	Navigates Neighbor Partitions (K-Means centroids) or Neighbor Graphs (hierarchical directed skip-lists).
4. Architectural Scale-Out	Scales query throughput across massive enterprise environments.	Combines Relational Partitioning, RAC Parallel Execution, Sharding, and Exadata Smart Storage.

By pairing semantic vector embeddings with high-performance graph and partition indexes, enterprise database engines deliver fast similarity searches over massive, multi-dimensional datasets.

 

4 Mind-Bending Takeaways from Oracle Database 26ai's Native AI Vector Search

Traditional database queries have long operated on a simple premise: exact literal matching. Whether searching string patterns or filtering integer primary keys, enterprise systems have historically relied on structured metadata and exact keyword hits. However, this approach completely falls short when dealing with context, nuance, or actual human meaning. A keyword query cannot grasp the underlying frustration in an incident log, nor can a pixel-matching search recognize that two photos capture the same scenic mountain trail under different lighting.

For database administrators and application architects, bridging this divide between structured relational schemas and unstructured semantic context has traditionally required a painful compromise. Engineering teams were forced to export operational data via complex ETL pipelines into standalone, niche vector databases. This fragmented architecture introduced immediate operational friction: brittle synchronization loops, dangerous data drift, replication lag, and completely duplicated security and governance models across systems.

Oracle AI Database 26ai fundamentally dismantles this multi-database complexity with native AI Vector Search. By storing high-dimensional vector embeddings natively alongside relational attributes inside standard database tables, Oracle Database 26ai enables real-time, high-performance semantic search directly within the enterprise core—allowing developers to run unified queries without leaving the familiar SQL ecosystem or sacrificing transactional integrity.

Takeaway 1: Vectors Don't Store Pixels or Words—They Store Pure "Meaning"

At its core, an AI vector is a mathematical array of numbers. Rather than recording exact textual strings or raw pixel coordinates, these numerical values serve as a dense, high-dimensional translation of unstructured content—such as documents, photos, audio, and video—into pure semantic context.

To understand why this represents a quantum leap for enterprise data, consider how information is conventionally indexed versus how it is evaluated semantically:

• The Image Context Problem: Suppose you have two photos of the same dog—one capturing the dog looking happy, and another showing it looking sad. Evaluating the images purely by pixel colors or basic tags misses the deeper visual context. Similarly, a photograph depicting a Douglas Fir tree, a bridge in the background, a river running underneath, a hiker on a path, and the path itself contains a rich web of semantic relationships. Vectors map these elements mathematically so that queries searching for outdoor activities or river crossings yield proximity matches, even if not a single pixel color aligns.
• Business Incident Intelligence: Consider IT support or customer service incident reports. Instead of searching strictly for exact text terms like "network failure," an incident report can be converted into a vector embedding capturing product type, severity, symptoms, operating system, and historical resolution status. The database calculates proximity across historical incident vectors, surfacing identical root causes written in entirely different phrasing.

To generate these embeddings, Oracle Database 26ai offers architects full operational flexibility through a dual-pathway model framework: developers can either import pre-trained external ONNX models directly into the database using DBMS_VECTOR.load_onnx_model, or generate vector embeddings natively inside Oracle Database without creating external third-party dependencies.

"AI Vector Search is a breakthrough technology when it comes to be able to understand the semantics behind a particular element... rather than the understanding of the whole semantic and what is actually being described."

By evaluating data through native semantic vectors, enterprise applications move past rigid syntax parsing and begin querying the holistic meaning encoded across business assets.

Takeaway 2: Forget Learning a New Database—SQL Now Speaks Vector

One of the greatest impediments to enterprise AI adoption is the cognitive and operational burden of adopting proprietary query frameworks for vector engines. Oracle Database 26ai removes this barrier by embedding vector data types directly into standard relational tables. Vectors sit natively in the same row as primary keys, foreign keys, timestamps, and customer IDs.

Furthermore, querying these vectors requires no specialized syntax outside of standard SQL. Oracle introduces powerful native vector functions and standard language extensions:

• VECTOR_DISTANCE: A built-in SQL function that computes the exact mathematical distance between two vectors. It natively supports multiple distance metrics, including Cosine distance (the default metric for directional similarity), Euclidean distance (straight-line point distance), and Dot product (vector scalar projection).
• FETCH APPROXIMATE: An extension to the standard SQL ORDER BY and FETCH clauses that accelerates similarity searches by instructing the query optimizer to utilize vector indexes for fast proximity retrieval.
• Versatile Search Paradigms: Beyond standard Similarity Search (finding the nearest vectors to a target concept), native SQL supports Outlier Search—identifying data points that lie far away from normal cluster centroids or trend axes, exposing anomalies and fraud—and Retrieval-Augmented Generation (RAG), which feeds enterprise context to Large Language Models (LLMs).

Consider this real-world SQL query combining relational filters with a semantic vector search:

SELECT incident_id, 
       summary, 
       VECTOR_DISTANCE(incident_vector, :search_vector, COSINE) AS distance
FROM support_incidents
WHERE client_state = 'GA' 
  AND severity = 1
ORDER BY distance
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

In this query, :search_vector is an application variable containing the target query embedding. The database first applies structured relational filters (client_state = 'GA' and severity = 1), and then uses VECTOR_DISTANCE alongside FETCH APPROXIMATE to return the 10 semantically closest support incidents in milliseconds—all in a single, atomic SQL call.

Takeaway 3: The Need for Speed—Why You Will Gladly Trade 1% Accuracy for Instant Results

In massive enterprise systems, vector tables quickly scale to hundreds of millions or billions of rows. Performing an exact, unindexed vector comparison across high-dimensional space requires evaluating millions of floating-point distance formulas per query—a computationally expensive linear scan. To achieve sub-second response times, specialized vector indexes are required.

Oracle Database 26ai introduces two foundational vector index types designed to balance memory usage, build time, and search speed:

1. In-Memory Neighbor Graph Indexes (ORGANIZATION INMEMORY NEIGHBOR GRAPH): Constructed in memory, this multi-layered graph organizes vector relationships hierarchically. Layer 0 at the bottom contains every vector point in the dataset. Higher layers contain progressively sparser subsets of vectors representing broader topological regions.
2. Neighbor Partition Indexes (ORGANIZATION NEIGHBOR PARTITION): Using K-means clustering algorithms, this approach partitions vectors into discrete physical clusters based on centroid proximity. During execution, the engine evaluates the distance from the query vector to the partition centroids, identifies the closest clusters (e.g., the top 2 out of 5), and restricts the fine-grained search exclusively to those partition spaces.

Graph Traversal Mechanics

To understand why neighbor graphs deliver astonishing speed, consider how a query traverses the multi-layer graph without scanning the full dataset:

[Top Layer: Entry Point]  --> Identifies broad region; locates entry node
        |
        v
[Middle Layers]           --> Follows outgoing relationship vectors down
        |
        v
[Bottom Layer 0]          --> Performs detailed evaluation of local neighbor points

Query execution enters at the sparse top layer, immediately locating an initial entry point. The engine then follows outgoing relationship vectors downward layer-by-layer, continuously narrowing the proximity path. Upon reaching Layer 0, the engine evaluates only the immediate local proximity points to return the top nearest matches. By pruning vast swaths of the dataset at higher layers, graph traversal reduces search complexity exponentially.

Practical DDL and Target Accuracy

When building vector indexes, database architects can explicitly balance mathematical precision against query latency using the TARGET ACCURACY clause:

CREATE VECTOR INDEX idx_incident_vector 
ON support_incidents(incident_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

By configuring TARGET ACCURACY (e.g., to 95%), database administrators explicitly instruct the optimizer that sacrificing a negligible 5% window of exact theoretical precision is an acceptable trade-off for orders-of-magnitude faster execution. In real-world similarity applications—such as surfacing relevant KB articles or providing enterprise context to an LLM—trading 1% accuracy for near-instant results is an architectural slam dunk.

Takeaway 4: Enterprise AI Search Doesn't Have to Break Transaction Consistency or Scale

A major liability of bolt-on vector databases is their inability to enforce core database guarantees. When underlying operational records change, isolated vector databases fall out of sync, returning stale data or requiring risky double-commit logic in application code.

Oracle Database 26ai solves this by maintaining absolute ACID transaction consistency. The moment an application commits an INSERT, UPDATE, or DELETE on operational data, the associated vector columns and underlying vector indexes are automatically updated behind the scenes within the exact same transaction boundary.

Furthermore, native AI Vector Search inherits Oracle's proven suite of enterprise scaling technologies:

• Real Application Clusters (RAC): Vector distance calculations and index traversals are automatically parallelized across multiple active database nodes in a RAC cluster, delivering high-throughput processing over massive vector collections.
• Exadata Smart Storage: Vector search operations are transparently offloaded directly down to the Exadata storage cell hardware. Storage nodes execute vector distance calculations in parallel before returning matching rows to the database compute layer, preserving main database CPU resources.
• Database Sharding: For horizontally scaled or globally distributed deployments, tables are sharded across distinct physical databases. Crucially, vector indexes are built locally and independently on each shard. Because indexes are shard-independent, there is zero cross-shard index synchronization overhead or global index lock contention. Queries route directly to local shards, ensuring unlimited horizontal scale while enforcing strict regional data sovereignty.
• Partitioning: Vector indexes natively support partitioning and sub-partitioning by standard relational attributes (e.g., partitioning by country or region). Queries filtering on partition keys skip non-relevant index partitions entirely, dramatically shrinking the index memory footprint and search space.

Conclusion: The Future of Hybrid Data and the Next Frontier

The integration of native AI Vector Search into Oracle Database 26ai represents a structural turning point in enterprise application design. By removing the wall separating relational business records from unstructured AI vectors, enterprise systems no longer require dedicated vector databases, fragile synchronization pipelines, or duplicate security models.

Whether importing pre-trained embeddings via DBMS_VECTOR.load_onnx_model or generating embeddings directly inside the database engine, developers can now combine operational relational attributes, LLM context pipelines, and visual media in unified SQL statements backed by full ACID guarantees.

As you evaluate your organization's enterprise data architecture, ask yourself: What unstructured data—incident logs, customer communications, media libraries, or diagnostic records—is currently locked away in your operational systems, waiting for native semantic vector search to unlock its hidden value?

Oracle 26ai Vector Database Explained in Simple Words

Oracle 26ai can search your data by meaning, not only by matching exact words. That is the whole idea behind vector search, and you can use it with plain SQL.

The problem with normal search

A customer searches your support system for "laptop won't turn on". The perfect answer already exists in a ticket, but it says "notebook fails to boot".

SELECT * FROM tickets WHERE description LIKE '%laptop%';

This query misses that ticket because the words are different. A person would see at once that both sentences mean the same thing. Vector search gives the database a similar ability.

What is a vector?

A vector is a list of numbers that represents the meaning of something. Think of it as GPS coordinates for meaning.

GPS uses two numbers to place a city on a map, and nearby cities have similar coordinates. A vector does the same for a sentence, an image or a document: similar meanings get placed close together.

A small AI program called an embedding model creates these numbers. You give it text, and it gives you back a vector, usually with hundreds of numbers. Real vectors have far too many dimensions to draw, so the two-dimensional map below is only an illustration.

Illustration of a meaning map, not real model output

The question lands beside both computer-problem tickets, including one that shares none of its words. The money tickets are far away, so they rank last.

What Oracle 26ai adds

Oracle 26ai is not a separate vector database. It is the normal Oracle database with a feature called AI Vector Search built in.

That means your vectors sit in an ordinary table, right beside your business data. Three additions make this work:

• VECTOR data type: a new column type that stores the numbers.
• VECTOR_DISTANCE function: a SQL function that measures how close two vectors are. A smaller distance means a closer meaning.
• Vector indexes: special indexes that keep searches fast on large tables.

For a DBA, this is the real benefit. You keep the SQL, security model and operational tools you already know, instead of running and syncing a second database.

How it works, end to end

Vector search has two parts: you store your data as vectors once, then compare each new question against them.

How vector search works: store, then search

The question must go through the same model as the data. Vectors from different models are not comparable, just as coordinates from two different maps would not line up.

Try it: a small SQL example

This example uses tiny hand-written vectors with three numbers each, so you can see the idea without installing a model. The numbers are chosen to illustrate the concept; a real model would generate them.

Step 1: create a table and add some tickets.

CREATE TABLE tickets (
  id          NUMBER PRIMARY KEY,
  description VARCHAR2(200),
  embedding   VECTOR(3, FLOAT32)
);

INSERT INTO tickets VALUES (1, 'Notebook fails to boot', '[0.9, 0.1, 0.1]');
INSERT INTO tickets VALUES (2, 'Laptop screen stays black at startup', '[0.8, 0.2, 0.1]');
INSERT INTO tickets VALUES (3, 'Invoice payment is overdue', '[0.1, 0.9, 0.2]');
INSERT INTO tickets VALUES (4, 'Update the supplier address', '[0.1, 0.7, 0.6]');
COMMIT;

Step 2: search by meaning. Here, [0.85, 0.15, 0.1] stands in for the question "laptop won't turn on".

SELECT description,
       ROUND(VECTOR_DISTANCE(embedding,
             TO_VECTOR('[0.85, 0.15, 0.1]', 3, FLOAT32), COSINE), 4) AS distance
FROM   tickets
ORDER  BY distance;

The smaller the distance, the closer the meaning:

Ticket	Distance
Notebook fails to boot	0.0020
Laptop screen stays black at startup	0.0025
Update the supplier address	0.6897
Invoice payment is overdue	0.7022

The top result is the ticket that LIKE '%laptop%' missed at the start of this article.

Step 3: use a real model. Oracle can load an ONNX embedding model into the database and create vectors in SQL. The model in Oracle's tutorial produces 384 numbers per text, so your column would be VECTOR(384, FLOAT32).

EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL('DM_DUMP', 'all-MiniLM-L12-v2.onnx', 'doc_model');

SELECT description
FROM   tickets
ORDER  BY VECTOR_DISTANCE(embedding,
          VECTOR_EMBEDDING(doc_model USING 'laptop won''t turn on' AS data), COSINE)
FETCH  FIRST 5 ROWS ONLY;

This needs a database directory (DM_DUMP here) that holds the model file, plus the CREATE MINING MODEL privilege.

Step 4: mix meaning with normal filters. This is the biggest advantage of keeping vectors in the database. In Oracle's example, one query finds similar houses within a budget and a ZIP code:

SELECT price
FROM   houses
WHERE  price < 2000000 AND zipcode = 94065
ORDER  BY VECTOR_DISTANCE(description_vector, :query_vector)
FETCH  APPROX FIRST 10 ROWS ONLY;

Vector indexes in plain language

A vector index makes search faster by checking only the most promising rows. Without one, Oracle compares your question with every row, which is accurate but slow on large tables.

The trade-off is accuracy. An exact search gives 100% accuracy at a heavy compute cost, while an index gives an approximate answer much faster. You choose the balance with TARGET ACCURACY.

	HNSW	IVF
Oracle's name	In-Memory Neighbor Graph	Neighbor Partition
Easy picture	Asking friends who each point you closer to the right person	Going straight to the right section of a library
Where it lives	In memory, in the Vector Pool	On disk, cached in the buffer cache
Good fit	The index fits in memory and speed matters most	The data is too large to keep in memory

-- HNSW: in memory
CREATE VECTOR INDEX tickets_hnsw_idx ON tickets (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

-- IVF: on disk (create one or the other for this example)
CREATE VECTOR INDEX tickets_ivf_idx ON tickets (embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 90;

To use the index, write FETCH APPROX FIRST 5 ROWS ONLY in your query. Use the same distance metric as your embedding model. Syntax reference: HNSW and CREATE VECTOR INDEX.

RAG: how vectors help AI answer from your data

RAG (Retrieval Augmented Generation) lets an AI model answer from your private data without retraining the model. Think of it as an open-book exam: the AI looks up the right pages first, then writes its answer.

1. A user asks a question in normal language.
2. Oracle finds the most similar pieces of your documents with vector search.
3. Those pieces and the question go to the AI model.
4. The model writes an answer based on what it was given.

Other common uses include similarity search, pattern recognition, and anomaly and fraud detection.

What this means on Exadata

On Exadata, vector search can run on the storage servers through AI Smart Scan. It is the familiar Smart Scan idea applied to vectors: do the work where the data lives, and send back only the results.

It works automatically, with no code changes. Oracle quotes up to 30 times faster AI workloads with Exadata System Software 24ai, so treat that as a best-case vendor figure and test your own workload.

What this means for EBS

EBS 12.2 is certified with Oracle AI Database 26ai on on-premises Linux x86-64 and on-premises Exadata. That was announced on April 7, 2026, and other on-premises platforms were still pending at that time.

• Upgrade path: 19c is the only database release with a direct EBS upgrade path to 26ai.
• Tooling: the upgrade uses AutoUpgrade, and DBUA is desupported.
• Architecture: the certified layout is multitenant with a single PDB.

This certification covers running EBS on the 26ai database. The announcement does not list AI Vector Search among its certified or supported features, so check My Oracle Support before adding vector columns or indexes to an EBS database.

Notes for DBAs

Before you enable vector search, plan for four things: memory, version, licensing and restrictions.

Memory. HNSW indexes live in the Vector Pool, an SGA memory area you set with VECTOR_MEMORY_SIZE. Oracle's rough sizing formula is:

1.3 × vectors × dimensions × bytes per dimension

For 1 million vectors with 384 FLOAT32 dimensions (4 bytes each), that comes to about 2 GB (1.86 GiB).

ALTER SYSTEM SET vector_memory_size = 1G SCOPE = BOTH;

-- Check the allocation after you create an HNSW index
SELECT SUM(alloc_bytes) FROM v$vector_memory_pool;

This example sets the limit at PDB level. At CDB level, the parameter sets the pool's current size, and reducing it fails while vectors are in use.

Version. A 23ai database reaches 26ai by applying the October 2025 release update. Oracle provides upgrade paths from 19c and 21c, and older releases must reach one of those first.

Licensing. Oracle says AI Vector Search is included at no additional charge, and an Oracle tutorial says it is available in all 26ai editions. Independent licensing sites disagree about Standard Edition 2, so confirm your edition with Oracle. Options such as Partitioning are still licensed separately if you use them.

Restrictions. A few documented limits can catch you out:

• Oracle clients from 19c and 21c see vectors as CLOBs.
• A vector can have at most 65,535 dimensions.
• Data redaction is not supported on VECTOR columns.
• Vector columns are not supported in index-organized, global temporary, blockchain or immutable tables, or in clusters.
• Truncating or moving a table marks its global vector indexes unusable.

Key takeaways

• A vector is a list of numbers that represents meaning.
• Vector search finds the closest meaning, not the same words.
• Oracle 26ai does this inside the database, using SQL.
• HNSW lives in memory, and IVF lives on disk.
• You can combine meaning with normal WHERE filters in one query.

A good first step is to run the small tickets example on a test database, then try a real embedding model on your own sample data.

Sources

• Oracle AI Vector Search, Oracle
• Introducing Oracle AI Database 26ai, Oracle
• AI Database FAQ, Oracle
• Getting started with AI vector search, Oracle
• Size the Vector Pool, Oracle documentation
• HNSW index syntax and parameters, Oracle documentation
• CREATE VECTOR INDEX, Oracle documentation
• Restrictions for AI Vector Search, Oracle documentation
• Exadata AI Smart Scan Deep Dive, Oracle
• EBS 12.2 certified with Oracle AI Database 26ai, Oracle
• Accelerate generative AI use cases with Amazon Bedrock and Oracle Database@AWS, AWS

Oracle AI Vector Search Explained Simply | Vectors, Embeddings, HNSW, IVF & SQL in Oracle 26ai

Oracle AI Vector Search Explained Simply

A Practical Learning Guide for Oracle DBAs

Oracle AI Database 26ai | Vectors | HNSW | IVF | SQL | RAG

1. Introduction: What Is AI Vector Search?

Imagine you are an Oracle DBA managing thousands of production incidents. You want to find previous incidents related to slow database performance.

Your database contains the following records:

Incident	Description
INC101	Database is running slowly
INC102	SQL queries taking too long
INC103	High CPU affecting application

Traditional SQL keyword searches may miss related incidents when different words are used.

Simple Explanation: AI Vector Search helps find information based on similar meaning, not just matching keywords.

2. What Is a Vector? (ELI10)

A vector is simply a collection of numbers. An AI embedding model converts text, documents, or other supported content into numerical representations.

Database is running slowly

↓

AI Embedding Model

↓

[0.12, 0.85, 0.34, 0.91]

These numbers are called a vector embedding. The example numbers are illustrative.

Easy Example: Think of vectors like GPS coordinates for meaning. Two sentences describing similar problems may have nearby vector representations.

3. How Oracle AI Vector Search Works

Oracle AI Database can store vector embeddings alongside traditional relational data.

User Question

↓

Embedding Model

↓

Oracle AI Database 26ai

Relational Data + Vector Data

↓

Similarity Search

↓

Most Relevant Results

4. Vector Distance Methods

Oracle supports different ways to compare vectors.

• Cosine Distance: Compares vector directions.
• Euclidean Distance: Measures straight-line distance.
• Dot Product: Measures vector alignment.

Easy Memory Trick:
Cosine = Direction
Euclidean = Distance
Dot Product = Alignment

5. Practical Oracle SQL Example

Create a table containing a VECTOR column:

CREATE TABLE support_incidents (
    incident_id NUMBER PRIMARY KEY,
    description VARCHAR2(500),
    severity NUMBER,
    incident_vector VECTOR(4, FLOAT32)
);

Insert sample data:

INSERT INTO support_incidents
VALUES (
    101,
    'Database running slowly',
    1,
    TO_VECTOR('[0.12,0.85,0.34,0.91]')
);

COMMIT;

Search for similar incidents:

SELECT incident_id,
       description,
       VECTOR_DISTANCE(
           incident_vector,
           :search_vector,
           COSINE
       ) AS distance
FROM support_incidents
WHERE severity = 1
ORDER BY distance
FETCH FIRST 10 ROWS ONLY;

The search vector must be generated using a compatible embedding model. This query performs an exact nearest-neighbor search.

6. HNSW vs IVF Vector Indexes

HNSW - Hierarchical Navigable Small World

HNSW uses a graph of connected vectors. Imagine finding a house by first locating the city, then the neighborhood, and finally the street.

Top Layer
↓
Middle Layer
↓
Bottom Layer
↓
Nearest Vectors

Remember: HNSW = Graph navigation.

CREATE VECTOR INDEX idx_incident_hnsw
ON support_incidents(incident_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

IVF - Inverted File Index

IVF groups similar vectors into clusters. Instead of searching every vector, it searches selected nearby clusters.

All Vectors
↓
Cluster A | Cluster B | Cluster C
↓
Closest Clusters
↓
Matching Vectors

Remember: IVF = Group first, search next.

HNSW vs IVF Comparison

Feature	HNSW	IVF
Method	Graph	Clusters
Memory	Often higher	Can be lower
Strength	Fast approximate search	Scalable clustered search

7. Oracle RAC, Exadata and Sharding

• Oracle RAC: Distributes database workloads across multiple instances.
• Exadata: Supported vector operations can benefit from smart-storage processing.
• Partitioning: Can reduce the search space through partition pruning.
• Sharding: Distributes data across separate databases with local vector indexes.

8. Real DBA Use Case

Imagine your DBA team has historical incidents related to:

• ORA-12505 listener errors
• RMAN backup failures
• ASM diskgroup issues
• Data Guard synchronization problems
• Oracle EBS login failures

A DBA asks:

"How did we fix a similar listener problem after an environment refresh?"

Vector search can retrieve relevant previous incidents, even when they use different terminology.

9. Vector Search and RAG

RAG means Retrieval-Augmented Generation. It combines information retrieval with AI-generated answers.

DBA Question
↓
Embedding Model
↓
Oracle Vector Search
↓
Relevant Incident Documents
↓
Large Language Model
↓
Answer with References

RAG helps AI applications use relevant business information. However, generated answers still require verification.

10. Interview Questions

Q1. What is a vector?
A collection of numbers representing data features.

Q2. What is an embedding?
A numerical representation generated by an AI model.

Q3. What is VECTOR_DISTANCE?
An Oracle SQL function used to calculate vector distance.

Q4. What is HNSW?
A hierarchical graph-based approximate vector index.

Q5. What is IVF?
A cluster-based approximate vector search approach.

Q6. What is RAG?
Retrieval-Augmented Generation, which combines retrieved information with an AI model.

11. Final Thoughts

Oracle AI Vector Search allows database administrators to combine traditional SQL with AI-powered similarity searches.

For Oracle DBAs, the most important concepts to learn are vectors, embeddings, distance calculations, HNSW, IVF, and RAG.

Quick Revision:

• Vector = Numbers
• Embedding = Numerical representation
• Cosine = Direction
• HNSW = Graph
• IVF = Clusters
• RAG = Retrieval + AI Generation

Happy Learning & Happy DBA-ing!

Oracle DBA Learning Series

Upgrading a MariaDB Master/Slave Pair in Production

I'm an Oracle DBA by background, and most of my years have gone into Oracle EBS and Exadata. Lately I've been looking after MariaDB too. Recently I took a production MariaDB master/slave pair from 11.4.4 to 11.4.13. The data was about 640 GB, running on RHEL 8 in AWS, and the plan was a full outage with no application traffic.

It was my first MariaDB upgrade on a replicated production pair. It also took two attempts, because the first window was postponed. Along the way DEV and SIT taught me several lessons the hard way.

This post covers the lessons, the runbook, and the health check script I used. Hostnames in this article are generic placeholders: "db-primary" is the master and "db-replica" is the slave.

---

The setup

• MariaDB 11.4.x on RHEL 8, installed from MariaDB's own yum repository
• One master and one slave, using classic file and position replication (not GTID)
• SSL replication, with the slave replicating one filtered database
• About 640 GB of data
• Backups: AWS AMI snapshots, taken with the instances stopped
• DEV and SIT first (standalone servers, no replication), then production

The upgrade itself is simple: stop MariaDB, run dnf upgrade on the MariaDB packages, start MariaDB, and run mariadb-upgrade. The complicated part is everything around it.

---

Lesson 1: Check your repo file before anything else

On DEV, my first dnf upgrade said "Nothing to do". Then a partial upgrade updated only the compat packages, and the server stayed on the old version.

The cause was the MariaDB repo file. The [mariadb-main] section had its baseurl pinned to the exact version I was already running:

baseurl = https://dlm.mariadb.com/repo/mariadb-server/11.4.4/yum/rhel/8/x86_64

So dnf could never see anything newer. I first suspected a conflict with the RHEL AppStream repository, which was wrong. The thing that showed the truth was:

dnf repoquery --qf '%{name}-%{version} : %{repoid}' MariaDB-server MariaDB-client MariaDB-common MariaDB-shared

It listed only versions up to 11.4.4 from the mariadb-main repo. The fix is to point the baseurl at the 11.4 series instead of the exact version:

cp /etc/yum.repos.d/mariadb.repo /etc/yum.repos.d/mariadb.repo.bak_$(date +%Y%m%d)
sed -i 's|mariadb-server/11.4.4/yum/rhel/8/x86_64|mariadb-server/11.4/yum/rhel/8/x86_64|' /etc/yum.repos.d/mariadb.repo
dnf clean all && dnf makecache --refresh

dnf clean all and makecache only refresh the list of available packages. They are safe on a running database. After that, repoquery showed 11.4.13.

Takeaway: before any upgrade window, run repoquery and confirm the target version is visible on every node.

---

Lesson 2: A hostname change can split your binary logs

After an OS patching cycle, one of my servers came back with a different hostname. MariaDB names its binary logs after the hostname by default, so the binlog series split into two: one set named after the old hostname and one after the new.

Two things were behind it:

1. cloud-init was allowed to reset the hostname on boot (preserve_hostname: false).
2. The binlog basename was not set explicitly, so it followed the hostname.

On DEV and SIT I fixed it in two parts. First, I locked the hostname:

echo "preserve_hostname: true" | sudo tee /etc/cloud/cloud.cfg.d/99_preserve_hostname.cfg

Second, I set the binlog name explicitly in the server config so it no longer depends on the hostname:

log_bin = /database/mysql/mysql-bin

On production I made a different, deliberate choice. Replication there is file and position based, and the slave knows the master's binlog file names. I applied the hostname lock but did not change the binlog naming, so the logs keep their existing names and the slave simply resumes after the restart. Changing the binlog name on a live master is a separate planned change, not something to slip into an upgrade window.

Takeaway: lock the hostname on cloud instances, and know how your binlogs are named before any change that could touch the hostname.

---

Lesson 3: "Already upgraded" is normal within 11.4

After a successful package upgrade on SIT, running mariadb-upgrade printed:

This installation of MariaDB is already upgraded to 11.4.3.
There is no need to run mariadb-upgrade again for 11.4.13, because they're both 11.4.

That looks like a failure, but it isn't. Within the same release series (11.4.x), there is nothing for mariadb-upgrade to change in the system tables. You can still run it as a habit, but don't use --force just to see output.

Another normal message: the package step warns that server.cnf.rpmnew was created. That means your custom server.cnf was kept and the new default was saved next to it. Your configuration stays in place.

---

Lesson 4: The physical backup that never finished

My first plan for a backup was mariadb-backup, a hot physical backup, taken on the slave while it was running. I let it run for about 3 hours. It copied close to 600 GB, and then failed with a message about the redo log:

Was only able to copy log ... try increasing innodb_log_file_size

During a 3-hour copy, the InnoDB redo log wrapped around faster than the backup could read it. The redo log was only 512 MB (innodb_log_file_size = 536870912). Raising it needs a restart, so it is a separate planned change and could not be done in that window. Compression also saved much less than I'd estimated, around 7 to 10 percent, not the 40 to 60 percent I'd guessed.

I stopped chasing it and used AMI snapshots, taken with the instances stopped, as the only restore point.

Takeaway: on a large, busy database, check innodb_log_file_size before you rely on mariadb-backup, and always have a second restore method.

---

Lesson 5: No verified backup means no upgrade

The first production window was postponed because I did not have a verified restore point. That was the right decision, even though it was uncomfortable.

On the rescheduled night, the same rule was tested. In the first attempt, the slave's AMI did not complete, so we stopped before changing anything. Nothing was upgraded, and I brought the slave back to normal and confirmed replication. In the second attempt, both AMIs completed before I touched a single package.

Rule I now follow: no confirmed AMI on both nodes means no upgrade. The upgrade never starts until the SA team says, in words, that both snapshots are complete.

---

Lesson 6: Replication does not restart itself

Two weeks before the window, during pre-flight checks, I found the slave with both replication threads stopped and no errors recorded:

Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Seconds_Behind_Master: NULL

I hadn't stopped it. After a MariaDB restart, the slave's replication threads did not come back on their own. On the upgrade night it showed the same behavior after the restart. So the runbook has an explicit START SLAVE and a checkpoint right after it. Never assume replication resumes by itself.

---

Lesson 7: Verify the filesystem before you start the database

After the AMI snapshots, on the night of the upgrade, the /database mount did not come up automatically on the restarted instances, and the SA team attached it manually. If MariaDB had started against an unmounted data directory, it could have created or used the wrong location.

Before starting anything, I checked:

df -h /database
mount | grep /database
du -sh /database
ls -ld /database/mysql

I looked for the right device, the expected size, the real database files and mysql:mysql ownership. Only then did I start MariaDB and check its journal for errors. I also asked the SA team to fix the /etc/fstab entry so a future reboot doesn't repeat it.

Takeaway: after any instance restart, confirm that storage is mounted and correct before the database starts.

---

The runbook

This is the order I used for a master/slave pair with a full outage. Slave first, always.

Pre-flight (the day before and again before the window)

On both nodes:

grep -E "^\[|baseurl" /etc/yum.repos.d/mariadb.repo
dnf clean all && dnf makecache --refresh
dnf repoquery --qf '%{name}-%{version} : %{repoid}' MariaDB-server MariaDB-client MariaDB-common MariaDB-shared | grep mariadb-main
rpm -qa | grep -i MariaDB | sort
cat /etc/cloud/cloud.cfg.d/99_preserve_hostname.cfg
hostname
df -h /database

On the slave:

mariadb -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Errno"

Checks: both nodes see the same target version, the rpm database has no errors, the hostname lock exists, there is enough disk space, and replication shows Yes, Yes, lag 0.

Also check which packages are actually installed on each node. In my case, MariaDB-backup was installed on the slave but not on the master, so the two upgrade commands differed.

Phase 1: stop everything and take the AMIs

1. The application team confirms in writing that traffic is stopped.
2. On the slave, confirm lag 0, then:

   mariadb -e "STOP SLAVE;"
   systemctl stop mariadb

3. On the master:

   systemctl stop mariadb

4. Confirm both are stopped (systemctl status mariadb, and no mariadbd process in ps), then shut down both instances.
5. The SA team takes AMI snapshots of both. Wait for an explicit confirmation that both completed.
6. Power on, then check hostnames, mounts and data directories.

Phase 2: upgrade the slave first

systemctl start mariadb
mariadb -e "SELECT VERSION();"
mariadb -e "STOP SLAVE;"
systemctl stop mariadb
dnf upgrade MariaDB-server MariaDB-client MariaDB-backup MariaDB-common MariaDB-shared MariaDB-client-compat MariaDB-server-compat -y
rpm -qa | grep -i MariaDB | sort
systemctl start mariadb
mariadb-upgrade -u root
mariadb -e "SELECT VERSION();"
mariadb -e "START SLAVE;"
sleep 20
mariadb -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Errno|Last_IO_Error|Last_SQL_Error"

Checkpoint 1: both threads Yes, lag 0, no errors. If it is not clean, stop. Do not touch the master. Troubleshoot or roll back.

Phase 3: upgrade the master

Start the master first at the old version so the slave has something to connect to. After Checkpoint 1 passes:

systemctl stop mariadb
dnf upgrade MariaDB-server MariaDB-client MariaDB-common MariaDB-shared MariaDB-client-compat MariaDB-server-compat -y
rpm -qa | grep -i MariaDB | sort
systemctl start mariadb
mariadb-upgrade -u root
mariadb -e "SELECT VERSION();"
mariadb -e "SHOW MASTER STATUS;"

Phase 4: validate

On both nodes: hostname, SHOW VARIABLES LIKE 'server_id'; and the health check script below. On the slave, run the replication check again.

Checkpoint 2: both threads Yes, lag 0, no errors, same version on both nodes, hostnames and server IDs unchanged.

Then the application team runs their smoke test and confirms before traffic resumes.

Rollback

Decide the trigger before you start, not during. Mine was a fixed time in the window, set so that there was still enough time to restore from the AMIs, restart, and validate. The primary rollback is restoring the AMI snapshots with the SA team. Package downgrade is a fallback only. If the slave is the only affected node, the master stays untouched.

---

The health check script

This is a one-go script I run before and after every change. It checks the service, connectivity, version, connections, InnoDB, query counters, replication, disk and the error log. It connects as root over the local socket, because root on my servers uses unix_socket authentication with no password, so no credentials are stored in the script.

#!/bin/bash
# MariaDB Health Check - run as root (socket auth)

LOGFILE="/tmp/mariadb_health_$(date +%Y%m%d_%H%M%S).log"
MYSQL_CMD="mariadb"
command -v mariadb &>/dev/null || MYSQL_CMD="mysql"

if [ "$EUID" -ne 0 ]; then
    echo "Run as root: sudo $0"
    exit 1
fi

log() { tee -a "$LOGFILE"; }

echo "MariaDB Health Check - $(date)" | log

echo -e "\n--- 1. Service Status ---" | log
if systemctl is-active --quiet mariadb; then
    echo "MariaDB service: RUNNING" | log
else
    echo "MariaDB service: NOT RUNNING" | log
    exit 1
fi

echo -e "\n--- 2. Connectivity ---" | log
if $MYSQL_CMD -e "SELECT 1;" &>/dev/null; then
    echo "Connection: OK" | log
else
    echo "Connection: FAILED" | log
    exit 1
fi

echo -e "\n--- 3. Version and Uptime ---" | log
$MYSQL_CMD -e "SELECT VERSION() AS version;" 2>/dev/null | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS LIKE 'Uptime';" 2>/dev/null | log

echo -e "\n--- 4. Connections and Threads ---" | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Threads_connected','Threads_running','Max_used_connections','Connections','Aborted_connects');" 2>/dev/null | log
$MYSQL_CMD -e "SHOW VARIABLES LIKE 'max_connections';" 2>/dev/null | log

echo -e "\n--- 5. InnoDB Buffer Pool and Locks ---" | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Innodb_buffer_pool_pages_free','Innodb_buffer_pool_pages_total',
   'Innodb_row_lock_current_waits','Innodb_row_lock_time_avg');" 2>/dev/null | log

echo -e "\n--- 6. Query Performance ---" | log
$MYSQL_CMD -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Slow_queries','Questions','Com_select','Com_insert','Com_update','Com_delete');" 2>/dev/null | log

echo -e "\n--- 7. Replication Status ---" | log
REPL=$($MYSQL_CMD -e "SHOW SLAVE STATUS\G" 2>/dev/null)
if [ -n "$REPL" ]; then
    echo "$REPL" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Last_Error" | log
else
    echo "No replication configured (or not a replica)." | log
fi

echo -e "\n--- 8. Disk Space (Data Directory) ---" | log
DATADIR=$($MYSQL_CMD -N -e "SHOW VARIABLES LIKE 'datadir';" 2>/dev/null | awk '{print $2}')
[ -n "$DATADIR" ] && df -h "$DATADIR" | log

echo -e "\n--- 9. Recent Errors ---" | log
ERRLOG=$($MYSQL_CMD -N -e "SHOW VARIABLES LIKE 'log_error';" 2>/dev/null | awk '{print $2}')
if [ -n "$ERRLOG" ] && [ -f "$ERRLOG" ]; then
    tail -n 20 "$ERRLOG" | log
else
    # log_error is empty on some systems: errors go to the journal
    journalctl -u mariadb --no-pager -n 20 2>/dev/null | log
fi

echo -e "\n--- 10. Storage Engine Summary ---" | log
$MYSQL_CMD -e "SELECT ENGINE, COUNT(*) AS table_count
  FROM information_schema.tables
  WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
  GROUP BY ENGINE;" 2>/dev/null | log

echo -e "\nReport saved to: $LOGFILE" | log

One lesson from this script: on my servers log_error was empty, so the original version said "error log not found". The journal fallback in section 9 fixes that. If an error log "can't be found", check where your logging really goes before you assume it's fine.

---

What I'd tell anyone doing this for the first time

1. Do DEV and SIT first, and write down every surprise. Mine paid for production.
2. Verify the repo, the target version and the rpm database on every node before the window.
3. Have two checkpoints and respect them. Slave first, and never touch the master until the slave is clean.
4. No confirmed restore point, no upgrade. Wait for a clear confirmation from the team that takes the snapshots.
5. Decide your rollback trigger before you start.
6. After any restart, check mounts, hostnames and replication before you trust anything.
7. Get the expectations right with other teams: who stops traffic, who confirms, who takes the AMIs, and who tests afterwards.

The upgrade itself took very little time. Everything that mattered happened before it and around it. Next on my list, in separate windows, are OS patching and raising innodb_log_file_size so that physical backups can work on a database this size.

---

If you run MariaDB in production on AWS and you've hit the same issues, or found a better way to handle any of these, I'd like to hear about it in the comments.