How to configure max avail mode in Dataguard?

We are implementing Dataguard, and we have set the log_archive_dest_2 to ...LGWR SYNC AFFIRM, which is the setting for Maximum Availabilty, but this is also the setting for Maximum Protection. When we set these parameters, and we have a network problem, the Primary database will hang, we have to set the log_archive_dest_2 back to ...LGWR ASYNC AFFIRM, for the database to clear. I am sure maximum availablity mode should revert to Maximum performance mode, if we get network problems. Why is the primary database hanging? When I check the protection_level on v$database, it says 'Maximum performance. Do I need to set the database to maximum availability? and if so, do I change the protection level on the standby or primary database. Thanks.

Your primary database will shutdown if you setup Maximum Protection Mode and the system is not able to write ReDo to at least one remote location; it shouldn't hang, it should shutdown.

It has to shut down becasue of Maximum Protection Mode means ReDo has to be written to both local and remote sites at commit time. Maximum Availability Mode is like Maximum Protection Mode without this extra protection feature.

If you find the same ReDo issue -inability to write to at least one remote site- and your setup is for Maximum Availability Mode database would automatically switch to Maximum Performance Mode until the issue gets solved then it will resume on Maximum Availability Mode.

In none of these scenarios database should hang.

Thanks for the info. We chose Max availability as we don't want the database to 'shutdown', but our database actually hung until we set the log_archive_dest_2 to LGWR ASYNC NOAFFIRM, and it then went into maximum performance mode, it didn't do it automatically.

The question I need answered is "do I need to : 'alter database set standby to maximize availabilty;' on the primary database" to disguish between max prot and max availabilty. I am a bit confused, to set max prot and max avail mode, you need to set the same parameters within log_archive_dest_2 i.e. LGWR SYNC AFFIRM. How does the database differentiate between the protection levels? Thanks.

Answer: Yes, you do.

Since you are upgrading from Maximum Performance to Maximum Availability...

shutdown immediate; --<< Primary database
startup mount;
alter database set standby to maximize availabilty;
alter database open;

Oracle documentation is pretty clear on this matter, I'm encouraging you to check Oracle documentation in regards to your specific Oracle version and architecture -it's slightly different for RAC.

Thanks for the info.

I have researched futher on Dataguard, and apparently if you don't set the net_timeout within the log_archive_dest_2, and the primary database looses network connection to the standby database, it can 'hang' whilst waiting for the network to timeout, which can take between 15 mins to 2 hours, depending on the TCP Timeout setting. Rgds. Sheryl