Active Data Guard DML Redirection Feature in 19c

 

In this post we will see how we can we DML Redirection feature in Oracle 19c Active Data Guard Standby.

Primary Source Environment setup

RAC Database : RENODBPR ( renodbpr1 & renodbpr2)
PDB                        : ONEPDB
GRID Home : /u01/app/19.3.0.0/grid
RDBMS Home    : /u01/app/oracle/product/19.3.0.0/db_1
Version         : Oracle Database 19c EE - Production Version 19.17.0.0.0
hosts : labhost01
                          labhost02

Standby database environment setup

RAC Database : RENODBDR ( renodbdr1 & renodbdr2)
GRID Home : /u01/app/19.3.0.0/grid
RDBMS Home    : /u01/app/oracle/product/19.3.0.0/dbhome_1
Version         : Oracle Database 19c EE - Production Version 19.17.0.0.0
hosts : labdrhost01
                          labdrhost02

What is DML redirection feature ?
DML Redirection is new feature in Oracle 19c which allows DML operations to be executed on an Active Data Guard standby database.

How it works :  When a user performs a DML operation in the Active Standby database, the DML operation is actually passed to the Primary database where it is executed and the redo generated from that particular DML operation will be shipped and applied in the Standby database and after that the control is returned to the user who ran the command.

Lets see how it works.

We have this schema, appuser in the pluggable database, onepdb.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONEPDB                         READ WRITE YES
SQL>

we have a table with name, APP_TAB1 in the pdb, one_pdb.

On Primary database:

On Standby database

without  enabling DML redirection feature, when we run insert operation in the standby database, it will error out as expected.

Enable DML redirection at session level  by using  below command 

 alter session enable adg_redirect_dml; 
and retry the insert operation.

Even though it works fine, this might not helpful for all the application as internally all the DMLs are being redirected to Primary database where its getting executed and the Standby database is getting updated with the redo data that it receives from the primary database for that DML transaction.

May be it will be useful for applications who perform mostly the read operations and very minimum write operations.

Thanks

Sambaiah Sammeta