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.<\/p>\n
<\/p>\n
Theorical info about DDL in the standby you can find here<\/a> and here<\/a>. The basic workflow you can see below:<\/p>\n 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<\/a>. If you want to see what I made, you can check this file that contains the output from the steps for my config \u201cLog-Create-PHY-STB-19c<\/a>\u201d.<\/p>\n Before continue, since here we have a lot of interaction between primary and standby, I put all the code that I executed over primary identified with PRIMARY, and executed as standby with the STANDBY in the codes below.<\/p>\n The first step to configure is guarantee that everything is ok with the config for DG. The main thing is check the \u201cReal Time Query\u201d and ON:<\/p>\n After that you enable the parameter ADG_REDIRECT_DML in both sides. Primary:<\/p>\n And in standby:<\/p>\n 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:<\/p>\n In the standby you can see the table and do some DML over it:<\/p>\n And in the primary, you can see that the data was deleted:<\/p>\n 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:<\/p>\n In the primary I deleted all the data but without commit to lock all the table:<\/p>\n After that I tried to update all the rows in the table. But, as expected, my session got stuck:<\/p>\n At same time I went to production and check for locks and events:<\/p>\n 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.<\/p>\n But, after some time in lock state, the session in the standby reported a new error. ORA-02049<\/em><\/strong>: timeout: distributed transaction waiting for lock<\/em> and ORA-02063<\/em><\/strong>: preceding line from ADGREDIRECT<\/em>. These exists just to avoid eternal wait in the standby.<\/p>\n 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.<\/p>\n 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$<\/strong> 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.<\/p>\n First, create and add the service in primary database:<\/p>\n After that you do the same in the standby:<\/p>\n Here something to add. I don\u2019t 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<\/a> (original post from Ivica Arsov) and here<\/a>.<\/p>\n After that you can test the same as before (create table in PDB and populate):<\/p>\n After that do some DML over standby:<\/p>\n 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:<\/p>\n 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:<\/p>\n The behaviour it is so normal that you will see the cursors open in the primary (the lock system is by row):<\/p>\n If you receive the error ORA-16397<\/em><\/strong>: statement redirection from Oracle Active Data Guard standby database to primary database failed<\/em> this is linked with the way that you connected in the database. If you use \u201c\/ as sysdba\u201d without username and password, when you try to do the DML you hit the error. To solve this, connect using username, password and database:<\/p>\n If you disable the ADG_REDIRECT_DML you will receive the error ORA-16000<\/em><\/strong>: database or pluggable database open for read-only access<\/em> when you try to execute the DML:<\/p>\n If you have something to add, please coment!<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[71,50,41,29,5],"tags":[72,73,67,65],"class_list":["post-472","post","type-post","status-publish","format-standard","hentry","category-19c","category-broker","category-data-guard","category-database","category-oracle","tag-19c","tag-active-data-gaurd","tag-data-guard","tag-oracle"],"yoast_head":"\n![\"\"](\"http:\/\/www.fernandosimon.com\/blog\/wp-content\/uploads\/2019\/04\/DML-STB-Basic-flow.png\")
<\/a><\/p>\nDGMGRL> show configuration\r\n\r\nConfiguration - dg19b2\r\n\r\n Protection Mode: MaxAvailability\r\n Members:\r\n dg19 - Primary database\r\n dg19s - Physical standby database\r\n\r\nFast-Start Failover: Disabled\r\n\r\nConfiguration Status:\r\nSUCCESS (status updated 31 seconds ago)\r\n\r\nDGMGRL> show database dg19s\r\n\r\nDatabase - dg19s\r\n\r\n Role: PHYSICAL STANDBY\r\n Intended State: APPLY-ON\r\n Transport Lag: 0 seconds (computed 0 seconds ago)\r\n Apply Lag: 0 seconds (computed 0 seconds ago)\r\n Average Apply Rate: 2.00 KByte\/s\r\n Real Time Query: ON\r\n Instance(s):\r\n dg19s\r\n\r\nDatabase Status:\r\nSUCCESS\r\n\r\nDGMGRL><\/pre>\n
[oracle@orcl19p ~]$ sqlplus sys\/oracle@dg19 as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 17:50:38 2019\r\nVersion 19.2.0.0.0\r\n\r\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n\r\nSQL> show parameter adg_redirect_dml\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nadg_redirect_dml boolean FALSE\r\nSQL> \r\nSQL> alter system set adg_redirect_dml = true scope = both sid = '*';\r\n\r\nSystem altered.\r\n\r\nSQL><\/pre>\n
[oracle@orcl19s ~]$ sqlplus sys\/oracle@dg19s as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 17:51:19 2019\r\nVersion 19.2.0.0.0\r\n\r\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n\r\nSQL> select open_mode from v$database;\r\n\r\nOPEN_MODE\r\n--------------------\r\nREAD ONLY WITH APPLY\r\n\r\nSQL> show parameter adg_redirect_dml\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\nadg_redirect_dml boolean FALSE\r\nSQL>\r\nSQL> alter system set adg_redirect_dml = true scope = both sid = '*';\r\n\r\nSystem altered.\r\n\r\nSQL><\/pre>\n
SQL> CREATE TABLE testDML(c1 NUMBER, c2 VARCHAR2(50), c3 DATE) TABLESPACE users;\r\n\r\nTable created.\r\n\r\nSQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> select c1, TO_CHAR(c3, 'DD\/MM\/RRRR HH24:MI') FROM testDML;\r\n\r\n C1 TO_CHAR(C3,'DD\/M\r\n---------- ----------------\r\n 1 14\/04\/2019 17:58\r\n\r\nSQL><\/pre>\n
SQL> select c1, TO_CHAR(c3, 'DD\/MM\/RRRR HH24:MI') FROM testDML;\r\n\r\n C1 TO_CHAR(C3,'DD\/M\r\n---------- ----------------\r\n 1 14\/04\/2019 17:58\r\n\r\nSQL> select database_role from v$database;\r\n\r\nDATABASE_ROLE\r\n----------------\r\nPHYSICAL STANDBY\r\n\r\nSQL> delete from testDML;\r\n\r\n1 row deleted.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL><\/pre>\n
SQL> select c1, TO_CHAR(c3, 'DD\/MM\/RRRR HH24:MI') FROM testDML;\r\n\r\nno rows selected\r\n\r\nSQL> SELECT TO_CHAR(sysdate, 'DD\/MM\/RRRR HH24:MI') FROM dual;\r\n\r\nTO_CHAR(SYSDATE,\r\n----------------\r\n14\/04\/2019 18:01\r\n\r\nSQL><\/pre>\n
Locks<\/h2>\n
SQL> select c1, TO_CHAR(c3, 'DD\/MM\/RRRR HH24:MI') as c3 FROM testDML;\r\n\r\n C1 C3\r\n---------- ----------------\r\n 1 14\/04\/2019 18:04\r\n\r\nSQL><\/pre>\n
SQL> delete from testDML;\r\n\r\n1 row deleted.\r\n\r\nSQL><\/pre>\n
SQL> update testDML set c1 = 2;<\/pre>\n
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;\r\n\r\nUSERNAME FINAL_BLOCKING_SESSION FINAL_BLOCKING_INSTANCE LOCKWAIT STATUS MACHINE SERVICE_NAME SQL_ID\r\n-------------------- ---------------------- ----------------------- ---------------------------------------------------------------- -------- -------------------- ---------------------------------------- -------------\r\nSYS OFS idle ACTIVE orcl19p.oralocal SYS$BACKGROUND\r\nSYSRAC SQL*Net message from client INACTIVE orcl19p.oralocal SYS$USERS\r\nPUBLIC SQL*Net message from client INACTIVE orcl19s.oralocal SYS$USERS\r\nSYS SQL*Net message to client ACTIVE orcl19p.oralocal SYS$USERS 4dzzccufjscvp\r\nSYS 86 1 enq: TX - row lock contention ACTIVE orcl19s.oralocal SYS$USERS 49b39kubnb2d1\r\n\r\nSQL>\r\nSQL> col sql_text format a50\r\nSQL> select sql_text from v$sql where sql_id = '49b39kubnb2d1';\r\n\r\nSQL_TEXT\r\n--------------------------------------------------\r\nUPDATE \"TESTDML\" \"A1\" SET \"C1\" = 2\r\n\r\nSQL><\/pre>\n
SQL> update testDML set c1 = 2;\r\nupdate testDML set c1 = 2\r\n*\r\nERROR at line 1:\r\nORA-02049: timeout: distributed transaction waiting for lock\r\nORA-02063: preceding line from ADGREDIRECT\r\n\r\nSQL><\/pre>\n
Over PDB<\/h2>\n
[oracle@orcl19p ~]$ srvctl add service -db dg19 -service dgpdb_dml -pdb dgpdb -role physical_standby\r\n[oracle@orcl19p ~]$ srvctl start service -db dg19 -service dgpdb_dml -pdb dgpdb\r\n[oracle@orcl19p ~]$\r\n[oracle@orcl19p ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 18:48:28 2019\r\nVersion 19.2.0.0.0\r\n\r\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n\r\nSQL> alter session set container = DGPDB;\r\n\r\nSession altered.\r\n\r\nSQL> set linesize 250\r\nSQL> col NAME format a30\r\nSQL> col NETWORK_NAME format a30\r\nSQL> col pdb format a30\r\nSQL> SELECT name, network_name, pdb FROM service$;\r\n\r\nNAME NETWORK_NAME PDB\r\n------------------------------ ------------------------------ ------------------------------\r\nDGPDB DGPDB DGPDB\r\ndgpdb_dml dgpdb_dml DGPDB\r\n\r\nSQL> exit\r\nDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n[oracle@orcl19p ~]$<\/pre>\n
[oracle@orcl19s ~]$ srvctl add service -db dg19s -service dgpdb_dml -pdb dgpdb -role physical_standby\r\n[oracle@orcl19s ~]$ srvctl start service -db dg19s -service dgpdb_dml -pdb dgpdb\r\n[oracle@orcl19s ~]$\r\n[oracle@orcl19s ~]$ srvctl stop database -d dg19s\r\n[oracle@orcl19s ~]$ srvctl start database -d dg19s\r\n[oracle@orcl19s ~]$\r\n[oracle@orcl19s ~]$ srvctl status service -d dg19s\r\nService dgpdb_dml is running\r\n[oracle@orcl19s ~]$<\/pre>\n
[oracle@orcl19p ~]$ sqlplus simon\/simon@orcl19p\/DGPDB\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 21:37:36 2019\r\nVersion 19.2.0.0.0\r\n\r\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\r\n\r\nLast Successful login time: Sun Apr 14 2019 21:36:05 +02:00\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n\r\nSQL> CREATE TABLE testDML(c1 NUMBER, c2 VARCHAR2(50), c3 DATE) TABLESPACE users;\r\n\r\nTable created.\r\n\r\nSQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL><\/pre>\n
[oracle@orcl19s ~]$ sqlplus simon\/simon@orcl19s\/DGPDB\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 21:38:17 2019\r\nVersion 19.2.0.0.0\r\n\r\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\r\n\r\nLast Successful login time: Sun Apr 14 2019 21:37:36 +02:00\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n\r\nSQL>\r\nSQL> delete from testDML;\r\n\r\n1 row deleted.\r\n\r\nSQL><\/pre>\n
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;\r\n\r\nUSERNAME FINAL_BLOCKING_SESSION FINAL_BLOCKING_INSTANCE LOCKWAIT STATUS MACHINE SERVICE_NAME SQL_ID CON_ID\r\n-------------------- ---------------------- ----------------------- ---------------------------------------------------------------- -------- -------------------- -------------------- ------------- ----------\r\nSYS OFS idle ACTIVE orcl19p.oralocal SYS$BACKGROUND 0\r\nSYSRAC SQL*Net message from client INACTIVE orcl19p.oralocal SYS$USERS 1\r\nSYSRAC SQL*Net message from client INACTIVE orcl19p.oralocal SYS$USERS 1\r\nPUBLIC SQL*Net message from client INACTIVE orcl19s.oralocal SYS$USERS 1\r\nSIMON 102 1 enq: TX - row lock contention ACTIVE orcl19p.oralocal dgpdb 4sqjaugwcxar0 3\r\nSYS SQL*Net message to client ACTIVE orcl19p.oralocal SYS$USERS cssctts2u81n4 1\r\nSIMON SQL*Net message from client INACTIVE orcl19s.oralocal SYS$USERS 3\r\n\r\n7 rows selected.\r\n\r\nSQL>\r\nSQL> select username, machine, service_name, program from gv$session where inst_id = 1 and sid = 102;\r\n\r\nUSERNAME MACHINE SERVICE_NAME PROGRAM\r\n-------------------- -------------------- -------------------- ------------------------------------------------\r\nSIMON orcl19s.oralocal SYS$USERS oracle@orcl19s.oralocal (TNS V1-V3)\r\n\r\nSQL><\/pre>\n
2019-04-14T22:12:57.977135+02:00\r\n14-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\r\n2019-04-14T22:14:02.121149+02:00\r\n14-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<\/pre>\n
SQL> create index ixtestDML on testDML(c1) tablespace users;\r\n\r\nIndex created.\r\n\r\nSQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);\r\n\r\n1 row created.\r\n\r\nSQL> INSERT INTO testDML(c1, c3) VALUES (2, sysdate);\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL>\r\n\r\n ###########\r\n In the sdtandby database\r\n ###########\r\n SQL> delete from testDML where c1 = 1;\r\n\r\n 1 row deleted.\r\n\r\n SQL>\r\n\r\nSQL> update testDML set c1 = 3 where c1 = 2;\r\n\r\n1 row updated.\r\n\r\nSQL>\r\nSQL> select sql_text, user_name from v$open_cursor where user_name = 'SIMON' and upper(sql_text) like '%TESTDML%';\r\n\r\nSQL_TEXT USER_NAME\r\n---------------------------------------------------------------------------------------------------- --------------------\r\nDELETE FROM \"TESTDML\" \"A1\" WHERE \"A1\".\"C1\"=1 SIMON\r\nupdate testDML set c1 = 3 where c1 = 2 SIMON\r\n\r\nSQL><\/pre>\n
ORA-16397<\/h2>\n
[oracle@orcl19s ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 19:34:45 2019\r\nVersion 19.2.0.0.0\r\n\r\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n\r\nSQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);\r\nINSERT INTO testDML(c1, c3) VALUES (1, sysdate)\r\n *\r\nERROR at line 1:\r\nORA-16397: statement redirection from Oracle Active Data Guard standby database\r\nto primary database failed\r\n\r\n\r\nSQL> exit\r\nDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n[oracle@orcl19s ~]$ sqlplus sys\/oracle@dg19s as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 14 19:34:55 2019\r\nVersion 19.2.0.0.0\r\n\r\nCopyright (c) 1982, 2018, Oracle. All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n\r\nSQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);\r\n\r\n1 row created.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> exit\r\nDisconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta\r\nVersion 19.2.0.0.0\r\n[oracle@orcl19s ~]$<\/pre>\n
ORA-16000<\/h2>\n
SQL> alter system set ADG_REDIRECT_DML=false scope = both sid = '*';\r\n\r\nSystem altered.\r\n\r\nSQL> INSERT INTO testDML(c1, c3) VALUES (1, sysdate);\r\nINSERT INTO testDML(c1, c3) VALUES (1, sysdate)\r\n *\r\nERROR at line 1:\r\nORA-16000: database or pluggable database open for read-only access\r\n\r\n\r\nSQL><\/pre>\n