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:

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

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.

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

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

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

I set the “_query_on_physical”=false and restart:

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:

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:

It seems that the apply is stopped.

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

Here is the alert.log:

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:

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

But…

Nothing was opened…

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:

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

and the APPLY is running:

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.

https://twitter.com/FranckPachot Passionate about all databases. Oak Table member, Oracle ACE Director & OCM 12c. Other blog posts: http://blog.dbi.pachot.net

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store