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)