DML over Standby for Active Data Guard in 19c

With the new 19c version the Data Guard received some attention and now we can do DML over the standby and it will be redirect to primary database. It is not hard to implement, but unfortunately there is no much information about that in the docs about that.
As training exercise I tested this new feature and want to share some information about that. First, the environment that I used (and the requirements too):
  • Primary and Standby databases running 19c.
  • Data Guard in Maximum Availability .
  • Active Data Guard enabled.
Remember that the idea of DML over the standby it is to use in some cases where your reporting application need to update some tables and few records (like audit logins) while processing the data in the standby. The volume of DML is (and will be) low. At this point there is no effort to allow, or create, a multiple active-active datacenters/sites for your database. If you start to execute a lot of DML in the standby side you can impact the primary database and you adding the fact that you can maximize the problems for locks and concurrency.

 

Theorical info about DDL in the standby you can find here and here.  The basic workflow you can see below:

 

 

 

To create the DG configuration you can follow the steps that are available in the internet in many sources, one good example it is here. If you want to see what I made, you can check this file that contains the output from the steps for my config “Log-Create-PHY-STB-19c.txt”.
Before continue, since here we have a lot of interaction between primary and standby, I put primary with BLUE background and the standby it is GREEN in the codes below.
The first step to configure is guarantee that everything is ok with the config for DG. The main thing is check the “Real Time Query” and ON:
 
DGMGRL> show configuration




Configuration - dg19b2




  Protection Mode: MaxAvailability

  Members:

  dg19  - Primary database

    dg19s - Physical standby database




Fast-Start Failover:  Disabled




Configuration Status:

SUCCESS   (status updated 31 seconds ago)




DGMGRL> show database dg19s




Database - dg19s




  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: 2.00 KByte/s

  Real Time Query:    ON

  Instance(s):

    dg19s




Database Status:

SUCCESS




DGMGRL>




After that you enable the parameter ADG_REDIRECT_DML  in both sides. Primary:

[oracle@orcl19p ~]$ sqlplus sys/oracle@dg19 as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 17:50:38 2019

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>

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




System altered.




SQL>




And in standby:

[oracle@orcl19s ~]$ sqlplus sys/oracle@dg19s as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 17:51:19 2019

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>

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




System altered.




SQL>

To test the DML redirection I created one table with some data in the primary database. Here I amd connected as SYS and without PDB, after I will do the same for PDB and with a normal user:

 

SQL> CREATE TABLE testDML(c1 NUMBER, c2 VARCHAR2(50), c3 DATE) TABLESPACE users;




Table created.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') FROM testDML;




        C1 TO_CHAR(C3,'DD/M

---------- ----------------

         1 14/04/2019 17:58




SQL>

In the standby you can see the table and do some DML over it:

SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') FROM testDML;




        C1 TO_CHAR(C3,'DD/M

---------- ----------------

         1 14/04/2019 17:58




SQL> select database_role from v$database;




DATABASE_ROLE

----------------

PHYSICAL STANDBY




SQL> delete from testDML;




1 row deleted.




SQL> commit;




Commit complete.




SQL>
And in the primary, you can see that the data was deleted:

 

SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') FROM testDML;




no rows selected




SQL> SELECT TO_CHAR(sysdate, 'DD/MM/RRRR HH24:MI') FROM dual;




TO_CHAR(SYSDATE,

----------------

14/04/2019 18:01




SQL>

 

Locks
But in the case of locks, how it works? Some new event? Some different? To simulate this I inserted the data in the primary again and in the standby I checked and the data appeared in the table:

 

SQL> select c1, TO_CHAR(c3, 'DD/MM/RRRR HH24:MI') as c3 FROM testDML;




        C1 C3

---------- ----------------

         1 14/04/2019 18:04




SQL>
 
In the primary I deleted all the data but without commit to lock all the table:

 

SQL> delete from testDML;




1 row deleted.




SQL>
After that I tried to update all the rows in the table. But, as expected, my session got stuck:

 

SQL> update testDML set c1 = 2;

 

At same time I went to production and check for locks and events:

 

SQL> SELECT username, final_blocking_session, final_blocking_instance, event lockwait, status, machine, service_name , sql_id FROM gv$session WHERE username IS NOT NULL;




USERNAME             FINAL_BLOCKING_SESSION FINAL_BLOCKING_INSTANCE LOCKWAIT                                                         STATUS   MACHINE              SERVICE_NAME                             SQL_ID

-------------------- ---------------------- ----------------------- ---------------------------------------------------------------- -------- -------------------- ---------------------------------------- -------------

SYS                                                                 OFS idle                                                         ACTIVE   orcl19p.oralocal     SYS$BACKGROUND

SYSRAC                                                              SQL*Net message from client                                      INACTIVE orcl19p.oralocal     SYS$USERS

PUBLIC                                                              SQL*Net message from client                                      INACTIVE orcl19s.oralocal     SYS$USERS

SYS                                                                 SQL*Net message to client                                        ACTIVE   orcl19p.oralocal     SYS$USERS                                4dzzccufjscvp

SYS                                      86                       1 enq: TX - row lock contention                                    ACTIVE   orcl19s.oralocal     SYS$USERS                                49b39kubnb2d1




SQL>

SQL> col sql_text format a50

SQL> select sql_text from v$sql where sql_id = '49b39kubnb2d1';




SQL_TEXT

--------------------------------------------------

UPDATE "TESTDML" "A1" SET "C1" = 2




SQL>
As you can see, there is no different event. The primary database reports TX coming from one connection of standby database. And you can even see the SQL. Normal behaviour.
But, after some time in lock state, the session in the standby reported a new error. ORA-02049: timeout: distributed transaction waiting for lock and ORA-02063: preceding line from ADGREDIRECT. These exists just to avoid eternal wait in the standby.

 

SQL> update testDML set c1 = 2;

update testDML set c1 = 2

*

ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock

ORA-02063: preceding line from ADGREDIRECT


SQL>
 
If you do in the opposite site, deleting the registry standby and not doing the commit. The session in the primary that it is waiting never receive the ORA-02049. And the same for the session in standby, it holds the lock until you commit/rollback.
Over PDB
If you want to do the same using PDB, you need to work a little with services to allow direct connections in the physical standby using the services. Basically you need to create the service and set the role (to start it) as physical_standby. The trick part here is that you need to, at least, call the start command in the primary because doing this you registry it in the service$ table. Since you are in the DG config and running the Active Data Guard (this is requirement because the redo is open with Real Time Query) you can open it in the standby too. I added this info here because with DML over standby this config start to be recurrent.
First, create and add the service in primary database:

 

[oracle@orcl19p ~]$ srvctl add service -db dg19 -service dgpdb_dml -pdb dgpdb -role physical_standby

[oracle@orcl19p ~]$ srvctl start service -db dg19 -service dgpdb_dml -pdb dgpdb

[oracle@orcl19p ~]$

[oracle@orcl19p ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 18:48:28 2019

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> alter session set container = DGPDB;




Session altered.




SQL> set linesize 250

SQL> col NAME format a30

SQL> col NETWORK_NAME format a30

SQL> col pdb format a30

SQL> SELECT name, network_name, pdb FROM service$;




NAME                           NETWORK_NAME                   PDB

------------------------------ ------------------------------ ------------------------------

DGPDB                          DGPDB                          DGPDB

dgpdb_dml                      dgpdb_dml                      DGPDB




SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0

[oracle@orcl19p ~]$
After that you do the same in the standby.

 

[oracle@orcl19s ~]$ srvctl add service -db dg19s -service dgpdb_dml -pdb dgpdb -role physical_standby

[oracle@orcl19s ~]$ srvctl start service -db dg19s -service dgpdb_dml -pdb dgpdb

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$ srvctl stop database -d dg19s

[oracle@orcl19s ~]$ srvctl start database -d dg19s

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$ srvctl status service -d dg19s

Service dgpdb_dml is running

[oracle@orcl19s ~]$

 

Here something to add. I don’t know why but for me just worked (the service appears as enable to connect) after I restart the standby. Since it is not focus for this post this point, you can find good info here (original post from Ivica Arsov) and here.
After that you can test the same as before (create table in PDB and populate):

 

[oracle@orcl19p ~]$ sqlplus simon/simon@orcl19p/DGPDB




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 21:37:36 2019

Version 19.2.0.0.0




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




Last Successful login time: Sun Apr 14 2019 21:36:05 +02:00




Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL> CREATE TABLE testDML(c1 NUMBER, c2 VARCHAR2(50), c3 DATE) TABLESPACE users;




Table created.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL>

 

After that do some DML over standby:

 

[oracle@orcl19s ~]$ sqlplus simon/simon@orcl19s/DGPDB




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 21:38:17 2019

Version 19.2.0.0.0




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




Last Successful login time: Sun Apr 14 2019 21:37:36 +02:00




Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0




SQL>

SQL> delete from testDML;




1 row deleted.




SQL>

 

And if you have some locks the behaviour it is the same than before. But now, with the information from user and con_id from connection:

 

SQL> select USERNAME, FINAL_BLOCKING_SESSION, FINAL_BLOCKING_INSTANCE, EVENT LOCKWAIT, STATUS, machine, service_name , sql_id, con_id from gv$session where username is not null;




USERNAME             FINAL_BLOCKING_SESSION FINAL_BLOCKING_INSTANCE LOCKWAIT                                                         STATUS   MACHINE              SERVICE_NAME         SQL_ID            CON_ID

-------------------- ---------------------- ----------------------- ---------------------------------------------------------------- -------- -------------------- -------------------- ------------- ----------

SYS                                                                 OFS idle                                                         ACTIVE   orcl19p.oralocal     SYS$BACKGROUND                              0

SYSRAC                                                              SQL*Net message from client                                      INACTIVE orcl19p.oralocal     SYS$USERS                                   1

SYSRAC                                                              SQL*Net message from client                                      INACTIVE orcl19p.oralocal     SYS$USERS                                   1

PUBLIC                                                              SQL*Net message from client                                      INACTIVE orcl19s.oralocal     SYS$USERS                                   1

SIMON                                   102                       1 enq: TX - row lock contention                                    ACTIVE   orcl19p.oralocal     dgpdb                4sqjaugwcxar0          3

SYS                                                                 SQL*Net message to client                                        ACTIVE   orcl19p.oralocal     SYS$USERS            cssctts2u81n4          1

SIMON                                                               SQL*Net message from client                                      INACTIVE orcl19s.oralocal     SYS$USERS                                   3




7 rows selected.




SQL>

SQL> select username, machine, service_name, program from gv$session where inst_id = 1 and sid = 102;




USERNAME             MACHINE              SERVICE_NAME         PROGRAM

-------------------- -------------------- -------------------- ------------------------------------------------

SIMON                orcl19s.oralocal     SYS$USERS            [email protected] (TNS V1-V3)




SQL>

 

As you can see above the connection appear in the primary as a normal connection, nothing complicate or special to handle. And in the listener log the connection (coming from standby when receiving the DML) is normal:

 

2019-04-14T22:12:57.977135+02:00

14-APR-2019 22:12:57 * (CONNECT_DATA=(SERVICE_NAME=8106653b19ca6636e053016410ac3c21)(CID=(PROGRAM=oracle)(HOST=orcl19s.oralocal)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.200.1)(PORT=36782)) * establish * 8106653b19ca6636e053016410ac3c21 * 0

2019-04-14T22:14:02.121149+02:00

14-APR-2019 22:14:02 * (CONNECT_DATA=(SERVER=DEDICATED)(SID=dg19)(CID=(PROGRAM=oracle)(HOST=orcl19s.oralocal)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.16.200.1)(PORT=36786)) * establish * dg19 * 0

 

The behaviour it is so normal that you will see the cursors open in the primary (the lock system is by row):

 

SQL> create index ixtestDML on testDML(c1) tablespace users;




Index created.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> INSERT INTO testDML(c1, c3) VALUES (2, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL>




    ###########

    In the sdtandby database

    ###########

    SQL> delete from testDML where c1 = 1;




    1 row deleted.




    SQL>




SQL> update testDML set c1 = 3 where c1 = 2;




1 row updated.




SQL>

SQL> select  sql_text, user_name from v$open_cursor where user_name = 'SIMON' and upper(sql_text) like '%TESTDML%';




SQL_TEXT                                                                                             USER_NAME

---------------------------------------------------------------------------------------------------- --------------------

DELETE FROM "TESTDML" "A1" WHERE "A1"."C1"=1                                                         SIMON

update testDML set c1 = 3 where c1 = 2                                                               SIMON




SQL>

 

ORA-16397
If you receive the error ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed this is linked with the way that you connected in the database. If you use “/ as sysdba” without username and password, when you try to do the DML you hit the error. To solve this, connect using username, password and database:

 

[oracle@orcl19s ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 19:34:45 2019

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> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);

INSERT INTO testDML(c1, c3) VALUES (1, sysdate)

            *

ERROR at line 1:

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

to primary database failed







SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0

[oracle@orcl19s ~]$ sqlplus sys/oracle@dg19s as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 19:34:55 2019

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> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);




1 row created.




SQL> commit;




Commit complete.




SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.2.0.0.0

[oracle@orcl19s ~]$

 

ORA-16000
If you disable the ADG_REDIRECT_DML you will receive the error ORA-16000: database or pluggable database open for read-only access when you try to execute the DML:

 

SQL> alter system set ADG_REDIRECT_DML=false scope = both sid = '*';




System altered.




SQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);

INSERT INTO testDML(c1, c3) VALUES (1, sysdate)

            *

ERROR at line 1:

ORA-16000: database or pluggable database open for read-only access







SQL>

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community.”