Active Data Guard DML Redirection 19c (Doc ID 2465016.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 19.1.0.0.0 to 19.3.0.0.0 [Release 18]
Information in this document applies to any platform.
GOAL
How to use DML Redirection feature in Active Data Guard Standby?
如何在Active Data Guard Standby中使用DML重定向功能?
SOLUTION
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data
from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material.
Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
Table name :- tab1 and GTT_tab1
Active Data Guard DML Redirection
This new feature allows incidental DML to be executed and allow DDL to dynamically create Global Temporary Tables (GTTs) on an Active Data Guard standby database. When DML is executed the update is passed to the Primary database where it is executed and the resulting redo of the transaction will update the Standby database after which control will be returned to the application. The DML is executed preserving all ACID properties of the transaction. When DDL to create a GTT is executed on the standby, the command is captured and passed to the Primary where it is executed. Control is return to the application when the redo for the GTT creation has been applied at the standby database.
此新功能允许执行偶然的DML，并允许DDL在Active Data Guard备用数据库上动态创建全局临时表（GTTs）。执行DML时，update将传递到执行该数据库的主数据库，并且事务的结果redo将更新Standby数据库，然后将控制权返回给应用程序。执行DML时保留事务的所有ACID属性。当在备用数据库上执行用于创建GTT的DDL时，该命令将被捕获并传递到执行该数据库的主数据库。当在备用数据库上执行了GTT创建重做时，控制权将返回给应用程序。
DDL Operations: Global temporary tables can be created on, and dropped from, Active Data Guard standby databases. The DDL for these operations is transparently redirected to the primary database. The Active Data Guard session then waits until the corresponding changes are shipped and applied to the Active Data Guard standby.
DDL操作：可以在Active Data Guard备用数据库上创建或删除全局临时表。这些操作的DDL透明地重定向到主数据库。然后，Active Data Guard会话将等待，直到相应的更改已交付并应用于Active Data Guard备用数据库为止。
The following criteria should be met for the DML redirection to succeed:
为了使DML重定向成功，应满足以下条件：
- The managed standby recovery at the Active Data Guard standby database must be started with the real-time apply option and the Active Data Guard standby database must be in sync with the primary database.
- 必须使用实时应用(real-time apply)选项启动Active Data Guard备用数据库上的managed standby recovery(MGR)，并且Active Data Guard备用数据库必须与主数据库同步。
- Connection to the Active Data Guard Standby database from a client machine or using a tns string like ‘$ sqlplus sys@CDB19SBY as sysdba’
- 客户端计算机或使用tns字符串,例如’$ sqlplus sys@CDB19SBY as sysdba’连接到Active Data Guard Standby数据库。
TEST:
Primary: Create table tab1 and insert values
Standby: Check if table is created and populated
Standby: Run DML on the table and see the result.
Primary:
SQL> CREATE TABLE tab1(id NUMBER, description VARCHAR2(20));
Table created.
SQL> alter system archive log current;
System altered.
SQL> insert into tab1 values (1, 'a');
1 row created.
SQL> alter system archive log current;
System altered.
SQL> commit;
Commit complete.
Standby:
SQL> desc tab1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DESCRIPTION VARCHAR2(20)
SQL> select * from tab1;
ID DESCRIPTION
---------- --------------------
1 a
SQL> insert into tab1 values (2, 'b');
insert into tab1 values (2, 'b')
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
The above error would be encountered if the parameter adg_redirect_dmlis not enabled at the session level.
如果未在session级别启用参数adg_redirect_dmlis，则会遇到上述错误。
The default value of ADG_REDIRECT_PLSQL is FALSE.
SQL> alter session enable adg_redirect_dml;
Session altered.
SQL> insert into tab1 values (2, 'b');
1 row created.
SQL> commit;
Commit complete.
Primary:
SQL> select * from tab1;
ID DESCRIPTION
---------- --------------------
1 a
2 b
Creation of a Global Temporary Table from ADG
SQL> alter session enable adg_redirect_dml;
SQL> CREATE GLOBAL TEMPORARY TABLE GTT_tab1(id NUMBER, description VARCHAR(20))
2 ON COMMIT DELETE ROWS;
Table created.
This feature was also present in the 18c version but the underscore parameter “_enable_proxy_adg_redirect=True” had to be set in the parameters file for this feature to work.
此功能在18c版本中也存在，但必须在参数文件中设置下划线参数"_enable_proxy_adg_redirect=True"，该功能才能起作用。
An enhancement bug, Bug 27847368 : DML REDIRECTION NEEDS TO BE A PUBLISHED FEATURE FOR ACTIVE DATA GUARD was logged that developed the session level parameter ADG_REDIRECT_PLSQL that can be used in the 19c release.
