How to Sync Oracle Data to Elasticsearch with Logstash: Step‑by‑Step Guide

Environment: Elasticsearch 7.8.0 + Logstash 7.8.0

Solution 1: Direct DB → Logstash → Elasticsearch. Simple to configure but introduces latency and can pressure the database under large volume or short intervals.

Solution 2: Real‑time dual write. The business system writes/updates both the database and Elasticsearch simultaneously. Provides good real‑time performance but tightly couples the business system and may affect its performance.

Solution 3: Asynchronous dual write using a message queue. The business system publishes changes to MQ, and a separate consumer syncs them to Elasticsearch. Avoids impact on the business system but adds latency and system complexity.

Elasticsearch and Logstash Installation & Configuration

Refer to the tutorial "Springboot Integration of ELK Log Collection Detailed Steps" for installation details.

Prepare Files

Obtain the Oracle driver JAR (e.g., ojdbc7-12.1.0.2.jar).

Configuration File

Create jdbc.conf under %logstash_home%\bin with the following content:

input {
  jdbc {
    jdbc_driver_library => "E:/12.1.0.2/ojdbc7-12.1.0.2.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521:orcl"
    jdbc_user => "scott"
    jdbc_password => "tigger"
    statement => "SELECT * FROM (SELECT NP.*, ROWNUM RN FROM (SELECT * FROM PRODUCTS) NP) T WHERE T.CREATE_TIME BETWEEN to_date('2019-01-01', 'yyyy-mm-dd') AND to_date('2019-06-30', 'yyyy-mm-dd') AND RN > :sql_last_value "
    record_last_run => "true"
    use_column_value => "true"
    tracking_column => "RN"
    last_run_metadata_path => "E:/es/logstash-7.8.0/config/last_id"
    clean_run => "false"
    type => "es-jdbc"
  }
}
output {
  if [type] == "es-jdbc" {
    elasticsearch {
      hosts => "localhost:9200"
      index => "p_users_index"
      document_id => "%{id}"
    }
  }
}

Key parameters:

jdbc_driver_library : Path to the Oracle driver JAR.

jdbc_driver_class : Driver class name (prefixed with Java::).

jdbc_connection_string : JDBC URL for the Oracle database.

jdbc_user and jdbc_password : Oracle credentials.

statement : SQL query whose result set is sent to Elasticsearch.

record_last_run : When true, Logstash records the last processed value of the tracking_column to a metadata file.

use_column_value : Enables tracking a custom column (set to true when record_last_run is true).

tracking_column : The column used for incremental tracking (must be monotonically increasing, e.g., ROWNUM).

last_run_metadata_path : File path where the last run position is stored.

Start Logstash with the configuration:

%logstash_home%\bin\logstash -f jdbc.conf

View Data

Monitor synchronization in real time via Kibana:

Alternatively, query the Elasticsearch index through its REST API: