Convert Standby data protection mode to Maximum Availability

Standby Maximum Availability

What is Maximum Availability Mode?

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database.

What happens to the Primary database if the standby database is not available?

If the primary database cannot write its redo stream to at least one synchronized standby database, 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.

Benefits of Maximum Availability Mode:

This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

What are the prerequisites for Maximum Availability Mode?

Make sure “Required Redo Transport Attributes for Data Protection Modes” are set as below

Maximum Availability	Maximum Performance	Maximum Protection
AFFIRM	NOAFFIRM	AFFIRM
SYNC	ASYNC	SYNC
DB_UNIQUE_NAME	DB_UNIQUE_NAME	DB_UNIQUE_NAME

Demo:

Convert the physical standby database with Maximum Performance mode to Maximum Availability Mode:

SQL> col DB_UNIQUE_NAME for a10

SQL>

SQL> select db_unique_name,database_role,open_mode,protection_mode from v$database;

DB_UNIQUE_ DATABASE_ROLE    OPEN_MODE        PROTECTION_MODE

----------     ---------------- -------------------- --------------------

msft           PRIMARY        READ WRITE        MAXIMUM PERFORMANCE

Change the redo transport attributes on Primary & Secondary:

On Primary:

From:

SQL> show parameter log_archive_dest_2

NAME                    TYPE     VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2            string     service=orcl VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UN

                       IQUE_NAME=orcl

To:

SQL> alter system set log_archive_dest_2='service=orcl LGWR AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';

System altered.

SQL> show parameter log_archive_dest_2

NAME                    TYPE     VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2            string     service=orcl LGWR AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES, PR

                       IMARY_ROLE) DB_UNIQUE_NAME=orcl

On Standby:

SQL> alter system set log_archive_dest_2='service=msft LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=msft';

System altered.

SQL> show parameter log_archive_dest_2

NAME                    TYPE     VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2            string     service=msft LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRI

                       MARY_ROLE) DB_UNIQUE_NAME=msft

Convert the protection mode to Maximum Availability:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  430075904 bytes

Fixed Size           2214056 bytes

Variable Size         352323416 bytes

Database Buffers       71303168 bytes

Redo Buffers           4235264 bytes

Database mounted.

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> select db_unique_name,database_role,open_mode,protection_mode from v$database;

DB_UNIQUE_ DATABASE_ROLE    OPEN_MODE        PROTECTION_MODE

----------     ---------------- -------------------- --------------------

msft           PRIMARY        MOUNTED        MAXIMUM AVAILABILITY

SQL> alter database open;

Database altered.

Now verify the protection mode on Standby:

SQL> select db_unique_name,database_role,open_mode,protection_mode from v$database;

DB_UNIQUE_ DATABASE_ROLE    OPEN_MODE        PROTECTION_MODE

---------- ---------------- -------------------- --------------------

orcl       PHYSICAL STANDBY MOUNTED        MAXIMUM AVAILABILITY