Why do I have missing events from an Oracle database between 12:00 and 13:00?

[hunyady]

Hi,

We have tracelogs in Splunk. We have more examples where there are missing all events between 12:00 and 12:59. All other events are correct.
Does anyone have any idea why this would happen?
DB is Oracle; Eventtime generated in query: to_char(eventtime,'yyyy-mm-dd hh24:mi:ss')

Thank you !

[grijhwani]

I'll add a second answer, because this is actually fundamentally different, but equally plausible problem. It is something else I have been tripped up by in the past.

The second problem is that Splunk has a nasty tendency to ignore an explicit human-readable timestamp if it recognises a potential embedded epoch time (currently 1442nnnnnn) in the event data. I have no idea why it should do this as a default, rather than adhering to a regular interpretation of a fixed timestamp placement. The only way to get around this seems to be to explicitly configure the sourcetype to limit the depth of search from the start of the line to no deeper than the fixed timestamp.

[hunyady]

we are near by our problem, but not solved.
in our record we have eventtime : 12:36:13, but Splunk eventtime showed for the same record is 00:36:13. And that's by all events between 12:00 and 13:00 hours in all days.
App is "DB Connect v2"
Anybody an idea ?

[grijhwani]

As has been hinted at, missing log data is often a symptom of source, indexer, and/or search head not agreeing on the time standard. If your data is consistently missing for the most recent hour, but then appears to back fill, you might find that there is a discrepancy causing the data you search for to be forward-dated by an hour. That happens when (for instance) the indexer is working to GMT and the source is working to BST and the indexer is taking its time reference from the log entry, not the current clock. (In that case the logs will be forward-dated by an hour, and any search up to the present moment will not find it.

If, however, you consistently lose data during one specific hour of the day I would look to some daily task or other interrupting the log generation or forwarding on the host. (Premature deletion of logs by a daily process causing the data to go missing until the log service is restarted for instance, or something actually stalling the log generator and restarting it an hour later.)

[hunyady]

on our Linux server (Splunk) we have Berlin/Europe
one of our Oracle has Timezone +2:00, the other has UTC
we have on both Oracle-Server the same error with event-missing

[grijhwani]

In that case I would suggest you widen the time parameters of your search (+/- 24 hours) and see if you can locate a sample of the "missing" data by some other distinctly identifiable property, to see if the is a bogus time offset in play. The fact that your two sources have different timezones just means that they might both be wrong in different ways. Try locating a sample from one first and then a distinct sample from the other, both at times that should be appearing near identically. If they do appear, do they appear at the same wrong place in the timeline, or are they different. And if they do appear at the wrong time, how does that offset compare with the difference(s) between their appearance in the timeline. Mixing timezones with Splunk can get quite hairy (as it can with anything if you don't normalise on a common time reference).

I should say that I offer this suggestion from a position of experience, having faced problems in the past with aggregating international data and losing things in the timeline due to misinterpretation of the datestamps. If you're using light forwarders and relying on the indexer to do all the cooking of the data, then you're almost guaranteeing problems if your timestamps don't have timezone references, because it will interpret timestamps with the assumption that they are in its local time.

[somesoni2]

What is the timezone of Oracle server and your splunk server (check the current time in both and see if they match)? We've seem similar posts where events were not ingested for particular time period of day and usually because of timezone issues.

[hunyady]

my query: select (fieldlist) where eventtime > sysdate -3/1440 and eventtime<= sysdate - 5/86400

[DennisMohn]

Have you checked if the corresponding events are present in the source(file)? How did you configure your data input?

Without further information it is hard to help.

[hunyady]

of course the events between 12 and 13 o'clock are presented in DB (with oracle sql developer checked, they are to see)
data input is Splunk DB Connect V2, we have all other events, missing only between 12 and 13 o'clock an all day.

[richgalloway]

What is your exact (sanitized, if necessary) DB Connect query?