DBTRAN -ir: How can I find out the log offset values for older log ranges?

[VolkerBarth]

Now and then, I have to translate parts of a larger transaction log. Usually I can restrict the log range of interest to a certain timespan, so a translation of the whole log would be contra-productive.

Attempts to use "DBTRAN -j date/time" have usually felt very inefficient since DBTRAN seems to scan the whole log in order to find the according timespan - it somewhat looks like it would do a full-text search over the checkpoint date...

In contrast, restricting the range with DBTRAN -ir offset1,offset2 works well, however, it leaves the question how to find according log offset ranges when only a timespan is known.

Note, in the current task, I could cope with that as the system is using SQL Remote, so log offsets are relevant in their own and are documented with SQL Remote's message output.

However, for non-replicating databases, I would like to know how to get to know which log offset would fit to an according date/time value - at least approximately.

[johnsmirnios · Accepted solution]

Hmmm. If you get the start and end offset from dbinfo you could do a bit of a binary search: translate a portion of the middle of the log to see the time, then go earlier or later depending on whether you are looking for an earlier or later time. Repeat as necessary. I bet that doing this manually will generally take more time than translating the entire log; however, it sounds like a reasonable enhancement request for dbtran -j. There would be problems, of course, if the time is not monotonically increasing (ie, if the clock was changed while the server was running) but it should largely work well enough.

Internally, dbtran can only start translating operations starting at a checkpoint operation. Each checkpoint operation in the log contains the offset to the previous checkpoint and the first page of the log contains the offset of the last checkpoint. It wouldn't be too hard for a tool like dbtran to quickly find the time associated with each checkpoint in the file. It could produce a map of checkpoints & times or it could use that information to improve the -j support.

[VolkerBarth · Accepted solution]

Thanks for the feedback, John. Would it be an alternative if the message output for a checkpoint (written via dbsrv12 -o) would also contain the current log offset?

In my case, I would usually still have the message logs available, so I could use them to retrieve the log offset.

(Of course I could add an event myself (say, on a hourly schedule) that would MESSAGE out the contents of db_property('CurrentRedoPos'), methinks...)

[johnsmirnios · Accepted solution]

That's thinking outside of the box. Yes, server messages would help you create the mapping you are looking for. I like the idea of the self-contained solution that gets the information from the log itself but your solution could be implemented without waiting for a product change.