Written by Johannes Ahrends
The availability and consistence of the Standby Database changed evermore throughout the last years and by that the loss of transactions decreased till 0 transactions.
Originally only the archived Redolog files were transmitted to the Standby side and loaded there. By this the loss of transactions was in scale of one redolog file. With the 9i version Oracle then introduced the Standby Redologs, so transactions could now be transmitted right from the Online Redologs into the Standby Redologs and loaded immediately. So the loss of transactions was only a few transactions.
With the 10g version then the LNS process was introduced, which gets the data right from the logwriter and transmits it to the Standby side. By this it is now possible to build up a no-data-loss configuration with Dataguard.
Three parameters determine which protections mode should be used in the transmission of the redolog data to the Standby side.
AFFIRM: This tells that a transactions is commited, if it arrived at the Standby Redolog. NOAFFIRM would disable this behavior, which means transactions are commited immediately.
SYNC: This tells Redolog entries are concurrently written into the local Redolog files and transmitted to the Standby side. With ASYNC the local information is maintained first and just afterwards the Standby Redolog one.
NET_TIMEOUT: This parameter is only available with SYNC and AFFIRM and regulates after what time to switch to the NOAFFIRM mode.
In the Maximum Protection mode a transaction is only confirmed as “Committed”, when the data has been written both local and into at least one Standby Redolog file. If the Standby Database or the network between the databases breaks down, transactions could not be performed any longer, the primary database shuts down automatically. Oracle recommends to only use Maximum Protection mode, if at least two Standby databases exist.
For the Maximum Protection mode the following parameters must be set for the Redo transport:
This mode is a compromise between data security and performance. Fist the Maximum Availability mode works just like the Maximum Protection mode. That means all transactions are transmitted simultaneously and the commit of the transaction is only sent, when the transaction is saved both local and on at least one Standby Redolog file. With the 12c Oracle feature “FastSync” the performance can be increased a bit by already confirming the transaction, when the data reached the Standby side, so when it is located in that memory.
Unlike the Maximum Protection mode, the primary database continues working after a short time, if the Standby side breaks down or a network error occurs. By this it switches to the Maximum Performance mode automatically, that means transactions are commited immediately. Once the Standby database is available again it automatically switches back to the Maximum Protection mode.
Following parameters are responsible for the Redolog transport in Maximum Availability mode:
The parameter NET_TIMEOUT (default 30) tells the time (in seconds) after that the database shall switch to the Maximum Performance mode.
The Maximum Performance mode is used, when the primary database must not be compromised. That means transactions are confirmed, once they are saved into the local Redolog files and asynchronously transmitted to the Standby database. So in case of a breakdown of the primary database you can expect a loss of transactions.
Following parameters are responsible for the Redolog transport here: