19c的重要特性之一DML重定向，原理发送到ADG备库上的DML操作，自动转发到主库执行，然后通过主库日志传递到备库实时应用，在保证了ACID的前提下，增强了备库的功能性
在 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 test(id int,sdate char(20));
create table test(id int,sdate char(20))
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
SQL> conn luda/luda
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
——————————————————————————–
223 44
/u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_1768553.trc
格式化输出
tkprof /u01/app/oracle/diag/rdbms/std/orcl/trace/orcl_ora_1768553.trc /home/oracle/orcl_ora_1768553.out
[oracle@oracle19c ~]$ cat orcl_ora_1768553.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_1768553.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_1768553.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.
