GV$RECOVERY_LOG

select
inst_id,                 -- INST_ID NUMBER
lhthp,                   -- THREAD# NUMBER
lhseq,                   -- SEQUENCE# NUMBER
to_date                  -- TIME DATE
  (lhlot, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
nvl (lhnam, alnam)       -- ARCHIVE_NAME VARCHAR2(513)
from
x$kcclh,
(
select
althp,
alseq,
alrls,
alrlc,
alnam /* filter out duplicate al */ 

from
(
select
althp,
alseq,
alrls,
alrlc,
alnam,
alstm,
alrid,
max (alstm) over /* get latest archivelog */ 
(partition by althp,
alseq,
alrls,
alrlc) almstm,
max (alrid) over /* filter out duplicate stamp */ 
(partition by althp,
alseq,
alrls,
alrlc,
alstm) almrid 
from
x$kccal 
where
bitand (alflg, 8) = 0 /* standby_dest = NO */ 
and bitand (alflg, 2) = 2 /* archived = YES */ 
and bitand (alflg, 1) = 0) /* deleted = NO */ 

where
alrid = almrid and alstm = almstm) 
where
lhthp = althp (+) and lhseq = alseq (+) and lhrls = alrls (+) and lhrlc = alrlc (+) and to_number (lhnxs) > (
select
min (to_number (fhscn)) 
from
x$kcvfhmrr 
where
hxerr = 0) and lhseq not in (
select
leseq 
from
x$kccle 
where
lethr = lhthp) and to_number (lhlos) < (
select
max (to_number (hxsts)) 
from
x$kcvfhmrr 
where
hxerr = 0)
home |  up  |  mailbox