Changing Protection Mode in Data Guard !!
3 min readIn Oracle Data Guard, changing the protection mode involves adjusting the configuration to one of the three protection modes:
Maximum Protection, Maximum Availability, or Maximum Performance.
The steps to change the protection mode are straightforward but need to be done carefully to avoid any potential issues with your Data Guard setup.
Primary database:
Verify the Current Protection Mode:
SQL> select DATABASE_ROLE,name,log_mode,protection_mode from v$database;
DATABASE_ROLE NAME LOG_MODE PROTECTION_MODE
---------------- --------- ------------ --------------------
PRIMARY PROD101 ARCHIVELOG MAXIMUM PERFORMANCE
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
----------------- ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string SERVICE=prod101s NOAFFIRM ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=prod101s
SQL> alter system set log_archive_dest_2='service=prod101s LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=prod101s';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
----------------- ----------- ------------------------------
log_archive_dest_2 string service=prod101s LGWR AFFIRM SYNC valid_for=
(online_logfiles,primary_role)
db_unique_name=prod101s
Shutdown the primary database and mount it.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1778381840 bytes
Fixed Size 8897552 bytes
Variable Size 419430400 bytes
Database Buffers 1342177280 bytes
Redo Buffers 7876608 bytes
Database mounted.
Now, change the protection mode on the primary database as needed using the following command:
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PROTECTION | PERFORMANCE};
1. For example, to change the protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY:
SQL> alter database set standby database to maximize availability;
Database altered.
Once the mode is changed, open the primary database
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 PROD101 PRIMARY MAXIMUM AVAILABILITY
Check if the standby database is in sync with the primary database
Primary:
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 45 45 0
1 45 45 0
SQL> alter system switch logfile;
System altered.
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 46 46 0
1 46 46 0
2. For example, to change the protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY:
Primary:
SQL> alter system set log_archive_dest_2='service=prod101s LGWR NOAFFIRM ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=prod101s';
System altered.
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database altered.
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
Hope it helped !! 🙂
