Data Guard Protection Modes

Oracle 10G Data Guard Protection Modes:
---------------------------------------------------------
This chapter contains the following sections:

• Data Guard Protection Modes
• Setting the Data Protection Mode of a Primary Database

Data Guard Protection Modes:
This section describes the Data Guard protection modes.
In these descriptions, a synchronized standby database is meant to be one that meets the minimum requirements of the configured data protection mode and that does not have a redo gap.

Maximum Availability:
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. 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.

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.

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

This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.

This is the default protection mode.

Maximum Protection
This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.

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

Setting the Data Protection Mode of a Primary Database
Perform the following steps to change the data protection mode of a primary database:
Step 1 Select a data protection mode that meets your availability, performance and data protection requirements.

Step 2 Verify that redo transport is configured to at least one standby database
The value of the LOG_ARCHIVE_DEST_n database initialization parameter that corresponds to the standby database must include the redo transport attributes listed below for the data protection mode that you are moving to.

If the primary database has more than one standby database, only one of those standby databases must use the redo transport settings.

The standby database must also have a standby redo log.

Required Redo Transport Attributes for Data Protection Modes

Maximum Availability:

• AFFIRM
• SYNC
• DB_UNIQUE_NAME

Maximum Performance:

• NOAFFIRM
• ASYNC
• DB_UNIQUE_NAME

Maximum Protection:

• AFFIRM
• SYNC
• DB_UNIQUE_NAME

Step 3 Verify that the DB_UNIQUE_NAME database initialization parameter has been set to a unique name on the primary and standby database.

For example, if the DB_UNIQUE_NAME parameter has not been defined on either database, the following SQL statements might be used to assign a unique name to each database.

Execute this SQL statement on the primary database:

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='CHICAGO' SCOPE=SPFILE;

Execute this SQL statement on the standby database:

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='BOSTON' SCOPE=SPFILE;

Step 4 Verify that the LOG_ARCHIVE_CONFIG database initialization parameter has been defined on the primary and standby database and that its value includes a DG_CONFIG list that includes the DB_UNIQUE_NAME of the primary and standby database.

For example, if the LOG_ARCHIVE_CONFIG parameter has not been defined on either database, the following SQL statement could be executed on each database to configure the LOG_ARCHIVE_CONFIG parameter:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CHICAGO,BOSTON)';

Step 5 Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.
For example:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 6 Set the data protection mode.
Execute the following SQL statement on the primary database:

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

If the primary database is an Oracle Real Applications Cluster, any instances stopped in Step 5 can now be restarted.

Step 7 Open the primary database.
If the database was restarted in Step 5, open the database:
SQL> ALTER DATABASE OPEN;

Step 8 Confirm that the primary database is operating in the new protection mode.
Perform the following query on the primary database to confirm that it is operating in the new protection mode:
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;