Disclaimer: I got informed that the mentioned underscore parameter is not reliable on every database version and that it never was intended for customer use. That is also why the quoted MOS note has been removed. I leave the article in spite, because I know it got referenced by many sites. And don’t mess with Larry Carpenter 😉
Although Real-Time Query is a great feature, it requires the Active Data Guard option to be licensed AND it is very easy to turn it on. That has been a concern for some customers I encountered. Now I realized that we have an undocumented parameter to prevent exactly that. Thanks to Marc, who mentioned the MOS Note 1436313.1 in a recent comment!
I am here connected to a Physical Standby database, running on 22.214.171.124 and don’t want to use Real-Time Query. The startup command would normally trigger the database to open READ ONLY, together with a Data Guard Broker configuration, Real-Time Query would be started. Not any more:
SQL> alter system set "_query_on_physical"=false scope=spfile; System altered. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2214936 bytes Variable Size 314573800 bytes Database Buffers 201326592 bytes Redo Buffers 3821568 bytes Database mounted. ORA-16669: instance cannot be opened because the Active Data Guard option is disabled SQL> host oerr ora 16669 16669, 00000, "instance cannot be opened because the Active Data Guard option is disabled" // *Cause: The attempt to open the instance failed because the Active Data // Guard option was not enabled and Redo Apply was either running // or was about to be started by the Data Guard broker. // *Action: Stop Redo Apply or set the database state to APPLY-OFF and then // open the database.
It is still possible to open the Physical Standby READ ONLY when the MRP background process is stopped:
DGMGRL> edit database physt set state=apply-off; Succeeded. SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY
Only drawback is that it is an undocumented parameter, so you should confirm with Oracle Support that it is okay to use it in your case.
Conclusion: There is an easy way to prevent license violation by accident with Real-Time Query. Don’t believe it, ask Oracle Support 🙂