DEV Community

Cover image for DML Operations over Standby in Active Dataguard [19c feature]
Project-42
Project-42

Posted on

DML Operations over Standby in Active Dataguard [19c feature]

One of the new Features for 19c database is the possibility to execute DML operations (INSERT, UPDATE, DELETE...) over a Standby in a Active Dataguard configuration.
The operation itself won't be actually executed in the standby, but redirected to the primary where will be executed and then apply in the standby by the Recovery Process.

Something we have to consider first is that the system should have Active Dataguard enabled and Protection Mode in Maximum Availability

DGMGRL> show configuration

Configuration - dg_broker_config

  Protection Mode: MaxAvailability <<<<<<<<<

  Members:
  db19 - Primary database
    st19 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL>


DGMGRL> show database st19

Database - st19

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 23.00 KByte/s

  Real Time Query:    ON  <<<<<<<<<

  Instance(s):
    st191 (apply instance)
    st192

Database Status:
SUCCESS

DGMGRL>
Enter fullscreen mode Exit fullscreen mode

Once we have that, we have to enable on both Primary and Standby databases the parameter "adg_redirect_dml"


PRIMARY - SQL> show parameter adg_redirect_dml

NAME              TYPE        VALUE
----------------- ----------- -------
adg_redirect_dml  boolean     FALSE


PRIMARY - SQL> alter system set adg_redirect_dml = true scope = both sid = '*';

System altered.

PRIMARY - SQL> show parameter adg_redirect_dml

NAME              TYPE        VALUE
----------------- ----------- -------
adg_redirect_dml  boolean     TRUE


STANDBY - SQL> show parameter adg_redirect_dml

NAME              TYPE        VALUE
----------------- ----------- -------
adg_redirect_dml  boolean     FALSE

STANDBY - SQL> alter system set adg_redirect_dml = true scope = both sid = '*';

System altered.


STANDBY - SQL> show parameter adg_redirect_dml

NAME              TYPE        VALUE
----------------- ----------- -------
adg_redirect_dml  boolean     TRUE
Enter fullscreen mode Exit fullscreen mode

Let's now create a table and insert some data in the primary database

PRIMARY - SQL> CREATE TABLE P42(column1 NUMBER) TABLESPACE users;

Table created.

PRIMARY - SQL> INSERT INTO P42(column1) VALUES (1);

1 row created.

PRIMARY - SQL> commit;

Commit complete.

PRIMARY - SQL> select * FROM P42;

   COLUMN1
----------
         1

PRIMARY - SQL>
Enter fullscreen mode Exit fullscreen mode

And let's now insert more data directly from the standby:

STANDBY - SQL> INSERT INTO P42(column1) VALUES (2);

1 row created.

STANDBY - SQL> commit;

Commit complete.

STANDBY - SQL> select * FROM P42;

   COLUMN1
----------
         2
         1



PRIMARY - SQL> select * FROM P42;

   COLUMN1
----------
         2
         1

PRIMARY - SQL>

Enter fullscreen mode Exit fullscreen mode

If you get "ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed" during the operation, try to connect to the system using User/Password instead of "sqlplus / as sysdba"

[oracle@rac2-node1 ~]$ sqlplus / as sysdba

STANDBY - SQL> INSERT INTO P42(column1) VALUES (2);
INSERT INTO P42(column1) VALUES (2)
            *
ERROR at line 1:
ORA-16397: statement redirection from Oracle Active Data Guard standby database
to primary database failed


STANDBY - SQL>


[oracle@rac2-node1 ~]$ sqlplus sys/Welcome1@st19 as sysdba

STANDBY - SQL> INSERT INTO P42(column1) VALUES (3);

1 row created.
Enter fullscreen mode Exit fullscreen mode

This is a new feature that can enable certain Reporting applications to work better on your systems, but please understand this option will cause some overhead in the system, so is not pretended to be used for high number of DML transactions

If you want to know more, please visit Fernando Simon's post DML over Standby for Active Data Guard in 19c
You will see more details about this new feature and things like how the system manage the locks and timeout during transactions.
He also listed some of the oficial Oracle Documents/pdfs where you can learn more about this new feature and Dataguard in 19c

Top comments (0)