2 Replies Latest reply: Apr 23, 2012 11:55 PM by malhi RSS

    Find missing archived log for streams

    Midhun GT
      i'm trying a way to find all missing archived logs that are still need for streams.

      The problem here is, when using ASM, dba_registered_archived_log view is truncating the file name column and it is really difficult to pin point the logs.

      So is it fine to join this view with V$archived_log? Is deleted and status column would do the trick?

      I modified the plsql as below. Is this fine/accurate?
      dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:');
      for cr in 
        (select decode (a.name, NULL, 'NOT FOUND', a.name) name, to_char(a.completion_time, 'dd:mm:yy hh42:mi:ss') completion_time from v$archived_log a,dba_registered_archived_log b where lscn between b.first_scn and b.next_scn
        and a.deleted = 'YES' and a.status != 'A')
      loop
           f_rec :=1;
        if cr.name = 'NOT FOUND' then
          dbms_output.put_line ('No archived log required');
        else
          dbms_output.put_line ('Misssing archived log: ' || cr.name ||' generated on '||cr.completion_time);
        end if;
      end loop;
      if f_rec = 0 then
      dbms_output.put_line ('No archived log required');
        end if;
      Thanks and Regards,
      Midhun
        • 1. Re: Find missing archived log for streams
          hitgon
          Hello Midhun

          For 10GR2 and 11GR1:

          ----- Begin ckpt_scn_query_10GR2_11GR1.sql -----

          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;
          /

          ----- End ckpt_scn_query_10GR2_11GR1.sql -----

          Regards
          Hitgon
          • 2. Re: Find missing archived log for streams
            malhi
            Use this query
            SELECT thread,
                        seq + 1 first_seq_missing,
                        next_seq - 1 last_seq_missing,
                        next_seq - seq - 1 missing_count
                   FROM (SELECT THREAD# thread,
                                SEQUENCE# seq,
                                LEAD (SEQUENCE#, 1, SEQUENCE#)
                                   OVER (PARTITION BY thread# ORDER BY sequence#)
                                   next_seq
                           FROM dba_registered_archived_log, dba_capture
                          WHERE capture_name = <CAPTURE NAME>'
                                AND consumer_name = capture_name)
                  WHERE (next_seq - seq) > 1
               ORDER BY 1, 2;
            {code}