Oracle redo log management

Redo logs are an important part of oracle databases and there are a lot of administration and monitoring activities that you can perform on redo logs.

The status which a redo log can hold are:



• UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
• CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
• ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
• CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
• CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
• INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.

Find how many times then redo log switch occurred in each hour:

set lines 160;
set pages 999;
col day for a15
col "00" for a4
col "01" for a4
col "02" for a4
col "03" for a4
col "04" for a4
col "05" for a4
col "06" for a4
col "07" for a4
col "08" for a4
col "09" for a4
col "10" for a4
col "11" for a4
col "12" for a4
col "13" for a4
col "14" for a4
col "15" for a4
col "16" for a4
col "17" for a4
col "18" for a4
col "19" for a4
col "20" for a4
col "21" for a4
col "22" for a4
col "23" for a4
select *
from
(SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
   v$log_history
GROUP by to_char(first_time,'YYYY-MON-DD')
)
order by to_date(day,'YYYY-MON-DD');

----------------------------------------------------------------------------------------------------------------
Calculate the amount of redo generated in last 7 days

set pages 100 lines 160
select trunc(completion_time) DAY, sum(blocks*block_size)/1024/1024 M_BYTES
from v$archived_log where completion_time>trunc(sysdate)-7
group by trunc(completion_time)
order by 1;

----------------------------------------------------------------------------------------------------------------
Check the redo log size and their status:

set lines 270 pages 1000
col member format a50
select l.group#, lf.member, l.bytes/1024/1024 mb,  l.status, l.archived
from v$logfile lf, v$log l
where l.group# = lf.group#
order by 1, 2;

----------------------------------------------------------------------------------------------------------------
Adding logfile to redo log group

alter database add logfile group 6 ('/u01/app/oracle/oradata/new_location/redo06.log') size 50M;

----------------------------------------------------------------------------------------------------------------
Adding redo log group with two logs

alter database add logfile group 4 ('/base/oradata/PRODCMS1/redo04_1.log','/base/oradata/PRODCMS1/redo04_2.log') size 50M;

----------------------------------------------------------------------------------------------------------------
Dropping log group

alter database drop logfile group 1;

----------------------------------------------------------------------------------------------------------------
References: http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2031.htm#REFRN30127