Oracle 19c Data Guard 备库DML自动重定向

1.在主库执行命令https://www.cndba.cn/hbhe0316/article/22183

SQL> create user c##hbhe identified by wwwwww;

User created.

SQL> grant connect,resource,dba to c##hbhe;

Grant succeeded.

SQL> create table c##hbhe.t1(id int,name varchar(10));

Table created.

SQL> insert into c##hbhe.t1 values(1,'a');

1 row created.

SQL> commit;

Commit complete.

2.在备库执行命令查看https://www.cndba.cn/hbhe0316/article/22183

SQL>     select * from c##hbhe.t1;

        ID NAME
---------- ------------------------------
         1 a

3.备库启用会话级别adg_redirect_dmlhttps://www.cndba.cn/hbhe0316/article/22183https://www.cndba.cn/hbhe0316/article/22183

[oracle@dg02 ~]$ sqlplus c##hbhe/wwwwww

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 15 22:01:32 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> show parameter ADG_REDIRECT_DML

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
adg_redirect_dml                     boolean
FALSE
SQL> alter session enable adg_redirect_dml;

Session altered.

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ ONLY WITH APPLY

SQL> show user;
USER is "C##HBHE"

4.备库启用10046事件，跟踪当前会话

SQL> alter session set tracefile_identifier='10046C';

Session altered.

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

Session altered.

SQL> insert into t1 values (2,'b');

1 row created.

5.数据提交之前，查看主库事务信息https://www.cndba.cn/hbhe0316/article/22183

SQL> select addr from v$transaction;

ADDR
----------------
0000000083CB4C28

SQL> select prev_hash_value,prev_sql_id from v$session where taddr='0000000083CB4C28';

PREV_HASH_VALUE PREV_SQL_ID
--------------- ---------------------------------------
     2631369082 4ywrmwkfdg0bu

SQL> select sql_text,sql_id,hash_value,service from v$sql where sql_id='4ywrmwkfdg0bu';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID                                  HASH_VALUE
--------------------------------------- ----------
SERVICE
--------------------------------------------------------------------------------
INSERT  INTO "T1" "A1" ("ID","NAME") VALUES (2,'b')
4ywrmwkfdg0bu                           2631369082
orcl_p

备库端执行的DML语句，实际上重定位到主库上执行了。https://www.cndba.cn/hbhe0316/article/22183

6.备库：提交事务，生成10046跟踪文件

https://www.cndba.cn/hbhe0316/article/22183

[oracle@dg02 trace]$ cd /u01/app/oracle/diag/rdbms/orcl_s/orcl/trace
[oracle@dg02 trace]$ ls -lrt *.trc
-rw-r----- 1 oracle oinstall   25318 Nov 15 22:09 orcl_ora_2605_10046C.trc

在备库端执行DML时，备库端通过dblink远程连接到主库，在主库端执行DML语句，待主库执行成功并将数据同步回备库端后，备库端显示DML语句执行完成。

https://www.cndba.cn/hbhe0316/article/22183

https://www.cndba.cn/hbhe0316/article/22183

https://www.cndba.cn/hbhe0316/article/22183

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle