Preventing standby databases opening in Active DataGuard mode

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

At this stage we can access all objects from the standby database without any issues. This is Active Data Guard in action.

 Alert log:

...

Physical standby database opened for read only access.
Completed: alter database open
Tue Oct 02 08:30:46 2012
Data Guard: Database open completed; restarting redo-apply ...
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (emrepsby)
Tue Oct 02 08:30:46 2012
MRP0 started with pid=35, OS id=20294
MRP0: Background Managed Standby Recovery process started (emrepsby)
 started logmerger process
Tue Oct 02 08:30:51 2012
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
...

There is the oracle hidden parameter “_Query_on_physical”. When the physical standby database is configured, this parameter is not set and the normal behaviour is expected. However when the parameter is applied to the standby database, we see the following changes;

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

System altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16669: instance cannot be opened because the Active Data Guard option is
disabled

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED
...

alter database open
Data Guard Broker initializing...
Data Guard: Active Data Guard is not enabled, database open not allowed
ORA-16669 signalled during: alter database open...

...