Dataguard : Setting protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY

Data guard : Setting protection mode from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY

Whenever we change the protection mode in primary database  it will automatically changed in standby database.

Before changing set  ASYNC to SYNC on log_archive_dest_2state. We set  protection mode  in mount state on primary database.

• Connect primary database  check the current status 

 [oracle@srv6 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 23 16:31:53 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         orcl12c          PRIMARY          MAXIMUM PERFORMANCE

SQL> sho parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=orcl12cdr LGWR ASYNC V
                                                 ALID_FOR=(ONLINE_LOGFILES,PRIM
                                                 ARY_ROLE) DB_UNIQUE_NAME=orcl1
                                                 2cdr
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string

Change the value to AFFIRM SYNC state log_archive_dest_2 or standy destination location
SQL> alter system set log_archive_dest_2='service=orcl12cdr LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=orcl12cdr';

System altered.

SQL> sho parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=orcl12cdr LGWR AFFIRM
                                                 SYNC valid_for=(online_logfile
                                                 s,primary_role) db_unique_name
                                                 =orcl12cdr
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string

• Start primary database in mount stage and change value from MAXIMUM PERFORMANCE to MAXIMUM AVAILABILITY . Same as  MAXIMUM PROTECTION mode.

 
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1862269648 bytes
Fixed Size                  9136848 bytes
Variable Size             687865856 bytes
Database Buffers         1157627904 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;

Database altered.

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         orcl12c          PRIMARY          MAXIMUM AVAILABILITY

• Standby database mode status before changes 

 

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      orcl12cdr        PHYSICAL STANDBY MAXIMUM PERFORMANCE

      
Standby database mode after changes 
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      orcl12cdr        PHYSICAL STANDBY MAXIMUM AVAILABILITY

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

PROCESS      THREAD#  SEQUENCE# STATUS
--------- ---------- ---------- ------------
ARCH               1        305 CLOSING
DGRD               0          0 ALLOCATED
DGRD               0          0 ALLOCATED
ARCH               1        307 CLOSING
ARCH               1        304 CLOSING
ARCH               1        306 CLOSING
RFS                1          0 IDLE
RFS                1        308 IDLE
RFS                0          0 IDLE
RFS                0          0 IDLE

10 rows selected.

  
 

• Apply recovery MRP after changes 

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

Database altered.

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

PROCESS      THREAD#  SEQUENCE# STATUS
--------- ---------- ---------- ------------
ARCH               1        305 CLOSING
DGRD               0          0 ALLOCATED
DGRD               0          0 ALLOCATED
ARCH               1        307 CLOSING
ARCH               1        304 CLOSING
ARCH               1        306 CLOSING
RFS                1          0 IDLE
RFS                1        308 IDLE
RFS                0          0 IDLE
RFS                0          0 IDLE
MRP0               1        304 APPLYING_LOG

11 rows selected.



  

• Check the sync status between primary and standby database

 
  
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#
;   2    3    4    5    6    7

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    307                   307          0

SQL>