PROTECTION_MODE和PROTECTION_LEVEL的区别

版本：10.2.0.5 on windows 2008 64 bit

在primary database上查看dataguard运行的模式：

select PROTECTION_MODE,PROTECTION_LEVEL v$database;

1 MAXIMUM AVAILABILITYMAXIMUM AVAILABILITY

这两个有啥区别呢，参考TRANSPORT: Data Guard Protection Modes (文档 ID 239100.1)。

里面有一句话：
PROTECTION_MODE : Protection mode set for the database
PROTECTION_LEVEL : Actual, real time mode in affect for the database; this will change after outages/faults

其实意思就是，protection mode是设定的dataghuard保护模式；而protection level是实际生效的保护模式。

如primary 运行于maximize availability，standby 遇故障关闭后，protection level

select PROTECTION_MODE,PROTECTION_LEVEL v$database;

  MAXIMUM AVAILABILITY RESYNCHRONIZATION

一般情况下standby和primary database这个view里这两个column的值都是同步的。

但是我在做一个physical switch over的例子的时候出现在primary和standby 不一致的情况。

TRANSPORT: Data Guard Protection Modes (文档 ID 239100.1)

Purpose

Scope

Details

MAXIMUM PROTECTION

MAXIMUM AVAILABILITY

MAXIMUM PERFORMANCE

HOW TO CHANGE THE PROTECTION MODE:

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 21-Sep-2012*** 

PURPOSE

A Data Guard configuration always runs in one of three data protection modes:

• MAXIMUM PROTECTION
• MAXIMUM AVAILABILITY
• MAXIMUM PERFORMANCE (default)

All three modes provide a high degree of data protection, but they differ in terms of the effect that each has on the availability and performance of the primary database.  This document attempts to provide clarification on how each work, their requirements, and how to change the protection mode of a Data Guard environment.

This document supplements the information provided in the Data Guard Concepts and Administration guide and is not a replacement.

SCOPE

This document is intended for Oracle Data Guard database administrators that would like to understand more about protection levels.

DETAILS

MAXIMUM PROTECTION 

This protection mode guarantees that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to a standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to at least one synchronized standby database.

To participate in MAXIMUM PROTECTION the following requirements must be met:

• Redo Archival Process : LGWR
• Network Transmission mode : SYNC
• Disk Write Option : AFFIRM
• Standby Redo Logs : Yes
• Standby Database Type :  Physical Only in 9i, Physical AND Logical in 10g

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of  twostandby 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.

MAXIMUM AVAILABILITY

This protection mode provides the highest level of data protection that is possible without affecting the availability of the primary database. Like maximum protection mode, 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. Unlike maximum protection mode, the primary database will not shut down if a fault prevents it from writing its redo stream to a synchronized standby database. Instead, the primary database will operate in RESYNCHRONIZATION until the fault is corrected and all log gaps have been resolved. When all log gaps have been resolved, the primary database automatically resumes operating in 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. 

To participate in MAXIMUM AVAILABILITY the following requirements must be met:

• Redo Archival Process : LGWR
• Network Transmission mode : SYNC
• Disk Write Option : AFFIRM
• Standby Redo Logs : Yes
• Standby Database Type : Physical and Logical

MAXIMUM PERFORMANCE

This protection mode provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data. 

When network links with sufficient bandwidth and latency are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance. 

To participate in maximum performance the following requirements must be met:

• Redo Archival Process : LGWR or ARCH
• Network Transmission mode : ASYNC when using LGWR only 
• Disk Write Option : NOAFFIRM
• Standby Redo Logs : No, but recommended and required for Real-Time Apply
• Standby Database Type : Physical and Logical

When using LGWR to remotely archive in ASYNC mode, the LGWR process does not wait for each network I/O to complete before proceeding. This behavior is made possible by the use of an intermediate process, known as a LGWR network server process (LNS), which performs the actual network I/O and waits for each network I/O to complete. In Oracle9iR2 and 10gR1, each LNS has a user configurable buffer that is used to accept outbound redo data from the LGWR. This is configured by specifying the size in 512 byte blocks on the ASYNC attribute in the archivelog destination parameter. For example ASYNC=2048 indicates a 1Mb buffer. As long as the LNS process is able to empty this buffer faster than the LGWR can fill it, the LGWR will never stall. If the LNS cannot keep up, then the buffer will become full and the LGWR will stall until either sufficient buffer space is freed up by a successful network transmission or a timeout occurs.  For further informaton on the buffer, see the Data Guard Concepts and Administration guide regarding LOG_ARCHIVE_DEST_N and the SYNC/ASYNC attributes.

In Oracle10gR2, this buffer is no longer in use as LNS will instead read from the online redo log itself on the primary when ASYNC is defined.

When remotely archiving using the ARCH attribute, redo logs are transmitted to the destination during an archival operation. The background archiver processes (ARCn) or a foreground archival operation serves as the redo log transport service. Using ARCH to remotely archive does not impact the primary database throughput as long as enough redo log groups exist so that the most recently used group can be archived before it must be reopened.

HOW TO CHANGE THE PROTECTION MODE:

If using Oracle9i and the primary is RAC enabled, downgrading and upgrading the protection level  must be done with only one instance mounting the controlfile and CLUSTER_DATABASE=FALSE.

1. Make sure the LOG_ARCHIVE_DEST_N settings on the primary are configured and the destination enabled to support the target protection mode as documented above. All parameters should be set as needed in the spfile or pfile. For the mode of MAXIMUM PROTECTION, the standby must be up and mounted. For purposes of example, LOG_ARCHIVE_DEST_2 will be used.
   SQL> alter system set log_archive_dest_2='service=STBY LGWR SYNC AFFIRM .......... '; 
   SQL> alter system set log_archive_dest_state_2=enable;
   If using Oracle10g or higher, attributes VALID_FOR and DB_UNIQUE_NAME should also be set as part of the LOG_ARCHIVE_DEST_n.
2. Skip this step if you are on Oracle 11.x or you want to downgrade the Protection Mode in Oracle 10.x
   Shut down the primary database and restart it in mounted mode. If the primary database is RAC enabled, shut down all of the instances and then start and mount a single instance.
   
   For example:
   SQL> shutdown immediate
   SQL> startup mount
3. Change the protection mode.  If step #2 was done, also open the database and restart the other RAC instances.
   
   SQL> alter database set standby database to maximize {AVAILABILITY | PERFORMANCE | PROTECTION}; 
   
   SQL> alter database open;
4. To confirm the change in protection mode, query v$database on the primary.
   SQL> select protection_mode, protection_level from v$database;
   
   PROTECTION_MODE : Protection mode set for the database
   PROTECTION_LEVEL : Actual, real time mode in affect for the database; this will change after outages/faults

 The above can be done via the Data Guard Broker if it is configured, using DGMGRL or the Data Guard GUI. Please see the Broker documentation for instructions.

REFERENCES

NOTE:219344.1  - Usage, Benefits and Limitations of Standby Redo Logs (SRL)
NOTE:232649.1  - Data Guard Gap Detection and Resolution
NOTE:240874.1  - 9i Data Guard Primary Site and Network Configuration Best Practices
NOTE:248382.1  - Creating a 10gr1 Data Guard Physical Standby on Linux
NOTE:255959.1  - Data Guard and Network Disconnects
NOTE:387174.1  - MAA - Data Guard Redo Transport and Network Best Practices
NOTE:800212.1  - How to upgrade the protection mode in Data Guard environment
NOTE:1537316.1  - Data Guard Gap Detection and Resolution Possibilities

TRANSPORT: Data Guard Protection Modes (文档 ID 239100.1)

Purpose

Scope

Details

MAXIMUM PROTECTION

MAXIMUM AVAILABILITY

MAXIMUM PERFORMANCE

HOW TO CHANGE THE PROTECTION MODE:

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 21-Sep-2012*** 

PURPOSE

A Data Guard configuration always runs in one of three data protection modes:

• MAXIMUM PROTECTION
• MAXIMUM AVAILABILITY
• MAXIMUM PERFORMANCE (default)

All three modes provide a high degree of data protection, but they differ in terms of the effect that each has on the availability and performance of the primary database.  This document attempts to provide clarification on how each work, their requirements, and how to change the protection mode of a Data Guard environment.

This document supplements the information provided in the Data Guard Concepts and Administration guide and is not a replacement.

SCOPE

This document is intended for Oracle Data Guard database administrators that would like to understand more about protection levels.

DETAILS

MAXIMUM PROTECTION 

This protection mode guarantees that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to a standby redo log on at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to at least one synchronized standby database.

To participate in MAXIMUM PROTECTION the following requirements must be met:

• Redo Archival Process : LGWR
• Network Transmission mode : SYNC
• Disk Write Option : AFFIRM
• Standby Redo Logs : Yes
• Standby Database Type :  Physical Only in 9i, Physical AND Logical in 10g

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of  twostandby 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.

MAXIMUM AVAILABILITY

This protection mode provides the highest level of data protection that is possible without affecting the availability of the primary database. Like maximum protection mode, 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. Unlike maximum protection mode, the primary database will not shut down if a fault prevents it from writing its redo stream to a synchronized standby database. Instead, the primary database will operate in RESYNCHRONIZATION until the fault is corrected and all log gaps have been resolved. When all log gaps have been resolved, the primary database automatically resumes operating in 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. 

To participate in MAXIMUM AVAILABILITY the following requirements must be met:

• Redo Archival Process : LGWR
• Network Transmission mode : SYNC
• Disk Write Option : AFFIRM
• Standby Redo Logs : Yes
• Standby Database Type : Physical and Logical

MAXIMUM PERFORMANCE

This protection mode provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data. 

When network links with sufficient bandwidth and latency are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance. 

To participate in maximum performance the following requirements must be met:

• Redo Archival Process : LGWR or ARCH
• Network Transmission mode : ASYNC when using LGWR only 
• Disk Write Option : NOAFFIRM
• Standby Redo Logs : No, but recommended and required for Real-Time Apply
• Standby Database Type : Physical and Logical

When using LGWR to remotely archive in ASYNC mode, the LGWR process does not wait for each network I/O to complete before proceeding. This behavior is made possible by the use of an intermediate process, known as a LGWR network server process (LNS), which performs the actual network I/O and waits for each network I/O to complete. In Oracle9iR2 and 10gR1, each LNS has a user configurable buffer that is used to accept outbound redo data from the LGWR. This is configured by specifying the size in 512 byte blocks on the ASYNC attribute in the archivelog destination parameter. For example ASYNC=2048 indicates a 1Mb buffer. As long as the LNS process is able to empty this buffer faster than the LGWR can fill it, the LGWR will never stall. If the LNS cannot keep up, then the buffer will become full and the LGWR will stall until either sufficient buffer space is freed up by a successful network transmission or a timeout occurs.  For further informaton on the buffer, see the Data Guard Concepts and Administration guide regarding LOG_ARCHIVE_DEST_N and the SYNC/ASYNC attributes.

In Oracle10gR2, this buffer is no longer in use as LNS will instead read from the online redo log itself on the primary when ASYNC is defined.

When remotely archiving using the ARCH attribute, redo logs are transmitted to the destination during an archival operation. The background archiver processes (ARCn) or a foreground archival operation serves as the redo log transport service. Using ARCH to remotely archive does not impact the primary database throughput as long as enough redo log groups exist so that the most recently used group can be archived before it must be reopened.

HOW TO CHANGE THE PROTECTION MODE:

If using Oracle9i and the primary is RAC enabled, downgrading and upgrading the protection level  must be done with only one instance mounting the controlfile and CLUSTER_DATABASE=FALSE.

1. Make sure the LOG_ARCHIVE_DEST_N settings on the primary are configured and the destination enabled to support the target protection mode as documented above. All parameters should be set as needed in the spfile or pfile. For the mode of MAXIMUM PROTECTION, the standby must be up and mounted. For purposes of example, LOG_ARCHIVE_DEST_2 will be used.
   SQL> alter system set log_archive_dest_2='service=STBY LGWR SYNC AFFIRM .......... '; 
   SQL> alter system set log_archive_dest_state_2=enable;
   If using Oracle10g or higher, attributes VALID_FOR and DB_UNIQUE_NAME should also be set as part of the LOG_ARCHIVE_DEST_n.
2. Skip this step if you are on Oracle 11.x or you want to downgrade the Protection Mode in Oracle 10.x
   Shut down the primary database and restart it in mounted mode. If the primary database is RAC enabled, shut down all of the instances and then start and mount a single instance.
   
   For example:
   SQL> shutdown immediate
   SQL> startup mount
3. Change the protection mode.  If step #2 was done, also open the database and restart the other RAC instances.
   
   SQL> alter database set standby database to maximize {AVAILABILITY | PERFORMANCE | PROTECTION}; 
   
   SQL> alter database open;
4. To confirm the change in protection mode, query v$database on the primary.
   SQL> select protection_mode, protection_level from v$database;
   
   PROTECTION_MODE : Protection mode set for the database
   PROTECTION_LEVEL : Actual, real time mode in affect for the database; this will change after outages/faults

 The above can be done via the Data Guard Broker if it is configured, using DGMGRL or the Data Guard GUI. Please see the Broker documentation for instructions.

REFERENCES

NOTE:219344.1  - Usage, Benefits and Limitations of Standby Redo Logs (SRL)
NOTE:232649.1  - Data Guard Gap Detection and Resolution
NOTE:240874.1  - 9i Data Guard Primary Site and Network Configuration Best Practices
NOTE:248382.1  - Creating a 10gr1 Data Guard Physical Standby on Linux
NOTE:255959.1  - Data Guard and Network Disconnects
NOTE:387174.1  - MAA - Data Guard Redo Transport and Network Best Practices
NOTE:800212.1  - How to upgrade the protection mode in Data Guard environment
NOTE:1537316.1  - Data Guard Gap Detection and Resolution Possibilities