Enable Active DataGuard DML Redirection in Oracle 19c

Enable Active Data Guard 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.