Changing Data Guard Protection Modes

Overview

Maximum Protection:- It uses SYNC redo transport and commit acknowledgment will not happen in primary until transactions are not applies on to the atleast one standby database. Standby database in this mode guarantees data protection but it causes overhead to the primary database as it was to wait to acknowledge the commit. In case primary is not in sync with standby or there is some transaction faults then the primary database will be shutdown to ensure data protection.

Maximum Performance:- When you configure the standby database, default it operates in maximum performance mode. It allows transactions to be commited as soon as redo data required to recover is written on to the online redolog file. Primary database continues to function even when there is gap between primary and standby database or standby is in out of sync. It uses ASYNC redo transport.

Maximum Availability:- This mode works like the maximum protection mode by default i.e. transactions will not commit on the primary database until transactions are applied to atleast one standby database. But primary database will not be shutdown in case there is transaction fault or standby getting out of sync. When standby database gets out of sync it will wait for number of seconds specified in NET_TIMEOUT parameter, if it exceeds number of NET_TIMEOUT seconds then standby database will start to operate in Maximum performance mode.

Changing Dataguard to Maximum Availability Mode

Step 1:- (Standby Database) - Check the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

------------ ---------------- ---------------- --------------------

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM PERFORMANCE

Step 2:- (Standby Database) - Cancel the recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:- (Primary Database) - Set log_archive_dest_2 parameter 

SQL> alter system set log_archive_dest_2='SERVICE=PRODSTBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=PRODSTBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

System altered.

Step 4:- (Primary Database) - Now set the standby protection mode to maximum availability

SQL> alter database set standby database to maximize availability;

Database altered.

Step 5:-  (Standby Database) - Start the recovery using current logfile

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Step 6:-  (Standby Database) - Verify the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

------------ ---------------- ---------------- --------------------

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM AVAILABILITY

Changing Dataguard to Maximum Protection Mode

Step 1:- (Standby Database) - Check the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

------------ ---------------- ---------------- --------------------

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM AVAILABILITY

Step 2:- (Standby Database) - Cancel the recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:- (Primary Database) - Now set the standby protection mode to maximum protection

SQL> 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

Step 5:-  (Standby Database) - Start the recovery using current logfile

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Step 6:-  (Standby Database) - Verify the standby database protection mode

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

------------ ---------------- ---------------- --------------------

MOUNTED      PRODPRIM         PHYSICAL STANDBY MAXIMUM PROTECTION