# Oracle 11g Synchronization with logstash
**URL:** https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639
**Category:** Logstash
**Created:** [January 15, 2017, 10:29am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639 "2017-01-15T10:29:45Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 15, 2017, 10:29am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/1 "2017-01-15T10:29:45Z")
Hello,
I'm using the logstash 5.1.1 to get the data from Oracle DB 11g to elastic search, the connection and data fetching is working fine but the problem is with the synchronization between the DB and Elasticsearch, every time the job will run the statement and the data will be duplicated into elastic, how can i make it synchronized? to take the new inserted, updated and remove deleted records dynamically while the job is running?
here is the config file for the JDBC connection:
input {
jdbc {
jdbc\_driver\_library =\> "pat of (ojdbc6.jar)"
jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver"
jdbc\_connection\_string =\> "jdbc:oracle:thin:@DB\_IP:Port:DB\_ServiceName"
jdbc\_user =\> "db\_schema\_name"
jdbc\_password =\> "db\_schema\_password"
jdbc\_validate\_connection =\> true
statement =\> "SELECT \* FROM table\_1"
}
}
output {
elasticsearch {
action =\> "index"
hosts =\> "localhost:9200"
index =\> "Index\_1"
document\_type =\> "record"
workers =\> 1
}
}
Thanks.
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 15, 2017, 10:40am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/2 "2017-01-15T10:40:33Z")
This topic from a few days ago is relevant to your problem:
> [@Problem with dynamic log update using http\_poller](https://discuss.elastic.co/t/problem-with-dynamic-log-update-using-http-poller/71482):
>
> Hi , This is my configuration file input { http\_poller{ urls =\> { url129server =\> "http://xxx.log" } codec =\> "plain" metadata\_target =\> "http\_poller\_metadata" request\_timeout =\> 60 schedule =\> { "every" =\> "1h"} tags =\> check1 } file{ sincedb\_path =\> "since\_db" } } filter { if "check1" in [tags]{ split { field =\> "message" } grok { match =\> {"message" =\> '%{IP:client} - - \[%{MONTHDAY:day}/%{MONTH:month}/%{YEAR:year}:%{HOUR:hour}:%{MINUTE:minute…
You should also read the jdbc input documentation, specifically what's said about the `sql_last_value` parameter.
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 15, 2017, 11:49am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/3 "2017-01-15T11:49:47Z")
Hi,
I have checked the mentioned URL, but i am not getting the point how to configure the document\_id parameter in the input and output, can you give me an example on it using the same JDBC connection?
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 15, 2017, 12:22pm UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/4 "2017-01-15T12:22:55Z")
in addition, when i add the following to the output i have only one record:
document\_id =\> "%{field\_name (the primary key field)}"
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 16, 2017, 6:34am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/5 "2017-01-16T06:34:43Z")
> I have checked the mentioned URL, but i am not getting the point how to configure the document\_id parameter in the input and output, can you give me an example on it using the same JDBC connection?
You're on the right track. Since your data already has a primary key you don't need the fingerprint filter and you can just reference the existing field containing the primary key.
> in addition, when i add the following to the output i have only one record:
What id does that document have? What does the document look like?
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 7:54am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/6 "2017-01-16T07:54:43Z")
its an Oracle table, contains a string primary key (varchar2)
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 16, 2017, 8:17am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/7 "2017-01-16T08:17:00Z")
What id does that document have? What does the document look like?
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 10:17am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/8 "2017-01-16T10:17:09Z")
how can i identify the ID which is used by the document? the document will show all records from a statement in JDBC with the timestamp of each one
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 16, 2017, 10:48am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/9 "2017-01-16T10:48:35Z")
> how can i identify the ID which is used by the document?
In Kibana this is displayed as the `_id` field.
> the document will show all records from a statement in JDBC with the timestamp of each one
I'm not sure what you mean. Please copy/paste an example document from the JSON tab in Kibana.
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 10:49am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/10 "2017-01-16T10:49:37Z")
```
{
"_index": "bcc_customers",
"_type": "record",
"_id": "%{customer_NO}",
"_score": null,
"_source": {
"company_type": null,
"notes": "²ßáï",
"reg_no": null,
"birth_date": "1956-03-15T22:00:00.000Z",
"phone2": null,
"channel": null,
"phone_work": null,
"employee_no": null,
"cam_lan_id": null,
"bcc_cty_id": 1,
"type": "bcc_customers",
"in_id": null,
"manager_pass_id": null,
"modification_date": null,
"passport_expiry_date": null,
"employer": null,
"fax": null,
"customer_address": null,
"customer_no": 9018,
"id_number": "AA0684791",
"crg_prf_id": 1,
"use_parent_address": 1,
"vat": null,
"creation_date": "2005-12-19T22:00:00.000Z",
"valid_qs_status": 4,
"created_by": "EKHACHATRYAN",
"tags": [],
"father_name_2": "èáõµ»ÝÇ",
"father_name": "Ruben",
"phone": "010348901",
"old_prof_no_mig": null,
"corporate_name_2": null,
"gender": 1,
"family_name_2": "¼³ù³ñÛ³Ý",
"scale": null,
"eduacation": null,
"first_name_2": "²ßáï",
"crg_cnt_code": "AM",
"issue_date": null,
"brs_ret_code": "PROVIS",
"is_forensics_blacklisted": null,
"monthlysalary": null,
"activity_type": null,
"@version": "1",
"corporate_name": null,
"id_type": 1,
"forensics_status": 4,
"receive_market_msgs": null,
"crg_tit_id": 1,
"first_name": "Ashot",
"email": null,
"issued_by": null,
"contact_person": null,
"no_of_employees": null,
"bcc_cst_no": null,
"employment": null,
"vet_status": 4,
"in_cus_acc_id": null,
"bcc_cst_level": 1,
"manager_name": null,
"marital_status": 2,
"bcc_cct_id": 1,
"company_tax_code": null,
"@timestamp": "2017-01-16T10:23:29.273Z",
"modified_by": null,
"sub_info_no_reg": null,
"family_name": "Zakaryan",
"age": null
},
"fields": {
"birth_date": [
-435376800000
],
"creation_date": [
1135029600000
],
"@timestamp": [
1484562209273
]
},
"sort": [
1484562209273
]
}
```
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 10:51am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/11 "2017-01-16T10:51:13Z")
the \_ID field appears like '%{customer\_NO}' (with the text not the number)
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 16, 2017, 10:51am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/12 "2017-01-16T10:51:27Z")
Please format things like this as preformatted text.
> ```
> "_id": "%{customer_NO}",
>
> ```
The document doesn't contain a `customer_NO` field. Use `customer_no` instead.
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 10:55am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/13 "2017-01-16T10:55:23Z")
now its "\_id": "%customer\_no", but i still facing the same problem, i have ran the statement:
select \* from table1 where rownum \< 10
but i have only 3 records in Kibana (for two items - one is duplicated).
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 16, 2017, 10:57am UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/14 "2017-01-16T10:57:49Z")
Show the documents. **Format them as preformatted text.**
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 12:18pm UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/15 "2017-01-16T12:18:05Z")
```
{
"_index": "bcc_customers",
"_type": "record",
"_id": "%customer_no",
"_score": null,
"_source":
{
"company_type": null,
"notes": "23082003",
"reg_no": null,
"birth_date": "1974-03-10T22:00:00.000Z",
"phone2": null,
"channel": null,
"phone_work": null,
"employee_no": null,
"cam_lan_id": null,
"bcc_cty_id": 1,
"type": "bcc_customers",
"in_id": null,
"manager_pass_id": null,
"modification_date": "2012-12-26T08:44:35.000Z",
"passport_expiry_date": null,
"employer": null,
"fax": null,
"customer_address": null,
"customer_no": 9024,
"id_number": "126",
"crg_prf_id": null,
"use_parent_address": 1,
"vat": null,
"creation_date": "2005-12-19T22:00:00.000Z",
"valid_qs_status": 7,
"created_by": "KBALEKJIAN",
"tags": [],
"father_name_2": "úѳݻëë",
"father_name": "Ohaness",
"phone": "010249930",
"old_prof_no_mig": null,
"corporate_name_2": null,
"gender": 1,
"family_name_2": "´³É»ùçÛ³Ý",
"scale": null,
"eduacation": null,
"first_name_2": "γñÝÇ·",
"crg_cnt_code": "AM",
"issue_date": null,
"brs_ret_code": "PROVIS",
"is_forensics_blacklisted": null,
"monthlysalary": null,
"activity_type": null,
"@version": "1",
"corporate_name": null,
"id_type": 2,
"forensics_status": 7,
"receive_market_msgs": null,
"crg_tit_id": 1,
"first_name": "Karnig",
"email": null,
"issued_by": null,
"contact_person": null,
"no_of_employees": null,
"bcc_cst_no": null,
"employment": null,
"vet_status": 6,
"in_cus_acc_id": null,
"bcc_cst_level": 1,
"manager_name": null,
"marital_status": 2,
"bcc_cct_id": 6,
"company_tax_code": null,
"@timestamp": "2017-01-16T10:59:24.815Z",
"modified_by": "SYSADMIN",
"sub_info_no_reg": null,
"family_name": "Balekjian",
"age": null
},
"fields":
{
"creation_date": [1135029600000],
"@timestamp": [1484564364815],
"birth_date": [132184800000],
"modification_date": [1356511475000]
},
"sort": [1484564364815]
}
```
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 16, 2017, 12:33pm UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/16 "2017-01-16T12:33:28Z")
> "id": "%customerno",
Where are the curly braces?
When you're not posting your JSON as preformatted text (using the `` toolbar button) parts of the JSON text disappears, making it ambiguous and possibly leading to misunderstandings. I don't have time for misunderstandings so unless you start formatting the text as requested I won't spend more time in this thread.
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 12:37pm UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/17 "2017-01-16T12:37:55Z")
Sorry for that, i am still beginner in JSON at all, hope this will get you what you need exactly:
```
{
"_index": "bcc_customers",
"_type": "record",
"_id": "9026",
"_score": null,
"_source": {
"company_type": null,
"notes": "409100",
"reg_no": null,
"birth_date": "1962-04-16T22:00:00.000Z",
"phone2": null,
"channel": null,
"phone_work": null,
"employee_no": null,
"cam_lan_id": null,
"bcc_cty_id": 1,
"type": "bcc_customers",
"in_id": null,
"manager_pass_id": null,
"modification_date": null,
"passport_expiry_date": null,
"employer": null,
"fax": null,
"customer_address": null,
"customer_no": 9026,
"id_number": "AA0338434",
"crg_prf_id": 1,
"use_parent_address": 1,
"vat": null,
"creation_date": "2005-12-19T22:00:00.000Z",
"valid_qs_status": 7,
"created_by": "KHACHATRYAN",
"tags": [],
"father_name_2": "ê»ñÛáųÛÇ",
"father_name": "Seryozha",
"phone": "010 554000",
"old_prof_no_mig": null,
"corporate_name_2": null,
"gender": 1,
"family_name_2": "سÝáõÏÛ³Ý",
"scale": null,
"eduacation": null,
"first_name_2": "ÐáíѳÝÝë»ë",
"crg_cnt_code": "AM",
"issue_date": null,
"brs_ret_code": "PROVIS",
"is_forensics_blacklisted": null,
"monthlysalary": null,
"activity_type": null,
"@version": "1",
"corporate_name": null,
"id_type": 1,
"forensics_status": 7,
"receive_market_msgs": null,
"crg_tit_id": 1,
"first_name": "Hovhannes",
"email": null,
"issued_by": null,
"contact_person": null,
"no_of_employees": null,
"bcc_cst_no": null,
"employment": null,
"vet_status": 6,
"in_cus_acc_id": null,
"bcc_cst_level": 1,
"manager_name": null,
"marital_status": 2,
"bcc_cct_id": 1,
"company_tax_code": null,
"@timestamp": "2017-01-16T12:38:17.201Z",
"modified_by": null,
"sub_info_no_reg": null,
"family_name": "Manukyan",
"age": null
},
"fields": {
"creation_date": [
1135029600000
],
"@timestamp": [
1484570297201
],
"birth_date": [
-243309600000
]
},
"sort": [
1484570297201
]
}
```
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 12:41pm UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/18 "2017-01-16T12:41:01Z")
now its working, for the insert and the update it seems to be OK, but when i need to delete a record from DB it will not be deleted from the Kibana.
---
### Author:  [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [January 16, 2017, 1:06pm UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/19 "2017-01-16T13:06:05Z")
Yeah, deletions are harder. I don't know if there's a better way than reindexing everything from the beginning, unless you can somehow capture the deletions as events and funnel them to Logstash.
---
### Author:  [@Abdullah\_Alaiwat](https://discuss.elastic.co/u/Abdullah_Alaiwat)
#### Post date: [January 16, 2017, 1:09pm UTC](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639/20 "2017-01-16T13:09:09Z")
reindexing everything from the beginning will cause a huge performance problem specially that our DB is very huge, any other suggestions? we are not able to capture the deletions as events neither as we cant use any procedures or objects in the statement part in the JDBC connection.
[Next page](https://discuss.elastic.co/t/oracle-11g-synchronization-with-logstash/71639.md?page=2)