Configuring Data Protection Modes.

Configuring Data Protection Modes.

DRILL Set the Data Protection Mode by Using SQL (Maximum Performance to Maximum Availability)

• Maximum protection: LGWR, SYNC, AFFIRM, and standby redo logs files

• Maximum availability: LGWR, SYNC, AFFIRM, and standby redo logs files for physical standby databases

• Maximum performance: Any combination of LGWR or ARCH

Using the following SQL statement on the primary database, you can configure the Data Guard environment to maximize data protection, availability, or performance:

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

SQL> select name, open_mode,protection_mode,protection_level,database_role,switchover_status from v$database;

NAME      OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ORCL      READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=STDY VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES) DB_UNIQUE_NAME=STDY

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDY LGWR SYNC VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES) DB_UNIQUE_NAME=STDY';

System altered.

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=STDY LGWR SYNC VALID_F

OR=(PRIMARY_ROLE,ONLINE_LOGFIL

ES) DB_UNIQUE_NAME=STDY

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

SQL> select name, open_mode,protection_mode,protection_level,database_role,switchover_status from v$database;

NAME      OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ORCL      READ ONLY WITH APPLY MAXIMUM AVAILABILITY RESYNCHRONIZATION    PHYSICAL STANDBY NOT ALLOWED

NAME      OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ORCL      READ ONLY WITH APPLY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY NOT ALLOWED

Downgrading the Protection Mode

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDY ARCH VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES) DB_UNIQUE_NAME=STDY';

System altered.

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=STDY ARCH VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES) DB_UNIQUE_NAME=STDY

 SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

SQL> select name, open_mode,protection_mode,protection_level,database_role,switchover_status from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE   SWITCHOVER_STATUS

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

ORCL      READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY         TO STANDBY

  

DRILL Set the Data Protection Mode by Using Broker (Maximum Performance to Maximum Availability)

DGMGRL> EDIT DATABASE orcl SET PROPERTY LogXptMode='SYNC';

DGMGRL> EDIT DATABASE stdy SET PROPERTY LogXptMode='SYNC';

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Downgrading the Protection Mode

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

Succeeded.

DGMGRL> EDIT DATABASE stdy SET PROPERTY LogXptMode='ASYNC';

Property "logxptmode" updated

DGMGRL> EDIT DATABASE orcl SET PROPERTY LogXptMode='ASYNC';

Property "logxptmode" updated.