Oracle Standby/DR - Changing the Data Guard Protection Mode

-

Oracle Standby/DR - Changing the Data Guard Protection Mode


Oracle Data Guard provides three modes to protect the data in the primary database. While each provide a high degree of data protection they each have a different effect on both the availability and performance of the primary database. 

Redo Transport Modes

The redo transport services perform the automated transfer of redo data between the primary and the standby database. The transportation can be done in one of two redo transport modes: Synchronous and Asynchronous.

In Synchronous transfer mode (SYNC) the Log Writer process must wait for confirmation that redo data is written on the standby before acknowledging a transaction on the primary database.

The Synchronous transfer mode is a zero loss transfer mode favoring data protection above the availability of the primary database. Any delay in writing the redo data to the standby will result impact performance on the primary and a failure in writing redo data to the standby could result in a shutdown of the primary database. Synchronous transfer mode is required for Maximum Availability and Maximum Protection modes.

In Asynchronous transfer (ASYNC) mode the log writer does not wait for a confirmation that redo data is written on the standby. Delays in transfer of redo data including failure to write redo data on standby do not impact availability of the primary database and provide little if any impact on the performance of the primary database. The Asynchronous transfer mode is not a zero loss transfer mode. Asynchronous transfer mode is required for the Maximum Performance protection mode.

Data Guard Protection Modes

The three protection modes provided in Data Guard listed in order least data protection, least impact potential on the primary to greatest data protection highest impact potential on the primary: Maximum Performance, Maximum Availability and Maximum Performance

Both the Maximum Availability and Maximum Protection mode require Standby Redo logs. All three modes require specific log transport parameters be specified in the LOG_ARCHIVE_DEST_n parameter.

Maximum Performance – In Maximum Performance transactions on the primary database complete a commit as soon as all redo generated is written to the online log of the primary database. Redo data is written to the standby but the log writer does not wait for confirmation of the write, so the performance and the availability of the primary database is not impacted.

In the event that the primary cannot write redo data to the standby then standby is marked as failed and is ignored until it can be it can be connected to once again. Once reconnected any gaps in redo will be sent to resynchronize the standby.

Maximum Performance offers the lowest degree of protection as a failover can result in data loss. Maximum Performance is the default protection mode.

Below is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Performance configuration:

LOG_ARCHIVE_DEST_2=’SERVICE=standby ASYNC NOAFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)’

Maximum Availability – Maximum Availability is a mixture of Maximum Protection and Maximum Performance. In this mode transactions on the primary database do not complete a commit until the redo data required to recovery the transaction is written to the standby redo log. If the redo data cannot be written to the standbys online redo log then the behavior is that of Maximum Performance. Once the primary database is able to write to the standby, the behavior changes back to that of Maximum Availability.

A data loss occurs in Maximum Availability mode if the standby did not have a chance to resynchronize before the failover. There is a slight performance delay on the primary database before continuing processing in the case of a failed standby.

Requirements for Maximum Availability mode:
Redo Transport mode: SYNC
Disk Write Option: AFFIRM
Standby Redo Logs: Yes
Standby Database Type: Physical and Logical

Below is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Availability configuration.

LOG_ARCHIVE_DEST_2=’SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)’

Maximum Protection – Maximum Protection ensures that no data loss will occur in the event of failure of the primary database. The protection comes at the cost of performance and possibly availability of the primary database. In Maximum Protection mode transactions on the primary database do not complete a commit operation until the data to recover the transaction is written to both the primary online redo log and the standby redo log. If the redo data cannot be written to at least one standby the primary database will shutdown. Due to the possibility that the primary database can be shutdown it is recommended to have more than one standby in this configuration.

Requirements for Maximum Protection mode:
Redo Transport mode: SYNC
Disk Write Option: AFFIRM
Standby Redo Logs: Yes
Standby Database Type: Physical and Logical (10g and above)

Below is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Protection configuration.

LOG_ARCHIVE_DEST_2=’SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)'

Below are brief descriptions of the parameters used in the LOG_ARCHIVE_DEST_n examples presented for each protection mode.

SERVICE – Specifies a valid service name for the standby database.

SYNC | ASYNC – Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM – Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT – Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN – Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

DB_UNIQUE_NAME – Unique name for the standby database

VALID_FOR(logifile,role) –Specifies that a specific log destination is valid only when the database is in a particular role.

Prior to changing the protection there are a few preliminary steps that should be taken that are documented below.

Determining the current Protection Mode

SQL> select protection_mode from v$database;
 
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
 
SQL>
Determine the current value of LOG_ARCHIVE_DEST_n that is used to send redo to the standby
SQL> show parameter log_archive_dest_2
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=standby ASYNC NOAFFIRM
                                                  NET_TIMEOUT=30 REOPEN=300 DB_
                                                 UNIQUE_NAME=standby VALID_FOR=
                                                 (ALL_LOGFILES,PRIMARY_ROLE)

Ensure that the primary database and standby database both have DB_UNIQUE_NAME set to a unique value.

Primary:
SQL> show parameter db_unique_name
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      proddb
SQL>

Standby:
SQL> show parameter db_unique_name
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      standby
SQL>
Verify that LOG_ARCHIVE_CONFIG parameter contains the DG_CONFIG value that lists DB_UNIQUE_NAME of the primary and each standby database used in the Data Guard configuration.
SQL> show parameter log_archive_config
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(proddb,standby)

SQL>

Next set the LOG_ARCHIVE_DEST_n parameter to reflect the redo transport requirements for the new protection mode. In this example we are going to change Maximum Performance to Maximum Availability. Note: When changing to Maximum Availability or Maximum Protection mode standby redo logs must be present on the standby.

SQL> alter system set log_archive_dest_2='SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;
 
System altered.
 
SQL>

Next we set the protection mode.

SQL> alter database set standby database to maximize availability;
 
Database altered.
 
SQL>

We can verify that the protection mode has changed by query

SQL> select protection_mode from v$database;
 
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
 
SQL>

If you decide to change the protection mode to Maximum Protection you will need to change the protection while the primary is mounted and not open.

SQL> alter system set log_archive_dest_2='SERVICE=standby SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;
 
System altered.
 
SQL> alter database set standby database to maximize protection;
alter database
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
 
SQL>

==========================================================

Please check our other blogs for Dataguard ,Standby  & DR.




Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Oracle Block Corruption - Detection and Resolution

-
Oracle Block Corruption - Detection and Resolution Any kind of corruption in database can  lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database. Block Corruption :  Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption. Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone.  Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities. Detection of Block Corruption Whenever a corrupt block is read Oracle reports an error in the alert l...
Read more

Add or Remove a node in MySQL Innodb Cluster

-
Add or Remove a node in MySQL Innodb Cluster In any database Cluster Solution ,it's regular activity to add or remove node from the cluster. In this blog , We will discuss about addition and removal of the cluster node from the MySQL Innodb cluster. Remove a node from the MySQL Innodb cluster . 1) Connect to primary node and check status. mysqlsh --uri clusteradmin@mysqlhost01:3306 MySQL  mysqlhost01:3306 ssl  JS > cluster.status() {     "clusterName": "mysqlcluster",     "defaultReplicaSet": {         "name": "default",         "primary": "mysqlhost01:3306",         "ssl": "REQUIRED",         "status": "OK",         "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",         "topology": {     ...
Read more