When you happen to have a customer that want’s to use Data Guard on Oracle RAC without a license for Active Data Guard then you might want to read this post.
You probably have searched on the internet (just like I did) and already found this nice post from Uwe: http://uhesse.com/2013/10/01/parameter-to-prevent-license-violation-with-active-data-guard/
If you haven’t already then go ahead and read it, but come back when you also use RAC.
I had set-up Data Guard between two RAC databases with Data Guard being managed by the DG Broker.
Environment
Oracle 11.2.0.4.2 EE on Red Hat Linux 6.5 using ASM. Two RAC nodes in each cluster. A Primary and a Physical Standby with redo apply on.
Test scenario’s
- Crash node PRIMARY1 and get it running again.
- Crash node STANDBY1 and get it running again.
- Switchover from PRIMARY to STANDBY and back.
- Failover from PRIMARY to STANDBY and back.
Test 1 works like a charm, the services failover to node PRIMARY2 and bringing up node PRIMARY1 causes no problems.
Test 2 also works as designed. The apply process gets started on the other node. No problems there.
Tests 3 and 4 also seem to work as expected, but while taking a closer look it appears that the standby database is opened READ ONLY WITH APPLY!
That is NOT want I wanted. We cannot be using Active Data Guard! So I went looking for a solution and found the blog post mentioned above. Created a service request with Oracle Support to see if there are any issues I involved with this parameter but none were mentioned to me.
Trying to open the standby database now results in a : ORA-16669: instance cannot be opened because the Active Data Guard option is disabled. A bit rough but at least you won’t use a license option that we do not have.
Test again
Executing test1 again: Oeps! While trying to start the instance again I get:
ORA-01105: mount is incompatible with mounts by other instances ORA-03175: parameter _query_on_physical mismatch
The only way to get past this is to shutdown the other RAC instance as well and start them both together!
Thus: Setting the parameter _query_on_physical to false results in having to restart the whole database on an instance crash!
Digging Deeper
It appears that when we performed a switchover or a failover the startup parameters in clusterware weren’t adjusted by the broker. The primary database has a start_mode of OPEN, check with:
srvctl config database –d <dbname>
But the standby database should have a start_mode of MOUNT. That way the database is mounted when somebody uses the srvctl start database –d <dbname> command. Therefore when the primary database fails over to the standby database these start options should be adjusted by the broker but they aren’t! That results in the standby database having the start_mode of the former primary database, which is OPEN. And visa versa for the new primary database. The broker still opens the new primary database, and that is what we wanted (even when the start_mode is set to MOUNT), but the new standby database gets opened sometimes as well (READ ONLY WITH APPLY). And when you experience an cluster reboot (by a power outage) clusterware will restart with the specified start_mode, and thus open your database and activating Active Data Guard.
It appears that there is a patch for that issue: 15986647. So we needed to apply that. But check yourself if it isn’t already included in the patches you installed.
Conclusion
In our case setting the parameter _query_on_physical to false was not an option because of the side effect with an instance crash. It’s always good idea to test something yourself as well.
