Customer may want to know if the age of Capture queue (as shown in qstatus) is a true representation of the Capture latency with respect to the source database.
If the Capture is current with Oracle, meaning Capture is processing the latest redo log at a point in time, then it can be said that the age as shown in the qstatus is a true representation of the Capture latency with respect to the source database. However, if the Capture falls behind Oracle, meaning that it is processing a redo log which is earlier than the latest redo log, then the age in qstatus does not correctly depict the Capture latency. The following illustrates the point:
sp_ctrl>show capture detail
Host: host_name
Operations
Source Status Captured Since
---------- --------------- ---------- ------------------
o.sourceSID Running 653887934 04-Apr-16 10:33:01
Oracle current redo log : 30772
Capture current redo log : 30761
Capture log offset : 2990494496
Last redo record processed:
Operation on "OWNER".”TABLE_NAME" at 04/06/16 15:39:25
$ date (here $ represents the OS prompt)
Wed Apr 6 18:03:00 MDT 2016
The current server date is 18:03:00; however capture is still processing data from 15:39:25, 2 hours and 24 minutes behind. However, qstatus shows 0 minutes:
sp_ctrl>qstatus
Queues Statistics for host_name
Name: o.sourceSID (Capture queue)
Number of messages: 151900 (Age 0 min; Size 2 mb)
Backlog (messages): 0 (Age 0 min)
Name: host_name (Export queue)
Number of messages: 3851 (Age 0 min; Size 1 mb)
Backlog (messages): 0 (Age 0 min)
So the age in qstatus is not always the correct indicator of the Capture latency.
The following steps can be undertaken for assessing the true latency by using “show capture detail” instead of relying on the “age” stat in qstatus output.
When we do a "show capture detail", we display the Oracle time of the current redo record that we are processing. Assuming that the system time and the Oracle time are synchronized, then I would extract the date/time from the "show capture detail" or another show command that might have the same data, and then substract it from the current time.
For example, if Capture is up and running and you issue a shell command like:
echo "show capture detail" | sp_ctrl | grep "Operation on" | awk ' {print $(NF-1)" " $NF} '
It will do the "show capture detail" command, grep out the line that starts with "Operation on" and then grab the last two column values, which represent Oracle timestamp of the last redo record we process.
As an example, when I ran the shell command I got:
04/06/16 20:22:55
You then can do the "date" command and get the current time. By default, it will be in a different format, but there are many ways to solve the problem from here. But basically one way or another you convert one or both of the different data strings and then subtract one date from the other giving the latency.
Needless to say, the above commands will need to be modified if the format of “show capture detail” undergoes change as it can happen over time due to product modifications, etc.
