Tag Archives: change the mode

Check the Protection Mode in Dataguard Oracle

Check and Change the protection mode steps in Dataguard Environment

Protection Mode
There are three protection modes for the primary database:

  • Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
  • Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
  • Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

Note:
By default, for a newly created standby database, the primary database is in maximum performance mode.

Check the Protection Mode in Database

SELECT protection_mode FROM v$database;

PROTECTION_MODE
——————–
MAXIMUM PERFORMANCE

Change the protection mode in Standby Database

Startup mount
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

Following parameters involved in defining the protection mode:
AFFIRM: This conform that a transactions is committed at the Standby Redo log.
NoAFFIRM No Conform required
SYNC: Redolog entries are concurrently written into the local Redolog files and transmitted to the Standby side.
NET_TIMEOUT: This parameter is only available with SYNC and AFFIRM and regulates after what time to switch to the NOAFFIRM mode.

Following parameter must be set for protection mode:

Maximum Protection AFFIRM SYNC
Maximum Availability AFFIRM/NOAFFRIM SYNC NET_TIMEOUT: default 30 sec convert to maximum Performance mode
Maximum Performance NOAFFIRM ASYNC

Switch the Protection Mode
You can switch mode with the following commands. Note the modification in the redo transport attributes is also required.

Maximum Availability.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Maximum Performance.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Maximum Protection.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;