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 







RAC Tuning for redo logs

RAC tuning tips

December 12,  2015


Tuning Online Redo Logs in a RAC environment

The Online Redo Log (ORL) for Oracle RAC is slightly different than a single-instance database. Most database administrators know that it is good practice to have at least three ORL groups for the database. What may not be obvious is that in a single-instance database, it is good practice to have at least three redo log groups per thread. Many people incorrectly think of threads as an Oracle RAC concept but even in a single-instance database, there is one thread of redo. All Oracle databases have one thread per instance.


Database administrators working on single-instance databases should strive for three to four redo log switches per hour. Switching too frequently can cause performance problems.


The same is true for Oracle RAC databases except that each instance should have three to four redo log switches per hour. The following script can be used in Oracle RAC databases to determine the max number of log switches in any one hour.



<  max_hourly_log_switches.sql


     cursor c1 is






     group by


        to_char(first_time,'YYYY-MM-DD HH24')

     order by



     v_inst number;

     v_curr number;

     v_cnt  number;

     v_max  number;

     v_name varchar2(16);




     open c1;




        fetch c1 into v_inst,v_cnt;

        exit when c1%notfound;

        if (v_curr<>v_inst) then

           if (v_curr<>0) then


                 instance_name into v_name





           dbms_output.put_line('Instance: '||v_name||

                       '   Max Hourly Switches: '||v_max);

           end if;

           See code depot for full script

           v_max := -1;

           v_curr := v_inst;

        end if;

        if (v_max < v_cnt) then

           v_max := v_cnt;

        end if;


     end loop;


        instance_name into v_name





     dbms_output.put_line('Instance: '||v_name||

                 '   Max Hourly Switches: '||v_max);





Instance: orcl1   Max Hourly Switches: 5

Instance: orcl2   Max Hourly Switches: 8

Instance: orcl3   Max Hourly Switches: 3


PL/SQL procedure successfully completed.


The script above will work for any number of instances supporting the Oracle RAC database. Since each instance has its own thread of redo, we need to examine the maximum number of log switches per hour in each instance.


As previously stated, the online redo logs should be sized to see three or four log switches per hour at peak DML load. In the sample output above, there was at least one hour in one instance that experienced eight log switches. It may be a good idea to resize the online redo logs to twice the size so that the target of four log switches in an hour can be obtained.


Unfortunately, it is very common to see some Oracle RAC deployments that are experiencing sixty or more log switches in their busiest hours! Databases exhibiting this behavior should have the redo logs resized to be larger.   It is a best practice to have all redo logs sized the same for each thread and for each group in that thread.


Sizing the redo logs to meet the target of three or four log switches works well for the hour that has the highest redo generation rate. But what about the hour timeframe that has a very low redo generation rate? Some database administrators translate the three or four log switches per hour to a requirement that the redo logs should switch every fifteen or twenty minutes. As such, they often set the archive_lag_target parameter to 900 or 1200.


From a performance perspective, there is little reason to ensure the redo logs switch every fifteen or twenty minutes even during period of low redo generation. The only reason to force a log switch regularly, even when the redo log group is not full, is for archiving the redo on a regular schedule, not to improve performance.


If the services are configured differently for each instance, then it is likely each instance experiences different redo generation rates. For example, one instance may have 20GB of redo generated in one hour and another instance only has 1GB of redo generated in one hour. The database administrator may create redo logs of 5GB each for the first thread and 300MB each for the second thread.


Note that having different redo log group sizes for each thread can be problematic when the service fails over due to instance failure. If the first instance terminates, the second instance will find itself trying to handle 21GB of redo generated in one hour and will perform approximately 70 log switches in that hour. It is a good practice for the redo logs to be sized to handle the maximum redo generation rate imparted on it by any application workload taking into account service relocations due to instance failures.


This also means that all threads would have the same size redo logs groups. If You are worried that the second instance with 1GB of redo in its busiest hour would need five hours with the 5GB redo log groups, then turn to the archive_lag_target parameter to ensure the logs switch more often. Again, this parameter is not needed for performance reasons. It is needed for backup and recovery reasons to ensure an archived copy of the redo log is written to a backup location for disaster recovery purposes.


For those databases configured in archive log mode, the redo log switch will prompt the archiver process to copy the redo log to the archive log destination. We need to keep in mind that for Oracle RAC, there are multiple threads of redo, and there will be multiple archives being generated simultaneously. There is a natural tendency to use only one archive log destination for all threads, but for databases that have a high rate of redo generation (high DML activity), a single archive log destination for all threads may become a bottleneck. It may be more beneficial if each thread has its own archive log destination but that is not always the case.

Learn RAC Tuning Internals!

This is an excerpt from the landmark book Oracle RAC Performance tuning, a book that provides real world advice for resolving the most difficult RAC performance and tuning issues.

Buy it  for 30% off directly from the publisher.

Hit Counter


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