Active Data Guard's Real Time Query - avoid usage if not licensed

Mathias Zarick's Blog

Mathias is blogging here about Oracle and High Availability.

Active Data Guard's Real Time Query - avoid usage if not licensed

Rate This
  • Comments 5

Hi there,

Real Time Query is a very nice feature of 11g, it allows you to open you physical standby read only and to turn on media recovery concurrently. This means the data within the standby is always close to near real time. Real Time Query is part of the Active Data Guard option of Oracle Database which needs to be licensed separately.

If you need this feature, than of course you will pay the license and are happy with it. But sometimes you will not need it, so will not pay for it, so you should also assure that you do not use it. Well. Probably you know, that the feature can be activated by mistake very easy. For example if you start your standby database instance with a simple "startup" from sqlplus and the broker is activated it will turn on recovery right after the database has opened. Angry

So the right way would be "startup mount". But do you always think about this? And what about your scripts? Do they consider this? When your database runs on windows the, start of the instance is very likely be controlled by an automatic service start, right? And this will be a normal "startup", so you can end up with the licensing need of Active Data Guard also if you do not really need the feature.

A way to avoid wrong open would be usage of Clusterware or Oracle Restart, include the database as a cluster resource and set the right start mode (-s option of "srvctl add database" and "srvctl modify database"). The problem here is that database roles can change and you might forget to adapt the cluster resources after a switchover or a failover. Furthermore failover will happen automatically in case of usage of Fast Start Failover, so in this case nobody would control the correct start of database instances and nobody would change anything within the cluster resource definitions.

So the question is how to get safe here, I do not have a license, I do not need the feature, I want to disable it. Is this possible?
And the answer is: Yes I found a reliable way avoiding the usage of Real Time Query.

Connected as SYS create an after startup on database trigger like this:

CREATE OR REPLACE TRIGGER no_adg_trigger
AFTER STARTUP ON DATABASE
DECLARE
  v_sql v$sql.sql_text%TYPE;
BEGIN
  IF sys_context('userenv','database_role') = 'PHYSICAL STANDBY'  THEN
    SELECT sql_text INTO v_sql
    FROM v$sql sq, v$session se
    WHERE sq.sql_id = se.sql_id
    AND se.sid =  sys_context('userenv','sid');
    IF v_sql NOT LIKE '%FORCE%' THEN
      EXECUTE IMMEDIATE
        '/* do not open as we do not have adg licensed */ ALTER DATABASE CLOSE';

      /* restart DMON */
      EXECUTE IMMEDIATE('ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=MEMORY');
      EXECUTE IMMEDIATE('ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=MEMORY');
    END IF;
  END IF;
END;
/

This trigger will always fire, when the database is opening, and it will execute an "ALTER DATABASE CLOSE" (which is actually undocumented) in case we are on a standby, which will cause that the database does not really make it to the open state. It stays in mount.
We have achieved what we want. Smile

Why did I include this DMON restart?
Well - without this restart, sometimes I experienced following errors during instance start:

Fri Sep 07 11:12:56 2012
Archived Log entry 87 added for thread 1 sequence 366 ID 0xd38d0783 dest 1:
MRP0: Background Media Recovery terminated with error 1155
Errors in file /u01/app/oracle/diag/rdbms/lodur_site2/LODUR/trace/LODUR_mrp0_28085.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
MRP0: Background Media Recovery process shutdown (LODUR)

The managed recovery process did not start in some cases due to the concurrently running command for closure. This results in an inaccurate configuration, apply is stopped:
broker will show up like this:

DGMGRL> show configuration;

Configuration - LODUR

  Protection Mode: MaxAvailability
  Databases:
    LODUR_SITE1 - Primary database
    LODUR_SITE2 - Physical standby database
      Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

Stopping and starting the DMON right after the close command will assure that managed recovery will be active after startup (if this is the intended state).

Also, you might ask, why do we have this "v_sql NOT LIKE '%FORCE%'" condition?
Well it is for the DBA, who knows what he does, to be able to open the database instance, if he or she wants that with a controlled command.
"ALTER DATABASE OPEN /* FORCE */" would open the database.

Without Active Data Guard license it is still allowed to open the standby read only, but in this case you need to stop the managed recovery first.

The steps for doing this would be like this:
DGMGRL> edit database 'LODUR_SITE2' set state=apply-off;
SQL> ALTER DATABASE OPEN /* FORCE */;

To close the database and start the recovery again restart the database instance (normal usage of "startup" is allowed now, "startup mount" is also okay) and switch on the recovery afterwards:
DGMGRL> edit database 'LODUR_SITE2' set state=apply-on;

One last thing to mention: When you startup the standby database instance regularly with "startup" you will still see following output at the end:

Database mounted.
Database opened.

But the database is not open, as you can see with this simple query:

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

Have a nice day, Cheers Mathias

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post
  • Great. It works fine. Thank you, Mathias

  • Update: today I noticed an interesting parameter, posted by Uwe Hesse, that seems to do the same work:

    uhesse.com/.../parameter-to-prevent-license-violation-with-active-data-guard

  • If you are using broker you could always switch it off in config for database as shown by Uwe in previously linked article and Gavin Soorma  in this one - Both great DBA's

    gavinsoorma.com/.../11g-active-data-guard-enabling-real-time-query

  • Hi Mathias,

    Great work.

    I have a question for you. Could we remove the usage if active Data guard option is accidentally used? I can see one option is to recreate the database and do an export/import to remove the usage. Any other option you know of?

    Thanks

    Runzhuo

  • Hi Runzhuo,

    you can restart your standby to mount mode to disable real time query. The usage is visible through alert log (also traces) on standby. And it might be visible in view dba_feature_usage_statistics on primary.

    Cheers Mathias

Page 1 of 1 (5 items)
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post