Active Data Guard and DML Redirection

 Active Data Guard (also known as "ADG") allows you to open a Standby Database in Read Only mode and query it while Media Recovery (i.e. Redo Apply from the Primary) is concurrently running.

Caveat : ADG requires purchase of additional licences to use this feature on a Standby Database.

If you issue the commands from sqlplus and not from dgmrl, you must first stop Media Recovery before you OPEN the database and then re-enable it on the Standby :

SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
  2  from v$database;

FORCE_LOGGING                           FLASHBACK_ON       DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES                                     NO                 PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
MOUNTED


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
  2  from v$database;

FORCE_LOGGING                           FLASHBACK_ON       DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES                                     NO                 PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
READ ONLY


SQL>
SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Thereafter, you would be able to query the Standby Database.  DML (INSERT, UPDATE, DELETE) and DDL (CREATE, DROP, ALTER etc) are *not* allowed by default.  

19c does have a DML Redirection feature ADG_REDIRECT_DML which I demonstrate below :

SQL> connect hemant/hemant@stdbypdb1
Connected.
SQL> select * from x;

D
-
X

SQL> delete x;
delete x
       *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

SQL> delete x;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

The actual execution of the DML will take some time as Oracle has to actually push it to the Primary database for execution and then wait for the Redo Apply to replicate back to the Standby.

PLSQL also can be executed with ADG_REDIRECT_PLSQL     which I am not demonstrating here.

Note :  According to Oracle's documentation the DML Redirect feature is for "read-mostly applications, which occasionally execute DMLs, on the standby database."    .  I wouldn't advise trying this for all sessions at the Instance ("alter system") level but only to be used occasionally at session level.