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.”
