Oracle 19C ADG DML操作重定向测试

在19C最重要的一个特性就是ADG支持DML操作，这样就方便了在ADG上计算报表等操作后记录结果和日志。
1、Active Data Guard DML Redirection
Active Data Guard DML Redirection allows for incidental DML to be issued 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 after which control will be returned to the application. The DML is executed preserving all ACID properties of the transaction.

经过测试只需在ADG上设置ADG_REDIRECT_DML=true参数，即可在备库执行DML，然后ADG通过内部dblink将DML操作重定向到主库执行。

1、首先DUPLICATE+DG_BROKER方式搭建备库
[code]SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ora19c)
(ORACLE_HOME = /u01/app/oracle/product/192/dbhome_1)
)
(SID_DESC =
(SID_NAME = ora19cdg)
(ORACLE_HOME = /u01/app/oracle/product/192/dbhome_1)
)
)

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='cs19cdg'
SET control_files='/u02/oradata/CS19CDG/control01.ctl'
SET db_file_name_convert='/u02/oradata/CS19C','/u02/oradata/CS19CDG'
SET log_file_name_convert='/u02/oradata/CS19C','/u02/oradata/CS19CDG'
SET job_queue_processes='0'
NOFILENAMECHECK;

ALTER SYSTEM SET dg_broker_start=true;[/code]

2、在ADG上测试DML操作
[code]SQL> select count(*) from steven.a_bind_test where id1=100;

COUNT(*)
----------
1

SQL> alter system set ADG_REDIRECT_DML=true;

System altered.

SQL> delete from steven.a_bind_test where id1=100;
delete from steven.a_bind_test where id1=100
*
ERROR at line 1:
ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed


SQL> conn steven/oracle
Connected.
SQL> alter system set ADG_REDIRECT_DML=true;

System altered.

SQL> delete from a_bind_test where id1=100;

1 row deleted.

SQL> select count(*) from steven.a_bind_test where id1=100;

COUNT(*)
----------
0[/code]
3、10046日志如下：
[code]=====================
PARSING IN CURSOR #140331460660120 len=45 dep=0 uid=105 oct=7 lid=105 tim=42877641331 hv=1443228644 ad='6af39fc0' sqlid='5jzgzh1b0buz4'
delete from steven.a_bind_test where id1=101
END OF STMT
PARSE #140331460660120:c=4276,e=50094,p=0,cr=17,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=42877641331
WAIT #140331460660120: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42877641413
WAIT #140331460660120: nam='SQL*Net message from dblink' ela= 15746 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42877657178
EXEC #140331460660120:c=0,e=15908,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=42877657277
WAIT #140331460660120: nam='SQL*Net message to dblink' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42877657324
WAIT #140331460660120: nam='SQL*Net vector data to dblink' ela= 30 driver id=1413697536 #bytes=26 p3=2 obj#=-1 tim=42877657373
WAIT #140331460660120: nam='SQL*Net message from dblink' ela= 233 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=42877657621
WAIT #140331460660120: nam='standby query scn advance' ela= 158866 p1=2154352 p2=0 p3=12000 obj#=-1 tim=42877816529
WAIT #140331460660120: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=42877816664

*** 2019-2-14T20:46:36.686499+08:00
WAIT #140331460660120: nam='SQL*Net message from client' ela= 3820659 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=42881637349
CLOSE #140331460660120:c=24,e=24,dep=0,type=0,tim=42881637526
=====================[/code]
4、主库的锁：
[code] SID SPID TY LOCK_MIN SQL_ID USERNAME OWNER OBJECT_TYPE OBJECT_NAME
---------- ---------- -- ---------- ------------- --------------- --------------- ----------------------- ------------------------------
460 23438 TM 33 STEVEN STEVEN TABLE A_BIND_TEST
460 23438 TX 33 STEVEN STEVEN TABLE A_BIND_TEST[/code]
5、主库的会话信息
[code] SID SERIAL# SPID EVENT SQL_ID PREV_SQL_ID WS OBJ USERNAME
---------- ---------- ---------- ----------------------------------- ------------- ------------- ---------- ---------- ---------------
MACHINE MODULE B_SESS LOGON_TIM
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------
460 43227 23438 SQL*Net message from client 26zzjngxd5065 1622 -1 STEVEN
pr7 oracle@pr7 (TNS V1-V3) 14-FEB-19


SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
26zzjngxd5065 DELETE FROM "STEVEN"."A_BIND_TEST" "A1" WHERE "A1"."ID1"=101[/code]
在ADG执行commit后，锁释放。

更多内容请关注墨天轮DBHUB [url=https://cs.enmotech.com/topic/5]19C专题[/url]
[code]2019-02-16 09:24:28 | 19C EM Express
2019-02-16 08:59:47 | Oracle 19C ADG DML操作重定向测试
2019-02-16 03:09:59 | New search function for Oracle database features
2019-02-15 23:09:03 | Oracle 19C 91个新特性全记录
2019-02-15 10:34:20 | 官宣：Oracle数据库19c在Oracle Exadata上发布啦！
2019-02-15 00:05:12 | 抢鲜体验：Oracle 19C单实例数据库安装步骤详解
2019-02-14 21:26:34 | Oracle Database 19c requires OL7, RHEL7 or SLES12 or newer
2019-02-14 18:30:00 | Oracle 19C_Install_for_CentOS Linux release 7.5
2019-02-14 11:26:53 | Oracle 19C官方文档公布
2019-02-14 10:31:02 | 快讯：Oracle Database 19c 已发布提供公开下载
2019-02-14 06:26:19 | Oracle Database 19c Now Available on Oracle Exadata
2019-02-14 03:26:02 | Oracle 19c and my favorite list
2019-02-14 03:25:45 | Oracle Database 19.2 for Exadata is now available for download
2019-02-13 21:23:17 | Oracle 19c 新特性：ANY_VALUE 函数和 LISTAGG 的增强
2019-02-12 18:02:28 | 快讯：Oracle 19c 新特性及官方文档抢鲜下载
2019-02-11 22:08:01 | Oracle 19c 新特性：ADG的自动DML重定向增强读写分离
2019-01-21 13:06:07 | Oracle 19C率先在LiveSQL发布
2018-12-23 09:21:03 | 圣诞快乐：Oracle Database 19c 的10大新特性一览
2018-12-21 03:28:25 | Oracle Database 19c Beta Program
2018-12-07 10:26:08 | Oracle Database 19c: The best upcoming features..!![/code]