1.V$LOGSTDBY_PROCESS displays dynamic information about what is happening to the Data Guard log apply services. This view is helpful when diagnosing performance problems during the logical application of archived redo logs to the standby database, and it can be helpful for other problems. This view is for logical standby databases only.

 

INPUT QUERY:-

 

SELECT STATUS FROM V$LOGSTDBY_PROCESS WHERE TYPE = ‘READER’;

SELECT * FROM V$LOGSTDBY_PROCESS;

 

2.V$LOGMNR_DICTIONARY_LOAD displays information about LogMiner dictionaries for all active LogMiner sessions on the system. Each query of this view will return one row for each attached LogMiner session

 

INPUT QUERY:-

 

SELECT PERCENT_DONE, COMMAND
FROM V$LOGMNR_DICTIONARY_LOAD
WHERE SESSION_ID = (SELECT SESSION_ID FROM V$LOGSTDBY_STATE);

 

3.The following query can be used to check apply process and what archived logs are being mined currently.

 

INPUT QUERY:-

 

select thread# trd, sequence#,
first_change#, next_change#,
dict_begin beg, dict_end end,
to_char(timestamp, ‘hh:mi:ss’) timestamp,
(case when l.next_change# < p.read_scn then ‘YES’
when l.first_change# < p.applied_scn then ‘CURRENT’
else ‘NO’ end) applied
from dba_logstdby_log l, dba_logstdby_progress p
order by thread#, first_change#;

 

4.The DBA_LOGSTDBY_EVENTS view record interesting events that occurred during the operation of SQL Apply. By default, the view records the most recent 100 events. However, you can change the number of recorded events by calling DBMS_LOGSTDBY.APPLY_SET() PL/SQL procedure. If SQL Apply should stop unexpectedly, the reason for the problem is also recorded in this view

INPUT QUERY:-

 

SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;

 

5.V$LOGSTDBY displays dynamic information about what is happening to the Data Guard log apply services. This view is very helpful when diagnosing performance problems during the logical application of archived redo logs to the standby database, and it can be helpful for other problems. This view is for logical standby databases only

 

INPUT QUERY:-

SELECT TYPE, STATUS, HIGH_SCN  FROM V$LOGSTDBY;

 

6.The DBA_LOGSTDBY_LOG view provides dynamic information about archived logs being processed by SQL Apply

 INPUT QUERY:-

select a.sequence_received,a.thread,b.sequence_applied,b.thread from (select max(sequence#) sequence_received,thread# thread from dba_logstdby_log group by thread#) a,
(select max(sequence#) sequence_applied,thread# thread from dba_logstdby_log where applied=’YES’ group by thread#) b where a.thread=b.thread

 

7.This query is used to check latency between primary and logical standby database.

 

INPUT QUERY:-

 

select latest_time,applied_time,mining_time, (latest_time-applied_time)*24*3600 gap_in_second from V$logstdby_progress;