HADR delayed replay
helps prevent data loss due to errant transactions. To implement HADR delayed
replay, set the hadr_replay_delay database
configuration parameter on the HADR standby database.
intentionally keeps the standby database at a point in time that is earlier
than that of the primary database by delaying replay of logs on that standby.
If an errant transaction is executed on the primary, one has until the
configured time delay has elapsed to take action to prevent the errant transaction
from being replayed on the standby. To recover the lost data, one can either
copy this data back to the primary, or one can have the standby take over as
the new primary database.
Delayed replay works by
comparing timestamps in the log stream, which is generated on the primary, and
the current time of the standby. As a result, it is important to synchronize
the clocks of the primary and standby databases. Transaction commit is replayed
on the standby according to the following equation:
(current time on the
standby - value of the hadr_replay_delay configuration parameter)
timestamp of the committed log record
One should set the
hadr_replay_delay database configuration parameter to a large enough value to
allow time to detect and react to errant transactions on the primary.
One can use this
feature in either single standby mode or multiple standby mode. In multiple
standby mode, typically one or more standbys stays current with the primary for
high availability or disaster recovery purposes, and one standby is configured
with delayed replay for protection against errant transactions.
There are several important
restrictions for delayed replay:
- One can set the hadr_replay_delay configuration parameter only on a standby
- A TAKEOVER command on a standby with
replay delay enabled will fail. One must first set the hadr_replay_delay configuration
parameter to 0 and then deactivate and reactivate the standby to pick up
the new value, and then issue the TAKEOVER command.
- The delayed replay feature is supported
only in SUPERASYNC mode. Because log replay is delayed, a lot of
unreplayed log data might accumulate on the standby, filling up receive
buffer and spool (if configured). In other synchronization modes, this
would cause the primary to be blocked.
If one enables delayed replay, it
is recommended that log spooling is also enabled by setting the hadr_spool_limit database configuration
parameter. Because of the intentional delay, replay position can be far behind
log receive position on standby. Without spooling, log receive can only go
beyond replay by the amount of the receive buffer. With spooling enabled the standby
can receive much more logs beyond the replay position, providing greater
protection against data loss in case of primary failure. Note that in either
case, because of the mandatory SUPERASYNC mode, the primary will not be blocked
by enabling delayed replay.