Highlighted
zhaos2
3 Silver

Working Mode for Standby Database

If I currently have a production database running in ARCHIVELOG mode which will prevent any data loss at any point of time, and I am going to setup a standby database, what mode (archive/non-archive) should I run on the standby database so that I can switch from production to standby whenever it is required?

Tags (3)
0 Kudos
2 Replies
LouisLu
2 Iron

Re: Working Mode for Standby Database

Generally speaking, a production database by definition will run in ARCHIVELOG mode -- regardless of

whether you are using standby or not.  If you do not run in this mode, you must be willing to LOSE DATA at some point in time.

For other side, a standby database requires archivelog mode too. 

All production machines must run in archive log mode else they will lose committed transactions the instant a disk fails.

0 Kudos
zhaos2
3 Silver

Re: Working Mode for Standby Database

Please refer to the below link for the comprehensive description about Data Protection Modes for Primary and Standby database in Data Guard:

http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#i1178539

Maximum Protection Mode

This protection mode ensures 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 the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to at least one remote standby redo log. For multiple-instance RAC databases, Data Guard shuts down the primary database if it is unable to write the redo records to at least one properly configured database instance. The maximum protection mode requires that at least one standby instance has a standby redo log and the LGWR, SYNC, and AFFIRM attributes be used on the LOG_ARCHIVE_DEST_n parameter for this destination.

Maximum Availability Mode

This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to at least one remote standby redo log. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected and all gaps in redo log files are resolved. When all gaps are 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.

Like maximum protection mode, the maximum availability mode requires that you:

  • Configure standby redo log files on at least one standby database.
  • Set the SYNC, LGWR, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least 1 standby database.

Maximum Performance Mode

This protection mode (the default) 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 online 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 are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.

The maximum performance mode enables you to either set the LGWR and ASYNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination. If the primary database fails, you can reduce the amount of data that is not received on the standby destination by setting the LGWR and ASYNC attributes.

0 Kudos