I've never used any Data Guard protection mode except Maximum Performance before so please forgive me if my questions seem ignorant.
I have a primary DB with 2 standbys both currently operating in maximum performance mode. The primary is a 2 node RAC cluster. Both standbys are single instance. None of the databases in the configuration use standby logs (yet). I am currently using ARCH to ship the logs to both standbys. All databases are running version 11.2. I am not using Data Guard broker.
I am considering upgrading just standby # 2 to maximum availability protection mode, but I'm not sure if it's really what I want or not. Perhaps someone here with a better understanding can confirm for me whether its the best choice.
My goal is to achieve the maximum data protection possible with no performance impact whatsoever on the primary database.
If I understand maximum availability correctly (and I am not certain I do), a commit issued on the primary will not complete until the maximum availability standby has acknowledged receipt of the redo data. If the link between the two goes down, that could introduce a significant delay in the commit. Is that correct? In my situation any performance impact on the primary DB is unacceptable.
Perhaps what I want is maximum performance using standby logs, with the LGWR and ASYNC attributes enabled on the corresponding log_archive_dest_n parameter. If I understand that correctly, a commit on the primary will complete as soon as the redo is written to the local redo log and the availability of the standby destination will have no performance impact at all. In general the standby will remain only a few seconds behind the primary. If the link between the two goes down, I can alert myself to the situation with a simple query of v$dataguard_stats and correct whatever the underlying problem is.
Can someone please confirm for me whether I am understanding the behavior of commits under these two different protection modes and transport modes?
Also, if I do create standby logs for the one destination, do I also need to create them on the primary and other standby as well.
In Maximum Availability 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.
I would always create standby logs for every database. Otherwise you are not really prepared in the event of a switchover.
This short document might help :
Here's what I can think of :
Max protection mode will shutdown automatically if no standby destinations available for log transfer. To avoid this at least two log_archive_dest are needed.
Max protection mode will shutdown if lgwr,async and noaffirm is used.
Max Availability will behave like max performance mode when no standby archive destinations available.
Max Availability will remain in max availability mode when standby redo logs will not available.
So it will depend on the business
Sorry I meant to say what advantage does Max availability have over Max performance with async/lgwr in my case? Especially given the fact that my main concern is having no performance impact on the primary.
The 2 higher protection modes will have a performance impact on the primary. You cannot avoid that. You can only try to keep that impact small.
That is the price you pay for zero data loss in the event of a failover.
See here for details:
I think the protection mode I probably want is maximum performance with real time apply on the standby. I've been running in that mode for a few days now and haven't seen the apply lag go above 2 seconds. That's well within my tolerance for data loss.