Steps to change protection mode in Oracle Dataguard 19c

In this article we will practice to change Oracle dataguard protections modes.

Oracle Data Guard provides three protection modes:
=> maximum performance
=> maximum availability
=> maximum protection

As LGWR & ARCH parameters are deprecated now ,New parameters for LOG_ARCHIVE_DEST_n as below.



SYNC with AFFIRM and SYNC with NOAFFIRM

It is important to understand the possible results of using the LOG_ARCHIVE_DEST_n attributes SYNC/AFFIRM versus SYNC/NOAFFIRM (FastSync).

When a transport is performed using SYNC/AFFIRM, the primary performs write operations and waits for acknowledgment that the 
redo has been transmitted synchronously to the physical standby and written to disk. 

When a transport is performed using SYNC/NOAFFIRM, the primary performs write operations and waits only for acknowledgement 
that the data has been received on the standby, not that it has been written to disk. 

In the case of SYNC/AFFIRM, in which there is a check to confirm that data is written to disk on the standby, 
there would be no data loss because the data would be available on the standby when the system was recovered.
In the case of SYNC/NOAFFIRM, in which there is no check that data has been written to disk on the standby, there may be some data loss.

Protection Modes

Maximum Performance 

This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. 
This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. 
Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, 
so primary database performance is unaffected by the time required to transmit redo data and receive acknowledgment from a standby database. 
This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance. 
This is the default protection mode.

Maximum Availability

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. 
Under normal operations, transactions do not commit until all redo data needed to recover 
those transactions has been written to the online redo log AND based on user configuration, one of the following is true:

=> redo has been received at the standby, I/O to the standby redo log has been initiated, and acknowledgement sent back to primary(SYNC/NOAFFIRM)
=> redo has been received and written to standby redo log at the standby and acknowledgement sent back to primary(SYNC/AFFIRM)

If the primary does not receive acknowledgement from at least one synchronized standby, 
then it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database

Maximum Protection

Maximum protection is similar to maximum availability but provides an additional level of data protection in the event of multiple failure events. 
Unlike maximum availability, which allows the primary to continue processing if it is unable to receive acknowledgement from a standby database, 
maximum protection shuts the primary database down rather than allowing it to continue processing transactions that are unprotected.

Changing Database mode from Maximum Availabilty to Maximum Protection Mode

To change primary database protection mode from Maximum Availablity to Maximum Protection mode below requiremend must be fullfilled.

A=> Atleast one LOG_ARCHIVE_DEST_n parameter ,which corresponds to standbydatabase ,must use SYNC & AFFIRM.
B=> Mode changing is allowed from ONLY PERFORMNACE or ONLY AVAILABILITY to Maximize Protection Modes.
C=> Before proceeding to change modes Standby has to be in Synch with primary and NO GAP should be  there.
D=> Standby logfiles are required on both side for smooth operations.

Let we change modes now as below

Step 1=> Check Primary Mode

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM GV$DATABASE;
NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM AVAILABILITY
DBSET	  READ WRITE	       PRIMARY		MAXIMUM AVAILABILITY

Step 2=> Check LOG_ARCHIVE_DEST_1 status as blow.

log_archive_dest_2		     string	 SERVICE=TNSTAR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR

log_archive_dest_state_2	 string	 ENABLE

Step 3=> Check status of Standby database as below.

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM GV$DATABASE;

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM AVAILABILITY
DBSET	  READ ONLY	       PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS	SEQUENCE#
--------- ------------ ----------
ARCH	  CONNECTED		0
DGRD	  ALLOCATED		0
DGRD	  ALLOCATED		0
ARCH	  CONNECTED		0
ARCH	  CLOSING	    43
ARCH	  CONNECTED		0
ARCH	  CLOSING	       26
ARCH	  CONNECTED		0
ARCH	  CLOSING	       44
ARCH	  CLOSING	       25
RFS	  IDLE		       45

PROCESS   STATUS	SEQUENCE#
--------- ------------ ----------
RFS	  IDLE			0
RFS	  IDLE			0
RFS	  IDLE			0
RFS	  IDLE			0
RFS	  IDLE			0
RFS	  IDLE			0
RFS	  IDLE			0
RFS	  IDLE		       27
RFS	  IDLE			0
MRP0	  APPLYING_LOG	       27

21 rows selected.

Step 4=> Bring Down database on Primary Side and start NODE-1 only in mount mode.

[oracle@racnoden1 trace]$ srvctl stop database -d DBSET

[oracle@racnoden1 trace]$ srvctl start instance -db DBSET -instance DBSET1 -startoption MOUNT

[oracle@racnoden1 trace]$ sqlplus "/as sysdba"

SQL>  SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM GV$DATABASE;

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  MOUNTED	       PRIMARY		MAXIMUM PERFORMANCE

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE  PROTECTION;

Database altered.

SQL> exit

Step 5=> Let we start database and check status

[oracle@racnoden1 trace]$ srvctl start database -d DBSET

[oracle@racnoden1 trace]$ sqlplus "/as sysdba"

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM GV$DATABASE;

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PROTECTION
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PROTECTION

SQL> show parameter log_Archive_dest

log_archive_dest_2		    string	 SERVICE=TNSTAR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR
log_archive_dest_state_2    string   ENABLE

As we can see database is upgraded in Maximum Protection mode.

Testing Primary SIte Status if Standby Fails in Maximum Protection Modes.

As we are having Primary database in Maximum Protection mode ,Let we test what will happen if we Shutdown Standby database while running transaction on Primary.

Step 1=> Check DB status on Primary SIte

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM GV$DATABASE;

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PROTECTION
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PROTECTION

Step 2=> Shut Down Standby database .

[oracle@racsetn1 trace]$ srvctl stop database -d DBTAR

Step 3=> Insert records in test table on Primary as below and try to Commit.

SQL> insert into test_perf values (25);

1 row created.

SQL> commit;
commit
     *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19490
Session ID: 106 Serial number: 8345

SQL> SQL> exit

As we can see after few minute Commit session terminated and database on both nodes crashed as below alert logfile,

Errors in file /ora_home/app/oracle/diag/rdbms/dbset/DBSET1/trace/DBSET1_lgwr_23454.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
2022-07-01T17:24:42.716955+05:30
LGWR (PID:23454): Error 12514 verifying LAD:2
2022-07-01T17:24:42.717021+05:30
LGWR (PID:23454): LAD:2 is UNSYNCHRONIZED
LGWR (PID:23454): Continuing
LGWR (PID:23454): Minimum of one applicable standby database required
2022-07-01T17:24:42.727695+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/dbset/DBSET1/trace/DBSET1_lgwr_23454.trc:
ORA-16072: a minimum of one standby database destination is required
2022-07-01T17:24:42.728170+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/dbset/DBSET1/trace/DBSET1_ora_23504.trc:
ORA-16072: a minimum of one standby database destination is required
2022-07-01T17:24:42.758134+05:30
ARC6 started with pid=69, OS id=23600 
USER (ospid: ): terminating the instance due to ORA error 
2022-07-01T17:24:42.811728+05:30
System state dump requested by (instance=1, osid=23504), summary=[abnormal instance termination].
System State dumped to trace file /ora_home/app/oracle/diag/rdbms/dbset/DBSET1/trace/DBSET1_diag_23416.trc
2022-07-01T17:24:43.153034+05:30
Dumping diagnostic data in directory=[cdmp_20220701172442], requested by (instance=1, osid=23504), summary=[abnormal instance termination].
2022-07-01T17:24:44.353805+05:30
Instance terminated by USER, pid = 23504

Step 4=> Let we try to start  DB as below.

[oracle@racnoden1 trace]$ srvctl start database -d DBSET

PRCR-1079 : Failed to start resource ora.dbset.db
CRS-5017: The resource action "ora.dbset.db start" encountered the following error: 
ORA-03113: end-of-file on communication channel
Process ID: 3939
Session ID: 54 Serial number: 18115
. For details refer to "(:CLSN00107:)" in "/grid_home/app/oracle/diag/crs/racnoden1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.dbset.db' on 'racnoden1' failed
CRS-2632: There are no more servers to try to place resource 'ora.dbset.db' on that would satisfy its placement policy
CRS-5017: The resource action "ora.dbset.db start" encountered the following error: 
ORA-03113: end-of-file on communication channel
Process ID: 8499
Session ID: 54 Serial number: 62268
. For details refer to "(:CLSN00107:)" in "/grid_home/app/oracle/diag/crs/racnoden2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.dbset.db' on 'racnoden2' failed
[oracle@racnoden1 trace]$ 

Starting Primary database from Failed Protection Mode.

As we saw in above example Primary database is crashed due to unavailability to Standby Site.
Post crash of Primary database we need manual steps to start Primary database again as below.

Step 1=> Start database in Mount mode on NODE-1 only and Change Protection Mode ONLY.

[oracle@racnoden1 trace]$ srvctl start instance -db DBSET -instance DBSET1 -startoption MOUNT

[oracle@racnoden1 trace]$ sqlplus "/as sysdba"

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE  PERFORMANCE;

Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@racnoden1 trace]$ 


Step 2=> Recycle Complete Primary database as below.

[oracle@racnoden1 trace]$ srvctl stop database -d DBSET
[oracle@racnoden1 trace]$ srvctl start database -d DBSET


Step 3=> Check status of primary database and Log_archive_dest_n parameter.

[oracle@racnoden1 trace]$ sqlplus "/as sysdba"

SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM GV$DATABASE;

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE
DBSET	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> show parameter log_Archive_dest

log_archive_dest_2		     string	 SERVICE=TNSTAR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR

log_archive_dest_state_2	     string	 ENABLE

As we can see DB is started now and working in Maximum Performance Mode.