Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Sunday, August 23, 2015

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

No comments: