DataGuard DML Redirection(Oracle 19c and 18c)

Up until Oracle version 18c, performing DML operations in an Active Data Guard (ADG) environment was only possible on global temporary tables. Executing DML statements on system or application tables was not allowed — except when the database was in snapshot standby mode.

In Oracle 18c, with the introduction of two hidden parameters, namely _enable_proxy_adg_redirect and _ADG_REDIRECT_FLAGS, it became possible to run DML statements in a Data Guard environment. See the example below:

–PRIMARY:

sqlplus usef/a

SQL*Plus: Release 18.0.0.0.0 – Production on Thu Farvardin 15 13:54:30 1398

Version 18.3.0.0.0

SQL> create table usef.tbl18c(id number,tarikh  date);

Table created.

–ADG:

SQL> alter system set “_enable_proxy_adg_redirect“=true;

System altered.

SQL> alter system set “_ADG_REDIRECT_FLAGS“=1;

System altered.

SQL> conn usef/a

Connected.

SQL> insert into tbl18c values(1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select id,to_char(tarikh,’YYYY/MM/DD HH24:mi:ss’,’nls_calendar=persian’) tarikh  from tbl18c;

        ID TARIKH

———- ——————-

         1 1398/01/15 13:28:17

–PRIMARY:

SQL> select id,to_char(tarikh,’YYYY/MM/DD HH24:mi:ss’,’nls_calendar=persian’) tarikh  from tbl18c;

        ID TARIKH

———- ——————-

         1 1398/01/15 13:28:17

As shown above, by setting these hidden parameters, running DML statements in Data Guard on Oracle 18c was possible — but only unofficially, using hidden parameters.

With Oracle 19c, a new feature called ADG REDIRECT DML was introduced. Unlike in 18c, this functionality is now officially supported, enabling DML operations in Active Data Guard without requiring hidden parameters.

Demonstration Scenario

To explore this feature, let’s walk through a practical scenario.

Step 1 — Create a table in the Primary database:

 –PRIMARY:

[oracle@ol7 ~]$ sqlplus usef/a

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Apr 4 05:22:40 2019

Version 19.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

SQL> conn usef/a

Connected.

SQL> create table mytbl(id number,name varchar2(19));

Table created.

Step 2 — Enable ADG Redirect DML in Data Guard:

–ADG:

SQL> alter system set ADG_REDIRECT_DML=TRUE;

System altered.

Step 3 — Insert data into the table from ADG:

–ADG:

SQL> select OPEN_MODE from  v$database;

OPEN_MODE

——————–

READ ONLY WITH APPLY

SQL> insert into mytbl values(1,’USEF’);

1 row created.

SQL> commit;

Commit complete.

Step 4 — Verify data on the Primary:

 –PRIMARY:

SQL> select * from usef.mytbl;

        ID NAME

———- ——————-

         1 USEF

Locking Behavior

When executing DML in Data Guard, the corresponding row will be locked on the Primary:

–PRIMARY:

SQL> select SID,TYPE,CTIME from v$lock where type in (‘TM’,’TX’);

no rows selected

–ADG:

SQL> update mytbl set name=’VAID’ where name=’USEF’;

1 row updated.

 –PRIMARY:

SQL> select SID,TYPE,LMODE from v$lock where type in (‘TM’,’TX’);

       SID TY      LMODE

———- — ———-

        11 TX          6

        11 TM          3

Here, the row is locked in row exclusive mode. If you try to update the same row on the Primary, the session will be blocked:

–PRIMARY:

SQL> select distinct sid from v$mystat;

       SID

———-

       387

SQL> update mytbl set name=’VAID’ where name=’USEF’;

waiting….

Checking the lock status again:

–PRIMARY:

SQL>  select SID,TYPE,LMODE,BLOCK,REQUEST from v$lock where type in (‘TM’,’TX’);

       SID TY      LMODE      BLOCK    REQUEST

———- — ———- ———- ———-

        11 TX          6          1          0

       387 TX          0          0          6

        11 TM          3          0          0

       387 TM          3          0          0

Failure Scenarios

If the Primary is unavailable while running DML in ADG:

–ADG:

SQL> insert into mytbl values(2,’ALI’);

ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed

If the Primary goes down during commit:

–ADG:

SQL> insert into mytbl values(2,’ALI’);

1 row created.

SQL> commit;

ORA-03150: end-of-file on communication channel for database link

ORA-02063: preceding line from ADGREDIRECT

Performance Comparison: DML in Primary vs. ADG

–PRIMARY:

SQL> create table prim_tbl as select * from sys.source$ where 1=2;

Table created.

SQL> create table adg_tbl as select * from sys.source$ where 1=2;

Table created.

–PRIMARY:

SQL> set timing on

SQL> insert into prim_tbl  select * from sys.source$;

295279 rows created.

Elapsed: 00:00:02.73

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

–ADG:

SQL> set timing on

SQL> insert into adg_tbl select * from sys.source$;

295279 rows created.

Elapsed: 00:00:02.53

SQL> commit;

Commit complete.

Elapsed: 00:00:01.06

As observed, the commit operation in ADG is slightly slower (about 1 second) compared to the Primary. However, the insert execution time is nearly identical. Naturally, the underlying system infrastructure has a significant impact on overall performance.