![Cover image for DML Operations over Standby in Active Dataguard [19c feature]](https://media2.dev.to/dynamic/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fbzuimc35tu425a0mhpw6.png)
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>
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
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>
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>
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.
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)