How to get the oracle dataguard log transfer and applying status?

Question

Logs are transferred from primary db instances to stand-by db and applied continuously.

Please advise how to get the report in the following format (preferably in browser) at some frequency (say 5sec/10sec) for monitoring purposes at stand-by db (prefer to any built-in function or class in oracle dataguard):

-----------------------------------------------------------------------------------
log_name   instance   created_time   tx_start    tx_end  tx_duration  tx_success   apply_start    apply_end   apply_duration apply_success
-----------------------------------------------------------------------------------
...
...
...
-----------------------------------------------------------------------------------

Answer 1

Oracle Dataguard does not transfer transactions, it transfers log records. Physical standby knows nothing about transactions (logical things), it knows only about changes to the files (physical changes).

From the standby side, you can check v$dataguard_stats view. It gives information for "transport lag" and "apply lag" in terms of time.

From primary and standby you can see what logs were archived (transmitted) and what were applied to the standby:

select sequence#, to_char(completion_time, 'dd.mm.yyyy hh24:mi:ss') completion_time,
       deleted, applied, dest_id, STANDBY_DEST, fal, round(blocks*block_size/1024/1024) mb
  from v$archived_log
 where completion_time > sysdate - 3/24
 order by sequence#, dest_id;

If you have logical standby, then you can look into v$logstdby_progress, etc;

https://docs.oracle.com/cd/B19306_01/server.102/b14239/manage_ps.htm#CHDFFFAJ https://docs.oracle.com/cd/B28359_01/server.111/b28294/manage_ls.htm#g1057004