Oracle 19c 新特性 ADG的自动DML重定向

最新推荐文章于 2022-08-12 08:20:19 发布

黄继生 jisen

最新推荐文章于 2022-08-12 08:20:19 发布

阅读量571

点赞数

分类专栏： oracle 文章标签： adg dml oracle

本文链接：https://blog.csdn.net/jisen_huang/article/details/105780160

版权

oracle 专栏收录该内容

65 篇文章 4 订阅

订阅专栏

特性特点：将偶然发送到ADG上的DML操作，自动转发到主库执行，然后通过主库日志传递到备库实时应用，在保证了ACID的前提下，大大增强了备库的实用性，这被称为 DML Redirection

在 18c 中，这个特性是否启用通过隐含参数 _enable_proxy_adg_redirect 控制；

在 19c 中，则由显式参数 ADG_REDIRECT_DML控制；

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> create user test identified by test;
create user test identified by test
            *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

SQL>  create table jisen.test(id int,sdate char(20));
 create table jisen.test(id int,sdate char(20))
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


SQL> conn jisen/jisen
Connected.
SQL> show parameter ADG_REDIRECT_DML

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
adg_redirect_dml                     boolean     TRUE
SQL>

alter session set events '10046 trace name context forever ,level 12';

insert into test values (1,  to_char(current_timestamp at time zone dbtimezone,'dd-mon-rr hh:mi:ss'));

alter session set events '10046 trace name context off';

select distinct(m.sid),p.pid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;

       SID        PID
---------- ----------
TRACEFILE
--------------------------------------------------------------------------------
       257         44
/u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_16636.trc

格式化输出

tkprof /u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_16636.trc /home/oracle/orcl_ora_16636.out

[oracle@oracle19c ~]$ cat orcl_ora_16636.out 

TKPROF: Release 19.0.0.0.0 - Development on Mon Apr 27 07:56:09 2020

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Trace file: /u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_16636.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

insert into test values (1,  to_char(current_timestamp at time zone dbtimezone,'dd-mon-rr hh:mi:ss'))

Error encountered: ORA-01157
********************************************************************************

SQL ID: 8r8b2pyy9qjm2 Plan Hash: 0

alter session set events '10046 trace name context forever, level 12'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       26.12         47.13
  SQL*Net break/reset to client                   3        0.00          0.00
  single-task message                             1        0.01          0.01
  SQL*Net message from dblink                     7        0.01          0.02
  SQL*Net message to dblink                       6        0.00          0.00
  SQL*Net vector data to dblink                   1        0.00          0.00
  asynch descriptor resize                        1        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  control file sequential read                    8        0.00          0.00
********************************************************************************

SQL ID: 06nvwn223659v Plan Hash: 0

alter session set events '10046 trace name context off'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106  



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       26.12         47.13
  SQL*Net break/reset to client                   3        0.00          0.00
  single-task message                             1        0.01          0.01
  SQL*Net message from dblink                     7        0.01          0.02
  SQL*Net message to dblink                       6        0.00          0.00
  SQL*Net vector data to dblink                   1        0.00          0.00
  asynch descriptor resize                        1        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  control file sequential read                    8        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_16636.trc
Trace file compatibility: 12.2.0.0
Sort options: default

       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
      88  lines in trace file.
      47  elapsed seconds in trace file.


[oracle@oracle19c ~]$

黄继生 jisen

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 19c 新特性 ADG的自动DML重定向

特性特点：将偶然发送到ADG上的DML操作，自动转发到主库执行，然后通过主库日志传递到备库实时应用，在保证了ACID的前提下，大大增强了备库的实用性，这被称为 DML Redirection在 18c 中，这个特性是否启用通过隐含参数 _enable_proxy_adg_redirect 控制；在 19c 中，则由显式参数 ADG_REDIRECT_DML控制；SQL>...
复制链接

扫一扫