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.
ConversionConversion EmoticonEmoticon