DML重定向说明：
在Active Data Guard Standby Databases执行DML操作
Performing DML Operations on Active Data Guard Standby Databases
您可以在ADG备库端运行DML操作。这使您能够在备用数据库上运行以读为主的应用程序，这些应用程序偶尔执行DML。
You can run DML operations on Active Data Guard standby databases. This enables you to run read-mostly applications, which occasionally execute DMLs, on the standby database.
standby数据库上的DML操作可以透明地重定向到primary数据库并在primary数据库上运行。这包括作为PL/SQL块一部分的DML语句。
DML operations on a standby can be transparently redirected to and run on the primary database. This includes DML statements that are part of PL/SQL blocks.
ADG会话将等待，直到将相应的更改发送到并应用到ADG standby。在DML操作期间保持读取一致性，运行DML的standby 数据库可以查看其未提交的更改。但是，所有其他standby 数据库实例只能在事务提交后才能查看这些更改。
The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby. Read consistency is maintained during the DML operation and the standby database on which the DML is run can view its uncommitted changes. However, all the other standby database instances can view these changes only after the transaction is committed.
Note:
避免在ADG端执行过多的DML，因为执行最终是在主库端执行，过多的DML操作会影响主库性能。
Avoid running too may DML operations on Active Data Guard standby databases. Because the operations are actually performed on the primary, too many DMLs may impact the performance of the primary.
ALTER SESSION ENABLE ADG_REDIRECT_DML;
ALTER SYSTEM ENABLE ADG_REDIRECT_DML;
Example 10-1 Performing DML Operations on a Physical Standby Database
SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
SQL> INSERT INTO employees VALUES (.......);
此时，更改后的数据仅对运行该命令的备用数据库可见。在主数据库上提交插入操作之后，将把更改发回并应用于所有备用数据库。
At this point, the changed data is visible only to the standby database on which the command was run. After the insert operation is committed on the primary database, the changes are shipped back and applied to all the standby databases.
实验过程如下：
一 主库cjcdb:创建测试数据
SQL> create user c##cjc identified by oracle;
SQL> grant connect,resource,dba to c##cjc;
SQL> create table c##cjc.t1 as select level as id from dual connect by level<=10;
备库：chendb
SQL> select * from c##cjc.t1;
ID
----------
1
2
......
10
10 rows selected.
二 备库chendb启用会话级别adg_redirect_dml
SQL> conn c##cjc/oracle
SQL> show parameter ADG_REDIRECT_DML
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
adg_redirect_dml boolean FALSE
SQL> alter session enable adg_redirect_dml;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> show user
USER is "C##CJC"
三 启用10046事件，跟踪当前会话
SQL> alter session set tracefile_identifier='10046C';
SQL> alter session set events '10046 trace name context forever ,level 12';
SQL> insert into t1 values(1);
四 数据提交之前，查看主库事务信息
1 主库：通过v$transaction查询出当前事务对应的会话地址
SQL> select addr from v$transaction;
2 主库：通过上一步的会话地址，在v$session中找到对应sql_id等
SQL> select prev_hash_value,prev_sql_id from v$session where taddr='000000007BE7CF20';
3 主库：通过上一步的sql_id，在v$sql中查询出正在执行的SQL
SQL> select * from v$sql where sql_id='66xyn363mxuzu';
---INSERT INTO "T1" "A1" ("ID") VALUES (1)
SQL> select sql_text,sql_id,hash_value,service from v$sql where sql_id='66xyn363mxuzu';
结论：备库端执行的DML语句，实际上重定位到主库上执行了，具体是怎么实现的呢？
五：备库：提交事务，生成10046跟踪文件
SQL> commit;
SQL> alter session set events '10046 trace name context off';
[oracle@cjcos02 trace]$ pwd
/u01/app/oracle/diag/rdbms/chendb/chendb/trace
六 查看10046跟踪文件
[oracle@cjcos02 trace]$ vim chendb_ora_3590_10046C.trc
等待事件：和dblink有关
SQL*Net message to dblink
SQL*Net message from dblink
格式化跟踪文件：
[oracle@cjcos02 trace]$ tkprof chendb_ora_3590_10046C.trc 10046.trc
递归SQL也和dblink有关
SQL ID: b3zgx1xckrwxu Plan Hash: 1278882766
select host,userid,password,flag,authusr,authpwd, passwordx, authpwdx
from link$ where owner#=:1 and name=:2
结论：在备库端执行DML时，备库端通过dblink远程连接到主库，在主库端执行DML语句，待主库执行成功并将数据同步回备库端后，备库端显示DML语句执行完成。
七：常见问题
备库执行DML语句时，报如下错误：
ORA-16397: statement redirection from Oracle Active Data Guard standby database
问题原因：
不支持SYS用户会话级别启用DML重定向。
解决方案：
使用非SYS用户启用和测试。
错误重现过程如下：
SQL> show user
USER is "SYS"
SQL> alter session set tracefile_identifier='10046';
SQL> alter session set events '10046 trace name context forever ,level 12';
SQL> insert into test1 values(1);
insert into test1 values(1)
*
ERROR at line 1:
ORA-16397: statement redirection from Oracle Active Data Guard standby database
to primary database failed
更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：
http://blog.itpub.net/29785807/
