set verify off
set feed off
set timing off

PROMPT
PROMPT Recovery lag time from Source...
select  'This DR env is '||trunc(sysdate-max(first_time))||' Hrs and '||
        trunc(((sysdate-max(first_time))*24-trunc((sysdate-max(first_time))*24))*60)||' Mins'||
        ' behind last archive log from PROD...' "TIME LAG"
from v$archived_log
where applied = 'YES';

PROMPT
PROMPT Recovery details per thread
PROMPT '--------------------------'
select
	al.thread#,min(al.sequence#) "MIN SEQ#",
	max(al.sequence#) "MAX SEQ#",
	min(to_char(FIRST_TIME,'dd-mon-yy hh24:mi:ss')) "MIN FIRST TIME",
	round(sum(blocks*block_size) /1024/1024,2) "MB"
from v$archived_log al,
	(select thread#, nvl(max(sequence#),0) max_seq from v$archived_log where applied='YES' and registrar='RFS' and standby_dest='NO' group by thread#) maxa
where applied= 'NO'
and registrar = 'RFS'
and standby_dest = 'NO'
and (al.thread# = maxa.thread# and al.sequence# > maxa.max_seq)
and exists (select 'x' from v$instance where status != 'OPEN')
group by al.thread#
having min(al.sequence#) > 0;

PROMPT
PROMPT Last logs applied
PROMPT '----------------'
col archived for a8
col applied for a7
SELECT * FROM (
   SELECT sequence#, thread#, archived, applied,TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI:SS') AS completed
   FROM sys.v$archived_log
   WHERE applied='YES'
   ORDER BY completed DESC)
WHERE ROWNUM <= 10;

PROMPT
PROMPT The following query should show varying block# for error thread#
PROMPT '---------------------------------------------------------------'
select process, status, sequence#, blocks, block#
from v$managed_standby
where sequence# <> 0 ;
PROMPT

PROMPT Number of files to catch up; registered by NOT applied...
PROMPT '--------------------------------------------------------'
break on report
compute sum of count(1) on report
SELECT thread#, count(1) FROM V$ARCHIVED_LOG where applied <> 'YES' group by thread# order by thread#;
set timing on