prompt ++ Minimum Archive Log Necessary to Restart Capture ++
prompt Note: This query is valid for databases where the capture processes
-- exist for the same source database.
prompt

SET serveroutput ON

DECLARE
   hScn NUMBER := 0;
   lScn NUMBER := 0;
   sScn NUMBER;
   ascn NUMBER;
   alog VARCHAR2(1000);
   
BEGIN

   SELECT
      MIN(start_scn),
      MIN(applied_scn)
   INTO
      sScn,
      ascn
   FROM
      dba_capture;
      
   DBMS_OUTPUT.ENABLE(2000);
   
   FOR cr IN (
      SELECT DISTINCT
         (a.ckpt_scn)
      FROM
         system.logmnr_restart_ckpt$ a
      WHERE
         a.ckpt_scn <= ascn
      AND a.valid    = 1
      AND EXISTS (
            SELECT
               *
            FROM
               system.logmnr_log$ l
            WHERE
               a.ckpt_scn BETWEEN l.first_change# AND l.next_change#
         )
      ORDER BY
         a.ckpt_scn DESC
   )
   LOOP
      IF (hScn = 0) THEN
         hScn := cr.ckpt_scn;
      ELSE
         lScn := cr.ckpt_scn;
         EXIT;
      END IF;
   END LOOP;
   
   IF lScn  = 0 THEN
      lScn := sScn;
   END IF;
   
   
   dbms_output.put_line('Capture will restart from SCN ' || lScn || ' in the following file:');
   
   FOR cr IN (
      SELECT
         name,
         first_time
      FROM
         DBA_REGISTERED_ARCHIVED_LOG
      WHERE
         lScn BETWEEN first_scn AND next_scn
      ORDER BY
         thread#
   )
   LOOP
      dbms_output.put_line(cr.name||' ('||cr.first_time||')');
   END LOOP;
   
END;
/