OGB Appreciation Day : “_query_on_physical” (again)

6 min readOct 10, 2019

Looks like we are on the #ThanksOGB day.

One place where the Oracle Community is great is when it helps users with the technology, far from the commercial considerations. We all know that it can be very easy to use some features that are not covered by the license we bought, and this can cost a lot in case of an LMS audit. Here is a post about trying to avoid to activate Active Data Guard option by mistake, as there were many attempts to find a solution in the community.

Originally, Data Guard was a passive standby, to reduce RTO and RPO in case of Disaster Recovery. We were able to open it for queries, but then it was not syncing anymore. Then came many features that allow doing a lot more on the standby. But those were subject to an additional cost option called Active Data Guard. One of the major features is the ability to continue the APPLY while the standby is opened READ-ONLY: executing real-time queries, still in fully ACID consistent mode. And because the developers do not think about those who do not have the option, and the sales do not really care about this, the default when doing a “startup” on a standby database was to OPEN it READ ONLY. Then here is what happens: the broker starts the log APPLY and the database is flagged (in the primary) as using Active Data Guard.

So, the Oracle community came with some ideas to prevent this. Unfortunately mostly unsupported…

Mathias Zarick came with the idea to ALTER DATABASE CLOSE in an AFTER STARTUP ON DATABASE to avoid the automatic open:

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

Hi there, Real Time Query is a very nice feature of 11g, it allows you to open you physical standby read only and to…

web.archive.org

(I linked the archive.org here because at the time of writing the Trivadis blog it seems that the Trivadis blog does not exist anymore)

Then Uwe Hesse has mentioned a parameter which looks exactly like what we need: “_query_on_physical”

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…

uhesse.com

But there are many mentions that it is not recommended and not supported. It goes further than “undocumented”: the non-recommendation is itself well-documented (MOS note 2269239.1)

The real interesting supported thing is that, since 18c, we can now open the CDB and the Active Data Guard usage is not enabled as long as the user PDBs stay in mount.

18c: No Active Data Guard required (and detected) when only CDB$ROOT and PDB$SEED are opened in…

By Franck Pachot . When you clone a PDB on the primary CDB, you want that the same is automatically done on the standby…

blog.dbi-services.com

Here, the best is using Grid infrastructure or Oracle Restart to open the services (and then the PDB) correctly depending on the role.

For non-CDB, try to use SQLcl to run the startup, as this one does it in two steps (MOUNT+OPEN):

No risk to activate Active Data Guard by mistake with SQL Developer SQLcl

If you have a Data Guard configuration without the Active Data Guard license, you can:

medium.com

Ok, what is new then? Here is a small test to show how “_query_on_physical” does not help anymore.

Connected as SYSDG.
DGMGRL> show configurationConfiguration - cdb1Protection Mode: MaxAvailability
  Members:
  cdb1a - Primary database
    cdb1b - (*) Physical standby databaseFast-Start Failover: Enabled in Observe-Only ModeConfiguration Status:
SUCCESS   (status updated 47 seconds ago)DGMGRL> show database cdb1bDatabase - cdb1bRole:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 7.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    CDB1BDatabase Status:
SUCCESS

this is an “Active Data Guard” configuration as mentioned by “Real Time Query: ON”.

I set the “_query_on_physical”=false and restart:

SQL> alter system set "_query_on_physical"=false scope=spfile;System altered.SQL> startup force
ORACLE instance started.Total System Global Area 4.0668E+10 bytes
Fixed Size                 30386032 bytes
Variable Size            5100273664 bytes
Database Buffers         3.5433E+10 bytes
Redo Buffers              103829504 bytes
Database mounted.
Database opened.SQL> select open_mode from v$database;OPEN_MODE
--------------------
READ ONLYSQL> show pdbsCON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

So far so good, no Active Data Guard is in use here. Even better, if I try to open an PDB by mistake, it is not possible:

SQL> alter pluggable database all open;
alter pluggable database all open
*
ERROR at line 1:
ORA-10887: An Oracle Active Data Guard license is required to open a pluggable database while standby recovery is applying changes.SQL> alter pluggable database all open read only;
alter pluggable database all open read only
*
ERROR at line 1:
ORA-10887: An Oracle Active Data Guard license is required to open a pluggable
database while standby recovery is applying changes.
SQL>
SQL> select open_mode from v$database;OPEN_MODE
--------------------
READ ONLYSQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterpris

Now looking at the broker, “Intended State: APPLY-ON” and “Real Time Query: OFF” is the right configuration when you don’t have Active Data Guard and want the standby to be synchronized.

But:

[oracle@db193 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Aug 1 06:28:02 2019
Version 19.4.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.Welcome to DGMGRL, type "help" for information.
Connected to "CDB1B"
Connected as SYSDG.
DGMGRL> show configurationConfiguration - cdb1Protection Mode: MaxAvailability
  Members:
  cdb1a - Primary database
    cdb1b - (*) Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the memberFast-Start Failover: Enabled in Observe-Only ModeConfiguration Status:
ERROR   (status updated 47 seconds ago)DGMGRL> show database cdb1bDatabase - cdb1bRole:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    CDB1BDatabase Error(s):
    ORA-16766: Redo Apply is stoppedDatabase Warning(s):
    ORA-16854: apply lag could not be determinedDatabase Status:
ERROR

It seems that the apply is stopped.

And if I want to start it I get an error:

DGMGRL> edit database cdb1b set state=apply-on;Error: ORA-16773: cannot start Redo Apply
Failed.

Here is the alert.log:

2019-10-09T13:46:01.619883+00:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2019-10-09T13:46:01.641127+00:00
Errors in file /u01/app/oracle/diag/rdbms/cdb1b/CDB1B/trace/CDB1B_rsm0_20946.trc:
ORA-16136: Managed Standby Recovery not active
ORA-16136 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL...
2019-10-09T13:47:57.602491+00:00
RSM0: Active Data Guard Option is not enabled, Redo Apply Services cannot be started on an open database

Ok, so this is not what I wanted: not opened and no apply. Probably because this underscore parameter is not supported, it is not aware that we can have the CDB opened even without the option.

This is far too strict as now the APPLY is off and I cannot open the PDBs:

SQL> alter pluggable database all open read only;
alter pluggable database all open read only
*
ERROR at line 1:
ORA-10887: An Oracle Active Data Guard license is required to open a pluggable
database while standby recovery is applying changes.

only when I stop the broker I can issue an OPEN:

SQL> alter system set dg_broker_start=false scope=memory;System altered.SQL> alter pluggable database all open read only;Pluggable database altered.

But…

SQL> show pdbsCON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

Nothing was opened…

SQL> alter pluggable database all open read only;
alter pluggable database all open read only
*
ERROR at line 1:
ORA-10887: An Oracle Active Data Guard license is required to open a pluggable database while standby recovery is applying changes.

But… it is not applying changes here.

Ok, now cleaning up this ugly parameter, starting manually and ensuring that all PDBS are closed before the broker starts:

alter system reset "_query_on_physical";
shutdown immediate;startup mount;
alter system set dg_broker_start=false scope=memory;
alter database open read only;
alter pluggable database all close;
alter system set dg_broker_start=true scope=memory;

Everything is ok now. No PDB is opened in the standby:

SQL> show pdbsCON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

and the APPLY is running:

DGMGRL> show configuration lag;Configuration - cdb1Protection Mode: MaxAvailability
  Members:
  cdb1a - Primary database
    cdb1b - (*) Physical standby database
            Transport Lag:      0 seconds (computed 1 second ago)
            Apply Lag:          0 seconds (computed 1 second ago)Fast-Start Failover: Enabled in Zero Data Loss ModeConfiguration Status:
SUCCESS   (status updated 20 seconds ago)

In summary: do not use this “_query_on_physical” parameter. Just be careful when opening pluggable databases and you are in a standby CDB. And if you are not (yet) in CDB, be careful with the “startup” command: use SQlcl or do the same (startup mount + alter database open read write) in two commands.