Query to check Redo logs details


select lg.group#,l.thread#,lg.member,l.bytes/1024/1024/1024 Size_GB,l.archived,l.status,l.blocksize from v$log l,v$logfile lg where l.group#=lg.group# order by group#;

Add new redo log group


alter database add logfile group 3 '/u01/DBNAME/REDO/redo03.log' size 2G;

Drop redo log group


Candidate redo logs state should be in INACTIVE to drop that group.
alter database drop logfile group 2;

Database users that generate the most redo


SELECT s.sid, s.username, s.program, t.value "redo blocks written" FROM v$session s, v$sesstat t WHERE s.sid = t.sid AND t.value != 0 AND t.statistic# = (SELECT statistic# FROM v$statname WHERE name = 'redo size') ORDER BY 4;