Monitoring REDO Log Tips

Answer:  Rampant author Ben Prusinski (author of Exploring Oracle Internals), offers these tips for measuring the amount of redo log generation:

One topic for new projects such as 11g Data Guard and 11g Streams is a way to calculate the total volume of redo generated. I am going to show you a quick and dirty way to calculate the amount of redo generated by an Oracle 11g database.

Now lets examine an 11g AWR report:

WORKLOAD REPOSITORY report for . . .

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
. . .
Redo size: 62.9 5,874.9

So in our example above, we had a grand total of 62.9K of redo per second.

We can correlate redo log size to the V$ data dictionary performance views with the following SQL*PLUS script that queries against the v$log and v$database views:

select
Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes,
Vdb.NAME AS Dbname
FROM
(SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM
V$log_History Vlh
GROUP BY
To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE
Vl.Group# = 1
ORDER BY
Log_Hist.Start_Date,
Log_Hist.Start_Time;



START_DATE START NUM_LOGS MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2009-03-21 15:00 8 400 BEN11G
2009-03-21 16:00 1 50 BEN11G
2009-03-22 10:00 1 50 BEN11G
2009-03-23 17:00 4 200 BEN11G
2009-03-25 21:00 2 100 BEN11G
2009-03-26 13:00 2 100 BEN11G
2009-04-06 22:00 1 50 BEN11G

We can then take the number of redo logs from the NUM_LOGS column of the above query and multiply by size of each online redo log (ORL) file.

select
group#,
members,
bytes,
archived,
status
from
v$log;

GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 52428800 YES INACTIVE
2 1 52428800 NO CURRENT
3 1 52428800 YES INACTIVE

Another method to calculate redo generated for Oracle is to examine the pattern of log switches that occur within your Oracle database. Upon examination of your alert.log file, you can take a differential of log switches to assess exactly how much redo is generated at peak and idle times.

First lets perform some log switches in our test 11g database!

SQL> alter system switch logfile;

System altered.

Note: we are using 11gR1 on Oracle Enterprise Linux 5.2 for the examples here. So we start a new ADRCI session

[oracle@sandiego ~]$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Fri Aug 7 18:41:22 2015

Copyright (c) 1982, 2015, Oracle. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/rdbms/ben11g/ben11g
2: diag/clients/user_oracle/host_3399978961_11
3: diag/clients/user_unknown/host_411310321_11
4: diag/tnslsnr/sandiego/listener
Q: to quit


2009-08-07 18:45:58.653000 -07:00
Thread 1 advanced to log sequence 21
Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ben11g/redo03.log
2009-08-07 18:46:03.171000 -07:00
Thread 1 advanced to log sequence 22
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log
Thread 1 cannot allocate new log, sequence 23
Checkpoint not complete
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log

You can add up the total number of logs between log switches and divide by total time to obtain a rough estimate of the number of bytes of redo log generation.

For complete scripts to monitor UNDO, download the Oracle script collection.