说明

在19c版本中，ADG中的备库支持DML操作，原理是：是通过将备库上的DML重定向到主库上执行，然后备库应用DML变化数据，至此完成备库DML操作。

注：避免在备库上执行过多的DML操作，实际上是在主库上执行。

不支持XA事务中的DML操作。

配置

自动重定向DML操作支持会话级别和系统级别，会话级别会覆盖系统级别配置。

  • 在所有备库上启用DML重定向
    将初始化参数ADG_REDIRECT_DML设置为true
ALTER SYSTEM SET ADG_REDIRECT_DML=true SCOPE=BOTH;
  • 当前会话启用DML重定向
ALTER SESSION ENABLE ADG_REDIRECT_DML;

具体例子

会话级别的DML重定向

  • 确认当前数据库角色
SQL>  select open_mode,database_role from v$database;

OPEN_MODE                                DATABASE_ROLE
---------------------------------------- --------------------------------
READ ONLY WITH APPLY                     PHYSICAL STANDBY
  • 在备库上启用会话级别DML重定向
SQL> conn system/oracle   --非sys用户
Connected.
SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
Session altered.
  • 确认表数据
  SQL> select * from test;

  no rows selected
  • 在备库上执行DML操作
SQL> insert into test values(1);
1 row created.

SQL> select * from test;

      ID
  ----------
       1

注意：不支持sys用户会话启用：ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed

错误可能原因：

[oracle@19dg_p admin]$ oerr ORA 16397
16397, 00000, "statement redirection from Oracle Active Data Guard standby database to primary database failed"
// *Cause:  The statement redirection failed because of one of the following reasons:
//          1. The primary database connect string was not established.
//          2. The primary database could not be reached.
//          3. The undo-mode or incarnation were not the same.
//          4. The current user and logged-in user were not the same.
//          5. Redirecting CREATE TABLE AS SELECT (CTAS) of the global temporary
//             table was not supported.
//          6. Redirecting PL/SQL execution having bind variable was not supported.
// *Action: Run the statement after fixing the condition that caused the failure.

系统级别启用DML重定向

  1. 查看ADG_REDIRECT_DML参数值
SQL> SHOW PARAMETER ADG_REDIRECT_DML;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
adg_redirect_dml                     boolean
FALSE
  1. 在备库上设置为true
SQL> ALTER SYSTEM SET ADG_REDIRECT_DML=true SCOPE=BOTH;

System altered.
  1. 执行DML
 SQL> conn system/oracle
 Connected.

 SQL> insert into system.test values(2);
 1 row created.

 SQL> COMMIT;

 Commit complete.
  1. 验证数据
 SQL> select * from system.test;

         ID
 ----------
         1
         2

以上就是关于ADG中备库DML重定向特性测试，