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:
-
Both Primary and Standby databases running 19c.
-
Data Guard is in Maximum Availability.
-
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.