We have seen Protection Modes in depth in my previous Article.
To read protection modes in data guard :
In this article, we will see changing protection modes .
First, we need to decide what protection mode we want in our environment. Which protection mode suits better.What is important for us Database Availability , Database Performance or Data protection??
After choosing what protection mode we want to use , we need to set redo transport accordingly.
Maximum Availability | Maximum Performance | Maximum Protection |
---|---|---|
AFFIRM | NOAFFIRM | AFFIRM |
SYNC | ASYNC | SYNC |
DB_UNIQUE_NAME | DB_UNIQUE_NAME | DB_UNIQUE_NAME |
Check Protection mode :
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- TESTDB READ WRITE PRIMARY MAXIMUM PERFORMANCE
Here database is in Maximum Performance mode and we want to change it to Maximum Availability mode.
Step 1 :
DB_UNIQUE_NAME is important parameter and need to be set on all database participated in data guard environment.
To set db_unique_name use following command.
On Primary
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='TESTDB' SCOPE=SPFILE; System altered.
On Standby
SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='STD_TESTDB' SCOPE=SPFILE; System altered.
Step 2 : Check log_archive_config parameter on both side :
On Primary :
SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(TESTDB,STD_TESTDB)
On Standby :
SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(TESTDB,STD_TESTDB,DBTEST)
Step 3 : Change redo transport parameter accordingly.
SQL> alter system set log_archive_dest_2='SERVICE=STD_TESTDB LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 REOPEN=50 DB_UNIQUE_NAME=STD_TESTDB'; System altered.
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 50 seconds.
Note : Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.
Now, shutdown database :
SQL> SHUT IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down.
Startup database in mount mode.
SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 663908352 bytes Fixed Size 2256192 bytes Variable Size 578814656 bytes Database Buffers 79691776 bytes Redo Buffers 3145728 bytes Database mounted. SQL>
Use the following command to change protection mode.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; Database altered.
Now, open database.
SQL> alter database open; Database altered.
Check Protection mode of a database.
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- TESTDB READ WRITE PRIMARY MAXIMUM AVAILABILITY
Stay tuned for More articles on Oracle DataGuard
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
Can i have multiple protection modes?One primary and two standby
NO