Description:-
- Oracle Database Release 19c New Features
- Allows DML to be executed against the standby database
- Incidental Data Manipulation Language (DML) operations can be run on Active Data Guard standby databases.This allows more applications to benefit from using an Active Data Guard standby database when some writes are required.On execution the DML operation is passed to the primary database.
- DML redirection helps in load balancing between the primary and standby databases. When incidental DML is issued on an Active Data Guard standby database, the update is passed to the primary database where it is executed. The resulting redo of the transaction updates the standby database after which control is returned to the application.
- DML operations on a standby can be transparently redirected to and run on the primary database. This includes DML statements that are part of PL/SQL blocks. The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby.
Environment Details:-
Let’s Start Demo:-
1)Primary and Standby databases is running Oracle 19c – Click here
2)Note :-Data Guard in Maximum Availability .
Primary Side:-
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
———— —————- —————- ——————–
OPEN chennai PRIMARY MAXIMUM AVAILABILITY
Standby Side:-
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
———— —————- —————- ——————–
OPEN delhi PHYSICAL STANDBY MAXIMUM AVAILABILITY
SQL> SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY READ ONLY WITH APPLY
How it works?
1) DML on Standby Side
2) DML is Redirected to primary side
3) DML is applied to primary side
4) Data change is streamed to standby side
5) Data is visible to client
Note:-Avoid running too may DML operations on Active Data Guard standby databases. Because the operations are actually performed on the primary, too many DMLs may impact the performance of the primary.
Note:-DML operations in Oracle XA transactions are not supported on Active Data Guard standby databases.
Enable-adg_redirect_dml Parameter (on both side)
Primary side
[oracle@dev19c admin]$ export ORACLE_SID=chennai
[oracle@dev19c admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Sat Oct 19 01:19:33 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.4.1.0.0
SQL> show parameter adg_redirect_dml
NAME TYPE VALUE
———————————— ———– ——————————
adg_redirect_dml boolean FALSE
SQL> alter system set adg_redirect_dml=true scope=both;
System altered.
SQL> show parameter adg_redirect_dml
NAME TYPE VALUE
———————————— ———– ——————————
adg_redirect_dml boolean TRUE
Standby Side
[oracle@devdr19c DELHI]$ export ORACLE_SID=delhi
[oracle@devdr19c DELHI]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 19.0.0.0.0 – Production on Sat Oct 19 01:23:29 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.4.1.0.0
SQL> show parameter adg_redirect_dml
NAME TYPE VALUE
———————————— ———– ——————————
adg_redirect_dml boolean FALSE
SQL> alter system set adg_redirect_dml=true scope=both;
System altered.
SQL> show parameter adg_redirect_dml
NAME TYPE VALUE
———————————— ———– ——————————
adg_redirect_dml boolean TRUE
Testing the DML redirection (select)
Primary Side
[oracle@dev19c admin]$ sqlplus sys/oracle@chennai as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Sat Oct 19 01:49:24 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.4.1.0.0
SQL> create table oracledbwr (name varchar2(10), testdate timestamp);
Table created.
SQL> insert into oracledbwr values (‘hari’,sysdate);
1 row created.
SQL> select * from oracledbwr;
NAME TESTDATE
—————————————————————————
hari 19-OCT-19 01.51.17.000000 AM
SQL> commit;
Commit complete.
Standby side
[oracle@devdr19c DELHI]$ sqlplus sys/oracle@delhi as sysdba (we need connect using username/password)
SQL*Plus: Release 19.0.0.0.0 – Production on Sat Oct 19 01:53:29 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.4.1.0.0
SQL> select * from oracledbwr;
NAME TESTDATE
—————————————————————————
hari 19-OCT-19 01.51.17.000000 AM
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
———— —————- —————- ——————–
OPEN delhi PHYSICAL STANDBY MAXIMUM AVAILABILITY
SQL> SELECT database_role, open_mode FROM v$database;
DATABASE_ROLE OPEN_MODE
—————- ——————–
PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> delete from oracledbwr;
1 row deleted.
SQL> commit;
Commit complete.
Primary Side
select * from oracledbwr;
no rows selected
Note:-sqlplus sys/oracle@delhi as sysdba (we need connect using username/password)
If we connecting / as sysdba it is not work
ORA-00604: error occurred at recursive SQL level 1
ORA-16397: statement redirection from Oracle Active Data Guard standby database
to primary database failed
Connect to me:-
Telegram App:https://t.me/oracledbwr
LinkedIn: https://www.linkedin.com/in/hariprasathdba
LinkedIN Page:https://www.linkedin.com/company/orcldbwr/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/oracledbwr/
FB Page : https://www.facebook.com/dbahariprasath/
Twitter : https://twitter.com/hariprasathdba