This discussion is archived
2 Replies Latest reply: Apr 23, 2012 9:55 PM by malhi RSS

Find missing archived log for streams

Midhun GT Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points