FAQ-Standby-Protection mode

1. How many protection modes? Oracle provides three types of protection mode. (Max Protection,max Availability and max performance)

2. Maximum Protection Mode Which guarantees no data loss. In this protection mode the redo data must be written to both the local online redo log and to a standby redo log on at least one standby database before the transaction commits.

3. What happens if redo writing fails on Standby in Maximum Protection Mode? To ensure that data loss cannot occur, the primary database will shut down.

4. How to prevent from failover in Maximum Protection Mode? We have to implement a minimum of two standby databases to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

5. Benefit of Maximum Protection Mode? This protection mode comes to the rescue when the primary database fails. The primary database shuts down and stops processing the transaction when this protection mode is enabled. So with this protection mode, you can be assured that there will be no data loss.

6. What is Maximum Availability This protection mode provides the highest level of data protection that is possible without affecting the availability of the primary database. Like maximum protection mode, transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronised standby database. Unlike maximum protection mode, the primary database will not shut down if a fault prevents it from writing its redo stream to a synchronised standby database.

7. Benefit of Maximum Availability? The biggest advantage with maximum availability mode is that the availability of the primary database is uncompromised, but the maximum possible level of protection is offered to the data. The transactions with this protection mode only commit when all the redone data needed to recover the transactions get written to a standby database and the online log.

8. What is Maximum Performance (default mode of operation) This protection mode provides the highest level of data protection that is possible without affecting the performance of the primary database. This protection mode allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local redo log. The primary database’s redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.

9. Benefit of Maximum Performance? The main advantage of this default mode is that the performance of the primary data is not affected while the highest possible level of protection for the data is ensured. The transactions begin to commit as soon as the redone data produced by the transactions get written on to the online log.

10. Prerequisites for Protection Mode?

* Maximum Protection Mode and Maximum Availability Mode : LGWR(Redo Archival process), SYNC (Network Transport Mode), AFFIRM (DISK Write Options) and required standby redo logs

* Maximum Performance Mode: ARCH(Redo Archival process), ASYNC (Network Transport Mode), NOAFFIRM (DISK Write Options) and required standby redo logs are not required but recommended for Real time only.

11. How to change Protection mode?

If want to set Max protection or max availability mode then we have to first add standby redo log and choose LGWR as a transporter

* For Maximum Protection we must choose SYNC mode, Startup the primary database in mount stage and run

alter database set standby database to maximize {AVAILABILITY | PERFORMANCE | PROTECTION};

Then open the database.

To confirm the change in protection mode, query v$database on the primary.

SQL> select protection_mode, protection_level from v$database;

* If the primary database is RAC enabled, shut down all of the instances and then start and mount a single instance.

12. LGWR SYNC and ASYNC

We use redo log transportation mode in SYNC and ASYNC mode, the difference is all about when the COMMIT happens.

* In SYNC mode, synchronizes the primary with the standby database and all DML on the primary server will NOT be committed until the logs have been successfully transported to the standby servers. The synchronous log transport mode is required for the Maximum Protection and Maximum Availability data protection modes.

* In (ASYNC) mode, allows updates (DML) to be committed on the primary server before the log file arrives on the standby servers. The asynchronous log transport mode is required for the Maximum Performance data protection mode.