Oracle 19c 新特性 --- ADG备库支持DML重定向

说明

在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

2. 在备库上设置为true

   SQL> ALTER SYSTEM SET ADG_REDIRECT_DML=true SCOPE=BOTH;
   
   System altered.
   

3. 执行DML

    SQL> conn system/oracle
    Connected.
   
    SQL> insert into system.test values(2);
    1 row created.
   
    SQL> COMMIT;
   
    Commit complete.
   

4. 验证数据

    SQL> select * from system.test;
   
            ID
    ----------
            1
            2
   

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