Exploring the ARCHIVE_LAG_TARGET INIT.ora Parameter

It is possible to control the lag associated with how often online redo log files are archived, and therefore the time between changes to a primary database and standby database. This is particularly helpful when using Change Data Capture and applying the changes from the archive logs and not the online redo logs.

The ARCHIVE_LAG_TARGET initialisation parameter can be used to force a log switch to occur at regular time intervals. The parameter is specified in seconds. A formula can be used to determine when a log switch will occur:

If N + M > ARCHIVE_LAG_TARGET, a log switch will occur.

N = number of seconds ago that log was created
M = estimated archival time for redo log

This parameter should be set with consideration for the performance hit of log switching. I use it in order to maintain a maximum amount of time that will pass outside of peak hours before a log switch happens. For example, we have change data capture configured in another, downstream database, which is dependent upon a log switch happening on the primary database for the changes to be sent across. Using the archive_lag_target parameter ensures that data is never more than 30 minutes behind at the downstream database.

If you configure the parameter so that it forces a log switch very frequently. Let’s say 3 minutes, for example, there will be a performance hit for the database having to complete a checkpoint operation. Ideally, log switches shouldn’t be more often than about every 15 minutes.

Like it, share it...

Category: Database Config

Related Posts

Comments (3)

Trackback URL | Comments RSS Feed

  1. noor sharif says:

    This was really helpful.
    Thank you and keep posting

Leave a Reply

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