How To Check and Change Redo Log Switch Frequency

If you want to learn What the Redo Log is, you should read the article named “Oracle Redo Log And Archive Log”. I will tell you finding and changing log switch frequency in this article.

If there is a performance problem in the database, you should look at the REDO LOG Switch frequency. If the log switch frequency is too much, it causes a significant performance problem in the database.

For example; if a very high IMPORT or DELETE operation is to be performed and the database FORCE LOGGING cannot be disabled, then it makes sense to add a new ONLINE REDO LOG as much as the size of the data to be IMPORTED, and then delete this ONLINE REDO LOG after the operation is finished.

Check Redo Log Switch Frequency

In addition, the Mbps values of the generated redo log can be calculated as follows.

The information of Redo Logs in the database can be found as follows;

Change Redo Log Switch Frequency

The log switch operation takes place when Online Redo Logs are full. If you increase redo log size your log switch frequenct will be reduced. But you may want to perform the Log switch operation periodically. This can be done with the ARCHIVE_LAG_TARGET parameter. ARCHIVE_LAG_TARGET parameter forces log switch.

Let’s test it. We suppose that we have a data guard in this example.

Query the current value of the parameter

Check current archive log sequence numbers

Change the ARCHIVE_LAG_TARGET Parameter

It is learned from ALERT LOG whether or not Log Switch operation is performed every minute. Or you can query with the script I shared at the beginning of the article.

When the value of the parameter is changed, the changes in ALERT LOG are as follows:

[Primary-1]—–>ALERT_LOG

[Primary-2]—–>ALERT_LOG

[Standby-1]—–>ALERT_LOG

[Standby-2]—–>ALERT_LOG

How we can force a log switch?

You can force log switch with the below command;

Onur ARDAHANLI
Author: Onur ARDAHANLI

Leave a Reply

Your email address will not be published. Required fields are marked *