Oracle Database to Elasticsearch: How to Index Your Data

Indexing Oracle Database into Elasticsearch is what you do when an application has outgrown LIKE queries against a system of record. Full-text search, typo tolerance and relevance ranking are things a relational database does poorly, and running them against an Oracle instance that is also serving transactions is worse still.

Before anything else, check one thing, because it decides whether this pipeline is available to you at all. The Elasticsearch destination runs on Airbyte Core and PyAirbyte, and is not offered on the Standard, Plus, Pro or Enterprise Flex plans.

Oracle Database to Elasticsearch at a glance:

Why move data from Oracle Database to Elasticsearch?

Two situations account for most of these pipelines.

The first is operational search over records: agents finding a customer by fragments of an address, or an internal tool searching several fields at once. Oracle instances are often the system of record for exactly this kind of data and a poor place to run that query.

The second is keeping search load off a licensed system. Oracle licensing is frequently tied to capacity, so moving a workload off the instance is a commercial argument as much as a technical one. Both cases want current data rather than analytical history, which is why change capture matters more here than sync frequency.

What do you need before you start?

The first item can stop the project outright, and the second brings another person into it:

A self-managed Airbyte deployment. The Elasticsearch destination is available on Airbyte Core and PyAirbyte, and not on the paid Cloud tiers. Everything below assumes self-managed.

A decision about deletes, and DBA time if you want them. The standard Oracle source replicates using a cursor and cannot observe a deletion. Log-based change data capture is on the Enterprise source and needs supplemental logging plus a grant list covering flashback access, select on any table, the catalog roles, log mining and more.

An identifier length audit. LogMiner ignores tables and columns whose names exceed 30 characters, silently. Old Oracle schemas are under that by construction; anything built on 12.2 or later can exceed it easily.

A view that produces the document you want to search. This is the part people skip. A table row is usually a poor document, because the thing users search for is spread across a parent table and several children, and Elasticsearch does not join.

Finally, an index mapping you have designed rather than inherited, deciding which fields are analysed for search and which are keyword fields for filtering.

How do you build an Oracle to Elasticsearch pipeline in Airbyte?

Step 1: Build the document in Oracle

Create a view that joins whatever a search result needs into a single row: the customer with their addresses, account status and contact details, rather than four tables of identifiers. Oracle is very good at joins, and doing this here means one document per business entity rather than one per row.

Step 2: Configure the Oracle source

Click Sources in the left navigation, then New Source, and select the Oracle connector you decided on, following adding a source. Supply the host, port, service name or SID, schema and credentials, and pick an encryption method. Native network encryption and TLS with certificate verification are both available.

Step 3: Configure the Elasticsearch destination

Click Destinations, then New Destination, and select Elasticsearch, following adding a destination. Supply your endpoint and authentication. Create the index with your intended mapping first, so the connector writes into a schema you designed rather than one dynamic mapping invented on the first document.

Step 4: Create the connection and schedule it

Click Connections, then New connection, select your view or tables and an incremental sync mode. Set a frequency reflecting how quickly users expect new records to become findable, which for an operational search index is usually minutes rather than hours.

Note that change data capture applies to tables rather than views, so if you took the view approach in step 1 you will be using cursor-based incremental on it and should plan deletes accordingly.

Why does the index mapping matter so much?

Because Elasticsearch decides at index time how a field can be searched, and changing that decision later means reindexing everything. Dynamic mapping guesses from the first document it sees, and its guesses are reasonable rather than right.

The distinction that matters is analysed text versus keyword. A customer name or a product description should be analysed, so that a partial search matches. An account number, a status code or a branch identifier should be a keyword field, so it filters and aggregates exactly. Get these the wrong way round and your filters return nothing while your searches match everything.

Oracle numeric and date columns usually map cleanly, but check anything stored as a string that should behave as a number or a date, since dynamic mapping takes it at face value. Note also that Oracle's DATE carries a time component, which surprises anyone assuming it behaves like a plain date.

How do deletes behave in the index?

This is the main argument for the Enterprise connector here. A search index that returns deleted records is worse than one that is slightly stale, because a user clicks the result and lands on nothing. Cursor-based replication cannot observe a deletion at all, so those rows persist indefinitely.

With log-based capture, deleted rows arrive carrying a deletion marker rather than vanishing. In a warehouse you would choose between mirroring current state and keeping history. In a search index there is no such tension: you want the document gone, so filter on that marker when querying or handle removal in whatever layer sits between your application and the index.

One caveat if you took the view-based approach. Deleting a child row may change what the view produces without deleting the parent, so a document can need updating rather than removing. Work out what a deletion in each underlying table means for the document before relying on this.

Frequently asked questions

Why can I not find the Elasticsearch destination in Airbyte Cloud?

It is available on Airbyte Core and PyAirbyte, and not on the Standard, Plus, Pro or Enterprise Flex plans. This pipeline needs a self-managed deployment.

Should I index one table or a joined view?

A joined view, almost always. Elasticsearch does not join, so build the document you want to search inside Oracle and index that as a single stream.

Does the standard Oracle source support CDC?

No. Log-based change data capture is on the Enterprise Oracle source. The standard connector uses a cursor and cannot capture deletes.

Why is one of my Oracle tables never updating in the index?

Check the identifier length. LogMiner ignores tables and columns whose names exceed 30 characters, without raising an error.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want the Oracle view and the index mapping, both of which are short and both of which determine whether the result is any good.

Get your Oracle data into Elasticsearch

Check your Airbyte plan first, because that decides everything else. Then build the document in Oracle rather than hoping a table row will do, design the index mapping before the first sync, audit identifier lengths if you are using CDC, and decide what a deletion in each underlying table should mean.

Airbyte's connector catalog includes 700+ pre-built connectors, so the same Oracle source can feed a warehouse alongside the index. For that pairing, see Oracle Database to BigQuery, and for the same destination fed by another relational source, MySQL to Elasticsearch.

Start syncing now →