Enable Active DataGuard DML Redirection in Oracle 19c

 What is Active DataGuard DML Redirection?

Active Dataguard DML Redirection feature present in Oracle 19c.

In this feature DML(insert/update/delete) operation can performed on Standby Database which redirected to Primary Database and executed on primary site then updated data will be apply back through redo logs to the Standby database.

Requirement for enable DML Redirection:

1. Both Primary and Standby databases running 19c.

2. Data Guard is in Maximum Availability.

3. Active Data Guard enabled(means standby is open in Readonly mode).

Enable the Active DataGuard DML Redirection

Need to set the Parameter ADG_REDIRECT_DML = TRUE on both primary and standby database.

Enable on Primary Server

[oracle@orcldb1 ~]$ sqlplus sys/password1@orclprim as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 22 11:10:11 2020
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.2.0.0.0
SQL> show parameter adg_redirect_dml

NAME                TYPE        VALUE
------------------- ----------- ------------
adg_redirect_dml    boolean     FALSE
SQL> alter system set adg_redirect_dml = true scope = both sid = '*';
System altered.

Enable on Standby Server

[oracle@orcldb2 ~]$ sqlplus sys/password1@orcldg as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 22 11:21:13 2020
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.2.0.0.0
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> show parameter adg_redirect_dml

NAME                  TYPE     VALUE
--------------------- -------- ------------
adg_redirect_dml      boolean  FALSE
SQL> alter system set adg_redirect_dml = true scope = both sid = '*';
System altered.

Note: You can also enable DML redirect at session level
alter session enable adg_redirect_dml;

Testing the Feature

–On Primary database

create table and insert data for testing purpose of DML redirection feature working in Dataguard environment.

SQL> CREATE TABLE test(FNAME varchar2(50), LNAME VARCHAR2(50));
Table created.
SQL> insert into test values ('RAM','SHARMA');
i row inserted.
SQL> commit;
Commit complete.
SQL> select * FROM test;

FNAME      LNAME
---------- ----------------
RAM        SHARMA

–On Standby databsae(connect with sqlplus username/password@tnsentry)

On standyby , delete the table test environment connect with user in which that table present.

SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> delete from test;
1 row deleted.
SQL> commit;
Commit complete.

–On Primary

Check the table row deleted on primary

SQL> select * FROM test;
no rows selected.