Parameter to prevent license violation with Active Data Guard

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 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

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;
SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;


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 🙂


  1. #1 von Neeraj am Oktober 1, 2013 - 13:02

    Good peice of information .

    Thanks 🙂

  2. #2 von Mathias Zarick am Oktober 2, 2013 - 09:00

  3. #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. #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. #5 von Uwe Hesse am Oktober 8, 2013 - 21:11

    Cool: Oracle Magazine shared this post on Facebook:

  6. #6 von Amit Saraswat am Oktober 9, 2013 - 08:25

    Nice piece of information, Thanks Uwe !!

  7. #7 von Uwe Hesse am Oktober 11, 2013 - 09:34

    You’re welcome 🙂 Thanks for taking the time to leave a nice feedback!

  8. #8 von sshdba am November 14, 2013 - 08:34

    Reblogged this on Easy Oracle DBA.

  9. #9 von sshdba am November 14, 2013 - 08:35

    Uwe thanks. Excellent Post. I just reblogged it.

  10. #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. #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. #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. #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

    thank you again for your helping Uwe 🙂

  14. #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‘?


  15. #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. #16 von bugnvl am Januar 17, 2015 - 07:53

    Fantastic information.
    Thank you.

  17. #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 ;

    ——— ——————– —————-

    SQL> show parameter _query_on_physical

    ———————————— ———– ——————————
    _query_on_physical boolean FALSE


    Database altered.


    Database altered.


    Database altered.

    SQL> select name,open_mode, database_role from v$database ;

    ——— ——————– —————-

    SQL> show parameterm,mm

    ———————————— ———– ——————————
    _query_on_physical boolean FALSE

  18. #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.

  1. Preventing standby databases opening in Active DataGuard mode + chopt !!! « Oracle DBA – A lifelong learning experience

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..