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:

  • apply the redo to keep the physical standby synchronized
  • or open the database read-only to query it

but not at the same time.

Risk with sqlplus “startup”

Being opened READ ONLY WITH APPLY requires the Active Data Guard option. But that this may happen by mistake. For example, in sqlplus you just type “startup”, instead of “startup mount”. The standby database is opened read-only. Then the Data Guard broker (with state APPLY-ON) starts MRP and the primary database records that you are using Active Data Guard. And then DBA_FEATURE_USAGE_STATISTICS flags the usage of: “Active Data Guard — Real-Time Query on Physical Standby”. And the LMS auditors will count the option.

The ways to prevent it are unsupported:

alter system set "_query_on_physical"=false scope=spfile;

No problem with SQLcl

I’m pretty careful when I work on production databases but not when I’m on a lab as, there, errors are a nice way to learn new things. On a sandbox database on the Oracle Cloud ( https://medium.com/@FranckPachot/oracle-19c-data-guard-sandbox-created-by-dbca-createduplicatedb-88aab20ea0ab) I restarted the standby with a quick “startup force” and had the nice surprise to see the startup stopping in mount state. Look:

Image for post
Image for post
ORA-16003: standby database is restricted to read-only access
ORACLE_SID=CDB1B sql / as sysdbaSQLcl: Release 19.1 Production on Tue Jul 16 18:15:34 2019Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup
ORACLE instance started.
ORA-16003: standby database is restricted to read-only access

The message “ORA-16003: standby database is restricted to read-only access” is not new. This is what we have when we try a “alter database open read write;” on a standby database.

Actually, I started writing this blog thinking it was a new feature in 19c. And only when re-reading the paragraph above I realized that I was using SQLcl and maybe it has implemented the startup in two times: “startup mount” + “alter database open read write”. That is one of the reasons I try to write a blog post for each thing I discover. When the goal is to publish it, I spend more time thinking about the reasons, the context, the questions that can arise…

If you want to avoid to risk to activate Active Data Guard inadvertently, forget about sqlplus and use SQLcl. The “startup” command will stop in the mount state for a standby database. You need to type “startup open read only” to explicitly open it. Or issue an “alter database open;” after the “startup” returned ORA-16003.

Update 17-JUL-2019

Note that in multitenant, since 18c, the Active Data Guard is activated only where a pluggable database is opened. Then there’s no risk to open CDB$ROOT read-only and it is even recommended to have a correct replication when cloning PDBs. This means that with SQLcl you should “startup open read only” rather than simply “startup”. More info about it:

Written by

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