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.
Delayed replay 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 database.
- 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.