----primary in mins:secs, hh24:mi:ss lag----
SELECT name, dest_id, first_time, completion_time, sequence#,
   --to_number(to_char(completion_time, 'SSSSS')) AS inc_current,
   --LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) AS inc_previous,
   CASE dest_id WHEN 2 THEN
   --to_char(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) )/60, 2), 'fm00.00')
    (round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) ), 2) - mod(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) ), 2), 60))/60
   || ':' || to_char(abs(mod(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) ), 2), 60)), 'fm00') 
   ELSE NULL
   END 
   AS "LAG MI:SS",
   CASE dest_id WHEN 2 THEN
   --to_char(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) ), 2), 'fm000000.00') LAGSEC,
   --mod(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) ), 2), 60) LAGSECRem,
   '-'||to_char(to_date(abs(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) ), 2)), 'sssss'), 'hh24:mi:ss')
   ELSE NULL
   END 
   AS "LAG hh24:mi:ss"
    FROM v$archived_log order by sequence# DESC, dest_id ASC;

----primary in mins lag----
SELECT name, dest_id, first_time, completion_time, sequence#,
   to_number(to_char(completion_time, 'SSSSS')) AS inc_current,
   LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) AS inc_previous,
   CASE dest_id WHEN 2 THEN
   to_char(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) )/60, 2), 'fm00.00')
   ELSE NULL
   END 
   AS LAGMINS
 FROM v$archived_log order by sequence# DESC, dest_id ASC;

----standby applied only in seconds----
 SELECT sequence#, dest_id, first_time, completion_time, archived, deleted, registrar, applied,
   --to_number(to_char(completion_time, 'SSSSS')) AS inc_current,
   --LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) AS inc_previous,
   CASE dest_id WHEN 2 THEN
   to_char(round((LAG(to_number(to_char(completion_time, 'SSSSS')) , 1, 0) OVER (ORDER BY sequence#, dest_id) - to_number(to_char(completion_time, 'SSSSS')) ), 2), 'fm00000.00')
   ELSE NULL
   END 
   AS DIFF_SECS
 FROM v$archived_log where dest_id = 2 and applied = 'YES'
  order by sequence# DESC, dest_id ASC;
 
--Primary ---- this is the total time each archive takes----
select a.recid, a.SEQUENCE#, a.name, a.dest_id, a.first_time, a.completion_time, to_char(round(24*60*(a.completion_time - a.first_time),2), 'fm000.00') diffmins from v$archived_log a order by a.sequence# DESC, a.dest_id ASC;

--Standby
select name,value,time_computed,datum_time from v$dataguard_stats where name = 'transport lag';


--
 select max(sequence#), applied from v$archived_log group by sequence#, applied order by sequence# DESC;
 select max(sequence#) from v$archived_log;
 select al.applied, al.sequence#, al.* from v$archived_log al where al.name = 'EPMPROD_SC8' order by al.sequence# DESC;
 select al.applied, al.sequence#, al.* from v$archived_log al order by al.sequence# DESC;
 
SYS@EPMPRODR AS SYSDBA > select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
3416
SYS@EPMPRODR AS SYSDBA > select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
3415

