Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Monitoring REDO Log Tips

Oracle Database Tips by Donald BurlesonFebruary 21, 2015

Question:  How do I monitor my redo logs in Oracle.  I need to know the frequency of redo log switches and monitor redo log activity.

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:


Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes,
Vdb.NAME AS Dbname
(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

V$log_History Vlh

To_Char(Vlh.First_Time, 'YYYY-MM-DD'),

To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
Vl.Group# = 1



---------- ----- ---------- ---------- ---------
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.


---------- ---------- ---------- --- ----------------
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 - 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.

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster