Oracle 11g Data Guard: How to Change Data Guard Protection M

How to Change Data Protection Mode ofa Primary Database Step 1Select a dataprotection mode that meets your availability, performance, and data protectionrequirements. Maximum Availability This protection mode provides the highest level of

How to Change Data Protection Mode ofa Primary Database

 

Step 1   Select a dataprotection mode that meets your availability, performance, and data protectionrequirements.

 

Maximum Availability

This protection mode provides the highest level of data protectionthat is possible without compromising the availability of a primary database.Transactions do not commit until all redo data needed to recover thosetransactions has been written to the online redo log and to the standby redolog on at least one synchronized standby database. If the primary databasecannot write its redo stream to at least one synchronized standby database, itoperates as if it were in maximum performance mode to preserve primary databaseavailability until it is again able to write its redo stream to a synchronizedstandby database.

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

Maximum Performance

This protection mode provides the highest level of data protectionthat is possible without affecting the performance of a primary database. Thisis accomplished by allowing transactions to commit as soon as all redo datagenerated by those transactions has been written to the online log. Redo datais also written to one or more standby databases, but this is doneasynchronously with respect to transaction commitment, so primary databaseperformance is unaffected by delays in writing redo data to the standbydatabase(s).

This protection mode offers slightly less data protection thanmaximum availability mode and has minimal impact on primary databaseperformance.

This is the default protection mode.

Maximum Protection

This protection mode ensures that no data loss will occur if theprimary database fails. To provide this level of protection, the redo dataneeded to recover a transaction must be written to both the online redo log andto the standby redo log on at least one synchronized standby database beforethe transaction commits. To ensure that data loss cannot occur, the primarydatabase will shut down, rather than continue processing transactions, if itcannot write its redo stream to at least one synchronized standby database.

Transactions on the primary are considered protected as soon as DataGuard has written the redo data to persistent storage in a standby redo logfile. Once that is done, acknowledgment is quickly made back to the primarydatabase so that it can proceed to the next transaction. This minimizes theimpact of synchronous transport on primary database throughput and responsetime. To fully benefit from complete Data Guard validation at the standbydatabase, be sure to operate in real-time apply mode so that redo changes areapplied to the standby database as fast as they are received. Data Guardsignals any corruptions that are detected so that immediate corrective actioncan be taken.

Because this data protection mode prioritizes data protection overprimary database availability, Oracle recommends that a minimum of two standbydatabases be used to protect a primary database that runs in maximum protectionmode to prevent a single standby database failure from causing the primarydatabase to shut down.

Note:

Asynchronously committed transactions are not protected by DataGuard against loss until the redo generated by those transactions has beenwritten to the standby redo log of at least one synchronized standby database.

For more information about the asynchronous commit feature, see:

• Oracle Database Concepts
• Oracle Database SQL Language Reference
• Oracle Database Advanced Application Developer's Guide
• Oracle Database PL/SQL Language Reference

 

Step 2   Verify thatat least one standby database meets the redo transport requirements for thedesired data protection mode.

 

The LOG_ARCHIVE_DEST_n database initializationparameter that corresponds to at least one standby database must include theredo transport attributes listed inTable5-1 for the desired data protection mode.

 

Table 5-1 RequiredRedo Transport Attributes for Data Protection Modes

 

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

 

Step 3   Verify thatthe DB_UNIQUE_NAME database initialization parameter has been set to a uniquevalue on the primary database and on each standby database.

 

Step 4   Verify thatthe LOG_ARCHIVE_CONFIG database initialization parameter has been defined onthe primary database and on each standby database, and that its value includesa DG_CONFIG list that includes the DB_UNIQUE_NAME of the primary database andeach standby database.

 

For example, the following SQL statement might be used to configurethe LOG_ARCHIVE_CONFIG parameter:

 

SQL> showparameter log_archive_config

 

NAME                                 TYPE        VALUE

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

log_archive_config                   string      DG_CONFIG=(prod,standby)

 

Step 5   Set the dataprotection mode.

Execute the following SQL statement on the primary database:

 ALTER DATABASE  SET STANDBY DATABASE TO MAXIMIZE{AVAILABILITY | PERFORMANCE | PROTECTION};

 

SQL> ALTERDATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.

 

SQL> selectLOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfrom v$database;

 

LOG_MODE     OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ARCHIVELOG   READ WRITE           MAXIMUM AVAILABILITY MAXIMUMAVAILABILITY PRIMARY          TO STANDBY

 

 

Note that the data protection mode can be set to MAXIMUM PROTECTIONon an open database only if the current data protection mode is MAXIMUMAVAILABILITY and if there is at least one synchronized standby database.

 

You can change the protection mode from MAXIMUM AVAILABILITY to MAXIMUM PROTECTION when the database is open:

 

SQL> ALTERDATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

 

SQL> selectLOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfrom v$database;

 

LOG_MODE     OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ARCHIVELOG   READ WRITE           MAXIMUM PROTECTION   MAXIMUM PROTECTION   PRIMARY          TO STANDBY

 

But you cannot change the protection mode from MAXIMUM PERFORCEMANCE to MAXIMUM PROTECTION when the database is open

 

SQL> selectLOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfrom v$database;

 

LOG_MODE     OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ARCHIVELOG   READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          FAILED DESTINATION

 

 

SQL> ALTERDATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

ALTER DATABASE SETSTANDBY DATABASE TO MAXIMIZE PROTECTION

*

ERROR at line 1:

ORA-01126: databasemust be mounted in this instance and not open in any instance

 

To change the data protection mode from MAXIMUMPERFORMANCE to MAXIMUM PROTECTION , the database must be mounted, not open.

 

SQL> shutdownimmediate

SQL> startupmount;

SQL> selectLOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfrom v$database;

 

LOG_MODE     OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ARCHIVELOG   MOUNTED              MAXIMUM PERFORMANCE  UNPROTECTED          PRIMARY          NOT ALLOWED

 

SQL> ALTERDATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

 

SQL> selectLOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfrom v$database;

 

LOG_MODE     OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ARCHIVELOG   MOUNTED              MAXIMUM PROTECTION   UNPROTECTED          PRIMARY          NOT ALLOWED

 

 

Pls Note, If the listener of the standby is notstarted, the switchover status here shows "FAILED DESTINATION" .Afteryou start the standby's listener

, the status will change to "TO STANDBY".

 

SQL> selectLOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfrom v$database;

 

LOG_MODE     OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS

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

ARCHIVELOG   READ WRITE           MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          TO STANDBY

作者：xiangsir

QQ：444367417

MSN：xiangsir@hotmail.com