Redo log switch History

Redo log switch History

Find out  date  & time, SCN and other details about log switch

-- this is to set date format

sql >alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';

-- this is to check redo log switch history

sql >
    col f format a3
    col switch_time form a15
    col first_change# format 999999999999

    select b.thread#,

           b.sequence#,

           b.first_time,

           trunc( ( e.first_time ) -

                  ( b.first_time ) ) days,

           to_char( trunc(sysdate) +

                    ( ( e.first_time ) -

                      ( b.first_time ) ),

                    'hh24:mi:ss' ) switch_time,

          decode( 15/1440, greatest( 15/1440,

                           ( e.first_time ) -

                           ( b.first_time ) ),

                           '*' ) f,

          e.first_change# - b.first_change# net_change,

          b.first_change#

     from v$loghist b, v$loghist e

    where e.sequence#(+) = b.sequence# + 1

      and e.thread#(+) = b.thread#

   order by ( b.first_time ) asc;

Hope this help. Regards Rupam