Logical Standby Databases Time Lag Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Data Guard

Oracle Data Guard - Logical Standby Databases with Time Lag

This parameter can be set using the ALTER SYSTEM statement or by restarting the primary database.   The time lag specified by the initialization parameter can be overridden using the following procedure:

DBMS_LOGSTDBY.APPLY_SET('APPLY_DELAY',<TimeLag>)

The dbms_logstdby package provides another procedure called apply_unset, which can be used to cancel the time lag in case of failover to the logical standby database.

Setting Time Lag between log transfer and log apply services

In order to minimize the risk of user error or data block corruption to the standby site, a time lag between log transfer and log apply services can be set. This can be achieved using the DELAY attribute of log_archive_dest_n parameter. The delay timer starts when the archived redo log is transferred to the standby destination. For example:

LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1
DELAY=30'

The delay attribute is specified in minutes.

A Sample Log Management Policy

Combining the various attributes of the log_archive_dest_n parameter, a comprehensive log management policy can be built for a Data Guard environment.  In this section, a sample policy for a configuration that includes a primary database and two standby databases will be presented. One Oracle instance is on a local host and other is on a remote host in the LAN. Assume that the names of the databases are appsdb, appsstdby1 and appsstdby2. Oracle instance appsstdby1 is on the local host and appsstdby2 is on the remote host. The following initialization parameters of the primary database show the log archival attributes:

#Init.ora file of primary database appsdb