Re: Overhead of Dataguard

Hey Jack

In regards to the different modes, they are documented here:

https://docs.oracle.com/database/121/SBYDB/protection.htm#SBYDB02000

But in a nutshell, maximum performance mode is the mode with the least impact on the primary database, but also the one offering the least protection. In this mode, the standby runs asynchronously, and data is committed on the standby whenever it arrives there.

Maximum protection on the other hand is the opposite and will shut down the primary if it can't reach the standby, to ensure that no data is lost. In this mode, the standby runs synchronously and the primary won't be able to commit the transaction until it has been committed on at least one standby as well. Normally you don't want to run this mode without having at least two standby databases.

Maximum availability runs in the same mode as max protection by default, but will automatically fall back to max performance if it can't reach the standby.

Aside from force logging (which is a must with Data Guard as Norman has already explained), the impact that synchronous commit has shouldn't be disregarded, as it vastly depends on your network infrastructure (particularly latency if e.g. the primary and standby are in different data centers) and can easily cripple your primary database's throughput.

Which mode to choose is usually decided by the business requirement that drives the need for a standby - be it for example with a primary focus to avoid data loss as much as possible, or to just have a failover database in place, in case the primary fails. It depends.

In extreme cases, you can also leave the primary database in "specific forced logging" mode. Meaning that you have the OLTP-focused data in force logging tablespaces, but not the batch-focused tablespaces. But you will then have to synchronize the standby each morning, using an incremental backup. I am assuming if you are using nologging, that you are already triggering an incremental after the batch completes. But, needless to say, a setup like this can be dangerous and needs greater care and particularly education, if multiple team members are working on said database. All it takes is for one guy to create a tablespace without force logging enabled on it, and you're up for data loss (at some random point months or years later down the road).

Stefan

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle

--
http://www.freelists.org/webpage/oracle-l