Oracle 11g: how to control the frequency of the redo log switches?

Let's assume that we have a request to switch redo logs every 15 minutes, so we could ship the archived logs to a third party application.

I've found a very nice entry about this at the blog below:

http://dba-masters.blogspot.ca/2012/08/11g-r2-managing-redo-logs.html

SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 900;

System altered.

The value is in seconds, so 900 seconds = 15 minutes.

You can force all redo log threads to switch their CURRENT redo logs at regular time intervals.  In a Primary / Standby database configuration, changes are made available to the standby database by archiving redo logs at the primary site, and then shipping them across to the Standby site and applying them to the Standby database.  The standby database must wait for the redo log files to be archived and shipped across to it before it can apply the latest changes.  With the ARCHIVE_LAG_TARGET initialization parameter you can specify in seconds, how long that lag should be.

When ARCHIVE_LAG_TARGET is set, then the database examines the current redo log of the database periodically.  If the following conditions are met, then the instance will switch the logfile.

·         The current redo log file was created prior to n seconds ago, and the estimated archival time for the current log = m seconds.  m seconds is calculated according to the number of redo blocks in the current redo log file.  If n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter, we have a yes for a redo log file switch

·         The current log contains redo records.  A log switch will not occur if there are no records in the current redo log file.

In a RAC(Real Application Clusters) environment, the instance will also cause other threads from other instances to switch and archive their redo log files, if they are falling behind.  This is a useful mechanism if one instance in a RAC environment is more active than the other instances.  In a RAC environment, the value of ARCHIVE_LAG_TARGET must be the same on all instances, otherwise you can expect unpredictable behaviour.

The ARCHIVE_LAG_TARGET is a dynamic parameter, so you can set it with ALTER SYSTEM … SCOPE=BOTH; to make it permanent.  The value of ‘0’ disables it, and is also the default value.  A typical value for this initialization parameter is half an hour:

SQL> alter system set ARCHIVE_LOG_TARGET=1800 scope=both;