Search

Top 60 Oracle Blogs

Recent comments

OGB Appreciation Day : “_query_on_physical” (again)

OGB Appreciation Day : “_query_on_physical” (again)

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

(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

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 read-only - Blog dbi services

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

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 configuration
Configuration - cdb1
Protection Mode: MaxAvailability
Members:
cdb1a - Primary database
cdb1b - (*) Physical standby database
Fast-Start Failover: Enabled in Observe-Only Mode
Configuration Status:
SUCCESS (status updated 47 seconds ago)
DGMGRL> show database cdb1b
Database - cdb1b
Role:               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):
CDB1B
Database 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 ONLY
SQL> show pdbs
CON_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 ONLY
SQL>
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.0
Copyright (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 configuration
Configuration - cdb1
Protection Mode: MaxAvailability
Members:
cdb1a - Primary database
cdb1b - (*) Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member
Fast-Start Failover: Enabled in Observe-Only Mode
Configuration Status:
ERROR (status updated 47 seconds ago)
DGMGRL> show database cdb1b
Database - cdb1b
Role:               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):
CDB1B
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16854: apply lag could not be determined
Database 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 pdbs
CON_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 pdbs
CON_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 - cdb1
Protection 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 Mode
Configuration 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.