Oracle Redo Log Generation

See how much redo logs you are generating per day. Good to use when you see your archivelog directories are filling up unusually.


This will not show you where the problem is though, will just give you the number of log switches and average MB generated per day so that you can compare day by day. If you do start seeing unusually high log switches, thus high volume of archivelog generation, maybe a good place to start is looking for transactions which do lots of UPDATE/DELETE operations in the STATSPACK.

I found this very nice SQL script on the Oracle website at this url :http://www.oracle.com/technology/oramag/code/tips2005/062705.html

Daily Count and Size of Redo Log Space (Single Instance)

SQL> SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;

Sample Output:

DAY COUNT# MIN# MAX# DAILY_AVG_MB
---------- ---------- ---------- ---------- ------------
2007-09-05 1 700 700 50
2007-09-04 1 699 699 50
2007-09-03 3 696 698 150
2007-09-02 2 694 695 100
2007-09-01 3 691 693 150
2007-08-31 4 687 690 200
2007-08-30 3 684 686 150
2007-08-29 2 682 683 100
2007-08-28 3 679 681 150
2007-08-27 3 676 678 150
2007-08-26 3 673 675 150

Hourly archivelog switches

There is another one which I find quite useful. I found it on the internet. I have also modified to show me a window of the last 7 days of the number of archivelog generation per hour (switches). I scripted it in unix so that shell sends me an email once a day with this log information. Very useful monitoring stuff!

SQL>SELECT to_date(first_time) 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') "08",
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
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

The output is like this:

DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
31-AUG-07 1 1 1 1 2 1 1 1 1 1 1 2 0 1 0 1 0 1 1 0 1 0 1 1
01-SEP-07 0 1 1 0 1 0 1 0 1 0 1 1 0 1 0 1 1 0 1 1 1 1 1 1
02-SEP-07 0 1 1 1 1 0 1 1 1 1 0 1 1 1 1 1 1 1 1 2 1 1 1 1
03-SEP-07 1 2 2 1 1 1 1 1 1 1 1 2 1 1 1 1 1 2 1 1 2 1 1 2
04-SEP-07 1 1 2 1 1 2 1 1 1 2 1 1 2 1 2 1 2 1 2 1 2 1 2 2
05-SEP-07 2 1 2 1 2 2 1 2 1 2 2 1 2 2 1 2 2 2 1 2 2 2 2 2
06-SEP-07 2 1 2 2 2 2 1 2 2 2 2 2 1 2 2 1 0 2 0 0 0 0 1 0
07-SEP-07 0 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

How to resize redolog groups on Oracle

Here is the procedure to resize the redo log group in SAP/Oracle
To check all Redolog files Run command:

Select * from v$log;

Now add a group, To add a log group

Alter database add logfile group 5 ('/oracle/SID/origlogA/log_g15m1.dbf','/oracle/SID/mirrlogA/log_g15m2.dbf') size 200M;

alter database add logfile group 6 ('/oracle/SID/origlogB/log_g16m1.dbf','/oracle/SID/mirrlogB/log_g16m2.dbf') size 200M;

alter database add logfile group 7 ('/oracle/SID/origlogA/log_g17m1.dbf','/oracle/SID/mirrlogA/log_g17m2.dbf') size 200M;

alter database add logfile group 8 ('/oracle/SID/origlogB/log_g18m1.dbf','/oracle/SID/mirrlogB/log_g18m2.dbf') size 200M;

After these commands, Force System for log switch
Alter system switch logfile;
Drop Existing Log groups files (Make sure no group has status active or current)

Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;
Alter database drop logfile group 4;

Remove these files from OS level
Now again add same Group

alter database add logfile group 1 ('/oracle/SID/origlogA/log_g11m1.dbf','/oracle/SID/mirrlogA/log_g11m2.dbf') size 200M;

alter database add logfile group 2 ('/oracle/SID/origlogB/log_g12m1.dbf','/oracle/SID/mirrlogB/log_g12m2.dbf') size 200M;

alter database add logfile group 3 ('/oracle/SID/origlogA/log_g13m1.dbf','/oracle/SID/mirrlogA/log_g13m2.dbf') size 200M;

alter database add logfile group 4 ('/oracle/SID/origlogB/log_g14m1.dbf','/oracle/SID/mirrlogB/log_g14m2.dbf') size 200M;

We can drop Log group 5,6,7,8
In case any of these groups are Active or current stage use “Alter system switch logfile"

Alter database drop logfile group 5;
Alter database drop logfile group 6;
Alter database drop logfile group 7;
Alter database drop logfile group 8;