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