select
  fn.inst_id,                              -- INST_ID NUMBER
  fn.fnfno,                                -- FILENUM NUMBER
  fn.fnnam,                                -- FILENAME VARCHAR2(513)
  decode(                                  -- STATUS VARCHAR2(13)
    nvl(mf.cps, 0), 0, 'NOT RECOVERED', 281474976710655, 'CURRENT', 'IN RECOVERY') 
from
  x$kcrmx mx,
  x$kccfn fn,
  x$kccfe fe,
  x$kcrmf mf
where
  fn.fntyp = 4 and mf.fno(+) = fn.fnfno and ((bitand(mx.flg,2) != 0 and fe.fedup != 0) or mf.fno = fn.fnfno) and fe.fenum = fn.fnfno