How to change mode of dataguard from Max Performance to Max Availability

Hello Friend's,

In This Post we will discuss about How to change mode of dataguard from Max Performance to Max Availability

Oracle Dataguard protection modes and how and when to use which mode .

Oracle Data Guard provides three primary protection options that balance data protection with performance expense.

Each protection mode configures the database to provide varying levels of data loss prevention and disaster recovery capabilities.

Below are three modes:

Maximum Protection:

Purpose: No data loss is ensured.

Mechanism: Transactions on the primary database do not commit until the redo data required to recover those transactions is written to both the local online redo log and the standby redo log on at least one backup database.

Performance Impact: This option may have an impact on performance since transactions must wait for an acknowledgement that the redo has been successfully written to the standby.

Fault Tolerance: If the standby database fails, the primary database will shut down to prevent the loss of any transactions.

Maximum availability:

The objective is to provide a balance between data security and performance.

Mechanism—Primary database transactions commit no quicker than the redo data required to recover those transactions, which is recognized by at least one synchronized standby database. Unlike Maximum Protection, if the

standby database becomes unavailable, the main will continue to run in Maximum Performance mode until the standby database becomes available.

Performance effect: In general, data is well-protected in this mode, with no significant performance effect, although the main is delayed while waiting for acknowledgements from the standby.

Fault Tolerance: Unlike previous synchronous required modes, the primary database can continue to function even if the standby database fails. Possible data loss is reduced.

Maximum performance:

Description: Ensures optimal performance while minimizing interference with the primary database's transaction throughput.

Mechanism: Transactions on the primary database commit without waiting for the redo data to be written to the backup database. Redo data is transferred asynchronously to the standby.

This option has the least performance effect since transactions do not need to wait for acknowledgement from the standby database.

Fault Tolerance: Data may be lost if the primary database fails because it does not wait for redo data to be written to the standby database.

Choosing a Protection Mode

Maximum Protection: This protection mode must be enabled in conditions where no data loss is permitted and system performance is enough to support activities on both primary and standby databases.

Maximum Availability: Used when a high level of data security is required, and the primary database must stay operational even if the standby is momentarily unavailable.

Maximum Performance: To be utilized when performance is of the utmost importance and minor data loss is acceptable in the event of a disaster.

So let's get started with steps

Step 1 : Check current state and mode of database

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

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN   GRT        PRIMARY       MAXIMUM PERFORMANCE

To set the protection mode to MAXIMUM AVAILABILITY/MAXIMUM PROTECTION, configure standby redo logs on the standby database. Configure the redo shippment option (log_archive_dest_2) in the primary database to utilize SYNCHRONOUS ("SYNC") mode.

Let’s check the number of online redo logs and it’s size on primary database


SQL> select group#,bytes/1024/1024 from v$log;
 
GROUP# BYTES/1024/1024
------ ------------------
1      200
2      200
3      200

As seen below, no standby redo log groups are configured on the primary database.


SQL> select group#,bytes/1024/1024 from v$standby_log;
 
no rows selected

Step 2 : Add standby redo log groups on the primary database with the same size as that of the online redo log groups.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '+DATA'  SIZE 200M;
 
Database altered.
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '+DATA'  SIZE 200M;
 
Database altered.
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '+DATA'  SIZE 200M;
 
Database altered.
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '+DATA'  SIZE 200M;
 
Database altered.

We can now see that four standby redo log groups have been added with the same size as the online redo logs. These standby redo logs will only be utilized during a switchover, not on the primary database.


SQL> select group#,bytes/1024/1024 from v$standby_log;
 
GROUP# BYTES/1024/1024
------ ---------------
4      200
5      200
6      200
7      200

Check the current log sequence on primary


SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
 
THREAD# MAX(SEQUENCE#)
------- --------------
1       401

Check the current log sequence on Standby :


SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
 
THREAD# MAX(SEQUENCE#)
------- --------------
1       401

Standby database is in sync with the primary database.

Standby database details:


SQL> select status,instance_name,database_role from v$database,v$Instance;
 
STATUS  INSTANCE_NAME DATABASE_ROLE
------- ------------- ------------------
MOUNTED GRTDR        PHYSICAL STANDBY

On the standby database, there are 3 online redo log groups with the size 200M and there are no standby redo log groups.


SQL> select group#,bytes/1024/1024 from v$log;
 
GROUP# BYTES/1024/1024
------ ----------------
1      200
3      200
2      200
 
SQL> select group#,bytes/1024/1024 from v$standby_log;
 
no rows selected

To add standby redo log groups to the standby database, first verify if MRP (Managed Recovery Process) is ongoing. If so, cancel it.


SQL> select process,status,sequence# from v$managed_standby;
 
PROCESS STATUS       SEQUENCE#
------- ------------ ----------
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
RFS     IDLE         0
RFS     IDLE         0
RFS     IDLE         0
MRP0    WAIT_FOR_LOG 411
 
8 rows selected.

Stpe 3 : Cancel the MRP process on the standby database:


SQL> alter database recover managed standby database cancel;
 
Database altered.

Step 4 : Add 4 Standby Redo Log (SRL) groups of size same as online redo log groups (200M) on the standby database:


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '+DATA'  SIZE 200M;
 
Database altered.
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '+DATA'  SIZE 200M;
 
Database altered.
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '+DATA'  SIZE 200M;
 
Database altered.
 
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '+DATA'  SIZE 200M;
 
Database altered.
 
SQL> select group#,bytes/1024/1024 from v$standby_log;
 
GROUP# BYTES/1024/1024
------ ---------------
4      200
5      200
6      200
7      200

Step 5 : Once the SRLs are added, start the MRP on the standby database


SQL> alter database recover managed standby database disconnectfrom session;
 
Database altered.
 
SQL> select process,status,sequence# from v$managed_standby;
 
PROCESS STATUS       SEQUENCE#
------- -----------  ----------
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
RFS     IDLE         0
RFS     IDLE         0
RFS     IDLE         0
MRP0    WAIT_FOR_LOG 411
 
8 rows selected.

Step 6 : On primary database change Log archive dest 2 settings to make database compatible for mode change


SQL> show parameter log_archive_dest_2
 
NAME               TYPE   VALUE
------------------ ------ -----------------------------------
log_archive_dest_2 string service=GRTDR valid_for=(online_logfiles,primary_role) db_unique_name=GRTDR
 
SQL> alter system set log_archive_dest_2='service=GRTDR LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=GRTDR';
 
System altered.
 
SQL> show parameter dest_2
 
NAME                        TYPE   VALUE
--------------------------- ------ ----------------------------
db_create_online_log_dest_2 string
log_archive_dest_2          string service=GRTDR LGWR AFFIRM SYNC valid_for=(online_logfiles,p
                            rimary_role) db_unique_name=GRTDR

As previously stated, enable the redo shippment option (log_archive_dest_2) on the primary database to utilize SYNCHRONOUS mode.

Step 7 : Shutdown the primary database and mount it.


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 208769024 bytes
Fixed Size 2226936 bytes
Variable Size 180356360 bytes
Database Buffers 20971520 bytes
Redo Buffers 5214208 bytes
Database mounted.
SQL>

Change the protection mode on the primary database according to the requirement using the following command.

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

Here, I change the protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY.


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

Step 8 : Once the mode is changed, open the primary database and verify the same.


SQL> alter database open;
 
Database altered.
 
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- ----------------------
OPEN   GRT        PRIMARY       MAXIMUM AVAILABILITY

Step 9 : Check if the standby database is in sync with the primary database

On primary:


SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
 
THREAD# MAX(SEQUENCE#)
------- --------------
1       441

Standby:


SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS  INSTANCE_NAME DATABASE_ROLE    PROTECTION_MODE
------- ------------- ---------------- --------------------
MOUNTED GRTDR        PHYSICAL STANDBY MAXIMUM AVAILABILITY
 
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
 
THREAD# MAX(SEQUENCE#)
------- --------------
 1      441

The standby database is in sync with the primary, and the PROTECTION mode has been adjusted to MAXIMUM AVAILABILITY.

Newest
Previous
Next Post »