We have seen Protection Modes in depth in my previous Article.

To read protection modes in data guard :

Protection modes in Oracle Dataguard

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

About The Author

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.