redolog

 SELECT i.instance_name, (r.value/e.value)*100 retry_pct, s.value/e.value/1024 av_entry_kb, s.value/w.value/1024/1024 av_write_mb
FROM gv$instance i, gv$sysstat r, gv$sysstat e, gv$sysstat w, gv$sysstat s
WHERE r.name='redo buffer allocation retries' AND e.name='redo entries' and s.name = 'redo size' and w.name = 'redo synch writes' and i.inst_id = r.inst_id and i.inst_id = e.inst_id and i.inst_id = w.inst_id and i.inst_id = s.inst_id

 

SELECT (100 - sum(percent_space_used)) + sum(percent_space_reclaimable)
FROM v$flash_recovery_area_usage

SELECT inst_id, TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY, TO_CHAR(FIRST_TIME,'HH24') HOUR, COUNT(*) "#ofSwitches" , round( 2.048 * count(*),0) "Redo- GB"
FROM gV$LOG_HISTORY a
where a.inst_id = a.thread# and first_time > SYSDATE - (1800 / (24 * 60)) GROUP BY inst_id,TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24') ORDER BY inst_id,TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')

 

 

select inst_id,name,value redosync
from gv$sysstat
where name in ('redo sync poll writes','redo synch polls') and value > 0 order by inst_id

 

 

select name param_name, value param_value
from v$parameter
where name in ( 'db_writer_processes', 'fast_start_mttr_target', 'log_checkpoint_timeout', 'db_block_checking', 'db_block_checksum', 'db_ultra_safe', '_use_adaptive_log_file_sync', 'log_archive_trace' ) union
SELECT name param_name , CASE when (value / rec_logbuffer * 100) > 70 THEN round(value/1048576,0) ||' MB' ELSE round(value/1048576,2) || ' MB *Warning* should be close to redo buffer ' || round(rec_logbuffer/(1048576),2)|| ' MB' END AS param_value
from v$parameter , (
select min(bytes) rec_logbuffer
from v$sgainfo
where name in ('Redo Buffers', 'Granule Size') ) b
where name ='log_buffer' 

 

 

select thread# Inst, bytes/(1048576) LogSizeMB, count(*)
from v$log group by thread#,bytes order by thread# 

 

 

SELECT Name, CASE when value >0 Then rpad(value,12)||' ' || '** set zero if logfile sync wait- Bug 9095696 **' else value END AS VALUE
from v$parameter
where name ='archive_lag_target' UNION
SELECT NAME, CASE WHEN value > 0 then rpad(value,12)||' ' || '- ** WARNING ** Reduce the trace level ' ELSE VALUE END AS VALUE
from v$parameter
where name ='log_archive_trace' union
SELECT Name, CASE when value ='TRUE' Then value ELSE rpad(value,12)||' ' || '- ** WARNING ** this needs to be TRUE' END AS VALUE
from v$parameter
where name ='log_archive_local_first' union
SELECT Name, value
from v$parameter
where name ='log_archive_max_processes' union
SELECT Name, CASE when value >1 Then rpad(value,12)||' ' || '- Set only for Mandatory Destinations' ELSE value END AS VALUE
from v$parameter
where name ='log_archive_min_succeed_dest' union
SELECT Name, CASE when value >7 Then rpad(value,12)||' ' || '- Not required to have more than 7 days unless specific reason' ELSE value END AS VALUE
from v$parameter
where name ='control_file_record_keep_time'