Oracle DataGuard – How to Check Log Transfer and Apply Status

dataguardoracle

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
-----------------------------------------------------------------------------------
...
...
...
-----------------------------------------------------------------------------------

Best Answer

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

Related Question