REDO LOGFILE SIZING

Redo Log Files and Sizing  

set heading off;

select '******************************************************' from dual;

select '****           Redo Log Files and Sizing          ****' from dual;

select '******************************************************' from dual;

timing start 'Redo Sizing';

set heading on;

col "File Name" for a60;

col "Size in MB" format 999,999,999,999,990

select a.group#, thread#, substr(a.member,1,80) as "File Name",b.bytes/1024/1024 as "Size in MB" from v$logfile a,v$log b where a.group#=b.group#;

timing stop 'Redo Sizing';

Redo log switch History

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;

ARCHIVE LOG GENERATION

Archive log generation per day 

SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');