版权声明：本文为Buddy Yuan原创文章，转载请注明出处。原文地址：ORACLE 19c新特性-Active Data Guard-DML Redirection
要说19c版本，这个Active Data Guard-DML Redirection是一个不错的发明，例如在某些情况下，我们的报表应用程序需要在备库中更新一些表和少量数据时非常适用。如果大规模的更新，肯定是不合适的，因为都需要转发到主库上去执行，这很大程序上会产生锁和并发等一系列的问题。
整个Active Data Guard-DML Redirection 的工作原理，我们可以参考官方的介绍。总共分成以下几个步骤。
1.备库的客户端发起DML操作。
2.DML操作被重定向到主库。
3.DML在主库被实施。
4.生成更改的redo信息流回到备用数据库。
5.完成DML重定向，客户端显示修改后的数据信息。
我们可以通过设置ADG_REDIRECT_DML为true，开启这个功能，或者是使用alter session命令开启会话级别的DML重定向。接下来我们来实际演示一下这个功能。首先我们要打开Real Time Query，这里已经是打开的。
DGMGRL> SHOW CONFIGURATION;
Configuration - db_broker_config
Protection Mode: MaxPerformance
Members:
db193h1 - Primary database
db193h1_stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 41 seconds ago)
DGMGRL> show database db193h1_stdby
Database - db193h1_stdby
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: 0 Byte/s
Real Time Query: ON
Instance(s):
DB193H1
Database Status:
SUCCESS
接下来修改主库和备库的参数ADG_REDIRECT_DML。
alter system set adg_redirect_dml = true scope = both sid = '*';
现在在主库创建一个表。
create table testdml (id number,name varchar2(50),start_date date);
然后在备库上执行。
SQL> insert into testdml values (1,'name',sysdate);
insert into testdml values (1,'name',sysdate)
*
ERROR at line 1:
ORA-16397: statement redirection from Oracle Active Data Guard standby database
to primary database failed
[oracle@standby ~]$ oerr ora 16397
16397, 00000, "statement redirection from Oracle Active Data Guard standby database to primary database failed"
// *Cause: The statement redirection failed because of one of the following reasons:
// 1. The primary database connect string was not established.
// 2. The primary database could not be reached.
// 3. The undo-mode or incarnation were not the same.
// 4. The current user and logged-in user were not the same.
// 5. Redirecting CREATE TABLE AS SELECT (CTAS) of the global temporary
// table was not supported.
// 6. Redirecting PL/SQL execution having bind variable was not supported.
// *Action: Run the statement after fixing the condition that caused the failure.
报错研究了一番，这主要是不支持sys用户使用这个功能。这是一个限制。我重新创建了一个用户就执行成功了。
SQL> create user a1 identified by a1; User created. SQL> grant dba to a1; Grant succeeded. SQL> connect a1/a1 Connected. SQL> create table testdml (id number,name varchar2(50),start_date date); Table created.
再次在备库上执行dml操作。这次成功了。
SQL> connect a1/a1 Connected. SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> insert into testdml values (1,'name',sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from testdml; ID NAME START_DAT ---------- -------------------------------------------------- --------- 1 name 08-JUN-20
接下来，我们在主库上执行一个事务，然后在备库上也执行一个事务。我们来看一下锁机制。
主库上执行不提交事务。
SQL> update testdml set id=2 where name='name'; 3 rows updated.
备库上执行。
update testdml set id=2 where name='name';
主库上查看锁的阻塞情况。这里可以看到会话49被阻塞，正在等待enq: TX – row lock contention，注意看machine，这里是standby主机。执行的sql语句文本我们也能看到。
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 SQL*Net message from client INACTIVE primary DB193H1 0 SYS OFS idle ACTIVE primary SYS$BACKGROUND 0 A1 SQL*Net message to client ACTIVE primary SYS$USERS cssctts2u81n4 0 PUBLIC SQL*Net message from client INACTIVE standby DB193H1 0 A1 49 1 enq: TX - row lock contention ACTIVE standby SYS$USERS du4ukzh4mjns1 0 SQL> select sql_text from v$sql where sql_id = 'du4ukzh4mjns1'; SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- UPDATE "TESTDML" "A1" SET "ID" = 2 WHERE "A1"."NAME"='name'
大约过了1分钟，在备库上就会出现下面的错误。分布式事务等待锁超时了。
SQL> update testdml set id=2 where name='name'; update testdml set id=2 where name='name' * ERROR at line 1: ORA-02049: timeout: distributed transaction waiting for lock ORA-02063: preceding line from ADGREDIRECT Elapsed: 00:01:00.05
如果我们现在把这个反过来操作一下。看看会不会出现这个错误ORA-02049:。也就是在备库上执行事务不提交，在主库上更新。
备库上执行 update testdml set id=2 where name='name'; 主库上执行 update testdml set id=2 where name='name';
这次是永远也没有发生分布式事务等待锁超时的情况。
当然官方文档还告诉我们可以在standby上执行Top-level PL/SQL操作，但是不能包含绑定变量。这个功能需要我们在会话级别执行ALTER SESSION ENABLE ADG_REDIRECT_PLSQL
Connected.
参考文档：Oracle (Active) Data Guard 19c Real-Time Data Protection and Availability
Post a Comment