Verificar tamanho idela para o Redo Log. – …::: César DBA :::…

A dica hoje é de como recriar os grupos do redo log do tamanho correto. O ideal é monitorar e em seguida recriar os grupos, segue abaixo os passos para monitorar e saber o tamanho ideal para poder recriar.

Verificando o numero de archives por hora gerados.

SELECT 
to_char(first_time,'YYYY-MON-DD') day, 
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", 
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", 
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", 
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", 
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", 
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", 
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", 
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", 
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0", 
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", 
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", 
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", 
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", 
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", 
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", 
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", 
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", 
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", 
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", 
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", 
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", 
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", 
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22", 
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" 
from v$log_history 
GROUP by 
to_char(first_time,'YYYY-MON-DD');

SQL

Verificar o tamanho ideal usando os últimos 30 dias como referencia.

SELECT (SELECT Round (Avg (bytes) / 1024 / 1024, 2) 
        FROM   v$log)  AS "Redo size (MB)", 
       Round (( 20 / average_period ) * (SELECT Avg (bytes) FROM v$log)/1024/1024,2) AS "Tamanho Recomendado (MB)" 
FROM   (SELECT Avg (( next_time - first_time ) * 24 * 60) AS AVERAGE_PERIOD 
        FROM   v$archived_log 
        WHERE  first_time > SYSDATE - 30 
               AND To_char (first_time, 'HH24:MI') BETWEEN '00:00' AND '23:59');

SQL

Leave a Reply Cancel reply