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

,

  1. #1 by Neeraj on October 1, 2013 - 13:02

    Good peice of information .

    Thanks :)

  2. #2 by Mathias Zarick on October 2, 2013 - 09:00

  3. #3 by Uwe Hesse on October 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 by suvajit maity on October 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 by Uwe Hesse on October 8, 2013 - 21:11

    Cool: Oracle Magazine shared this post on Facebook: https://www.facebook.com/OracleMagazine/posts/10151936368291240

  6. #6 by Amit Saraswat on October 9, 2013 - 08:25

    Nice piece of information, Thanks Uwe !!

  7. #7 by Uwe Hesse on October 11, 2013 - 09:34

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

  8. #8 by sshdba on November 14, 2013 - 08:34

    Reblogged this on Easy Oracle DBA.

  9. #9 by sshdba on November 14, 2013 - 08:35

    Uwe thanks. Excellent Post. I just reblogged it.

  10. #10 by yasinyazici on 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 by Uwe Hesse on 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 by yasinyazici on 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 by yasinyazici on 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. #14 by Anil on December 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. #15 by Uwe Hesse on December 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.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,959 other followers

%d bloggers like this: