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 11.2.0.1 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 🙂
Pickleball spielen 
#1 von Neeraj am Oktober 1, 2013 - 13:02
Good peice of information .
Thanks 🙂
#2 von Mathias Zarick am Oktober 2, 2013 - 09:00
Cool, so seems that I don’t need this trigger http://blog.trivadis.com/b/mathiaszarick/archive/2012/09/07/active-data-guard-s-real-time-query-avoid-usage-if-not-licensed.aspx
any more.
Thanx
#3 von Uwe Hesse am Oktober 2, 2013 - 11:01
Matthias, yes I would prefer the parameter over an after startup on database trigger that brings down the database with an undocumented command. Even when that parameter itself is undocumented also 😉
#4 von suvajit maity am Oktober 4, 2013 - 20:09
Mnay thanks for this small but million of dollars deal. We all know how oracle charges for license violations.
#5 von Uwe Hesse am Oktober 8, 2013 - 21:11
Cool: Oracle Magazine shared this post on Facebook: https://www.facebook.com/OracleMagazine/posts/10151936368291240
#6 von Amit Saraswat am Oktober 9, 2013 - 08:25
Nice piece of information, Thanks Uwe !!
#7 von Uwe Hesse am Oktober 11, 2013 - 09:34
You’re welcome 🙂 Thanks for taking the time to leave a nice feedback!
#8 von sshdba am November 14, 2013 - 08:34
Reblogged this on Easy Oracle DBA.
#9 von sshdba am November 14, 2013 - 08:35
Uwe thanks. Excellent Post. I just reblogged it.
#10 von yasinyazici am November 4, 2014 - 16:23
Hi Uwe
I set _query_on_physical=false on the standby database and restart database with startup command . I am not getting any error and database has opened read only.Do you have any idea?
#11 von Uwe Hesse am November 5, 2014 - 17:49
Could it be that you are not using the broker, so that Real-Time Query does not start automatically? Or maybe you have turned it off with
edit database set state=apply-off Then Read-Only opening is of course possible.
#12 von yasinyazici am November 5, 2014 - 22:29
Hımm yes broker has not been configured.Already When I start log applying later I seen that database has been taken mount mode automatically.
Thank you
#13 von yasinyazici am November 6, 2014 - 09:36
also when I enable broker and restart standby database I seen that above mentioned error
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 956303352 bytes
Database Buffers 687865856 bytes
Redo Buffers 7135232 bytes
Database mounted.
ORA-16669: instance cannot be opened because the Active Data Guard option is
disabled
thank you again for your helping Uwe 🙂
#14 von Anil am Dezember 11, 2014 - 18:03
Hi Uwe,
Just want to check with you, how we can put the standby back to managed recovery mode after we open it in READ-ONLY ode. Just use sql commands to mount the standby database and do dgmgrl > edit database dbname set state=’APPLY-ON‘?
Thanks,
Anil
#15 von Uwe Hesse am Dezember 12, 2014 - 16:15
Anil, it is even enough to shutdown the standby and bring ot up to mount again. The DG Broker will start the Redo-Apply automatically then.
#16 von bugnvl am Januar 17, 2015 - 07:53
Fantastic information.
Thank you.
#17 von Jay vardhan am April 24, 2016 - 09:40
Hi Uwe,
Even using the parameter _query_on_physical, we can convert the database snapshot standby database and probably that need the license . Kindly review below test performed on 12c database and suggest .
SQL> select name,open_mode, database_role from v$database ;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
TESTP MOUNTED PHYSICAL STANDBY
SQL> show parameter _query_on_physical
NAME TYPE VALUE
———————————— ———– ——————————
_query_on_physical boolean FALSE
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select name,open_mode, database_role from v$database ;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
TESTP READ WRITE SNAPSHOT STANDBY
SQL> show parameterm,mm
NAME TYPE VALUE
———————————— ———– ——————————
_query_on_physical boolean FALSE
SQL>
#18 von Uwe Hesse am April 27, 2016 - 09:09
Jay vardhan, Snapshot Standby does not require Active Data Guard to be licensed. So the parameter is not relevant for that feature – besides of being unsupported.